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
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, thecategoriesId
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 withthis 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 yourinnodb_flush_log_at_trx_commit=2
setting. 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: