Improving Magento Performance – MySQL query cache – Part 1

Specifically we’ll discuss improving Magento performance. So, the Magento MySQL Version can play an important role for optimizing store performance. Further both merchants and developers have wide interest to increase the speed which is good for the business.
So, choosing the right Magento MySQL version is necessary for merchants. Because it helps to configure MySQL database server properly. Further a MySQL configuration requires minimum technical expertise. For example: Hardware, Primarily & Ram (Memory) etc. So, we’ll discuss only few settings for Magento MySQL version that can boost the performance instantly and requires less technical understanding.

Enjoy lighting fast disk usage and performance with our upto 10 Gbps SSD coupled hosting server. Also a reason behind our success with comparison other providers. The comparison is here. So, contact us for more information and support.

Tanzia Farin Chy – Aspiration Hosting

MySQL’s cache queries configuration parameters

magento mysql version

Mage Commerce requires database access and read operations like any other database application. As Mage does different queries every time a page has been viewed. Though the changes are negligible. Hence a series of database queries happens every time a Mage page is loaded. So, the DB server comes in to operate often.

How Query works?

Firstly query execution is initiated and parsed in the beginning. Next the query retrieves data from storage then sorts and manipulates if required. Finally it shows results to the client. In this case, slower disk access might cause a slower response in return of the query made.

Magento MySQL version – Options to speed up query

However, Mage has a default parameter named query_cache_size. Hence, it refers MySQL server to save the query results in memory. So, this is a way faster process than access in to the disk. All over the total of query results and the size of cache depends on MySQL database server available memory status.
Generally, a 1 GB RAM hosting allows a 64 MB or might be large space for caching query made on server. As a result it allows 64 MB fast RAM to store queries each time the page loads. So, it’s seems big amount of data.

Bigger is not always the better

Now it’s time to turn over the opposite side of the coin. As invalidating a subset of a big query cache can also take long time to execute. In the meantime, no other operations can take place and server becomes efficiently unresponsive. So, the patch addresses the issue that causes freezing by moving locks. Also while the invalidation takes place the query cache is disabled.

So large query cache sizes should be avoided. Because large query cache size means equally large impact on data validation so MySQL performance with changes in the table. Hence results cache is no longer valid for the set of data. So, use appropriate cache size rather using larger sizes.

Case Study:

Finally large Mage sites with 800k products provides 10x more speed while the query cache remains disabled in 64 MB limit. Because almost half capacity of cache remains empty and make scope for cache growth up to 64 MB.
In conclusion, the performance increment may differ up to 500% or even more depending on query made and disk access over time.

Currently viewing : Part 01|Part 02|Part 03