Bug #21112 InnoDB slow with > 100,000 .ibd files
Submitted: 18 Jul 2006 12:47 Modified: 13 Sep 2006 17:57
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0 OS:Any (All)
Assigned to: Osku Salerma

[18 Jul 2006 12:47] Heikki Tuuri
Description:
InnoDB uses a lot of unnecessary CPU time if there are many .ibd files because it has to scan hash chains that are thousands of nodes long.

The reason is this:

fil0fil.c:

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

This bug was noticed and reported by Zimbra engineers. Thank you to them!

How to repeat:
Define innodb_file_per_table and create 100,000 tables.

Suggested fix:
/* With the hash table size 50,000, fil0fil.c should be quite fast up to a million .ibd files */

#define FIL_SYSTEM_HASH_SIZE (srv_file_per_table ? 50000 : 5000)
[21 Jul 2006 6:22] Osku Salerma
Fixed in Innobase's 5.0 and 5.1 trees.
[13 Sep 2006 17:15] Timothy Smith
Pushed to 5.0.25 and 5.1.12.
[13 Sep 2006 17:57] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.