… to tuning caches is to get them as large as you can if you have enough resources (such as memory). With MySQL common sense however does not always works – we’ve seen performance issues with large query_cache_size also… 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…
Post: MySQL Performance on Memory Appliance
March 31, 2008 By 23 Comments
… a chance to check out MySQL Performance on “Memory Appliance” by Violin Memory which can be used as extremely high speed storage… as possible so I use Sysbench to get 300Mil rows table and ran primary key lookups with uniform distribution some 90… and tested in such condition – normal case is to allocate cache memory to buffer pool so IOs will mostly come from drives…
Post: MySQL Server Memory Usage
May 17, 2006 By 21 Comments
… Operation System cache you would like MySQL to use for your table. Take this number add to it number of memory Operation System… memory if done to MyISAM tables. myisam_sort_buffer_size used for ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE commands. For OLTP applications with simple queries memory… can have very large memory consumption Innodb Table Cache. Innodb has its own table cache in which meta data about each table accessed from the start…
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… to be cached but does not fit even in distributed memory you can use file cache (ie on shared storage). Query Cache is good when there is no other cache If you…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload – remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data… your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but…
Post: 10+ Ways to Crash or Overload MySQL
November 13, 2007 By 20 Comments
… in memory. Memory Tables If you can create memory tables you can create any number of them and even though there is max_heap_table_size…-8K per table though complex tables can require larger sizes, so this is mainly the problem for smaller servers. Table Cache Merge Tables – Table Cache is allocated…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… tables still keep it 4-32MB to be used for temporary tables. key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache… to on disk MyISAM table. It does not limit size of temporary table, neither it applies to tables created as TEMPORARY TABLE, even in memory tables.
Post: The MySQL optimizer, the OS cache, and sequential versus random I/O
April 28, 2008 By 17 Comments
… the dimension tables doesn’t slow the query perceptibly, because these tables are small and they stay in memory, in the OS cache. (They don’t get evicted from memory by the cache‘s LRU policy, because they are frequently used — once per row in the fact table. The LRU…
Post: Query_cache and column level privileges
August 6, 2007 By 5 Comments
… found out that in the case when table has column level privileges – function query_cache_send_result_to_client just stopped and… | 1 | | Qcache_queries_in_cache | 1 | +————————-+———-+ As you see the query was inserted into query cache with allocated memory for. Let’s try…
Post: The tool I've been waiting for years
March 18, 2008 By 10 Comments
… cache content tracking for storage engines which cache everything in their own cache memory – Innodb, Falcon, Maria. Having information about how large portion of table is cached would allow…
