Caching in MySQL

7.1 Caching on the DB side

MySQL uses highly scalable algorithms when working with tables, so MySQL can run even with small amounts of memory. Naturally, for better performance, you need more RAM.

To view the current settings, connect to the database

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

First, make sure caching is enabled. Variable:

• query_cache_typemust be ON (1) or DEMAND (2)
• query_cache_limit- determines the maximum size of the result that will get into the cache
• query_cache_sizebe different from zero. When using DEMAND, only requests that contain the directive will be cachedSQL_CACHE;
• query_cache_min_res_unitthe minimum size of the allocated block of memory for storing the results of the cached query. MySQL does not store the cache in one large chunk of memory, instead it allocates blocks with a minimum size query_cache_min_res_unit(=4KB by default) on demand. The last such block is truncated to the data size, and the remaining memory is freed.

The effect of caching is that when the server receives a request, it looks to see if the hash of the request is in the cache. If the hash matches - the server immediately returns the result - without parsing the request, optimizing, etc. overhead - accompanied by a caching mechanism - browsing the cache, writing the query result to the cache, etc.

And if you have a lot of small requests in the cache, then this can lead to memory fragmentation due to a large number of free blocks. And this, in turn, causes cached entries to be deleted due to lack of memory. In this case, it makes sense to decrease the value of query_cache_min_res_unit. If most of your queries produce large results, then increasing this setting can improve performance.

Let's try to evaluate the effect. We look at how the cache hit counters change (Qcahe_hits), the number of requests declared invalid due to lack of memory (Qcache_lowmem_prunes), the total number of requests of the SELECT type (and only they are cached):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Current cache status

To monitor the query cache is used SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksshows how many free blocks there are in the cache (will decrease as cached requests increase);
• Qcache_total_blocks— the number of occupied blocks;
• Qcache_free_memory- shows the free "available" memory for caching;
• Qcache_hits- the number of requests, the results of which were taken from the cache, without actually accessing the database;
• Qcache_inserts- the number of requests that were added to the cache;
• Qcache_lowmem_prunes- the number of requests that were removed from the cache due to lack of memory;
• Qcache_not_cached- the number of requests that were not written to the cache due to the use of time management functions, etc.;
• Qcache_queries_in_cache- the number of requests that are in the cache.

You can view the total number of SELECT queries:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Caching efficiency

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

The current value can be viewed like this:

SHOW VARIABLES LIKE 'query_cache_size';

Again, the question arises: how to choose an adequate valuequery_cache_size?

This will help Qcache_lowmem_prunes. This variable stores the number of requests that have been removed from the cache due to the need to cache new requests. It is necessary to strive for such a cache size at which Qcache_lowmem_prunesit will increase only slightly. To do this, it is recommended to compare the difference in values Qcache_lowmem_prunes​​per hour and the number of requests received by mysql in the same hour.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (traffic volume in 10 minutes) * 1.2 "

This will cache requests for 10 minutes + give an additional 20% memory for cache fragmentation and additional caching reserve

You can calculate the number and average volume of a response to a request using the Bytes_sent variables , respectively

And so query_cache_sizewe increased the values, after which you should pay attention to the values Qcache_total_blocks​​, Qcache_free_blocksand Qcache_queries_in_cache. MySQL stores cache in blocks. For 1 request, 2 blocks are needed: one for the request text itself, the second for the result.

If we consider the table from the valueQcache%

Total number of cache blocksQcache_total_blocks – 28

Request 6 is cached now, which means 6 * 2 = 12 blocks are busy

Free blocks Qcache_free_blocks – 10. The more idle Qcache_free_blocks, the greater the degree of "fragmentation" of the cache.

If most queries have a small amount of resulting data, then it is worth reducing the minimum cache block size query_cache_min_res_unit, which is 4 KB by default.

If most requests return a lot of data, then it is worth increasing the size of the cache block.

The main thing is to achieve a minimum value Qcache_free_blocks.

If the counter Qcache_not_cachedis large, you can try to increase the variable query_cache_limit- it will allow you to increase the limit and cache the results of queries that "do not fit".

The following configuration variables are responsible for using the query cache:

• query_cache_size— the size of the query cache. query_cache_size = 0disables cache usage;
• query_cache_limit- the size of the maximum sample stored in the cache;
• query_cache_wlock_invalidate- determines whether the data will be taken from the cache if the table to which they belong is locked for reading.
=

To enable mysql query caching, just add the following lines to my.cnf (Section [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

And restart the service.

7.4 Where the cache is not used

Not cached:

• Requests fromSQL_NO_CACHE
• Prepared queries(Prepared statements);
• Queries that are subqueries of the outer query;
• Queries inside stored procedures and functions;
• Queries that use functions:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()with one argument, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()without arguments, USER (),UUID ();

• Queries using stored functions, user variables or referencing tables in mysql system databases or INFORMATION_SCHEMA;
• • Requests in the following forms:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Queries with temporary tables or not using tables at all;
• Requests generating warnings (warnings);

You can defragment the cache with the command:

mysql>flush query cache;

Clear - command:

mysql>flush query cache;

The most important

Never work in production with default settings. This will result in most of the server's resources not being used. Proper MySQL tuning can improve database performance by several times. This will not only speed up the application, but also cope with a heavy load.