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_type
must be ON (1) or DEMAND (2)query_cache_limit
- determines the maximum size of the result that will get into the cachequery_cache_size
be different from zero. When using DEMAND, only requests that contain the directive will be cachedSQL_CACHE;
query_cache_min_res_unit
the 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 sizequery_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_blocks
shows 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
“It is recommended to evaluate the effectiveness of the cache by dividing the value of the variable Qcache_hits on Qcache_hits + Com_select
, since when processing a request, the Qcache_hits counter increases (if the request is processed from the cache) or Com_select (if the request is not cached). This method is suggested in "Mysql Performance Optimization" O'reilly
There is another way online
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
If this value is > 0.8, then 80% of your requests are cached, which is a very good indicator.
If %
the cache hit is low, then you need to increase the value query_cache_size
.
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_prunes
it 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.
“In practice, one of 2 formulas can be used to calculate query_cache_size:
query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
or
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_size
we increased the values, after which you should pay attention to the values Qcache_total_blocks
, Qcache_free_blocks
and 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_cached
is 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 = 0
disables 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 from
SQL_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.
GO TO FULL VERSION