I know this type of question come often. But I have done a lot of research, tried a lot of different settings, but still have the same issue: queries that usually are very fast can take 3s to 5s seemingly randomly.

The server is an i7-3770 (8 cores) with 32GB RAM. The CPU usage is about 50% idle, not CPU spike. No swap used, free memory is about 10GB in average. I run mysql 5.5.32 on CentOS 6.

9GB of RAM has been allocated for MySQL, it uses about 2GB. All data should fit in memory (600MB of data, 700MB of index).

Number of queries per second in average (no real spike):

  • 1.5 SELECT
  • 0.2 UPDATE
  • 0.05 INSERT

Here is an example of query that takes just a few ms, but sometimes more than 3s:

# Query_time: 4.337884  Lock_time: 0.050146 Rows_sent: 1  Rows_examined: 1
SELECT me.id, me.url, me.filename, me.instance_id, me.virtual_id, me.status, me.user_id, me.time_added, me.time_finished, me.priority, me.size, me.delay, me.flash_delay, me.tries, me.details, me.json_file, me.html, me.shots, me.shot_interval, me.screen_width, me.screen_height FROM Screenshots me WHERE ( me.id = '5992705' ); 

id is a primary key.

Although I have more SELECT than INSERT queries, I have more slow INSERT than SELECT

What I have tried and tested:

  • Make sure all required indexes are there, but no redundant ones and no one unused
  • no CPU spike at the time, no IO spike, no swap
  • 2nd instance of MySQL as slave, most SELECT queries are done on the slave
  • remove and TEXT and equivalent data type
  • tune my.cnf

Tuning my.cnf helped a lot. I tried with query cache enabled and disabled, not much difference.

Using a slave for SELECT made things actually worst: I had fewer slow queries on the master, but they could go up to 12s!

Here is my current my.cf (with query cache in this case):

tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 1
query_cache_size               = 1M
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 4096

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 256M
innodb_log_buffer_size         = 8M
innodb_thread_concurrency      = 8
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 9G

transaction-isolation          = READ-UNCOMMITTED 
innodb_locks_unsafe_for_binlog = 1
innodb_io_capacity             = 1000
innodb_change_buffering        = inserts
innodb_fast_shutdown           = 0

key_buffer_size                = 2G

I'm out of ideas. I could not find any patterns (frequency, interval, etc.) that would explain these slow queries.

  • Were there any resource spikes prior to the slow query log? The reason I ask is because the queues that fill up during a spike may take some time to empty out. – Jason Zhu Feb 25 '14 at 4:53

What is your disk setup? You don't mention that. Sounds like its IO bound.

innodb_io_capacity - leave that at the default, there is no reason to change it unless you have a good reason or benchmarks to prove that your settings are correct.

I suspect that you have this set too high. InnoDB write threads can throw more data at the disks every second than they can handle, resulting I/O queueing.

  • avg-cpu: %iowait = 7% %idle=50% – Julien Feb 13 '14 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.