… off with small table cache. What I have not checked though is how does table_cache (or table_open_cache in newer version) size affects the hit… ? If you can fit your tables in table cache and eliminate (or almost eliminate) table cache misses it is best to size table_cache large enough – even in…
Post: Beware large Query_Cache sizes
March 23, 2007 By 3 Comments
… with MySQL Query Cache being source of serious problems. One would see queries both for MyISAM and Innodb tables mysteriously stalling for… you set query_cache_size relatively high at 256MB or more. It can be seen worse if your query cache size is in Gigabytes. At the same time check how many queries do you have in cache – Qcache_queries_in_cache – if…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… separate key cache for all actively accessed tables (assuming there are only few of them), allocating key_cache proportional to their size and load, but no more than the index size (assuming table sizes are relatively static) To get accurate information about table…
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
Recently I looked at table_cache sizing which showed larger table cache does not always provides the best performance. So I decided … closed. Furthermore besides MySQL table_cache Innodb maintains its own (called data dictionary) which keeps all tables ever accessed since table start – there is…
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… 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 key_buffer_size=DEFAULT…
Comment: table_cache negative scalability
November 16, 2009 By 23 Comments
… different table cache sizes and different data access distribution. I predict in this workload (because it Always causes misses as long as table_cache is… no gains due to miss rate reduction with increase of table_cache. And I expect semi-linear regression with increased table cache size.
Post: Performance problem with Innodb and DROP TABLE
February 3, 2011 By 23 Comments
… does a lot of CREATE and DROP table for Innodb tables and we’ve discovered DROP TABLE can take a lot of time… large buffer pool. Worst of all this is done while table_cache lock is being held so no other queries can start…, though this will vary a lot depending on buffer pool size. MyISAM tables creating/dropping was several times faster still.
Comment: table_cache negative scalability
November 18, 2009 By 23 Comments
Wagner, In your case number of tables is tiny and I trust you can fit them all in the cache easily – you can use large enough table cache. On the other hand i must say I did not see how table_cache size affects hits. I’m assuming only misses/invalidations slowdown but I have not tested it.
Post: Solving INFORMATION_SCHEMA slowness
December 23, 2011 By 25 Comments
… this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is very fast for Innodb… box: mysql> select count(*),sum(data_length) from information_schema.tables; +———-+——————+ | count(*) | sum(data_length) | +———-+——————+ | 130 | 2856365892 | +———-+——————+ 1 row in set…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…_stmt_cache_sizeblogpercona.commanual max_connect_errorsblogpercona.commanual max_connectionsblogpercona.commanual max_delayed_threadsblogpercona.commanual max_error_countblogpercona.commanual max_heap_table…_zoneblogpercona.commanual table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table_typeblogpercona.commanual…
