I have a few datasets I'm pulling into mysql, the small one has about 1.5 billion records in it (the large one has about 8 billion).
The server is some 64 bit virtual machine in our corporate data center. Since it was specked out to run databases and thats all the VM is used for i presume that it has a reasonable ammount of memory and is at least a dual core - more likely a quad core instance.
My table basically has 8 columns that are integer columns.
Each of the columns has a b-tree index.
The average size of the records is 37 bytes.
my insert scheme is to use SQL prepaired statements with placeholders. Using this scheme i insert 5,000 records at a time.
Experimentation has shown that a somewhat smaller chunk of records in my case is a little bit faster but in the big scheme of things i really dont care that much :)
As a practical matter I am inserting the records in batches of 876,000 records at a time, taking 5,000 of them per sql statment that gets sent to the server.
Last night when I started, I took 41,503 mSec - 45 seconds to commit 876K records to the server. 12 hours and half a billion records later its taking about 650,000 Msec - nearly 11 minutes to commit 876K records to the server.
since its just been chugging along since last night, my conclusion is that due to the nature of the data- many fields are in near sorted order, I have hit along a pathological case for a b-tree - turning it into a poorly implemented linear search!
Moral to this story?
when injesting LARGE ammounts of data, its probably a good idea - in addition to tuning your server - to turn or drop as many indices as you can and then reindex it later.
Having said that, one needs to have a good handle on just how much overhead the indexing has at insert time compared to re-indexing the whole database. In my case where there will be essentially NO updates - just pure reads/queries, doing it on the tail end, makes good sense.
to give you the (d)illusion of useful data:
one java program running at once (single threadded access to mysql)
876000 records are read from the data source and written 5,000 records at a time. time is tracked for each 'batch' of 87600.
Y axies is how long it took to insert
X axies is the 'batch' number
I'm going to drop the indices and see how that works
Stay tuned for another graph