Paolo Zaboi MySql Toolbox

How to improve mysql performance using CACHING

A deep analisys of a performance bottleneck could be long and is based on different kind of data (OS metrics, IO performance tests, DB wait stats and so on…). Before starting with this (or simply after a new delivery) some simple checks could help you to correct rapidly some of the common (and bigger) bottlenecks you can find in a low-performance installation.
One of this is cache sizing.

A cache disabled or not correctly configured is a common reason of low-performance problems.
MySQL engine use a result-caching to improve query speed. A query result is cached in memory to avoid slow IO reading operations. Mysql use the query_cache_size parameter to size the engine cache. A result is saved in the cache only if it’s larger then the query_cache_min_res_unit parameter but smaller then the query_cache_limit.

A query result is kept in memory until:
– the result is valid (data are not modified)
– there are no more-used results using spaces in cache dedicated memory

A good sizing of cache instance parameters is based on instance activity. My suggestion are:
– Start with a simple configuration using system memory available (avoiding paging). note: by default mysql cache is DISABLED
– Start keeping query_cache_min_res_unit little.
– Check istance metrics after some days of normal database activity to understand how you can tune better cache parameters

This is an example of an installed configuration:

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
+------------------------------+----------+
5 rows in set (0.00 sec)

After some days of normal instance activity start watching some metrics to understand whats heppening.
Those values are self explained

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 957      |
| Qcache_free_memory      | 15523704 |
| Qcache_hits             | 1978478  |
| Qcache_inserts          | 132298   |
| Qcache_lowmem_prunes    | 6037     |
| Qcache_not_cached       | 5969     |
| Qcache_queries_in_cache | 3389     |
| Qcache_total_blocks     | 8396     |
+-------------------------+----------+
8 rows in set (0.00 sec)

Talking about performances there are no “top configuration” usable. You have to work with users and system integrators to understand:
– Any application suggested cache configuration
– In low performance situations: what is the performance target ?
– Are there any test query usable to test configuration tuning?

For a deeper explanation of all mysql cache parameters start from here: http://dev.mysql.com/doc/refman/5.6/en/query-cache.html

Paolo Zaboi Administration, MySql, MySql Toolbox

Queries to see rapidly what your MySql is doing NOW

Those queries are useful to admin MySQL instances using mysql client.

1) Check active processes/connections running

mysql> show processlist;

the previous statement doesn’t show all queries text running. To see the complete statements running use:

mysql> show processlist;
--or--
mysql> show full processlist G;

the running queries statements could bee quite long. Use the “G” modifier to see processes in different output paragraphs

2) Check UPTIME
MySql store main infos under metadata views querable using “SHOW STATUS” command. Those are some of the most quick and useful.

mysql> show status like '%uptime%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| Uptime                    | 880329 |
| Uptime_since_flush_status | 8347   |
+---------------------------+--------+
2 rows in set (0.01 sec)

3) Check Connections

mysql> show status like '%conn%';
+-----------------------------------------------+---------+
| Variable_name                                 | Value   |
+-----------------------------------------------+---------+
| Aborted_connects                              | 0       |
| Connection_errors_accept                      | 0       |
| Connection_errors_internal                    | 0       |
| Connection_errors_max_connections             | 0       |
| Connection_errors_peer_address                | 0       |
| Connection_errors_select                      | 0       |
| Connection_errors_tcpwrap                     | 0       |
| Connections                                   | 4094917 |
| Max_used_connections                          | 222     |
| Performance_schema_session_connect_attrs_lost | 0       |
| Ssl_client_connects                           | 0       |
| Ssl_connect_renegotiates                      | 0       |
| Ssl_finished_connects                         | 0       |
| Threads_connected                             | 120     |
+-----------------------------------------------+---------+
14 rows in set (0.00 sec)

4) Check Caching Status
Caching mechanism are managed using startup parameters. First you have to check if caching is enabled:

mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 2097152  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 33554432 |
| query_cache_type             | ON       |
+------------------------------+----------+
5 rows in set (0.00 sec)

If query caching is enabled caching metadata infos are visible simply in this way.

mysql> show status like 'QCache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 957      |
| Qcache_free_memory      | 15523704 |
| Qcache_hits             | 1978478  |
| Qcache_inserts          | 132298   |
| Qcache_lowmem_prunes    | 6037     |
| Qcache_not_cached       | 5969     |
| Qcache_queries_in_cache | 3389     |
| Qcache_total_blocks     | 8396     |
+-------------------------+----------+
8 rows in set (0.00 sec)

5) Check Instance Wait Time using PERFORMANCE_SCHEMA
Starting with release 5.5 MySql introduce the new performance_schema. Now performance critical conditions can be analized quering a group of system tables realtime dinamically updated.
First we’ll check if performance_schema is turned on. This option can be turned on using startup parameter file.

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+

If performance_schema is started there are many tables available to check your instance status (use SHOW TABLES under performance schema to see the complete list). Here we’ll start with one of the most simple, used to analize instance wait conditions in realtime:

mysql> SELECT * FROM events_waits_currentG
*************************** 1. row ***************************
            THREAD_ID: 0
             EVENT_ID: 5523
           EVENT_NAME: wait/synch/mutex/mysys/THR_LOCK::mutex
               SOURCE: thr_lock.c:525
          TIMER_START: 201663444489586
            TIMER_END: 201660494576112
           TIMER_WAIT: 86526
                SPINS: NULL
        OBJECT_SCHEMA: NULL
          OBJECT_NAME: NULL
          OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 142270668
     NESTING_EVENT_ID: NULL
            OPERATION: lock
      NUMBER_OF_BYTES: NULL
                FLAGS: 0
...