MySQL Slow Writes

innodbMySQL

Inserts into the following table are taking up to 70 seconds to complete:

CREATE TABLE IF NOT EXISTS `productsCategories` (
  `categoriesId` int(11) NOT NULL,
  `productsId` int(11) NOT NULL,
  PRIMARY KEY (`categoriesId`,`productsId`),
  KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are around 100,000 rows in the table, and it is taking 7MB on disk.

Are there some settings in MySQL which can improve write performance?

My my.cnf file is as follows:

log-slow-queries="/var/log/mysql/slow-query.log"
long_query_time=1 
log-queries-not-using-indexes

innodb_buffer_pool_size=4G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

query_cache_size = 6G
key_buffer_size = 284M
query_cache_limit = 1024M
thread_cache_size = 128
table_cache = 12800

sort_buffer_size=2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M

read_buffer_size=128K

open_files_limit               = 1000
table_definition_cache         = 1024
table_open_cache               = 6000

max_heap_table_size=512M
tmp_table_size=4096M

max_connections=1000

thread_concurrency = 24

Here is the hardware setup:

  • Dell R710
  • RAID10
  • 48G RAM

Given this hardware, I wouldn't expect the problem to be a hardware bottleneck.

Best Answer

OBSERVATION #1

The very first thing that catches my eye is the table structure

CREATE TABLE IF NOT EXISTS `productsCategories` (
  `categoriesId` int(11) NOT NULL,
  `productsId` int(11) NOT NULL,
  PRIMARY KEY (`categoriesId`,`productsId`),
  KEY `categoriesId` (`categoriesId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please notice that the categoriesId index and the PRIMARY KEY start with the same column. It is a redundant index. Since this table is InnoDB, the categoriesId index is redundant for another reason: All secondary indexes contains keys into gen_clust_index (aka Clustered Index; See what is gen_clust_index used for in mysql?)

If you remove the categoriesId index with

ALTER TABLE productsCategories DROP INDEX categoriesId;

this will improve INSERTs dramatically because of not having to do extra Secondary and Clustered index maintenance.

OBSERVATION #2

If you are doing any bulk insert operations, you need a large bulk insert buffer.

Please see my past posts on this:

OBSERVATION #3

Your log file size is way too small !!! It should be 25% of the InnoDB Buffer Pool, which in your case should be 1G. See my post on how to resize InnoDB Log Files.

OBSERVATION #4

Please, do not set innodb_thread_concurrency !!! I learned firsthand at Percona Live NYC to leave that setting alone. It is disabled by default in MySQL 5.5, MySQL 5.1 InnoDB Plugin, and Percona Server 5.1+.

OBSERVATION #5

You need to use innodb_file_per_table. If this is disabled, I make file maintenance on ibdata1 a nightmare. Please read my post on how to cleanup InnoDB to implement this.

OBSERVATION #6

If you are using MySQL 5.5 or Percona Server, you have to set certain options to make InnoDB use mutiple CPUs/multiple cores. Please see my post on those settings.

OBSERVATION #7

You have innodb_log_buffer_size=4M. The default is 8M. That will cause twice as much flushing to the redo logs. That will also counteract your innodb_flush_log_at_trx_commit=2setting. Please set it to 32M. Also, please see MySQL Documentation on innodb_log_buffer_size.

In light of these observations, please add or replace the following settings:

[mysqld]
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
innodb_file_per_table
innodb_log_file_size=1G
innodb_log_buffer_size=1G
bulk_insert_buffer_size = 256M
Related Topic