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 max_connections=1000 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.