<story><title>Loading half a billion rows into MySQL</title><url>http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql</url></story><parent_chain></parent_chain><comment><author>alexatkeplar</author><text>Assuming your event data is immutable (i.e. no UPDATEs, just INSERTs), you&apos;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.&lt;p&gt;This is the architecture we use for eventstream analysis at SnowPlow (&lt;a href=&quot;https://github.com/snowplow/snowplow&quot; rel=&quot;nofollow&quot;&gt;https://github.com/snowplow/snowplow&lt;/a&gt;).</text></comment>
<story><title>Loading half a billion rows into MySQL</title><url>http://derwiki.tumblr.com/post/24490758395/loading-half-a-billion-rows-into-mysql</url></story><parent_chain></parent_chain><comment><author>spudlyo</author><text>&lt;i&gt;I saw chunk load time increase from 1m40s to around an hour per million inserts.&lt;/i&gt;&lt;p&gt;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&apos;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.&lt;p&gt;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.</text></comment>