Table Definition Cache

The Table Definition Cache buffers the parsed and interpreted table definitions. For a normal application, you will probably want to set this value to the number of tables in your MySQL instance. You can determine the number of tables with the command:

mysql> SELECT COUNT(*) FROM information_schema.tables;
+----------+
| count(*) |
+----------+
|      153 |
+----------+

The popular defaults for Table Definition Cache, from 256 to 400, would be sufficiently dimensioned for this example.

Query Cache

The next relevant MySQL parameter from the SQL area is the Query Cache. This should actually be called Result Cache because it caches the query results. Opinions differ on this cache – some people think it is useless and would prefer to disable it completely if possible. Others benefit from performance gains during SELECT queries.

Most MySQL users probably have to deal with a relatively small number of concurrent connections to the database and a high percentage of read queries. In this case, it makes more sense to enable the Query Cache.

Caution: MySQL 5.6 disables the Query Cache by default! Be careful when you upgrade. You should not choose too high a value for the Query Cache; otherwise, you might experience complete system freezes in certain scenarios. As a rule of thumb, it should not be greater than 128MB.

MyISAM key_buffer_size

In the case of the MyISAM Storage Engine, one critical parameter above all must be tuned: the MyISAM key_buffer_size. It defines how much RAM the MyISAM storage engine requests from the operating system for caching MyISAM index blocks. But, because almost no one uses MyISAM tables today, this parameter is unlikely to be particularly relevant in the future. As a rule of thumb, figure on 25 to 33 percent of RAM for a dedicated, exclusively MyISAM-reliant database system. The aria_pagecache_buffer_size is similar to this if you use the Aria storage engine provided with MariaDB.