Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Loading half a billion rows into MySQL

derwiki.tumblr.com

Post reply on HN
Print view View original

61–70 of 101 posts

Re: Loading half a billion rows into MySQL

#61

Post by eklitzke »

dkhenry wrote: post #59

I want to say this gentleman is doing it wrong. If his current set-up is what I perceive from the description he is not going to be gaining any speed or scalability. by moving to one giant table. In fact he might be losing some. It sounds like he set up 12 monthly tables and is inserting data into each one by month then he is querying across all 12 tables and joining the results. You can do that query in one fell swo…

Why do you think it's going to be faster to do a UNION query against 12 (or 13) tables than it will be to do a non-UNION query against a single table?

It's also unclear to me why you're suggesting the author switch to MyISAM. Presumably they're using InnoDB because it's actually ACID compliant, and even if they're not, according to MySQL's own benchmarks InnoDB is as fast or faster than MyISAM for heavy read workloads, e.g. as described at http://www.oracle.com/partners/en/knowledge-zone/mysql-5-5-i...

Re: Loading half a billion rows into MySQL

#62

Post by advocate1 »

dfragnito wrote: post #58
advocate1 wrote: post #22

It's more work for the computer to process data stored as "rows" than data stored as columns. Think of how a disk is organized. If you abandon MySQL and store data as columns you can load a trillion rows. But forget I mentioned this.

We have been doing some tests with http://www.monetdb.org/Home . We saw inserts per second in the range of 50k/sec on a laptop.

mmap. +1. 10MB source tarball. -1. Looks like it's worth a test. Thanks for the reference.

Re: Loading half a billion rows into MySQL

#63

Post by Andys »

fleitz wrote: post #56
jtchang wrote: post #47

Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.

I'm more impressed with how cheap it is. An SSD is on par with a 15K disk, but delivers 10X the perf, throw 10 SSDs in an array and you've got what used to cost $100,000 for about $5K

But SSDs that cost the same as a 15K disk do not have anywhere near the life time of the spinning disk due to write endurance limits.

So load half a billion rows but don't update it too often!

Re: Loading half a billion rows into MySQL

#64

Post by evanelias »

All great advice! As a fellow habitual migrator of billions of rows in MySQL, a few things I'd add:

Do several chunked LOAD DATA INFILE queries in parallel. It's counter-intuitive but you'll get a performance boost. I usually chunk large data sets into 100+ pieces and do 40 concurrent import queries. Mileage may vary, depends a lot on your hardware and data set's sweet spot.

Don't use mysqldump for the export. SELECT...INTO OUTFILE is faster, especially since you can apply the exact same parallelized chunking technique. Do this on a standby slave (ie, one not receiving reads) with replication stopped, so that you get a consistent dump at a point in time.

The TSV dump doesn't need to be in your database directory. It can be anywhere mysqld can access. It can be beneficial to put it on a separate volume entirely, so that you're reading heavily from one volume while writing heavily to another.

Be mindful of disk scheduler choices in Linux if you're exporting from a faster disk to a slower disk. There's some definite edge cases that can lead to starvation of core processes. Also, be mindful of disk scheduler choices in Linux in general. Avoid use of CFQ on your database data volume.

Watch disk utilization with iostat or another tool of your choice. If you're coming close to saturating one or more volumes, that probably means you're on the right track :)

As other commenters have mentioned, the row count in information_schema (or equivalently SHOW TABLE STATUS) is an estimate, due to how MVCC works. If you need a more accurate count (but not quite perfect count for a specific moment-in-time for a growing data set), do that in parallel chunked SELECT COUNT(*) queries.

Happy migrating!

Re: Loading half a billion rows into MySQL

#65

Post by lgas »

dredmorbius wrote: post #50
jerf wrote: post #41

Earlier quoted context omitted.

While it is possible to beat that command with backslashes until it works, I often just give up and make it a shell script, on the grounds that usually by the time I have something that complicated I want to "watch" I ought to be checking it into source control, or at least have it on disk for the next time I want it. For database stuff like that I've had nearly a 100% hit rate of either reusing such things, or at le…

Setting a really large $HISTSIZE can accomplish some of this. Though I'll admit to occasional traumatic moments when my workstation crashes and bash history is polluted with multiple shells filling it and the stuff I've been ctrl-R invoking (recursive reverse search) for months evaporates.

You should set up a simple cron job to backup your history file (to somewhere else in your home directory) on some schedule (hourly?) and then when this happens just have an short alias that restores the last backup.

Re: Loading half a billion rows into MySQL

#66

Post by jemfinch »

jtchang wrote: post #47

Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.

As a long time skeptic of the NoSQL craze that seems to have (happily!) peaked, I have to say that no, I'm not very surprised by this.

Databases (real databases) are using indexing technology that's been proven over not just years but decades of use and refinement. log2(500000000) is still only 28. Double that and it's only 29. And these databases' indexes are typically btrees with much higher branching factors than 2.

One of my favorite articles on this is Dennis Forbes' "The Impact of SSDs on Database Performance and the Performance Paradox of Data Explodification" (http://www.yafla.com/dforbes/The_Impact_of_SSDs_on_Database_...).

Re: Loading half a billion rows into MySQL

#67

Post by fizx »

kristjan wrote: post #15
jakejake wrote: post #12

Earlier quoted context omitted.

I ran this select from information_schema to get the count of a table with aprox 1.2 million rows. each time I run the query, though, the number returned is different. it seems to vary by about 200k either way. does anybody know why that would be happening?

It's just an estimate for the query engine, not a true row count. But good enough for a progress bar. From http://dev.mysql.com/doc/refman/5.0/en/tables-table.html : > For InnoDB tables, the row count is only a rough estimate used in SQL optimization.

If you run it a dozen times, it converges on the truth.

Re: Loading half a billion rows into MySQL

#68

Post by spudlyo »

jpitz wrote: post #54
jtchang wrote: post #47

Does it blow anyone else's mind that database servers are so powerful nowadays that you can just load half a billion rows into a SINGLE TABLE and pretty much expect things to work? Obviously there are caveats but all in all a nice chunk of RAM backed by a large SSD goes a long way.

My telemetry table is >2B rows and growing. 5B is starting to worry me; .5B, not so much. This is even backed by ( not much ) spinning rust.

Hopefully you don't have an auto-increment int (2^31) as a PK, or you'll be worrying sooner than you think.

Re: Loading half a billion rows into MySQL

#69

Post by bsg75 »

taligent wrote: post #33
james4k wrote: post #16

Earlier quoted context omitted.

One of the shockers I came across with MongoDB is that each instance of a key takes up memory. There is no form of a symbol table for the keys, so this means a huge amount of data overhead if each 'row' of data uses keys at all, which they likely do. No one just uses arrays.

Not really sure what you mean about the rows given that it is JSON but anyway. I think what you are referring to is the tokenization of field names: https://jira.mongodb.org/browse/SERVER-863

As in an INFORMATION_SCHEMA collection ?

Re: Loading half a billion rows into MySQL

#70

Post by dredmorbius »

lgas wrote: post #65
dredmorbius wrote: post #50

Earlier quoted context omitted.

Setting a really large $HISTSIZE can accomplish some of this. Though I'll admit to occasional traumatic moments when my workstation crashes and bash history is polluted with multiple shells filling it and the stuff I've been ctrl-R invoking (recursive reverse search) for months evaporates.

You should set up a simple cron job to backup your history file (to somewhere else in your home directory) on some schedule (hourly?) and then when this happens just have an short alias that restores the last backup.

No.

The problem is curation.

It's really handy to have stuff in history. But there's a ton of crap I don't need.

What I need to do is get better about scripting the stuff that I'm invoking frequently. Though zsh probably has an extension that does this for you, and scratches your back.

Mind: virtually all my shell scripts start off as one-liners that I've invoked and re-edited (M-x M-e FTFW) until I've realized I have something worth saving.

Post reply on HN