MySQL Bugs: #45107: Threads locked in "Freeing Items" state even without query cache

Bug #45107	Threads locked in "Freeing Items" state even without query cache
Submitted:	26 May 2009 21:17	Modified:	2 Jun 2009 9:48
Reporter:	Devon Weller	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1.34	OS:	Linux (Centos 5.2)
Assigned to:		CPU Architecture:	Any
Tags:	freeing items, hang

Description:
I am seeing threads lock in a freeing items state for a long time (3-20 seconds) on certain simple queries.  The snippet below is a common snapshot of my processlist:

| 3021 | sb   | sb01:44663 | sb_atlas | Query       |    9 | freeing items                                                  | UPDATE page_node SET `bgcolor` = '#FFFFFF', `body_tag_attributes` = 'text=\"#000000\"
leftmargin=\" | 
| 3036 | sb   | sb01:44669 | sb_atlas | Query       |    3 | freeing items                                                  | UPDATE page_node SET `bgcolor` = '#FFFFFF', `body_tag_attributes` = 'text=\"#000000\"
leftmargin=\"0 | 
| 3037 | sb   | sb01:44670 | sb_atlas | Query       |    3 | freeing items                                                  | UPDATE page_node SET `bgcolor` = '#FFFFFF', `body_tag_attributes` = 'text=\"#000000\"

The table above named "page_node" is an innoDB table with less than 1000 items in it.  It has about 46 fields with 4 indexes.

Note that I have disabled the query cache as a similar bug has suggested:

show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    | 
| query_cache_limit            | 1048576 | 
| query_cache_min_res_unit     | 4096    | 
| query_cache_size             | 0       | 
| query_cache_type             | OFF     | 
| query_cache_wlock_invalidate | OFF     | 
| query_prealloc_size          | 8192    | 
+------------------------------+---------+

How to repeat:
I don't have an exact process to repeat the bug.

For me it happens with a busy server acting as a replication master.  Have many repeated operations updating the same table.

Suggested fix:
I am not aware of any.

added hang tag

Please see last comment of bug http://bugs.mysql.com/bug.php?id=42951 if you are using InnoDB tables. Thanks in advance.

Thank you.  Turning off adaptive hash indexes for innoDB tables removed this symptom.

Thank you for the feedback.

Closing as "Not a Bug" regarding to last comment.