Mysql – Very poor insert performance MySQL / Percona Server

innodbMySQLpercona-serverperformancextradb

I'm running a Percona-Server instance off an Ubuntu server install. I'm using an application that needs to access this database, and its having VERY poor performance. Once the database has been established, the application goes in (upon install) and creates the schema. It defaults everything to MyISAM, however I have converted the table engines back to InnoDB. The issue that I'm having is VERY poor insert performance. This application is very write-heavy, and it seems as though its writing each row 1 at a time to disk, not using any sort of buffers, however I'm unsure how to check or verify this. Even a select(*) from one of the tables will take 2.4 seconds, and there is only 163,000 rows. I'm kind of at a loss as to what else I can do.

Server has 8GB of ram, and CPU is almost entirely idle while this is occurring.

my.cnf:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /mnt/Storage/mysql/

# BINARY LOGGING #
log_bin                        = /mnt/Storage/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 6144M
innodb_buffer_pool_instances   = 1
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_io_capacity             = 10000

# LOGGING #
log_error                      = /mnt/Storage/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /mnt/Storage/mysql/mysql-slow.log

desc of one table that is having poor insert performance:

mysql> desc parts;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| ID         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| binaryID   | int(11) unsigned    | NO   | MUL | 0       |                |
| messageID  | varchar(255)        | NO   |     |         |                |
| number     | bigint(20) unsigned | NO   | MUL | 0       |                |
| partnumber | int(10) unsigned    | NO   |     | 0       |                |
| size       | bigint(20) unsigned | NO   |     | 0       |                |
| dateadded  | datetime            | YES  | MUL | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

Best Answer

OBSERVATION #1

Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G)

OBSERVATION #2

You have sync_binlog set to 1. This provides the safest ACID compliant setup. It can also slow things down dramatically. You say it seems as though its writing each row 1 at a time to disk. That's the case because each completed DML (INSERT, UPDATE, DELETE) and DDL (ALTER TABLE) statement gets written to the binary logs. The default for sync_binlog is 0. That let's the OS be responsible for flush binary log changes to disk.

OBSERVATION #3

You have innodb_io_capacity set at 10000. That's really 10000 IOPs you are expecting of mysql. Try lowering it.

There are some things to do in this respect

  • When it comes to sync_binlog
    • Comment it out of my.cnf
    • Set sync_binlog to 100 or 200 to pace the flushing of binary logs
  • You should make sure data disk has a battery-backed cache (with a good battery)

RECOMMENDATION

STEP 01) Set these options in my.cnf

[mysqld]
innodb_fast_shutdown = 0
innodb_log_file_size = 1536M
sync_binlog = ( you choose 0 or 100 )

STEP 02) Remove innodb_io_capacity. Let it be set to default.

STEP 03) Run this command in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 04) Apply settings

cd /mnt/Storage/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile2 ib_logfile1.bak
service mysql start

This should speed things up

Related Question