… he confirmed MySQL has global table cache mutex (LOCK_open) which is held for all open table operation so table opens are serialized. Do… is fixed it is especially important to keep your table_cache large enough so table opens will be rare (I use 1/sec… worry about) and also worry about possible limited performance while table cache is being warmed up in addition to all other caches 
Post: Opening Tables scalability
November 21, 2006 By 27 Comments
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… very large buffer pool. If you’re using Innodb tables as transient tables where you create and drop them frequently you may…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… inefficient. It is especially inefficient with Innodb tables both in terms of space (some tables would keep only couple of small rows… significant because table_cache can’t be made large enough and so a lot of table reopens needs to happen which requires table header… (to keep it simple). There are one table per user so we have post123 table which keeps posts for user number 123…
Post: MySQL caching methods and tips
April 4, 2011 By 5 Comments
… change a single row in any table, the query cache entries for every query which accessed that table must be invalidated. If a… query cache, since the rate of invalidation can not be controlled, and multiple cache entries may be invalidated by a single table change… data into the summary table. One advantage of summary tables is that they are persistent unlike the query cache or Memcached. There is…
Post: Using Multiple Key Caches for MyISAM Scalability
November 26, 2008 By 8 Comments
… concat(“CACHE INDEX “,table_schema,”.”,table_name,” IN “,table_schema,”_”,table_name,”;”) from information_schema.tables where table_schema=’test’ and table_name in (“a”,”b”); +——————————————————————————————-+ | concat(“CACHE INDEX “,table…
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… it will not be cached. Table level granularity in invalidation – If table gets modification all queries derived from this table are invalidated at once… other caching systems. Also note – all queries are removed from cache on table modifications – if there are a lot of queries being cached this…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… the table has already been opened and table object is in the table_cache change anything with respect to metadata locks. Metadata Locking and Table Cache No matter if a connection accesses a table that is already in the Table Cache, any query to a table that has…
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size… with RAID controller cache set to Write-Through and then to Write-Back. 1. Baseline – misalignment on the partition table, no LVM… show you the difference between sequential writes with WT cache and WB cache: Random read. This is probably the most interesting number…
Post: Cache Performance Comparison
August 9, 2006 By 74 Comments
… APC Cache 98000 File Cache 27000 Memcached Cache (TCP/IP) 12200 MySQL Query Cache (TCP/IP) 9900 MySQL Query Cache (Unix Socket) 13500 Selecting from table (TCP/IP) 5100 Selecting from table…
Post: Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
… cache for given table. Instead we could track versions for tables (increment each time table is updated) and check whenever version matches for all tables…, preventing some queries with SQL_CACHE_TTL from expiring as well as re-populating the cache after table update invalidated a lot of items. Frankly if you improve query cache manageability and make stats…
