Bug #21112 InnoDB slow with > 100,000 .ibd files

• Submitted: 18 Jul 2006 12:47
• Modified: 13 Sep 2006 17:57
• Reporter: Heikki Tuuri
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Any (All)
• Assigned to: Osku Salerma
• CPU Architecture: Any

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