Bug #20876 InnoDB tablespace memory cache hash size needs to be increased
Submitted: 5 Jul 2006 23:39 Modified: 4 Aug 2006 5:16
Reporter: Boris Burtin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.16 OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[5 Jul 2006 23:39] Boris Burtin
Description:
We noticed that mysql was at 100% CPU when we created more
than ~5000 tables.  We tracked it down to this constant in
fil0fil.c:

/* The tablespace memory cache hash table size */
#define FIL_SYSTEM_HASH_SIZE 50 /* TODO: make bigger! */

By changing this constant to 20011, we were able to get mysql CPU
usage back down to 5%-7% during provisioning. The code that
flushes dirty pages was doing way too many linear searches.

Heikki acknowledged that the value is too small, and suggested that it should be set to 50000, rather than 50.

How to repeat:
Create 5000 tables or more, monitor mysql CPU usage.

Suggested fix:
Increase the size of FIL_SYSTEM_HASH_SIZE.
[7 Jul 2006 1:07] James Day
In MySQL 5.1 we expect this to be set to 10000, per discussion with Heikki. Bug 15653 fixed in 5.0.19 and 4.1.19 and later have a different fix for slow table flushing, keeping a list of modified tables.

Feedback needed for this is whether 4.1.19's fix is a sufficient solution for the problem.
[13 Jul 2006 23:18] James Day
Boris tested 4.1.19 without this change and found that the improvement to use a list of tables to be flushed still led to high CPU load for a few minutes at startup, so this looks like a useful change for 4.1, that is probably sufficiently safe.
[24 Jul 2006 11:52] Sveta Smirnova
I tried to create 10500 tables using last BK 4.1.22 sources and easy PHP script:

<?php
$link = mysql_connect('localhost', 'root');

mysql_select_db('bug20876');

for ($i =0; $i < 10500; $i ++)
	mysql_query("CREATE TABLE test$i (id INT) ENGINE=INNODB");
?>

Using this simple test I can not catch the problem. Is it correct test?
[1 Aug 2006 17:18] Boris Burtin
Was innodb_file_per_table enabled during your test?
[1 Aug 2006 21:15] Sveta Smirnova
>Was innodb_file_per_table enabled during your test?
I've just enabled innodb_file_per_table option and repeated the test with same result: maximum 2% CPU.

Could you please try using current version and if problem happens again, correct my test?
[1 Aug 2006 21:33] Sveta Smirnova
I reread James comments and changed status of the bug. If problem still exists in new version, simple open it again.
[3 Aug 2006 5:52] James Day
Sveta, could you repeat it using 4.1.20? Boris reported seeing it in that version.
[3 Aug 2006 7:32] Sveta Smirnova
No, I can not repeat it using MySQL 4.1.20 binary package. Could you please provide content of my.cnf, exact version of Linux you use, accurate version of MySQL package (name of file) and information about tables.
[4 Aug 2006 5:12] James Day
Further checking has shown that the existing improvement from bug 15653 seems to have resolved this sufficiently.