Database Administration is usually not my strength, but one friend asked me if I can help him with his project. He is running a WordPress (MySQL) website with WooCommerce. The postmeta table stores 160,000+ records and the postmeta table 3,000,000+. Especially the latter table is that large, because the way WordPress/WooCommerce stores product attributes seems to be a bit inefficient.
History
Regardless of this huge database (for WordPress standards), the website was running quite smoothly on a shared hosting account, except of the search feature. It inner joins both large tables to find matches in all product titles, descriptions, attributes etc.
Sometimes it took 15 seconds to load. Sometimes it did not bother to load at all and crashed the server.
Current Status
Pretending to be smart, I suggested to move the website to a VPS and I would set it up for him.
So I configured two VPS droplets (DigitalOcean), both running Ubuntu 14.04.3 x64. The first droplet serves the files (Nginx + PHP FPM), the second droplet only runs the MySQL server.
Result: A faster website, but still a slow search. Now 5-10 seconds.
Actual Question
So before I talk myself into more trouble, I would like to know what would be the best option to speed up the search.
Please note that the WordPress search is based on a single query. I assume that we have the following options and would love to hear some feedback, perhaps even other smarter suggestions:
- Boost Server Performance: Increasing the performance of the droplet that runs the MySQL server (currently 1 CPU / 1GB RAM). My friend's budget would only allow an upgrade to the next level: 2 CPUs / 2 GB RAM
- Database Replication: Instead of increasing the performance of the droplet that runs MySQL, we configure a third droplet as MySQL slave to run in a Master and Slave configuration with the current database droplet.
- Database Raid10 System: Just a wild guess… (please bare in mind I am far from being an expert on this topic) I am not even sure if DigitalOcean allows a Raid configuration.
As requested in the comments:
The Query (11.0650 seconds execution time)
SELECT SQL_CALC_FOUND_ROWS wp_6bv56cvp82_posts.ID
FROM wp_6bv56cvp82_posts
INNER JOIN wp_6bv56cvp82_postmeta
ON ( wp_6bv56cvp82_posts.ID = wp_6bv56cvp82_postmeta.post_id )
INNER JOIN wp_6bv56cvp82_postmeta AS mt1
ON ( wp_6bv56cvp82_posts.ID = mt1.post_id )
WHERE 1=1
AND (((wp_6bv56cvp82_posts.post_title LIKE '%tub%')
OR (post_excerpt LIKE '%tub%')
OR (wp_6bv56cvp82_posts.post_content LIKE '%tub%')))
AND ( wp_6bv56cvp82_postmeta.meta_key = 'total_sales'
AND ( ( mt1.meta_key = '_visibility'
AND CAST(mt1.meta_value AS CHAR) IN ('visible','search') ) ) )
AND wp_6bv56cvp82_posts.post_type = 'product'
AND (wp_6bv56cvp82_posts.post_status = 'publish'
OR wp_6bv56cvp82_posts.post_status = 'private')
GROUP BY wp_6bv56cvp82_posts.ID
ORDER BY wp_6bv56cvp82_postmeta.meta_value+0 DESC, wp_6bv56cvp82_posts.post_date DESC
LIMIT 0, 10
Table Definition
Both tables are based on InnoDB.
wp_6bv56cvp82_postmeta structure (2,592,840 rows):
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| meta_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_id | bigint(20) unsigned | NO | MUL | 0 | |
| meta_key | varchar(255) | YES | MUL | NULL | |
| meta_value | longtext | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
wp_6bv56cvp82_posts structure (130,063 rows):
+-----------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | MUL | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
| post_date_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content | longtext | NO | | NULL | |
| post_title | text | NO | | NULL | |
| post_excerpt | text | NO | | NULL | |
| post_status | varchar(20) | NO | | publish | |
| comment_status | varchar(20) | NO | | open | |
| ping_status | varchar(20) | NO | | open | |
| post_password | varchar(20) | NO | | | |
| post_name | varchar(200) | NO | MUL | | |
| to_ping | text | NO | | NULL | |
| pinged | text | NO | | NULL | |
| post_modified | datetime | NO | | 0000-00-00 00:00:00 | |
| post_modified_gmt | datetime | NO | | 0000-00-00 00:00:00 | |
| post_content_filtered | longtext | NO | | NULL | |
| post_parent | bigint(20) unsigned | NO | MUL | 0 | |
| guid | varchar(255) | NO | | | |
| menu_order | int(11) | NO | | 0 | |
| post_type | varchar(20) | NO | MUL | post | |
| post_mime_type | varchar(100) | NO | | | |
| comment_count | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------------------+----------------+
The EXPLAIN output of the above mentioned query:
+----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | mt1 | ref | post_id,meta_key | meta_key | 767 | const | 70052 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | wp_6bv56cvp82_postmeta | ref | post_id,meta_key | post_id | 8 | globalfluid.mt1.post_id | 13 | Using where |
| 1 | SIMPLE | wp_6bv56cvp82_posts | eq_ref | PRIMARY,type_status_date | PRIMARY | 8 | globalfluid.wp_6bv56cvp82_postmeta.post_id | 1 | Using where |
+----+-------------+------------------------+--------+--------------------------+----------+---------+--------------------------------------------+-------+----------------------------------------------+
Table Structure (this time with show create)
wp_6bv56cvp82_postmeta structure:
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_6bv56cvp82_postmeta | CREATE TABLE `wp_6bv56cvp82_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_ci,
PRIMARY KEY (`meta_id`),
KEY `post_id` (`post_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=2895543 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
wp_6bv56cvp82_posts structure:
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| wp_6bv56cvp82_posts | CREATE TABLE `wp_6bv56cvp82_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
`post_password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`)
) ENGINE=InnoDB AUTO_INCREMENT=136320 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Best Answer
WordPress
WordPress was not designed with scaling in mind. (Caveat: What I say here is based on looking at innards several years ago; things may have changed.) There is no "read-write" separation, so scaling reads via Replication would be a major undertaking. Each "user" has his own database with 13+ tables. For large installations, this leads to OS issues in searching the filesystem, and with MySQL issues with things like
table_open_cache.On the other hand, your question implies one huge user, not a huge number of users. And, I can't tell how much of the damage comes from WooCommerce instead of WordPress, itself.
Big table inefficiencies
Here are issues that make the search implementation inefficient for big tables:
LIKE '%tub%'is inefficient. It cannot use any index, so it must scan the entire table.ORadds insult to injury.FULLTEXTis the best alternative, but it comes with caveats, and requires changes to both the schema and theSELECT."Key-value" store ("EAV") does not scale well.
postmetais such a schema. And it leads to kludges likemeta_value+0andCAST(mt1.meta_value AS CHAR).The complexity of the
GROUP BYplusORDER BYmake it impossible use any index to prevent finding all the rows before delivering onlyLIMIT 0,10. However, that means that the extra burden ofSQL_CALC_FOUND_ROWSis minimal.Having
LONGTEXTformeta_valuevirtually eliminates any optimization relating to it.This would help some:
INDEX(post_id, meta_key). (Tacking onmeta_valuewould be even better, but it is not possible because ofLONGTEXT.) This may be the second best 'quick fix'.Other notes
One MySQL connection will use only one CPU. So, as long as you do not have concurrent searches, there is no need for more than one CPU core. Even if you have multiple simultaneous searches, there is some overlap since some of the effort is I/O.
1GB of RAM? You are probably terribly I/O-bound? You must have
innodb_buffer_pool_sizeset to a terribly small value? (Otherwise you would be swapping, which is terribly bad for MySQL.) 1GB is not "tiny", it is "minuscule"! Go for 4GB at least. Then set the buffer_pool to 1500M. This one change is the biggest "quick fix". Deal with RAM before RAID. Get metrics on CPU and I/O usage, if you can.Of the 2.5M meta rows, how many have
meta_key = '_visibility'. That's what theEXPLAINdecided to start with. It estimated 70K, but that could be seriously off.The schema is about twice as bulky as it needs to be, mostly because of sloppy datatypes. Smaller --> more cacheable --> less I/O --> faster. However making the change would be tedious and maybe impossible because of WordPress and/or WooCommerce.
Prototyping
In programming it is often good to start with a "prototype" to prove the various design concepts and implementation details. Prototyping often involves "off the shelf" tools. When the data size grows and the accesses increase, it is time to throw out the "prototype" and design a custom system based on what you learned. You are past that time.