Bug #15653 Slow inserts to InnoDB if many thousands of .ibd files
Submitted: 10 Dec 2005 12:24 Modified: 2 Feb 2006 19:16
Reporter: Heikki Tuuri
Status: Closed
Category:Server: InnoDB Severity:S2 (Serious)
Version:4.1, 5.0 OS:Any (All)
Assigned to: Heikki Tuuri Target Version:

[10 Dec 2005 12: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 12: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 20:11] Heikki Tuuri 
Patch over 4.1.16

Attachment: bug15653.patch (text/plain), 6.10 KiB.
[10 Jan 2006 20:12] Heikki Tuuri 
I have now attached a patch over 4.1.16.
[17 Jan 2006 15:57] Heikki Tuuri 
A new patch over 4.1.16

Attachment: bug15653.patch (application/octet-stream, text), 6.16 KiB.
[17 Jan 2006 15:58] Heikki Tuuri 
There was a small bug in the old patch. A new patch included.
[31 Jan 2006 13:31] Alexander Ivanov 
Patch:  http://lists.mysql.com/commits/1889
Pushed to 4.1.19.
[31 Jan 2006 19:59] Alexander Ivanov 
Fixed in 5.0.19.
Patch: http://lists.mysql.com/commits/1958.
[2 Feb 2006 19: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 15:26] Marko Mäkelä 
The bug fix caused Bug #24089.