> query cache system is case sensitive. If the same query is written in different formats it may not use the cache properly and create different caches for the same query > show status like "%qcache%"; +-------------+-----+ | Variable_name | Value | +-------------+-----+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 12569160 | | Qcache_hits | 1 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------+-----+ "Qcache_inserts" increases 1 after each new cache creation. "Qcache_hits" is increased 1 if an exiting cache is used > MySQL is clever enough to clear the query results whenever a change is made to the table. Any of INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE or DROP DATABASE will remove queries from the cache. You can manually clear the query cache with RESET QUERY CACHE. >> table_cache ------------------------ Each time MySQL accesses a table, it places the table in the cache. If your application accesses many tables, it is always good to have them in cache so that data retrieve is faster. You can check whether your system needs to have the table_cache value increased by checking the open_tables and opened_tables status variables during peak time. open_tables is the number of tables opened in cache. Whereas opened_tables is the total number of tables open. Since MySQL supports multi-threading, several queries might be executed on the same table at the same time. So each of these queries will open a table. The default value to table_cache is 64. Lets consider a sample scenario. table_cache = 64 mysql> SHOW STATUS LIKE "open%tables%"; open_tables = 64 opened_tables = 5426787 Here table_cache has maxed out and opened_tables is fairly high. In this case, if you have enough memory, increase table_cache to reduce the number of opened_tables. table_cache is a useful one. Each time MySQL accesses a table, it places it in the cache. If your system accesses many tables, it is faster to have these in the cache. A good way to see whether your system needs to increase this is to examine the value of open_tables at peak times (one of the extended status values, above). If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts increasing, you should increase the table_cache if you have enough memory. Look at these three scenarios. Scenario 1 table_cache - 512 open_tables - 98 opened_tables - 1513 uptime - 3046085 Here it looks like the table cache has been set too high. The server has been up for ages, the number of opened_tables is relatively low, and the open_tables (remember we're checking at a peak time) is nowhere near what it could be. Scenario 2 table_cache - 64 open_tables - 64 opened_tables - 517 uptime - 1662790 (measure in seconds) Here, although the open_tables is maxed out, the number of open_tablesis very low even though the server has been up for ages. There is probably not much benefit in upping the table_cache(this example comes from a development server). Scenario 3 table_cache - 64 open_tables - 64 opened_tables - 13918 uptime - 33754 This table_cache is set too low. The open_tables is running at maximum, and the number of open_tables is high, even though the uptime is less than a day. If you have the memory, up the table_cache. One thing to note is that even if you only have 64 tables in your database, you may still have more open tables. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. >> key_buffer_size: ---------------------- key_buffer_size is the size of buffer used by all the indexes. Ideally, it should be large enough to contain all the indexes ie., total size of all the .MYI files in the server. A rule of thumb is to set to to at least a quarter of the memory available, half the memory to the maximum but not more than that. When to increase your key_buffer_size: The status variables you should be checking to find this are Key_read_requests, Key_reads, Key_write_requests and Key_writes. Key_read_requests : The number of requests to read a key block from the cache. Key_reads : The number of physical reads of a key block from disk. Key_write_requests : The number of requests to write a key block to the cache. Key_writes : The number of physical writes of a key block to disk. The optimum solution is to keep the ratio Key_reads : Key_read_requests should be 1:100 and Key_writes / Key_write_requests should always be less than 1. If the Key_reads value is high compared to Key_read_requests, it is high time you increase your key_buffer_size. The key_buffer_sizeis another useful one to tweak. It affects the size of the index buffers, and making it as large as possible increases the speed of index handling, particularly reading. A good rule of thumb is to set it from between quarter to half of the available memory on your server (for systems dedicated to MySQL). A good way to check this is to look at the extended status variables, and compare key_read_requests to key_reads. Scenario 1 key_buffer_size - 402649088 (384M) Key_read_requests - 609601541 Key_reads - 67299 Scenario 2 key_buffer_size - 16777216 (16M) Key_read_requests - 609601541 Key_reads - 46729832 The values in scenario 1 are looking healthy. The ratio of key_reads to key_read_requests should be as low as possible, no more than 1:100. In scenario 1 it is close to 1:10000. In scenario 2, it is shocking, about 1:15, and the key_buffer size should be increased to as much as the memory allows (you can see that RAM is the primary hardware upgrade you can do to improve your system). >> sort_buffer_size: ------------------- Improves large and complex sorts. Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster ORDER BY or GROUP BY operations. The default value for sort_buffer_size is 2MB. Please note this buffer is at a per client level. >> tmp_table_size: Sometimes for executing a statement, a temporary table needs to be created. This variable determines the maximum size for a temporary table in memory. Always try to avoid temporary table creation by optimizing your query. But if it is unavoidable, make sure that the table is created in the memory. If the memory is not sufficient, a MyISAM table will be created in the disk. When to increase tmp_table_size: Check the processlist and see if any query is using temporary tables and is taking too long to resolve. In this case, you should increase the tmp_table_size. You can also check the status variables Created_tmp_disk_tables and Created_tmp_tables. Created_tmp_disk_tables : Number of temporary tables created on disk while executing a statement Created_tmp_tables : Number of in-memory tables created. If a large number of tables are created in the disk, its high time you increase your tmp_table_size. Please note memory is allocated in per client basis (per thread basis). >> Using less memory than available can reduce the performance, but using more memory than available can lead to worse performance or even crashes. A general resource allocation formula is memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections