Some tips on SQL performance optimization for Magento

The post is supposed to shed some light on magic of our DBA doing Magento SQL database optimization tasks.

Fitting all data in memory

Our typical optimization tasks starts from output of show status and show global variables commands and size of InnoDB buffer pool:

SELECT engine,
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"
FROM information_schema.tables
GROUP BY engine;

Every big table is checked, optimized and reduced or archived if possible.

Often, InnoDB buffer pool is set to default, pretty low 128MB or (twice higher, nonetheless useless) 256MB. And usually there is much more data in Magento’s tables. Monstrous select below show a nice feel of what InnoDB buffer pool should be set to.

SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
    SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
    FROM
    (
        SELECT SUM(data_length+index_length)*1.1*growth RIBPS
        FROM information_schema.tables AAA,
        (SELECT 1 growth) BBB
        WHERE ENGINE='InnoDB'
    ) AA
) A;

The important thing with setting InnoDB buffer pool to stay within allowed memory limits. We always recommend to have a separate server/cloud instance for MySQL server to let it use all available memory, and not to struggle with Apache/PHP for every bit of it.

Slow queries and performance schema

The next step is to enable slow log or performance schema. It really makes sense to collect such data for significant period, at least a week or so.

 SET global slow_query_log = 'ON';
 SET global log_queries_not_using_indexes = 'ON';
 SET global slow_query_log_file ='/var/log/mysql/slow-query.log';
 SET global long_query_time = /* get them all */ 0;

As you see we collect all queries in slow query log, as sometimes big troubles are caused by tiny weeny bits, huge number of perky nano-second queries flying out from some superb extension resulting in global slowdown.

Collected query log is parsed with a special tool, all queries are sorted, weighted and report is presented to our DBA for detailed review. During review some tables are adjusted adding missing indexes. The most common adjustment here is indexes on core_url_rewrite table, the most important table in overall Magento performance as every request from client’s browser results in lookup in core_url_rewrite table for further routing.

Query cache

The most important thing with query cache is to not set it too big. 256MB is usually enough for Magento.

SHOW global variables like 'query_cache%';

Note: The larger query cache is, the more time and resources spent to scan it for every lookup.

Let it keep more small queries.

Threads pool cache

With Magento, like with every PHP application this mostly depends on MySQL driver used to handle connections to server. Modern [mysqlnd] have pretty fine defaults and great performance. Problems usually come from old PDO drivers with keep-alive disabled or forced new connection per every request option.

Feel your database need some tuning? Get in touch!