Bug #15653 Slow inserts to InnoDB if many thousands of .ibd files

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.

This also makes mysqld shutdown extremely slow - and CPU-bound - if there are unflushed pages to thousands of .ibd files in the buffer pool.

Patch over 4.1.16

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

I have now attached a patch over 4.1.16.

A new patch over 4.1.16

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

There was a small bug in the old patch. A new patch included.

Patch:  http://lists.mysql.com/commits/1889
Pushed to 4.1.19.

Fixed in 5.0.19.
Patch: http://lists.mysql.com/commits/1958.

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>

The bug fix caused Bug #24089.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)