Improving Magento Performance With MySQL Query Cache
Posted by CTX Admin on 23 October 2014 11:16 AM
Improving Magento performance is at the top of the list for most professional Magento developers. There's no doubt that enhancing the speed at which Magento performs can only be good for business.
One of the most important enhancements you can make to your Magento installation is to properly configure your MySQL database server.
A proper MySQL configuration requires a low level understanding of your underlying hardware and, primarily, the memory (RAM) available.
There is one specific MySQL configuration parameter above all others that will produce significant performance improvements and takes very little understanding to implement.
The single most effective configuration item for enhancing the performance of Magento is money. The second is MySQL's cache queries configuration parameters.
Magento Commerce is a database application that requires a great deal of database access and read operations. However, Magento also makes many identical queries each time a page is viewed with relatively little changes in these queries over time.
Each time a Magento page is loaded, a series of database queries are made and the database server must go to work.
Each query is first parsed and the execution of the query is initiated. Then the data must be retrieved from the disk or storage media, sorted and manipulated, and then ultimately returned to the customer. This results in performance slowdowns because of, among other things, slower disk access.
MySQL offers a built in configuration parameter known as
The actual size of this cache and number of query result sets cached is dependent on the amount of memory available to your MySQL database server.
In a hosting environment with 1 GB RAM it allows for a query cache size of 64 MB or larger depending on other memory use factors, such as other services running on the server.
This allows 64 MB of queries to be stored in the very fast RAM, or memory of your hosting environment, as opposed to having to access the disk each time this query is executed or each time the page is loaded. This is an extremely large amount of data.
Now is a good time to point out a bug in MySQL query cache that proves bigger is not always better:
Extremely large query cache sizes should not be used. The larger the query cache size, the greater the impact of the data validation MySQL performs on the cache when a table has changed, and the resulting set of data in the cache is no longer valid. The bottom line is not to use a query cache size larger than what you need.
On a Magento installation with over 80,000 products, we have found 64 MB to provide 10x the performance improvement over disabled query cache. The actual use or capacity of the cache is less than 50 percent, which provides room for continued cache growth up to 64 MB.
The performance increase you should see from this single configuration enhancement will be in the order of 100-1000% depending on the query executed and the disk being accessed.
Shared vs. Dedicated
MySQL query cache is disabled by default and tells MySQL how much RAM to use for this cache and ultimately how many queries we can cache. This cache is cleared each time the MySQL server is restarted and must be rebuilt query by query after each restart of the MySQL server.
In a product database containing over 80,000 items test results show a non-cached query returning in just over 11 seconds.
This same query performed a second time results in using the cached query and produces the same result set in just over 1 second.
This further demonstrates the necessity of a dedicated or application-specific hosting environment for production e-commerce websites, which provide a far more stable MySQL database service, among many other advantages, to the less expensive shared hosting.
Contrast this performance enhancement with shared hosting, where a database server could be bounced daily, or even hourly in some cases.
Each time the MySQL server is restarted you lose your cached queries and each new query performed will result in a significantly longer wait for page load. That is, if cached queries are even enabled on your shared host.
How To Check If Query Caching Is Enabled
To check if your host has query caching enabled in the MySQL server, you can issue the following command from your MySQL command prompt:
If you are using a standard MySQL binary this value is always
To verify that query cache is actually operational, you can issue the following command to the MySQL server:
This shows that we have 64 MB available to our query cache size, a very respectable amount of memory.
The following demonstrates a server that has MySQL query cache disabled by setting the value to zero:
If you have root access, making a change to this value is trivial. Simply add the following line to your
Query caching is an extremely effective and simple method that can use to improve your Magento performance in minutes. Query caching offers several other configuration parameters that should also be carefully considered when configuring your MySQL server, including:
I highly recommend that you review these additional configuration parameters prior to implementing your query cache configuration.
Not only will this provide you with even better performance, but you'll also have a deeper understanding of how your MySQL configuration can enhance the performance and increase the speed of Magento.