The first part of this post was devoted to the tools for SQL optimization in MySQL. Here I would like to stop at several database parameters which are relevant for MySQL performance.

There are at least three areas to take a look at: Thread Cache, Table Cache and Query Cache.

Thread Cache

According to MySQL manual it is recommended to use a persistent connection (function pconnect()) in your applications. If it is not possible for some reason, you have to pay attention how many database threads are cached.

Check the following parameters:

• threads_cached
  number of threads reserved
• threads_created
  number of threads, create since database start
• max_connections
  maximum number of connections at the same time since db start

Sample Commands:

mysql> show status like 'threads_%';mysql> show variables like 'max_connections';

You can also use the commands like these to check other parameters listed below.

Table Cache

• table_cache
  number of tables in the cache
• open_tables
  currently opened tables
• opened_tables
  number of tables opened since db start
• uptime
  time in seconds since db start

First check the first two variables. If the number of open_tables is equal to tabel_cache it a sign that you table cache is full. It is not bad, however if you see it combined with the growing number of opened_tables (simpy do a refresh several times) combined with a short uptime – this could mean your table buffer is not sufficient. Ian Gilfillan provides here a detailed explanation with different scenarios.

Query Cache

Similar to its Big Brothers Oracle and DB2, MySQL also has a query cache. tores the text of a SELECT statement together with the corresponding result that was sent to the client. The query cache is extremely useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. Read more on this in the official documentation.

The most interesting variables for performance monitoring are:

• Qcache_hits
  Combined with the total number of SELECT-queries sent, it gives us an impression about the cache efficiency. The normal hit ratio on the well-tuned system shouldn’t be below 98%. Please take a look to the official docs to find out how to calculate the total number of selects sent to MySQL server.
• Qcache_total_blocks and Qcache_free_blocks
  As the query cache uses variable-length blocks these two variables can indicate a query cache fragmentation. Make a FLUSH QUERY CACHE to get one single free block and monitor the changes (e.g. growth of the free blocks) during the database runtime.
• Qcache_lowmem_prunes
  This status variable can help to tune the query cache size. It counts the number of queries that have been removed from the cache to free up memory for caching new queries.

The relevant variables could be obtained with the following commands (result samples included):

mysql> SHOW VARIABLES LIKE 'query_cache%';

+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+

| query_cache_limit            | 1048576 |

| query_cache_min_res_unit     | 4096    |

| query_cache_size             | 8388608 |

| query_cache_type             | ON      |

| query_cache_wlock_invalidate | OFF     |

+------------------------------+---------+

mysql> SHOW STATUS LIKE 'Qcache%';

+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+

| Qcache_free_blocks      | 852       |

| Qcache_free_memory      | 2758736   |

| Qcache_hits             | 191931713 |

| Qcache_inserts          | 41449892  |

| Qcache_lowmem_prunes    | 14429263  |

| Qcache_not_cached       | 93857     |

| Qcache_queries_in_cache | 2186      |

| Qcache_total_blocks     | 5436      |

+-------------------------+-----------+

Please not that this overview doesn’t pretend for a comprehensive coverage of all performance tuning measures related to MySQ. I’ve taken these variables based on my experience to provide you the starting points for further analysis. Please take a look to the knowledge sources below, which I’ve used for creating this material:

• Optimizing the MySQL Server
  Official documentation on the subject.
• Optimizing MySQL: Hardware and the Mysqld Variables
  Very good article by Ian Gilfillan, written in 2001 but remains topical