Magento is a heavy duty eCommerce solution that promises performance and scalability. This is perfect for retailers looking to grow fast or those who already have a significant presence on the internet. The software provides a complete website solution, together with plugins and extensions, that can grow your eCommerce site with any size inventory.
However, as a free open source content management system, like WordPress, it’s bugged with the same issues of conflicting scripts and database overload. For developers, this is important to be aware of because clients not only expect websites that function, but websites that perform well.
Due to the complexity of Magento, it’s beyond the scope of one article to describe all the areas you need to be aware of to make sure your site delivers instant results. However, proper configuration of your MySQL database server is one area that can dramatically speed up Magento.
Database queries
Databases are at the core of Magento, as they are of any retail business. Databases store your inventory, with details of size, color and stock. They also store your prices, your cost and revenue information, customer details and much more.
All this means is that whether a customer is using the web storefront, or you’re working in the admin area, Magento will be making a lot of database access and read operations and in many instances they will be identical queries each time a page is viewed with few changes in the queries over time.
The work Magento has to do in the database starts with the parsing of the query followed by retrieval of the data from the storage media. Too many queries will slow down access to the server.
One way of dealing with this is MySQL’s built-in configuration parameter known as ‘query_cache_size’. It allows the database to store the results of the query in memory rather than on the storage media—ultimately, a much faster route of access.
How many queries Magento can store in this cache depends on the amount of memory available on your MySQL database server. In most common hosting environments this will be around 64 MB stored as RAM. As far as database query results go, this is a very large amount.
One early e-commerce site I created for Nu Profile contained over 70,000 products and variations. When we enabled the query cache, performance increased by more than 10 times. Overall, performance increase will be between 100-1000% on most sites utilizing the database query cache.
The Nu Profile site also showed that, in a product database of over 70,000, a non-cached query returned in 11 seconds. Once queried and stored in the cache, it returned in just one second.
Dedicated servers
Even if you have the query cache enabled, depending on your hosting environment, it will be cleared every time the MySQL server is restarted. This means it will have to be rebuilt query by query from scratch after each servers restart.
You can avoid this by having a dedicated hosting environment for your e-commerce site. This provides a much more stable MySQL database service and won’t clear your query cache after each restart.
Shared hosting environments, while less expensive, provide database servers which could be bounced daily or even hourly in some cases. This means your cache could be constantly cleared.
How to Configure Query Caching
To configure your MySQL database query cache, you need to access the MySQL command prompt in the control panel area of your hosting environment. Once inside the command prompt, type the following command:
mysql> SHOW VARIABLES LIKE 'have_query_cache';This should return the following:
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| have_query_cache | YES |
+------------------+----------+What you’ll notice is that the ‘have_query_cache’ will be set to YES. This doesn’t necessarily mean that query caching is enabled since on a standard MySQL binary this value is always YES. To verify that query cache is actually operational, type the following into the command prompt:
mysql> SHOW VARIABLES LIKE 'query_cache_size';The result shows a respectable 64 MB available to the query cache.
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 67108864 |
+------------------+----------+
1 row in set (0.00 sec)If the cache was disabled, the query would return the following:
+------------------+----------+
| Variable_name | Value |
+------------------+----------+
| query_cache_size | 0 |
+------------------+----------+
1 row in set (0.00 sec)It’s relatively simple to make the necessary changes to enable the query cache, as long as you have root access. Open the my.conf file in a text editor and find the [mysqld] section and type in a value for the query cache size.
query_cache_size=32MSetting up the MySQL query cache and ensuring it is on a dedicated server are two of the most important performance enhancing tweaks you can make to increase the speed of your Magento-based e-commerce sites. It’s also relatively simple to implement.
However, there are other parameters that you should consider when configuring your MySQL server. These include the ‘query_cache_type’, ‘query_cache_min_res_unit’ and ‘query_cache_limit’. It’s important to familiarize yourself with all these configuration parameters before implementing your query cache configuration.
When first starting out an e-commerce venture using Magento, tweaks like this may not seem to make much difference to your customers’ experience. However, as your website grows, and database queries start clogging your system, you’ll need to look at these areas to fix performance issues.
kazizJai Paul is a programmer and tech writer. Besides his freelance writing, he spends his time running several online ventures. He consults on all things related to web development.
