Bug #15653 Slow inserts to InnoDB if many thousands of .ibd files
Submitted: 10 Dec 2005 11:24 Modified: 18 Jun 2010 23:15
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:4.1, 5.0 OS:Any (All)
Assigned to: Heikki Tuuri CPU Architecture:Any

[10 Dec 2005 11:24] Heikki Tuuri
Description:
When flushing files, InnoDB searches through the list of all tablespaces (and log spaces), and checks if a file is unflushed. With 100,000 .ibd files, this takes so much time that InnoDB only can do 1 - 10 inserts per second!

'top' shows CPU usage at about 100 % with almost all time spent in the 'user space'.

How to repeat:
Create and use 100,000 tables with:

innodb_file_per_table

in my.cnf.

Suggested fix:
Keep a list of unflushed files in fil0fil.c. Only scan that short list when flushing.
[10 Dec 2005 11:27] Heikki Tuuri
This also makes mysqld shutdown extremely slow - and CPU-bound - if there are unflushed pages to thousands of .ibd files in the buffer pool.
[10 Jan 2006 19:11] Heikki Tuuri
Patch over 4.1.16

Attachment: bug15653.patch (text/plain), 6.10 KiB.

[10 Jan 2006 19:12] Heikki Tuuri
I have now attached a patch over 4.1.16.
[17 Jan 2006 14:57] Heikki Tuuri
A new patch over 4.1.16

Attachment: bug15653.patch (application/octet-stream, text), 6.16 KiB.

[17 Jan 2006 14:58] Heikki Tuuri
There was a small bug in the old patch. A new patch included.
[31 Jan 2006 12:31] Alexander Ivanov
Patch:  http://lists.mysql.com/commits/1889
Pushed to 4.1.19.
[31 Jan 2006 18:59] Alexander Ivanov
Fixed in 5.0.19.
Patch: http://lists.mysql.com/commits/1958.
[2 Feb 2006 18:16] Mike Hillyer
Documented in 5.0.19 and 4.1.19 changelogs:

      <listitem>
        <para>
          InnoDB now caches a list of unflushed files instead of
          scanning for unflushed files during a table flush operation.
          This improves performance when
          <literal>--innodb-file-per-table</literal> is set on a system
          with a large number of InnoDB tables. (Bug #15653)
        </para>
      </listitem>
[8 Nov 2006 14:26] Marko Mäkelä
The bug fix caused Bug #24089.
[5 May 2010 15:18] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 1:45] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:13] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:41] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:08] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:27] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:19] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:06] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:47] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)