Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress queries extremely slow after updating database from MySql to MariaDB

I used CPanel elevate to update my CentOS 7 system to Almalinux. The database was upgraded from MySql to MariaDb 10.x

The database queries are taking 10x of the time they used to take, specifically those queries where WHERE is used and are non-indexed. My mysql slow log has over 500 entries since I updated the OS yesterday, whereas before updating, this had 10-15 entries per day.

For example – this query used to take 8 sec previously and now takes 80 secs –

 SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '_billing_phone' AND `meta_value` LIKE '9999999999';

The database is around 30 GB. The largest table wp_postmeta around 10GB. The size of database was same on MySQL before the OS update.

I have tried running the following commands on the database –

ANALYZE TABLE wp_postmeta PERSISTENT FOR ALL;
ANALYZE TABLE wp_postmeta;

The issue was not resolved by these commands. How to resolve this?

Tags:

2

Answers


  1. 0

    If you use LIKE the query is unable to use an index and the entire table is scanned.

    MariaDB [test]> explain SELECT * FROM `wp_postmeta` WHERE `meta_key` LIKE '_billing_phone' AND `meta_value` LIKE '9999999999';
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table       | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | wp_postmeta | ALL  | NULL          | NULL | NULL    | NULL | 9899 | Using where |
+------+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.001 sec)

    If = is used, it can:

    MariaDB [test]> explain SELECT * FROM `wp_postmeta` WHERE `meta_key` = '_billing_phone' AND `meta_value` = '9999999999';
+------+-------------+-------------+------+---------------+----------+---------+-------+------+------------------------------------+
| id   | select_type | table       | type | possible_keys | key      | key_len | ref   | rows | Extra                              |
+------+-------------+-------------+------+---------------+----------+---------+-------+------+------------------------------------+
|    1 | SIMPLE      | wp_postmeta | ref  | meta_key      | meta_key | 258     | const | 1    | Using index condition; Using where |
+------+-------------+-------------+------+---------------+----------+---------+-------+------+------------------------------------+
1 row in set (0.001 sec)

    This will have significant speedups on large tables.

    Login or Signup to reply.
  2. 0

    Does the meta_key really start with an underscore (_)? That is a wild card, thereby making avoiding indexing.

    On the other hand, if it is exactly '_billing_phone', then change from LIKE to = so that an index can be used.

    WP, by default, provides poor indexes; add this plugin to help: WP Index Improvements

    Also, check to see if innodb_buffer_pool_size shrank during the upgrade. That VARIABLE should be set to about 70% of available RAM. If it is too small, there might be a lot of I/O, thereby explaining the 10x slowdown.

    The number of entries in the slowlog depends heavily on the value of long_query_time. I like a value of 1 for that VARIABLE.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search