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 (with innodb_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 🙂
We have our table_cache set to 16384 on our server and we are running into performance issue with this setting especially on QA servers where we recreate hundreds of databases daily (with 300+ tables each) then process to recreate them get’s worse and worse over time with the table cache seeming to be the primary culprit. Is this listed as a bug anywhere currently?
-Ryan
Ryan,
See the previous post on this matter. If you’re running into hundreds of thousands or more tables large table cache may be actually hurting you.
We have not investigated it but I trust there is something relatively simple which gets proportionally slower.
Its wonderful and absolute
I posted a comment on http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ asking about open_file_limit.
One thing I see that is not talked about is how the os ulimit of “open files”, mysql setting “open_file_limit” affects the OS/MySQL and I believe is non-biased to MyISAM and InnoDB table types.
If the max open_file_limit is 65535, does this mean a linux 64bit server could never have more than that opened at a given time?
How would you work around this and why wouldn’t MySQL use LRU algorithm to close OS open’ed files if the max is hit and avoid errno 24 (too many open files)?
Thanks.
Hey,
Firstly , I’ve learned and implemented a lot of changes based on your posts, mostly with very clear impact on our overall performance.
In regards to the Dictionary size. we get dictionary locks :
–Thread 139586333174096 has waited at row/row0mysql.c line 3143 for 11.000 seconds the semaphore:
We have an huge amount of tables, and we constantly create / drop tables.
this is what my database has now :
+——————–+——–+
| Variable_name | Value |
+——————–+——–+
| Innodb_dict_tables | 557067 |
+——————–+——–+
we’re running : Percona Server (GPL), 12.7, Revision 224
and I tried the following :
set global innodb_dict_size_limit=81920000;
a few seconds later the database crashed with a segmentation fault 🙁
no idea why …
I am going to try it again, but more carefully
what is a “reasonable” size for the dictionary cache / open_table_cache ?
Thanks
Guy