Why Optimizing InnoDB is Key for Magento Performance

By December 10, 2011Magento, Website Speed

Why Optimizing InnoDB is Key for Magento Performance

Ron Peled 10:51 pm

As you may know by now, Magento is a great eCommerce platform if not the best out there. However, if you have downloaded and installed it you may have noticed that it does require lots of resources and tweaking to run smoothly. While everything is important, you will find that for the medium size web stores out there DB optimization is key, in particular InnoDB optimization.

Just recently I have switched one of my clients to move from a self managed colo environment to a fully managed hosting environment with one of the big hosting companies. Yes, they are a Magento hosting partner and even came to the Magento Imagine conference back in February 2011. To my surprise, when we received the server for our Magento site, it was delivered ‘stock’ with RedHat linux and LAMP installed. When I asked for optimization on the server, I was again surprised to know that they will not ‘adjust’ parameters for InnoDB without a full research, testing, or hiring a developer to do it – which of course means additional hosting cost at a relatively expensive hourly rate. So, a quick tip here: when you sign up for any Magento hosting verify ahead of time what you are getting, have the details be sent to you via emails so you have a record, and don’t let them oversell you…

Anyway, ‘nough ranting, here are the main parameters for optimizing MySQL InnoDB for Magento:

query_cache_size = 512M
query_cache_limit = 256M

tmp_table_size = 256M
key_buffer_size = 64M
read_buffer_size = 128M
read_rnd_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 128M
myisam_max_extra_sort_file_size = 128M
myisam_repair_threads = 2

innodb_additional_mem_pool_size = 256M
innodb_log_buffer_size = 128M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 0
innodb_buffer_pool_size = 512M
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:256M:autoextend

Notice the above is adjusted for a DB dedicated server with 2GB of RAM. You can play with the parameters if depending on your particular situation.

Ron Peled

About Ron Peled

Builder of things. Builder of teams. Passion: eCommerce & Marketplaces. Magento expert. CTO Mentor.

One Comment

  • Sean says:

    It’s amazing, but true that “Magento Hosting Partners” deliver stock OEM installations without any tuning. I’ve run across dumb minimal defaults left in place. Our first site completely lost the categories on a save after several weeks of work because the log file size had been left at 8MB and was unable to do a rollback when the MySQL server fell flat on its face during a category change and another person doing inventory item entry.

    Three other items that cause MySQL server to “go away” that need set are:
    max_allowed_packet = 16M
    wait_timeout = 1800
    connect_timeout = 120