MySQL Bugs: #55386: Query stalls in 'freeing items' state locking all of MySQL server

Bug #55386	Query stalls in 'freeing items' state locking all of MySQL server
Submitted:	20 Jul 2010 6:37	Modified:	8 Mar 2011 15:52
Reporter:	Kaspars Foigts	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.1.47	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
MySQL server stalls for a few minutes because one connection is held in state 'freeing items'. Since it is a production server, all I can think of when catching this behaviour, ir killing affected thread and waiting for it to release lock:

#  mysqladmin processlist | grep freeing
| 31292368 | nekur       | 10.0.0.12:52012  | nekur       | Killed         | 147   | freeing items      | -- /home/www/xxxxxxxx:432

Yes, I've read bugs #42951 and #43758. None of them helps. I would rather not turn off query cache.

How to repeat:
It occures irregularly from time to time and I do not have any way to trigger this behaviour.

Please, send your my.cnf file content.

MySQL configuration file

Attachment: my.cnf (application/octet-stream, text), 1.21 KiB.

I truly doubt that you need this:

query_cache_size = 8G

Please, send the results of:

show global status like 'Qcache%';

to check if I am mistaking.

> show global status like 'Qcache%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 1234183    |
| Qcache_free_memory      | 3444405600 |
| Qcache_hits             | 520059176  |
| Qcache_inserts          | 29366658   |
| Qcache_lowmem_prunes    | 248763     |
| Qcache_not_cached       | 57893809   |
| Qcache_queries_in_cache | 2593641    |
| Qcache_total_blocks     | 6422541    |
+-------------------------+------------+
8 rows in set (0.00 sec)

Please, decrease query cache to 4G at least:

query_cache_size = 4G

and check if queries will hang in "freeing items" state less often.

I decreased value to 1G for starters. If anything like this happens again, I will let you know.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am also facing similar issue as mysql server load often shoots up to 25 to 30% on load average , specifically consuming around 380% CPU resources.

It happens normally when in "mtop" i see alot of query status  as "freeing item"

My default value for cache was 250 M, i tried increasing to 4 G but no difference was made.

Please suggest the solution

Smaller query cache, 32M or so, or even explicitly set to 0 and query_cache_type set to OFF, should help to solve this problem. 

Also, please, check if you have the same problem with a recent server version, 5.1.54.

query_cache_type : This value is not present in my.cnf file

But i did set querycache to 0 but it instead increased the server load to 44% constantly and above that.

Its a live site and cannot try upgrading on live site...What do you think?

Version 5.1.49

What about setting query cache size to 32M? It may give you a right balance of performance and no hangs.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".