MySQL optimizing INSERT speed being slowed down because of indices

by

If you want fast inserts, first thing you need is proper hardware. That assumes sufficient amount of RAM, an SSD instead of mechanical drives and rather powerful CPU.

Since you use InnoDB, what you want is to optimize it since default config is designed for slow and old machines.

Here’s a great read about configuring InnoDB

After that, you need to know one thing – and that’s how databases do their stuff internally, how hard drives work and so on. I’ll simplify the mechanism in the following description:

A transaction is MySQL waiting for the hard drive to confirm that it wrote the data. That’s why transactions are slow on mechanical drives, they can do 200-400 input-output operations per second. Translated, that means you can get 200ish insert queries per second using InnoDB on a mechanical drive. Naturally, this is simplified explanation, just to outline what’s happening, it’s not the full mechanism behind transaction.

Since a query, especially the one corresponding to size of your table, is relatively small in terms of bytes – you’re effectively wasting precious IOPS on a single query.

If you wrap multiple queries (100 or 200 or more, there’s no exact number, you have to test) in a single transaction and then commit it – you’ll instantly achieve more writes per second.

Percona guys are achieving 15k inserts a second on a relatively cheap hardware. Even 5k inserts a second isn’t bad. The table such as yours is small, I’ve done tests on a similar table (3 columns more) and I managed to get to 1 billion records without noticeable issues, using 16gb ram machine with a 240GB SSD (1 drive, no RAID, used for testing purposes).

TL;DR: – follow the link above, configure your server, get an SSD, wrap multiple inserts in 1 transactions and profit. And don’t turn indexing off and then on, it’s not applicable always, because at some point you will spend processing and IO time to build them.

More Related Contents:

  1. How to do a batch insert in MySQL
  2. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  3. Mysql::Error: Specified key was too long; max key length is 1000 bytes
  4. How to copy a row and insert in same table with a autoincrement field in MySQL?
  5. MySQL Error 1264: out of range value for column
  6. How do I get the ID of multiple inserted rows in MySQL?
  7. Mysql Improve Search Performance with wildcards (%%)
  8. How to insert data to MySQL with auto-incremented column(field)?
  9. Mysql covering vs composite vs column index
  10. MySQL INSERT INTO … VALUES and SELECT
  11. MySQL error 1241: Operand should contain 1 column(s)
  12. What are practical differences between `REPLACE` and `INSERT … ON DUPLICATE KEY UPDATE` in MySQL?
  13. Does Order of Fields of Multi-Column Index in MySQL Matter
  14. Does a UNIQUE constraint automatically create an INDEX on the field(s)?
  15. MySQL – why not index every field?
  16. MySQL: Insert datetime into other datetime field
  17. “Unknown column in ‘field list'”, but column does exist
  18. Insert and set value with max()+1 problems
  19. When to use single quotes, double quotes, and backticks in MySQL
  20. How to join two tables mysql?
  21. Is it possible to have function-based index in MySQL?
  22. Multiple Updates in MySQL
  23. SQL split comma separated row [duplicate]
  24. What does the KEY keyword mean?
  25. MySQL – Subtracting value from previous row, group by
  26. How do I restore a dump file from mysqldump?
  27. How to count items in comma separated list MySQL
  28. MySQL order by “best match”
  29. Replace null with 0 in MySQL
  30. is there a length limit to group_concat or another reason why it would not work on a text field

Leave a Comment