mysql open files_innodb_open_files 是如何影响 mysql性能的 | 学步园

叁歲伎倆 2023-01-12 04:19 123阅读 0赞

Recently I looked at table\_cache sizing which showed larger table cache does not always provides the best performance. So I decided

to look at yet another similar variable – innodb\_open\_files which defines how many files Innodb will keep open while working in innodb\_file\_per\_table mode.

Unlike MyISAM Innodb does not have to keep open file descriptor when table is open – open table is purely logical state and appropriate .ibd file may be open or closed. Furthermore besides MySQL table\_cache Innodb maintains its own (called

data dictionary) which keeps all tables ever accessed since table start – there is no variable to control its size and it can take significant amount of memory in some edge cases. Percona patches though provide innodb\_dict\_size\_limit to restrict

growth of data dictionary.

So I started with same series of test and creating 100.000 tables with single integer column. The process of creating tables took about 45 minutes which is a lot more than MyISAM and the total size on disk was 12GB in .ibd

files plus some space allocated in system tablespace. So if you create Innodb tables you better store some data in them otherwise there will be a huge waste of space.

I used MySQL 5.4.2 for tests which should be as good as it gets in terms of optimizations in this space.

To keep test alligned to my previous experiments I was running with table\_open\_cache=64 and tried innodb\_open\_files=64 and 16384.

Reading 100.000 tables first time after MySQL time takes about 500 seconds (so it is some 200 tables/sec) – first time Innodb actually populates data dictionary. The second time we do same operation it takes about 25 seconds (4.000 tables/sec)

which is quite a difference. As we can see even in case table it fully in Innodb data dictionary the operation is slower than MyISAM tables. Though the difference can be related to the size of set of empty tables which is about 10 times smaller for MyISAM.

I found no significant difference whatever limit of open files was, which is not surprising as logical operation of opening file is rather fast on local file system – one can open/close file hundreds of thousands times per second.

To verify this I tried doing “open table” test for only 10K out of 100K tables – the performance was about the same, taking 1sec (on the second time) . Whenever innodb\_open\_files\_limit was 64 (virtually all misses ) or 16384 (all hits) performance was the same.

As I mentioned Data Dictionary can take considerable amount of memory – In my case after reading all tables I got“Dictionary memory allocated 392029720″which means very simple single tables takes about 4KB of space in data dictionary. More

complicated tables can take a bit more.

So innodb\_open\_files does not affect performance a lot on reads – what is about writes ? I tried again very simple test inserting the row in each of 100K tables. This test ran about 180sec first time and about 260sec second time (withinnodb\_flush\_log\_at\_trx\_commit=0)

go giving 550 and 380 updates/sec appropriately. Why was second time slower and not faster ? Because on the second run there were a lot of dirty pages in innodb buffer pool which had to be flushed before recycling. First ran however was done with clean buffer

pool (after reading all tables once)

Same as with select case I could not see any measurable difference between two tested innodb\_open\_files values.

Finally I decided to test the crash recovery – does it make any difference ?

The crash recovery in Innodb is nasty if you have a lot of tables:

091118 18:43:36 InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files…

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer…

InnoDB: Doing recovery: scanned up to log sequence number 12682768136

091118 18:47:44 InnoDB: Starting an apply batch of log records to the database…

If Innodb detects it is not shut down properly it will scan all .ibd files which took a bit over 4 minutes for 100K tables but which obviously can take a lot more if there are more tables or they are less cached than in this case. This part of data recovery

does not depends on amount of records which need to be applied just about number of tables.

With innodb\_open\_files=64 I got bunch of warning messages during recovery:

091118 18:47:44 InnoDB: Warning: too many (67) files stay open while the maximum

InnoDB: allowed value would be 64.

InnoDB: You may need to raise the value of innodb\_max\_files\_open in

InnoDB: my.cnf.

InnoDB: fil\_sys open file LRU len 0

091118 18:47:44 InnoDB: Warning: too many (68) files stay open while the maximum

InnoDB: allowed value would be 64.

InnoDB: You may need to raise the value of innodb\_max\_files\_open in

InnoDB: my.cnf

So we can see Innodb may with to have so many open files during recovery stage and it will open more files than allowed if needed.

Both scanning open files and applying logs took about 9 minutes in this setup. This number of course can change a lot depending on hardware log file size workload and even when crash happen (how many unflushed changes we had)

Repeating test with innodb\_open\_files=16384 I got about same crash recovery speed though with no warnings.

So it looks like innodb\_open\_files\_limit=300 is not being that large liability even with large number of tables and you can also safely increase this number if you like – there is no any surprises such as surprised slow downs for replacing open files in the

list. I guess Heikki knows how to implement LRU in the end

![3d66a746cc2b1344ca4b6f9c9d9bd1f9.gif][]

[3d66a746cc2b1344ca4b6f9c9d9bd1f9.gif]: /images/20221119/af4ed8789cc84e619f63d55b46db081c.png