-1

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!

enter image description here

enter image description here

Improve this question
2
  • SHOW CREATE TABLE is more descriptive than DESCRIBE. Which table are you worried about? What are the SELECTs that need the indexes? Are any of the indexes "composite"?
    – Rick James
    Aug 2, 2019 at 2:35
  • The only table that is gonna be super huge is transitions, the one with hundreds of billions of rows. I updated with show create table. I already dropped the indexes for inserting, as you suggest, and none of the indexes are composite.
    – Wenjun Chang
    Aug 2, 2019 at 18:45

2 Answers 2

Reset to default
0
  1. disable keys and FKs
  2. load the billions
  3. 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?

Improve this answer
3
  • if we disable the keys before insertion, isn't enabling the keys after having a huge table gonna take forever though?
    – Wenjun Chang
    Aug 1, 2019 at 19:43
  • It is likely to be faster to recreate an index all at once, than to incrementally add one new entry at a time. Step 3 rebuilds the index(es) all at once.
    – Rick James
    Aug 1, 2019 at 21:41
  • I will disable them. Thx! And I updated the tables, maybe you will have some advice on my table design?
    – Wenjun Chang
    Aug 1, 2019 at 22:38
-1

Do the following before you loading the table

SET UNIQUE_CHECKS=1;
SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

Once the import has been completed, enable all.

Use multiple-row insert [like in mysqldump --extended-insert]

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.