Hi Sergei, Pierre, On Wed, Feb 12, 2014 at 03:45:06PM +0100, Sergei Golubchik wrote:
Hi, Pierre!
Okay, there were quite a few replies already here. I'll chime in, but only to confirm that there's no easy solution, unfortunately :(
On Feb 10, Pierre wrote:
Hello,
Mariadb is getting slower and slower when I'm inserting a massive amout of data. I'm trying to insert 166 507 066 rows (12go) using load data infile '' into an empty table. I splitted my file in 13 parts of the same size because it was too long to insert in one shot. When I inserted more than 100M rows, it starts to be extremely slow.
Here are the time for each load data infile :
LOAD DATA INFILE '/tmp/hash/xaa' : 2min 49sec LOAD DATA INFILE '/tmp/hash/xab' : 2min 49sec LOAD DATA INFILE '/tmp/hash/xac' : 3min 48sec LOAD DATA INFILE '/tmp/hash/xad' : 3min 48sec LOAD DATA INFILE '/tmp/hash/xae' : 3min 49sec LOAD DATA INFILE '/tmp/hash/xaf' : 5min 59sec LOAD DATA INFILE '/tmp/hash/xag' : 10min 50sec LOAD DATA INFILE '/tmp/hash/xah' : 20min 7sec LOAD DATA INFILE '/tmp/hash/xai' : 47min 7sec LOAD DATA INFILE '/tmp/hash/xaj' : 4 hours 1 min 9.34 sec ( ouch !) LOAD DATA INFILE '/tmp/hash/xak' : still running...
Can someone explain me what is happening internaly and why it is so slow ?
Yes. That's because indexes are implemented as B-trees, and B-tree has a cost of insertion of O(log N). As you insert more data, insertions become slower. Furthermore, B-tree organizes data in pages. MariaDB tries to keep all pages in memory, but as soon as index becomes larger than keycache size, every key insertion means at least one disk read and one write (assuming your key values are distributed normally and randomly). Pierre noted that key_buffer_size is 6Gb. It should fit about 95M rows (or ~7.5 parts). According to my calculations resulting index size will be about 11Gb.
Do you think if I just create an index (not a unique index) it will be faster ?
Not really. Inserting values into a B-tree one-by-one will always be slow when your B-tree becomes big enough.
But you don't need to insert values into a B-tree one by one. MyISAM has a much faster (like, two orders of magnitude) way of building b-trees by creating a tree all at once.
This is used when you insert data into the empty table. Or when you add an index to the existing table. Or when you enable indexes. That is:
1. LOAD DATA INFILE 'everything, not in chunks' (a convenient way would be to load from a pipe, and cat all your chunks into it). 2. ALTER TABLE ... ADD KEY 3. ALTER TABLE DISABLE KEYS. insert the data. ALTER TABLE ENABLE KEYS
But unfortunately, all this only works for non-unique keys, when MariaDB doesn't need to expect a unique constraint error and won't need to delete rows when creating indexes. For unique indexes values will be inserted into a b-tree one by one. Indeed, it goes through key cache. Just curious why is that needed for
Said the above I believe just increasing key_buffer_size may greatly improve load performance. Note that performance is starting to go down heavily exactly after 8-th part. p.2 - it should never have to delete rows. AFAIR it should just drop temporary table in case of failure. I guess it was fixed this way in 10.0. And p.1 can just truncate data file...
Now, there's another trick you can use. Create a table. Then disable all indexes - not with ALTER TABLE ENABLE KEYS (which only disables non-unique indexes) - but from a command-line with myisamchk:
$ myisamchk --keys-used=0 /path/to/table.MYI
then insert your data. And REPAIR TABLE or REPAIR TABLE ... QUICK. This will rebuild all indexes, the fast way. The second variant (with QUICK) will not copy your (presumably, huge) data - it will only rebuild indexes. But it cannot remove duplicates either.
And also it's worth noting that MariaDB-10.0 rebuilds both UNIQUE and non-UNIQUE indexes the fast way when you do ALTER TABLE ... ADD KEY.
Thanks, Sergey