… for keeping the caches of a MySQL standby server hot using MySQL slow logs with long_query_time set to 0. Here… 65481 explains that the HUP signal also flushes tables in addition to logs. Flushing tables can impact running queries. Disable MySQL slow…/mysql -e ‘select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… table headers) is well cached so the cost of table cache miss is not very high, you may be better of with significantly reduced table cache… 5.1 – in this version table_cache was significantly redone and split in table_open_cache and table_definition_cache and I assumed the behavior may… has a very similar task of managing its own cache of file descriptors (set by innodb_open_files) As the time allows…
Post: SHOW OPEN TABLES - what is in your table cache
December 14, 2008 By 3 Comments
… in set (0.00 sec) This command lists all non-temporary tables in the table-cache, showing each of them only once (even if table… this table ? I would much rather see all entries in the table_cache used, not grouped by the table, with thread_id using the table set (0 if table is not in use…
Comment: More on table_cache
December 6, 2009 By 7 Comments
….000 tables. 2) Table_Cache set to 2.000 brings 16400 selects/sec. 3) Table_Cache set to 20.000 brings 13500 selects/sec. 4) Table_Cache set to 5 brings 8000 selects/sec. What if you set Table_Cache to 100… (from performance point of view) the best option is to set same number of buckets as you will have number of…
Post: Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
… is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed. OS Caches Operation Systems typically cache file… of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global…
Post: MySQL opening .frm even when table is in table definition cache
November 21, 2011 By 8 Comments
… question: I have an instance of MySQL with 100 tables and the table_definition_cache set to 1000. My understanding of this is that MySQL won’t revert to opening the FRM files to read the table… for information it already has cached (often at least twice – in InnoDB itself and in the table_definition_cache). Further reading: MySQL Forge…
Comment: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
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…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it… significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header… we do not pass user_id value to underlying table MySQL tries to set it to default, which does not work as…
Comment: table_cache negative scalability
November 17, 2009 By 23 Comments
is it possible to “play around” with the table_cache setting without restarting mysql? i currently use 2048 on a r900 / 64gb , we have 180 tables and about 300 to 400 queries per second, so far perforamnce is not any issue but its always good to stay ahead of possible bottlenecks 
Comment: MySQL File System Fragmentation Benchmarks
March 22, 2008 By 28 Comments
I might be wrong but if you have table_cache set to a large value then dir_index won’t really make much difference once the file is opened. This will be amortized over the entire length of the DB server.
