2

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.
frank
  • 41
  • 1
  • 5
  • What do the tables look like? What sort of database is it? What are the inserts? How much data is there? – Lightness Races in Orbit Sep 07 '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 07 '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 07 '18 at 12:39
  • Which RAID setup? And is the RAID hardware or software controlled. – Raymond Nijland Sep 07 '18 at 12:39
  • Do you have a bad disk in your RAID0 set up? One bad apple spoils the bunch here. I wonder, too, if you are dealing with mysql blocks being split across your RAID0 disks. I haven't ever dealt with that, but if your RAID0 stripe array and block size is smaller than your innodb blocks, I would imagine that would cause quite a mess (totally guessing here since I've never dealt with such a problem). – JNevill Sep 07 '18 at 13:15
  • 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. –  Sep 07 '18 at 15:59
  • RazorSQL seems to be a client end software like Toad. Please post a small sample of your INSERT script. – Michael Kutz Sep 07 '18 at 18:26
  • How are you doing the inserts? LOAD DATA (best)? Multi-row INSERT (also good)? Or one row at a time, each being effectively a transaction. – Rick James Oct 04 '18 at 04:49

1 Answers1

2

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
frank
  • 41
  • 1
  • 5