0

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? – Lightness Races with Monica Sep 7 '18 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 '18 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 '18 at 12:39
  • Which RAID setup? And is the RAID hardware or software controlled. – Raymond Nijland Sep 7 '18 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 '18 at 15:59
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. =)

[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
  • This is probably the main fix: innodb_flush_log_at_trx_commit = 0 – Rick James Oct 4 '18 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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