WordPress
Article

Database Changes to Improve WordPress Performance

By Shaumik Daityari

If you’re starting a website – be it a news site, a blog or an ecommerce site, chances are that you consider using WordPress. Once users begin to warm to the website as it gains popularity, core WordPress might not be enough to handle the incoming traffic efficiently. You will then need to scale up your website to handle the higher volume of incoming connections.

There are many ways of scaling your WordPress website, but we will focus on database tweaks in this post. WordPress uses MySQL as a database and this post assumes you are familiar to the database structure of WordPress.

As a precaution, you should never perform database queries on your server directly. Always keep proper backups before attempting to perform any actions mentioned here.

Clean Up

For the purpose of backup and security, WordPress stores a lot of data in the database that may not be directly visible in to the end user. For instance, WordPress saves every revision for your published posts, pages and drafts in the database. Comments which are yet to be published are also stored in the database. Unused tags, categories, dead links and media are stored in the database too.

When your website is new, all this data may not cause a significant decrease in WordPress performance, but as the size of your website grows, this unnecessary data can increase the size of your database, which in turn, makes queries slower.

You could turn off revisions for your posts, which generally take up the greatest amount of space in your database compared to the other junk data, but I would suggest you periodically clean up your database as revisions may come in handy sometimes. Here’s a list of 10 steps to clean up your WordPress database, which includes deleting all unnecessary data.

Defragment

When you add a few entries, delete some others, and add some more, the data doesn’t necessarily get stored sequentially in the same place. They may take up different sectors in the hard drive. These fragments are logical data units, without any individual meaning, but as a whole constitute the complete database. There are many types of fragmentation: horizontal, vertical and mixed or hybrid.

Although the operating system and MySQL take care of the fragmentation while writing or reading data, a high degree of fragmentation can lead to a higher execution time, thus making your site slower.

The easiest way to defragment a MySQL database is to use phpMyAdmin, which provides a GUI for performing this, as well as many other common database administration tasks.

If you want to defragment a single table, you can perform an empty ALTER TABLE command. What it essentially does is rebuild the table from scratch, therefore, all the data is stored at the same place. To defragment the whole database, take an SQL dump, drop the tables and restore the dumps – again, rebuilding all of the tables. Defragmenting helps in reducing indexing times.

Database Caching

In WordPress, a request is first served by a web server (typically Apache), then handed over to PHP, which processes the requests, extracts or manipulates some data in the database and then displays the results. When there are a lot of incoming requests on a site serving the same content on every request, it is inefficient to process the request and extract the same data from the database every single time. This is where caching comes in. There are many ways to cache data, but in this post, we will talk about database caching.

Broadly speaking, there are two ways to perform database caching. You can either cache full tables, or the results of SQL queries. There are various products that help you perform database caching, such as memcached. However, I would suggest you use a WordPress plugin to perform database caching unless you are experienced in this field.

A good plugin to perform database caching is W3 Total Cache, although it does many other types of caching too.

Alternately, you can cache your custom queries too. For example, if you have a query that displays the most active users, you do not need to run it every time WordPress serves a page (since it’s supposed to change rather infrequently). You can therefore make the query run only after certain intervals of time using the Transients API. Here’s a guide to using the Transients API to cache your queries in WordPress.

Scaling

Are you still searching for a solution? Have none of the other methods helped you tackle the enormously high traffic that you receive? Don’t worry, we saved the best for last.

Scaling databases is important in high availability architecture. One technique of scaling your databases is using the master-slave replication.

Master Slave replication
Source – MySQL performance blog

In Master-Slave replication, you have one master and one or many slaves. Each slave has a copy of the master’s data. A web application may read and write to the master, but slaves allow only read operations. Such a setup is useful when your application is expected to handle an unusually high number of read operations. If the master fails, all your read operations would work normally, but write operations would fail until a new master is appointed.

The master-slave system is not efficient for applications that involve write operations as it can lead to inconsistency. For instance, two different slaves can give different read values if one of them has not been updated to the latest state of the master.

You can achieve master slave replication in WordPress using the plugin HyperDB, and HAProxy as a load balancer. Here’s a tutorial detailing the steps to set up this configuration on DigitalOcean – you can apply the same techniques to any provider.

Final Thoughts

We discussed many ways that could help you prepare for the next surge of traffic on your site. Were you successful in handling the new visitors? Did this tutorial help you? Did you use a new technique not mentioned here? Feel free to let us know in the comments below!

  • http://about.me/jdesignz Jhay G.

    Good read. Thanks for sharing!

    • http://dada.theblogbowl.in/ Shaumik Daityari

      You are welcome Jhay :)

  • http://www.bigbluesquid.com Mike Thompson

    Hi Shaumik, this is an excellent article and very helpful. The image cleanup in the 10 steps returned almost 25,000 items with a little over 300 as obsolete. If you’re using Maps Marker Pro, beware that icons are seen as obsolete as they’re not tied to a post and moving them will remove the icons from your maps, but as you mentioned, a simple file restore fixes them again.
    The database fail over and load balancing will make life easier too as my site is seeing month on month increases in traffic.

    • http://dada.theblogbowl.in/ Shaumik Daityari

      Hi Mike,

      Glad you liked it!

  • http://www.brettwidmann.com Brett Widmann

    Very awesome article, Shaumik! I also suggest switching tables from MyISAM to InnoDB (presuming that InnoDB isn’t the default on your server). This helped me out considerably with a site that I manage.

  • Euclides Perez

    Really good article, cool tips!

Recommended

Learn Coding Online
Learn Web Development

Start learning web development and design for free with SitePoint Premium!

Instant Website Review

Use Woorank to analyze and optimize your website to improve your website to improve your ranking!

Run a review to see how your site can improve across 70+ metrics!

Get the latest in WordPress, once a week, for free.