Mysql – Slowing down in MySQL insertion with 3 indexes and 1 foreign key

bulk-insertMySQL

I was working on a MySQL database using Innodb with billions of lines of data.

My table got 16 fields, 3 indexes, 1 foreign key, and 1 primary key. My buffer pool size is 47 GiB.

When I tested inserting 10 million lines with indexes disabled, it took 100s.

When indexes are enabled, as I was continuously inserting billions of lines in chunks of 10 million lines, it initially took 150s…then went up 400s…then to 1000s…and now it went up to 2000s per 10 million lines (using load data statement).

I want to know how to speed up my insertion with these indexes that are necessary for later use.

MySQL crashes a couple times during the insertion and I have to restart it…I wonder if the slow down is correlated to the crashes. Thank you!

Best Answer

disable keys and FKs
load the billions
enable

Meanwhile, provide SHOW CREATE TABLE; there are likely to be some critiques that may help.

In particular, if this is a Data Warehouse application, do you have any summary tables?

Related Solutions

MySQL performance issue – intermittently slow queries

Comments about that Tuner output:

Data in InnoDB tables: 575M (Tables: 194)
Total fragmented tables: 194
Run OPTIMIZE TABLE to defragment tables for better performance

Notice how all InnoDB tables are fragmented? That is the nature of how it is implemented. Do not bother running OPTIMIZE; it will almost never provide substantive benefit.

Joins performed without indexes: 7320

Irrelevant if the tables are tiny. The important ones will show up in the slowlog even without log_queries_not_using_indexes. (Leave that setting off; it only clutters the slowlog.)

Temporary tables created on disk: 38% (31K on disk / 84K total)
Slow queries: 1% (476/982K)

Let's see the worst ones. Please provide EXPLAIN and SHOW CREATE TABLE. I recommend long_query_time of 2 or lower.

Don't increase any of the last 5 items from Tuner.

The rest of Tuner's comments are reasonable.

Next step

Get the output from pt-query-digest and let's see the first couple, plus EXPLAIN and SHOW CREATE TABLE.

MySQL CREATE INDEX slowing down

You must be doing something odd with your DDL or you have a very, very old version of MySQL.

MyISAM Index creation can be very lethargic. I wrote about this almost ten years ago.

Oct 10, 2006 : Why does mysql drop index very very slow in a large table? (from the MySQL General Discussion Forum)
May 12, 2011 : Does MySQL still handle indexes in this way? (where I mention this again)

Without any additional details from you, I can only guess. Here it goes:

I suspect your must be just doing the following:

ALTER TABLE mytable ADD INDEX (column01);
ALTER TABLE mytable ADD INDEX (column02);
ALTER TABLE mytable ADD INDEX (column03);
...
ALTER TABLE mytable ADD INDEX (column10);
ALTER TABLE mytable ADD INDEX (column11);

I would like to suggest the following

SUGGESTION #1 : Build all 11 indexes at the same time

You should create all 11 indexes in a single DDL statement

ALTER TABLE myisamtable
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;

SUGGESTION #2 : Delay indexes building

You can disable the building of non-unique indexes so that sorting and be done.

Doing so can make a smaller BTREE for each index

ALTER TABLE mytable DISABLE KEYS;
... 
ALTER TABLE mytable ENABLE KEYS;

SUGGESTION #3 : Use a temp table

If you wish to test this manually, you can make a temp table, add all the indexes to the empty temp table, disable indexes, insert the data, enable indexes, and switch table names.

Here is a sample of what I just described to you

CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new
    ADD INDEX (column01),
    ADD INDEX (column02),
    ADD INDEX (column03),
    ...
    ADD INDEX (column10),
    ADD INDEX (column11)
;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;