I am running an MySQL server. Build a small RAID with 3 harddisks which can Write 300MB/s and this damn MySQL just don't want to speed up the writing. 10 Inserts per second is the max I have seen till now.

What is the bottleneck?!

Here are some Screenshots:

MySQL Workbench Dashboard

htop during insert process

Answers to comments:

  • Example table: 10 Double Columns and one Datetime, 500.000 rows (to import)
  • The inserts I do are just initial data inputs like 500.000 times "insert into ... value ..."
  • I tried RazorSQL which is sending 500 transactions at once, but no effect.
  • It is a RAID0 with 3x2TB no-SSD-disks.

Tuning LOG:

  • SET GLOBAL sync_binlog = 500; results in 20.25 inserts per second.
  • SET GLOBAL innodb_buffer_pool_size = 2684354560; no effect
  • SET innodb_buffer_pool_instances = 4 (in mysqld.conf); setup in config file makes no effect. still instances = 1.
  • What do the tables look like? What sort of database is it? What are the inserts? How much data is there? Sep 7, 2018 at 12:35
  • 2
    This is not a programming question, this is a question either for a DBA or a system administrator.
    – Shadow
    Sep 7, 2018 at 12:36
  • Is there a bottleneck ? Is the statistics here from where you did a performance test - or is it just the normal operation of your database ? Do you actually try to insert an average of more than 12 rows every second averaged over the day ? In any case, make sure you do bulk inserts (insert many rows at a time) within a transaction, instead if wrapping a single insert into a single transaction if you need more performance.
    – nos
    Sep 7, 2018 at 12:39
  • Which RAID setup? And is the RAID hardware or software controlled. Sep 7, 2018 at 12:39
  • 3
    Please add the CREATE TABLE statement (including the indices) of your table and the complete INSERT INTO statement you try to run to your question.
    – Progman
    Sep 7, 2018 at 15:59

1 Answer 1


So this is my SOLUTION. Imported my data of 500.000 rows in 3 minutes. In fact a lot of this settings is not for company grade systems. In a private LAN with 1-2 workstations it should be okay.

Here is my config file for the record. May it help somebody and my future me. =)

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /raiddrive/mysql
log-error       = /raiddrive/mysql/error.log
bind-address    =
port            = 3306
#hardcore performance https://dba.stackexchange.com/questions/29913/innodb-insertion-faster
transaction-isolation = READ-COMMITTED
innodb_flush_log_at_trx_commit = 0
#if you lost root password
#init-file      = /raiddrive/mysql/setroot.sql
secure_file_priv= ""
  • This is probably the main fix: innodb_flush_log_at_trx_commit = 0
    – Rick James
    Oct 4, 2018 at 4:48

Your Answer

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

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