I have a Shop with 50,000 Products multi-linked in 27,000 Categories. (The Database has a products_to_categories index about 950.000)

It is hosted on a good single Root-Server. What changes can I do (In the Code, Database,...) to increase the performance? Cache is active, but the side has a "server respond time" about 5-20 seconds.

Some Ideas? Maybe to improve the Database Index?

  • 1
    How does server response time differ on category/product pages vs cart and checkout? Nov 3, 2014 at 16:41
  • I am assuming you have done things like REDIS and a separate DB server? Nov 3, 2014 at 16:52
  • The server response on every site (also start page) is about >10 seconds. there are not showing any products or modules on the start page, only 1 main- category.
    – Lars
    Nov 3, 2014 at 20:14
  • Ok well in that case the size of the catalog is unlikely to be having much of an effect on performance. While caching, whether that is Varnish or a FPC solution, is a good idea you do need to consider cache misses and uncached areas of the store like cart and checkout. It sounds to me like you do need to review your hosting with a view to upgrading, or at the very least ensure the server setup is optimal. Once performance is acceptable uncached, then implement caching for added performance. Slow cart and checkout is a real sales killer! Nov 3, 2014 at 21:05
  • 1
    HHVM is not yet a good option - although it is definitely worth keeping an eye on, it's not recommended for production installs. Currently it's not on the list of compatible frameworks and there are known issues using HHVM with Magento. Performance gain is actually not that great either (40%-50%) and PHP is catching up fast anyway. Nov 11, 2014 at 7:57

2 Answers 2

  1. MySQL Performance Tuning

    • Great wizard to get you started: https://tools.percona.com/wizard
    • Make sure mysql querycache is turned on and configured
    • After your mysql server has been running for atleast 48 hours, but optimally 30 days, use the MySQL tuner script is very helpful for making adjustments to MySQL
  2. If you are not using Redis or Memcache yet, run you cache folder in tmpfs by mounting your var/cache folder to it.

  3. Use Redis or Memcache.

  4. Make sure you indexing is up to date.


First you need to have a look at what is actually slowing down your site before you look at any other solution. Adding more caching might not solve the problem.


I'd install AOE_Profiler to see + the xdebug profiler this will give you an idea on whats slow in php/magento in terms of code. https://github.com/AOEpeople/Aoe_Profiler https://xdebug.org/docs/profiler


You could enable the mysql slow query log but this just lists the slow queries and doesn't really give you an idea on what code is actually executing the query. However Magento has something hidden in the core that can help. If you have a look at Varien_Db_Adapter_Pdo_Mysql found in lib/Varien/Db/Adapter/Pdo/Mysql.php you can turn on magento's so called slow query log by changing the $_debug flag to true. This will then start logging queries > than the $_logQueryTime property to var/debug/pdo_mysql.log. If you need the stack trace for these queries set $_logCallStack to true. This will help you pin point the bottle necks.

Once you have an idea about whats going on then you can start looking at improving the performance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.