Partitioning tables to improve WordPress Performance

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:

# Profile
# Rank Query ID           Response time  Calls R/Call  V/M   Item
# ==== ================== ============== ===== ======= ===== =============
#    1 0xC1623E6807CB8BE5 207.3160 45.9%    15 13.8211  1.83 SELECT wp_posts wp_term_relationships wp_term_taxonomy
#    2 0x5167A4457BE9231B 100.7565 22.3%    10 10.0756  0.83 SELECT wp_posts wp_postmeta
#    3 0xB6BD43F5DAA5EF11  67.0009 14.8%    17  3.9412  1.33 SELECT wp_posts wp_term_relationships wp_postmeta
#    4 0x2CCE270A1F4865ED  49.2758 10.9%    27  1.8250  0.15 SELECT wp_posts
#    5 0x626B2BE09C53BCF4   8.4502  1.9%     1  8.4502  0.00 SELECT wp_posts wp_postmeta wp_term_relationships
# MISC 0xMISC              19.0393  4.2%  1752  0.0109   0.0 <19 ITEMS>

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:

mysql> describe wp_term_relationships;
+------------------+---------------------+------+-----+---------+-------+
| Field            | Type                | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+---------+-------+
| object_id        | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_taxonomy_id | bigint(20) unsigned | NO   | PRI | 0       |       |
| term_order       | int(11)             | NO   |     | 0       |       |
+------------------+---------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Here is the partitioning Query for creating the partitions:

ALTER TABLE wp_term_relationships PARTITION BY RANGE(object_id) 
( 
PARTITION p0 VALUES LESS THAN (50000), 
PARTITION p1 VALUES LESS THAN (100000), 
PARTITION p2 VALUES LESS THAN (150000), 
PARTITION p3 VALUES LESS THAN (200000), 
PARTITION p4 VALUES LESS THAN (250000), 
PARTITION p5 VALUES LESS THAN (300000),
PARTITION p6 VALUES LESS THAN (350000)
);

After creating the partitions the terminal shows the following:

 ls -al | grep wp_term_relationships
-rw-rw----  1 mysql mysql  16777216 Jan  8 15:34 wp_term_relationships#P#p0.ibd
-rw-rw----  1 mysql mysql  15728640 Jan  8 15:34 wp_term_relationships#P#p1.ibd
-rw-rw----  1 mysql mysql  17825792 Jan  8 15:34 wp_term_relationships#P#p2.ibd
-rw-rw----  1 mysql mysql  17825792 Jan  8 15:34 wp_term_relationships#P#p3.ibd
-rw-rw----  1 mysql mysql  17825792 Jan  8 15:34 wp_term_relationships#P#p4.ibd
-rw-rw----  1 mysql mysql  14680064 Jan  8 15:34 wp_term_relationships#P#p5.ibd
-rw-rw----  1 mysql mysql    901120 Jan  8 16:52 wp_term_relationships#P#p6.ibd
-rw-rw----  1 mysql mysql      8666 Jan  8 15:33 wp_term_relationships.frm
-rw-rw----  1 mysql mysql        48 Jan  8 15:33 wp_term_relationships.par

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:

ALTER TABLE t1 ADD PARTITION (PARTITION p7 VALUES LESS THAN (500000));

Results

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