MySQL Performance – Limiting Factors for Max 10 Inserts Per Second

MySQL

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.

Best Answer

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. =)

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /raiddrive/mysql
log-error       = /raiddrive/mysql/error.log
bind-address    = 192.168.2.104
port            = 3306
#hardcore performance https://dba.stackexchange.com/questions/29913/innodb-insertion-faster
skip-log-bin
skip-innodb-doublewrite
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= ""
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_io_capacity=2000
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=8388608000
Related Question