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
• Status: Closed
• 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:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-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:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:41] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:08] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-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:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-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:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-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:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)