MySQL Bugs: #16708: MySQL hangs up when UPDATE a table

Bug #16708	MySQL hangs up when UPDATE a table
Submitted:	22 Jan 2006 14:34	Modified:	9 Mar 2006 8:44
Reporter:	Sungsoo Kim	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.16 and 4.1.12	OS:	Linux (CentOS 4.2)
Assigned to:		CPU Architecture:	Any

Description:
My database server is very busy with heavy traffic.

When I issue "UPDATE LOW_PRIORITY ProductInfo SET ArtistNo = 78, LicenseType = 'RF', RightsType = 'Exclusive', FileType = 'JPG', ImageType = 'V' WHERE ProductNo = 680234", the database system hangs up about 5 minutes. Even though I killed the UPDATE process, it remained. (ProductNo is the primary key. Just ignore 'LOW_PRIORITY' because the result was same without it.)

`ProductInfo` is a InnoDB table with over 1 million of rows, and it is being cached because the table is updated every weekends.

I could watch Qcache_free_memory was increasing in the result of "show status" while MySQL hanged up, and the processes were released as soon as the number stopped increasing.

When I use MySQL 4.0.22 everything was OK. With this problem the database server is in quite unstable condition after upgrading to 4.1.12.

mysql> show processlist;
+---------+---------+--------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+
| Id      | User    | Host               | db         | Command | Time  | State  | Info                                                                                                 |
+---------+---------+--------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+
| 1076008 | keyword | xx.xx.xx.095:1597  | excaldb    | Killed  |   297 | end    | UPDATE LOW_PRIORITY ProductInfo SET ArtistNo = ?, LicenseType = ?, RightsType = ?, FileType = ?, Ima |
| 1076025 | apache  | localhost          | excaldb    | Query   |   297 | NULL   | SELECT SQL_CACHE ProductNo, ArtistNo, ProductId, LicenseType, PartnerImageNo, PreviewWidth, PreviewH |
| 1076031 | apache  | localhost          | excaldb    | Query   |   297 | NULL   | SELECT SQL_CACHE ProductNo, ArtistNo, ProductId, LicenseType, PartnerImageNo, PreviewWidth, PreviewH |
.
.
.
| 1076087 | apache  | localhost          | excaldb    | Query   |   223 | NULL   | SELECT SessionNo FROM LogUser WHERE SessionNo = 687856                                               |
| 1076088 | apache  | localhost          | excaldb    | Query   |   218 | NULL   | SELECT SessionNo FROM LogUser WHERE SessionNo = 1206641                                              |
+---------+---------+--------------------+------------+---------+-------+--------+------------------------------------------------------------------------------------------------------+
68 rows in set (0.00 sec)

How to repeat:
I can repeat this on my database server several times a day on condition that the number of Qcache_free_memory is below half.

Hi!

Please post the full unedited output of SHOW INNODB STATUS\G during the hang.

Please post SHOW CREATE TABLE of the involved table.

Please post the complete, unedited .err lof if it contains warnings or errors.

Regards,

Heikki

can you please also add the output of SHOW VARIABLES and of
SHOW STATUS before and after such a hanging query?

Please, upload also a complete error log, as Heikki asked you. As I understood, you have the same problem with 4.1.16 also, aren't you?

There are no error messages concerned with this problem in the log file. Anyway I included tail part of mysqld.log file. As you can see, it has no messages since Jan 27, 2006. Recently I tested and submitted the error result on Feb 1st. So mysqld did not generate any error message.

In the view point of mysqld it might not be an error because the hang-up will be released after serveral minutes.

This problem occurred in 4.1.12 and 4.1.16. Current running version is 4.1.16. I have tested in the two version, so other version may have the same problem.

I could have missed a very important fact. When MySQL 4.0.22 was running, "ProductInfo" table was updated from time to time, but now it is updated every weekend. I changed the PHP code in the web site not to use SQL_CACHE when reading ProductInfo table, and tested if the same problem still exists. I cannot experience the same problem any more.

When mysqld flush the cached memeory once the table is updated, does it takes so long time over 5 minutes?

---------------------

060126 11:05:08 [ERROR] /usr/sbin/mysqld: Can't open file: 'KeywordSearchCache_113240.MYI' (errno: 145)
060126 11:05:08 [Warning] Checking table:   './SearchCache/KeywordSearchCache_113240'
060127  1:22:15 [ERROR] /usr/sbin/mysqld: Can't open file: 'KeywordSearchCache_114370.MYI' (errno: 145)
060127  1:22:15 [Warning] Checking table:   './SearchCache/KeywordSearchCache_114370'

Thank you for the additional information. 

> I changed the PHP code in the web site not to use SQL_CACHE when
> reading ProductInfo table, and tested if the same problem still exists. 
> I cannot experience the same problem any more.

So, the original problem reported is solved by removing SQL_CACHE, right?

> When mysqld flush the cached memeory once the table is updated, does it
> takes so long time over 5 minutes?

It depends on cache size and hardware. In your case (query_cache_size              =1073741824) query cache is quite large.

As for the following messages in the error log:

060126 11:05:08 [ERROR] /usr/sbin/mysqld: Can't open file:
'KeywordSearchCache_113240.MYI' (errno: 145)
060126 11:05:08 [Warning] Checking table:  
'./SearchCache/KeywordSearchCache_113240'
060127  1:22:15 [ERROR] /usr/sbin/mysqld: Can't open file:
'KeywordSearchCache_114370.MYI' (errno: 145)
060127  1:22:15 [Warning] Checking table:  
'./SearchCache/KeywordSearchCache_114370'

It is a different problem, possibly already fixed. Do you have FULLTEXT indexes on that your tables (KeywordSearchCache_113240)?

Thank you for your prompt reply!

> So, the original problem reported is solved by removing SQL_CACHE, right?

Yes

> It depends on cache size and hardware. In your case query_cache_size=1073741824) query cache is quite large.

I can understand and I will reduce the query_cache_size.

> It is a different problem, possibly already fixed. Do you have FULLTEXT indexes on that your tables (KeywordSearchCache_113240)?

It is quite different problem. But the table has no FULLTEXT index.

Please, reopen this bug report when you'll have similar hang after the changes. Send the SHOW CREATE TABLE results for ProductInfo table then. 

I also recommend you to upgrade to 4.1.18 already available, expecially if you have a multicolumn primary key and the first columns from it were used in WHERE clause. This is what seems the case for me from the original description.

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