Loading half a billion rows into MySQL • Hacker News Board

Re: Loading half a billion rows into MySQL

#21

Post by jakejake » Tue, Jun 05, 2012, 10:00 PM UTC

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.

aha, thanks

Re: Loading half a billion rows into MySQL

#22

Post by advocate1 » Tue, Jun 05, 2012, 10:15 PM UTC

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.

Re: Loading half a billion rows into MySQL

#23

Post by alexatkeplar » Tue, Jun 05, 2012, 10:18 PM UTC

Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily.

This is the architecture we use for eventstream analysis at SnowPlow (https://github.com/snowplow/snowplow).

Re: Loading half a billion rows into MySQL

#24

Post by crazygringo » Tue, Jun 05, 2012, 10:22 PM UTC

cookiecaper wrote: post #19

If this data is primarily archival and there are multiple backups of the same dataset out there, why not use MyISAM? In my mind the only reasons to use InnoDB are integrity-related, things like real foreign keys and a more ACIDy commit method. If the dataset is read-only and copied in several places, surely this stuff does not matter too much and MyISAM is much more performant. Maybe I misread the use case?

I'm thinking if the server dies, MyISAM tables tend to corrupt more easily, and repairing a table that large will take a long time.

But that's the only thing I can think of -- and the speed/size of MyISAM tables have a lot going for them, I'd be tempted to go with MyISAM. Anybody else?

Re: Loading half a billion rows into MySQL

#25

Post by spudlyo » Tue, Jun 05, 2012, 10:22 PM UTC

I saw chunk load time increase from 1m40s to around an hour per million inserts.

Your insert performance falls off a cliff when a majority of the index pages for the table no longer fit into the innodb buffer pool. After that happens, there is gonna be a bunch of random i/o. You can solve this problem by using partitioning, that way only a single partition's worth of index pages need to fit into the buffer pool to keep inserts into that partition fast. Of course you have to size your partitions accordingly.

A few other tips. Disable fsync() at commit entirely. Set innodb_flush_log_at_trx_commit=0. If you crash curing the data load, start over. Set your transaction logs to be as large as possible, which is usually 4G.

Re: Loading half a billion rows into MySQL

#26

Post by spudlyo » Tue, Jun 05, 2012, 10:25 PM UTC

crazygringo wrote: post #24

cookiecaper wrote: post #19

If this data is primarily archival and there are multiple backups of the same dataset out there, why not use MyISAM? In my mind the only reasons to use InnoDB are integrity-related, things like real foreign keys and a more ACIDy commit method. If the dataset is read-only and copied in several places, surely this stuff does not matter too much and MyISAM is much more performant. Maybe I misread the use case?

I'm thinking if the server dies, MyISAM tables tend to corrupt more easily, and repairing a table that large will take a long time. But that's the only thing I can think of -- and the speed/size of MyISAM tables have a lot going for them, I'd be tempted to go with MyISAM. Anybody else?

Ya, recovery time is a big deal. It's like the difference between ext2 and ext3. With ext2 a crash is gonna require a lengthy and painful fsck, especially if your table/filesystem is large. A journaled filesystem is a lot like a database engine with transaction/redo logs. After you crash, you replay the last 'n' uncommitted transactions (they're all idempotent so you can do this multiple times) in the journal and you're good to go.

Also don't forget about MyISAM's lack of row level locks, and really nowadays there are fewer and fewer cases where it's faster than InnoDB.

Re: Loading half a billion rows into MySQL

#27

Post by stonemetal » Tue, Jun 05, 2012, 10:30 PM UTC

Gonsalu wrote: post #5

I fail to see why setting the transaction isolation level to read uncommitted would make any difference to the data load process.

Without thinking about it to hard my guess is there is some overhead in tracking what is readable and what is not readable at the higher read levels. Read uncommitted means it doesn't have to track anything.

Re: Loading half a billion rows into MySQL

#28

Post by timaelliott » Tue, Jun 05, 2012, 10:32 PM UTC

alexatkeplar wrote: post #23

Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily. This is the architecture we use for eventstream analysis at SnowPlow ( https://github.com/snowplow/snowplow ).

Are there any good resources which cover HDFS+Hive? I'd really love to see performance measurements but the entire premise of "Let's analyze ALL the data every single query, we'll just use a zillion workers!" has always came across to me as incredibly inefficient and computationally expensive.

I'd wager they query their data frequently and in a predictable manner, at which point using some sort data structure actually designed for fast searching (such as a simple btree in most rdbms) makes perfect sense here.

If the data is purely for archive purposes and is rarely queried or is queried in very random patterns that can't really make adequate usage of indices, then I'd agree with your suggestion.

Re: Loading half a billion rows into MySQL

#29

Post by kodablah » Tue, Jun 05, 2012, 10:39 PM UTC

"push as much complexity as possible to the database"

In general, this is usually bad practice and difficult to scale. I do agree w/ using a single table, but I disagree with the general premise of that statement.

Re: Loading half a billion rows into MySQL

#30

Post by trimbo » Tue, Jun 05, 2012, 10:43 PM UTC

alexatkeplar wrote: post #23

Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you'd probably have fewer headaches long-term if you just dumped the database to flatfiles, stored in HDFS and queried using Hive (which has MySQLish query syntax anyway). This architecture will take you to billions of rows quite happily. This is the architecture we use for eventstream analysis at SnowPlow ( https://github.com/snowplow/snowplow ).

On the other end of the spectrum, for "small data" (i.e. millions of rows as opposed to tens of billons)... check out crush-tools. I love it, use it all the time.

http://code.google.com/p/crush-tools/