MySQL Query Cache Fragmentation Slows Down the Server (Doc ID 1308051.1)

Last updated on NOVEMBER 24, 2016

Applies to:

MySQL Server - Version 4.0 to 5.5 [Release 4.0 to 5.5]
Information in this document applies to any platform.

Symptoms

MySQL server becomes very slow and not responsive.

The Query Cache is enabled and Qcache_free_blocks > 10000.
Here is the command to collect this status variable :

SHOW GLOBAL STATUS LIKE 'Qcache_free_blocks';


If you use InnoDB, the InnoDB status seems frozen for 10 or more seconds, even minutes.
Many thread states are Query Cache related such as

---TRANSACTION 1 263063737, not started, process no 2588, OS thread id 1206266176
MySQL thread id 486210, query id 130927562 freeing items
SELECT * FROM dual

The SQL query profile shows CPU only queries and the CPU usage on one core is high, while others may be very low.

On Linux, the PMP (Poor's Man Profiler) output, (see http://poormansprofiler.org) shows that the Query Cache thread is busy freeing memory blocks.

1 Query_cache::free_memory_block(Query_cache_block*),
Query_cache::free_query_internal(Query_cache_block*),
Query_cache::invalidate_table(THD*,
Query_cache::invalidate(THD*,
mysql_insert(THD*,,mysql_execute_command(THD*),
mysql_parse(THD*,,dispatch_command(enum_server_command,,do_command(THD*),
handle_one_connection,start_thread,clone

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms