JIYIK CN >

One of the best ways to speed up your Web application is to enable query caching in your database, which caches frequently used SQL queries in memory so that the next page that makes the same request is almost instantaneous.

What makes this approach so powerful is that we don't have to make any changes to our web application, just sacrifice a little memory. This won't solve all your problems, but it definitely won't hurt.

注意: If our application updates the table frequently, then the query cache will be constantly cleared and we won't get much or any benefit from it. This is perfect for applications that primarily read the database, such as a WordPress blog. This will also not work if we are running on a shared host.

Enable caching while the server is running

The first thing we need to do is make sure that our installation of MySQLactually has query cache support available. Most distributions do, but we should check anyway.

We need to run this command from the MySQL console and it will tell us if the query cache is enabled.

mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

Don't be fooled into thinking this means query caching is actually enabled, as most hosting providers don't enable it by default. Oddly enough, my Ubuntu Feisty installation already had it enabled...

Next we need to check and see if query caching is enabled. We have multiple variables to check so we might as well query%do it all at once by checking the variable

mysql> show variables like 'query%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| query_alloc_block_size       | 8192    |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 8388608 |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
| query_prealloc_size          | 8192    |
+------------------------------+---------+

Here are the important items in the list and what they mean:

• query_cache_size – This is the size of the cache in bytes. Setting this value to 0 will effectively disable the cache.
• query_cache_type – This value must be ON or 1 to enable the query cache by default.
• query_cache_limit – This is the maximum query size in bytes that will be cached.

If query_cache_sizethe value is set to 0 or we just want to change it, we need to run the following command, remember that the value is in bytes. For example, if we want to allocate 8MB for the cache, we will use 1024 * 1024 * 8 = 8388608as the value.

SET GLOBAL query_cache_size = 8388608;

Likewise, other options can be set using the same syntax:

SET GLOBAL query_cache_limit = 1048576;
SET GLOBAL query_cache_type = 1;

Now how can we tell if it actually worked? We can use SHOW STATUSthe command to extract all variables starting with " Qc " to see what is going on behind the scenes.

mysql> SHOW STATUS LIKE 'Qc%';
+-------------------------+--------+
| Variable_name           | Value  |
+-------------------------+--------+
| Qcache_free_blocks      | 65     | 
| Qcache_free_memory      | 201440 | 
| Qcache_hits             | 18868  | 
| Qcache_inserts          | 2940   | 
| Qcache_lowmem_prunes    | 665    | 
| Qcache_not_cached       | 246    | 
| Qcache_queries_in_cache | 492    | 
| Qcache_total_blocks     | 1430   | 
+-------------------------+--------+
8 rows in set (0.00 sec)

You'll notice in the stats that I have a lot of free memory. If our servers are showing a lot of low memory pruning we might want to look into increasing this, but I wouldn't spend too much memory on a web server's query cache...we need that for apache, php, ruby, or whatever you're using.

Enable in the configuration file

If we want these changes to persist after a reboot or restart of the mysql server, we need to add them to MySQL's /etc/mysql/my.cnf configuration file.

请注意, which may be located in a different location on our installation.

Open the file with a text editor in sudoor rootmode and add these values ​​if they do not already exist in the file. If they do exist, simply uncomment them.

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

Query caching can significantly speed up a web application, especially when our application is primarily read. Use the above method to monitor the status and see how it changes over time.