---

Pre-requisites:

• (dv)2.0 Dedicated Virtual Server - Only if you have successfully upgraded MySQL via either of the following KB articles
• Upgrade to PHP5 and MySQL 4.1x on (dv) 2.0 Dedicated Virtual Servers
• Upgrade to MySQL 4.1x on (dv) 2.0 Dedicated Virtual Servers without PHP5
• (dv)3.x Dedicated Virtual Server
  

---

OVERVIEW:

This article will help you enable query caching in your my.cnf configuration file for MySQL. Although this article will actually increase the memory used by MySQL the performance gain can be dramatic depending on your queries and database usage.

This is Part 2 of a series of articles on how to tune your (dv) for better performance. Please see (dv) HOWTO: Performance tuning (Optimization) for additional articles in this series.

Things you will need:

• Root access and SSH access to your (dv)
• A basic understanding of the Vim editor

LINKS:

http://dev.MySQL.com/doc/refman/4.1/en/index.html MySQL Doumentation site
http://dev.MySQL.com/doc/refman/4.1/en/query-cache.html Documentation on the Query Cache

DETAILS:

Enabling query caching can improve performance of frequently used MySQL queries.
Step 1. Create a backup of your MySQL config

cp /etc/my.cnf /etc/my.cnf.YYYY-MM-DD.bak 

(replace YYYY-MM-DD with today's date, ie: 2006-06-30)

Step 2. Edit MySQL configuration

vi /etc/my.cnf

Insert the following lines under the [MySQLd] section:

query-cache-type = 1
query-cache-size = 8M

NOTE: The ##M is the amount of RAM to use for query cache. You may want to increase / decrease this value depending on your needs. Rage or Extreme server may want to set this value to 16 MBs.

And save the file.

Step 3. Restart MySQL

/etc/init.d/mysqld stop
/etc/init.d/mysqld start

You can verify your changes via command line MySQL with the following method:

mysql -uadmin -p`cat /etc/psa/.psa.shadow`

You can check whether the query cache is present in your MySQL server using the following statements from the MySQL prompt type:

SHOW VARIABLES LIKE 'have_query_cache';

to check that caching is in fact enabled:

SHOW VARIABLES LIKE 'query%';

This will cache a lot of the frequently used queries, so the performance increase may not be seen immediately.

More Tips:

• You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
  
  
• The RESET QUERY CACHE statement removes all query results from the query cache.
  
  
• The FLUSH TABLES statement also does this.
  
  
• To monitor query cache performance, use SHOW STATUS to view the cache status variables:

SHOW STATUS LIKE 'Qcache%';

The output of this command will look similar to this:

| Qcache_free_blocks             | 376       |
| Qcache_free_memory             | 1724608   |
| Qcache_hits                    | 65894     |
| Qcache_inserts                 | 53777     |
| Qcache_lowmem_prunes           | 9307      |
| Qcache_not_cached              | 1348      |
| Qcache_queries_in_cache        | 1676      |
| Qcache_total_blocks            | 4168      |

In the above example you can see that Qcache_lowmem_prunes is a large number. This means that the Query Cache had to remove queries to make room for other queries. Based on this example you would want to increase query-cache-size slightly to keep your queries inside cache. If your Qcache_lowmem_prunes is "0" then you may want to decrease query-cache-size to free up memory for other processes.

NOTES/RESOURCES

• Using MySQL Report on a Dedicated-Virtual Server
• Using MySQLTuner on your Dedicated-Virtual Server
  

Revisions:

02-16-2009: Added Notes/Resources