Magento Enterprise 1.13 was released yesterday and as we were upgrading one of our projects anyway I gave it a shot. Out of curiosity I enabled logging all SQL queries to the file system before I triggered the upgrade (from version 1.10 to 1.13).

In file lib/Varien/Db/Adapter/Pdo/Mysql.php change protected $_logAllQueries and protected $_debug to true and Magento will log all queries including some extra information to var/debug/pdo_mysql.log.

Using this command I'm counting the number of different query types:

cat pdo_mysql.log | grep '^SQL:' | awk '{print $2}' | sort | uniq -c | sort -nr

This is the result:

There's a nice script out there by Kirill Morozov that parses the previously recorded log file, removes all unneeded queries and combines queries wherever possible:

https://github.com/kirmorozov/Magento-Upgrade-Replay

(or check out my fork that comes with a modman file and allows you to pass the filename in the commandline script: https://github.com/fbrnc/Magento-Upgrade-Replay)

The script will even go one step further and split all the queries into multiple processes. I haven't tried this and it might not be easy to do this correctly while keeping track of all dependencies. If this should work you can reduce your upgrade time significantly. But even running one thread only might be a good improvement taking into account that there is no PHP overhead too.

The idea is to prepare this upgrade log on a copy of the production database and to replay it on the production database reducing the downtime during the deployment to a bare minimum. Depending on the size of your database this can make a huge difference.

Parsing the optimized SQL queries results in this distribution:

Comments

This website uses disqus for the commenting functionality. In order to protect your privacy comments are disabled by default.

Enable Comments