MySQL Bugs: #61215: FLUSH TABLES which never ends.

Bug #61215	FLUSH TABLES which never ends.
Submitted:	18 May 2011 11:21	Modified:	3 Sep 2011 12:18
Reporter:	Simon E	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.1.49-1ubuntu	OS:	Linux (Ubuntu 10.10)
Assigned to:		CPU Architecture:	Any

Description:
We have the problem, that much often a FLUSH TABLES tableX, tableY won't finish. It times out or has to be killed.

Look at this.. nothing is doing anything on the MySQL Server but the flush doesn't finish.

xy@localhost:~$ mysqlfullproc
Id User Host db Command Time State Info
640 main localhost NULL Query 227 Flushing tables FLUSH TABLES buffer.bucketcpvector_floating_impression,buffer.recommendations_seen,buffer.bucketcpvector_string_impression,buffer.widgetimpression,buffer.bucketcpvector_cluster_feedback,buffer.widgetintegrations,buffer.itemtagseen,buffer.itemtaghover,buffer.clickfeedback,buffer.bucketcpvector_floating_feedback,buffer.user_demographics,buffer.bucketcpvector_cluster_impression,buffer.bucketcpvector_string_feedback,buffer.activity,buffer.engagement,buffer.implicitratings
687 root localhost NULL Query 0 NULL show full processlist

Some "show full processlist" showed that the (connection) "Id" is growing one by one so i know nobody else comes on the server and is doing anything, all processes was stopped and after ~900s i killed the flush table command. "iotop" doesn't show anything doing on the harddrives. It would be really nice if somebody could explain how i can see who has a lock on a table.. thats something i really often wanted to know. Maybe there is a bug but it's hard to repeat (before this command there were many other commands in parallel but then stopped).

Best regards
Simon

How to repeat:
Can't repeat because the preconditions aren't clear.

When this will happen again, please 9after hanging for say 600 seconds), try to get the output of:

show innodb status\G
show full processlist;

and the results of:

mysqladmin status

in the error log. Just send the entire error log, compressed.

I'd also recommend to check if the same problem happens with recent version, 5.1.57.

Something similar was happening right now and to connections hanging on flush:

Id      User    Host    db      Command Time    State   Info
7       localuser  localhost       ff    Query   206     Waiting for table       SELECT          stats.categoryid,
                                        stats.domainid
                        FROM            stats_cross_domain_category stats
                        INNER JOIN      categories USING(categoryid)
622     maintenance     externalhost:41021   NULL    Query   979     Flushing tables FLUSH TABLE ff.stats_intext
626     system user             NULL    Connect 3631    Waiting for master to send event        NULL
627     system user             NULL    Connect 0       Has read all relay log; waiting for the slave I/O thread to update it   NULL
744     maintenance     localhost       NULL    Query   983     Flushing tables FLUSH TABLE ff.stats_cpo
790     root    localhost       NULL    Query   0       NULL    show full processlist

the code which is doing the flush looks like:

LOCK TABLES ff.stats_cpo WRITE
FLUSH TABLE ff.stats_cpo  # this hangs

so i don't understand how this can happen because it has already a lock on the table.

We have no innodb:

(none)> show innodb status\G
ERROR 1286 (42000): Unknown table engine 'InnoDB'
(none)> 

The mysqladmin status:
Uptime: 4161  Threads: 14  Questions: 953905  Slow queries: 30  Opens: 21359  Flush tables: 1  Open tables: 3493  Queries per second avg: 229.248

Error log: there was nothing in there since 1h...

Newer version isn't as easy as it sounds like.. we can't test too much on this server because it is in production..

Sorry, I had to ask for

mysqladmin debug

and its results in the error log when you see the problem, not mysqladmin status. You should see all table locks set as a result.

So there it is. You see the "Flushing tables" for ff.stats_widgetimpressions and statistics.by_channelid_and_day. The size of them are:

ff.stats_widgetimpressions (MYD/MYI)      = 80M  / 41M
statistics.by_channelid_and_day (MYD/MYI) = 235K / 136K

I have to say, stats_widgetimpressions is a partinioned table but this shouldn't matter because the other one is a 'normal' MyISAM table.

I see that thread 1888:

| 1888 | maintenance | server8:49997                          | NULL         | Sleep   |  1838 |                                                                       | NULL                                                                                                                                                                                                                                                                                             locked table:

Thread database.table_name          Locked/Waiting        Lock_type

1888    ff.stats_cross_domain       Locked - write        High priority write lock
1888    ff.stats_cross_domain       Locked - write        High priority write lock

and is sleeping for a long time. Do you know what is it doing, and why?

yes the server8 is syncing the big tables with rsync to itself so it locks and flushes the table and then copy it. The table is quite big so it needs some time. But it shoudn't bother the other ones, should it?

so is this a bug or not? nobody there with an idea?

Thank you for the report.

What is thhe difference between this bug and bug #45066 and bug #60917?

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".