Enhancing Performance in WordPress by Moving from MyISAM to Innodb

by Pythian Marketing on May 30, 2011 12:00:00 AM

Recently I was dealing with some locking issues in databases supporting WordPress. For those who don't know it yet WordPress is a really cool open source tool for easy blogging. This tool is really easy to install (just drag and dropping in your home directory) and making it really easy for you to get up and running. As a part of this easy installation there are some things that aren't best practices for good performance and scalable sites. These limitations are related to the use of MyISAM as a storage engine. This engine was for many years the default storage engine in MySQL. It changed, fortunately, with 5.5, but in previous installations MyISAM will be there for use. MyISAM storage engine is really good at retrieving data but: - it doesn't support concurrency - all operations causes table level locking (this is not entirely true but mostly) - it's not crash-safe (you'll hate it during a crash) Let's focus on the first 2 things. What could happen if you have a really visited site? Simply, with normal table degradation (data and index fragmentation) your site will become very slow to respond. Trust me, I've seen this situation, especially during your peak of usage when concurrency is critical. But (and there always is a but), there is good news for those who have these issues with locking during post or consulting them: Wordpress supports InnoDB! What does this mean? Well, InnoDB is the MySQL storage engine used in high concurrency, high stability and high performance scenarios. Ok, so now you are thinking how can you do this in your environment? Let's try some different approaches: 1- If you have already installed Wordpress these are 2 easy scripts for getting the list of command to perform proper changes and make those changes (thanks 619cloud!!):

mysql -uuser -p -e "show tables in db_name;" | tail --lines=+2 | xargs -i echo "ALTER TABLE {} ENGINE=INNODB;" > alter_table.sql

mysql -uuser -p db_name < alter_table.sql 2- If you don't have Wordpress installed yet then you have 2 possible choices to make before starting installation: a- For those of you that are feeling lazy, set the default storage engine by adding the line below to my.cnf file and restart mysql: default-storage-engine=innodb. From this point all new tables will be InnoDB except you set the storage engine in your DDL. b- For those of you who want to make extra work for yourselves and don't want to change your environment, you can modify the installation files that create and modify the schema used by Wordpress. How you can do this? Easy. Let's take a walk on this small how to. Run this shell command inside the Wordpress folder: find *.php | xargs -n1 sed -i -e 's/) ENGINE=InnoDB $charset_collate;/) ENGINE=innodb $charset_collate;/g' This will change all files that have DDL commands to use ENGINE=innodb during table creation. From now on, every time you install a new Wordpress instance you will have an scalable, high performance and user concurrent application.

Topics: Security Linux Site Reliability Engineering Technical Track DevOps Puppet

No Comments Yet

Let us know what you think

See all

Share this

Enhancing Performance in WordPress by Moving from MyISAM to Innodb

Share this

No Comments Yet

Subscribe by email