Image
Top
Navigation
January 8, 2014

Partitioning tables to improve WordPress Performance

Wordpress' considerable reliance on MySQL means that optimizing the database often helps in achieving significant speed benefits.

Problem

As we all know WordPress is a database heavy application which poses various scaling and speed challenges especially when your site starts to grow.

We were brought in to rescue such a wordpress portal and we analyzed every layer including:

  • Database
  • PHP
  • CSS
  • JS
  • Images
  • Webserver

Solution

Out of the several performance enhancements that we did on every layer we also decided to partition various wordpress tables to improve their performance after reviewing the slow query log and the total rows in the tables.

Given below is a slow query log summary analyses of the queries that are showing up in the slow log as well as their frequency and other statistics:

Here is the row count for the tables that were considered for partitioning:

  • wp_postmeta (3,049,294) rows
  • wp_term_relationships (571,138) rows
  • wp_posts (230,532) rows

We decided to partition the postmeta and term_relationships tables for now and monitor performance.

Partitioning Strategy

MySQL offers several partitioning options with details provided here. After reviewing the table structures and partitioning options available we decided to partition based on Ranges and used the PRIMARY key of the table for setting up the range.

Here is the table structure:

Here is the partitioning Query for creating the partitions:

After creating the partitions the terminal shows the following:

Over a period of time a DBA can easily modify this structure and continue to add more partitions as needed by using the Alter Table SQL query. A sample is given below:

Results

Upon benchmarking we saw average query times drop by 50% that involved the partitioned tables.

New-Relic Snapshot

Comments

  1. WP_Ninja

    Amazing…..

    I was looking for something like this. Thanks a lot for posting this.

  2. WPfreak

    WOW, I have been looking for something like this. A great way to solve speed issues with wordpress. I will surely try it out and paste the results here.

    Thanks

  3. i need partition wp_posts by month, can u give me example. thanks 🙂

Submit a Comment