How to optimize database to speed Magento up | Small tips
Posted by antoine on 09 November 2011 08:22 AM
Trying to provide quality support of all our extensions, we work with a great number of Magento stores. Every customer is unique for us and we try to offer individual approach to everyone. We also examine each Magento store performance and the reasons of slow work. We found some common reasons, which can be applied almost to every store. The problem is quite simple. It’s database default settings that don’t allow your Magento to work like you want. These settings are:
Most Magento stores have the default values for them (8Mb, 1Mb and 8Mb accordingly). We’ll try to describe what settings mean and how you can change them.
1) Let’s start with “key_buffer_size”. It is responsible for the buffer of the indexes of MyISAM database. These indexes are used in Magento only for fulltext search products in the tables like “catalogsearch_fulltext” and others. That’s why we recommend to set the size of “key_buffer_size” setting to be equal to the size of this table (“catalogsearch_fulltext”). If you regularly add more products, the size of this table will be increased. It’s better to specify the value for “key_buffer_size” with some reserve for such changes.
If you setup several Magento on one server, this setting should be the sum of sizes of “catalogsearch_fulltext” table in all your Magento stores.
2) The next setting is “innodb_buffer_pool_size”. It’s one of the most important parameters for work with innoDB tables and indexes.
Example: if your server has 1Gb RAM, it’s better to allow the following settings to use 30-40% of this memory: innodb_buffer_pool_size + innodb_additional_mem_pool_size + key_buffer_size
innodb_buffer_pool_size = 320 Mb
There are also many other minor settings that you should check. But for that we recommend to contact your hosting provider or system administrator. Please note, that none extension will speed up your Magento if you do not optimize your database.