MySQL Slow Writes

Question

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.

Can you provide some objective data? Logs, your testing/benchmarking methodology and results, that sort of thing? — womble, Jul 10 '12 at 09:50
What kind of logs would you like to see? The only thing i see why it takes so long is in de *mtop*. I'd test things as change the table to a myisam table, higher memory limits, higher threads. — Ronn0, Jul 10 '12 at 09:52
The best setting to improve mysql... is to switch to postgres lol — Antony Gibbs, Nov 09 '17 at 18:16
thread_concurrency = 24 has no effect... you can dump that line — Antony Gibbs, Nov 09 '17 at 18:19

score 17 · Accepted Answer · edited Jun 11 '20 at 10:02

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

Wow, thanks! I did print all points and working on it now and save it in the brain to help other people in the future. Many thanks! — Ronn0, Jul 10 '12 at 12:20
`query_cache_size` is enormous, too. Every insert will require up to 6GB of cache to be flushed. — Aaron Brown, Jul 10 '12 at 12:26
@AaronBrown I agree. I read in the High Performance MySQL book that InnoDB does tedious things with transaction ID on InnoDB table in the query cache which make it kind of needless, not to mention slow, to use a query cache. In fact, MySQL 4.1 has the query cache disabled for InnoDB. — RolandoMySQLDBA, Jul 10 '12 at 12:30
I think the query cache is completely separate from the storage engine. It basically serializes all write operations by forcing everything to go through and scan the query cache for queries to invalidate. I've never found a use case for it and it has always caused problems. — Aaron Brown, Jul 10 '12 at 16:05
one more thing to add: query_cache_size = 6G <-- this is totally and utterly absurd. Query cache is often better disabled, and for sure shouldn't be lager than 32M or 64M. The overhead of maintaining a 6G query cache is certainly hurting performance. — Gavin Towey, Jul 11 '12 at 06:02
Thanks for query_cache_size tips, i did lower it to 16MB. :) — Ronn0, Jul 11 '12 at 08:44

score 0 · Answer 2 · answered Jul 10 '12 at 10:03

You should check innodb_log_file_size, the default setting is 5M which is quite low for write intensive setups. Consider setting it to 100M. You will have to delete the old ib_logfile* files in order to start the DB with new settings. Please do not delete the log files while the DB server is running, you will have to stop it first. Probably you should backup the old log files first, not just delete them.