0

Edit:

Old server SHOW GLOBAL VARIABLES: https://pastebin.com/raw/8GPTpub0

And for the new (problematic) server: https://pastebin.com/raw/XnfbFzh7


This morning I moved my WP website from one server to another, similarly configured with MariaDB 10.5.8 on the old, and 10.5.9 on the new.

According to slow-query log, some queries are going very slow. Here is one.

SELECT DISTINCT a.id  FROM wp_bp_activity a  
WHERE a.type IN ( 'activity_update','created_group','new_avatar' ) 
  AND a.is_spam = 0 
  AND a.hide_sitewide = 0 
  AND a.type NOT IN ('activity_comment', 'last_activity') 
ORDER BY a.date_recorded DESC, a.id DESC LIMIT 0, 16;

It takes 5,6,7 seconds. Running the exact, same query on the old server, it takes 0.0002 seconds.

Here is an EXPLAIN from both servers:

enter image description here

Why is one "index" and the other "index_merge" using different keys? It's the same query on the same table.


Both tables are nearly identical, one was live earlier today so it has relatively updated information.

The OLD server DDL of the table:

CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060622 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The NEW server DDL:

CREATE TABLE `wp_bp_activity` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) NOT NULL,
 `component` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `type` varchar(75) COLLATE utf8mb4_unicode_ci NOT NULL,
 `action` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
 `primary_link` text COLLATE utf8mb4_unicode_ci NOT NULL,
 `item_id` bigint(20) NOT NULL,
 `secondary_item_id` bigint(20) DEFAULT NULL,
 `date_recorded` datetime NOT NULL,
 `hide_sitewide` tinyint(1) DEFAULT 0,
 `mptt_left` int(11) NOT NULL DEFAULT 0,
 `mptt_right` int(11) NOT NULL DEFAULT 0,
 `is_spam` tinyint(1) NOT NULL DEFAULT 0,
 PRIMARY KEY (`id`),
 KEY `date_recorded` (`date_recorded`),
 KEY `user_id` (`user_id`),
 KEY `item_id` (`item_id`),
 KEY `secondary_item_id` (`secondary_item_id`),
 KEY `component` (`component`),
 KEY `type` (`type`),
 KEY `mptt_left` (`mptt_left`),
 KEY `mptt_right` (`mptt_right`),
 KEY `hide_sitewide` (`hide_sitewide`),
 KEY `is_spam` (`is_spam`),
 FULLTEXT KEY `content` (`content`)
) ENGINE=InnoDB AUTO_INCREMENT=1060840 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
4
  • You say "tables are nearly identical" -- would you mind pointing out what are the differences, to spare us the sleuthing? How exactly did you "move my WP website from one server to another", particularly, how did you move the database?
    – mustaccio
    Apr 7, 2021 at 1:46
  • Dup of stackoverflow.com/questions/66976164/…
    – Rick James
    Apr 7, 2021 at 4:24
  • The rest of the requested information From each instance - would be very helpful, starting with - 1. Your frequency of using ANALYZE table_names; for current STATISTICS the optimizer will use? 2. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME D) SHOW FULL PROCESSLIST; E) STATUS; G) SHOW ENGINE INNODB STATUS; H) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload analysis to provide suggestions. Apr 10, 2021 at 2:29
  • Please consider posting to pastebin.com TEXT results of SHOW INDEX FROM wp_bp_activity; from the 10.5.8 and 10.5.9 instances. This will also cause rebuild of statistics used by the optimizer. I suspect we will observe significant differences. Apr 10, 2021 at 16:13

2 Answers 2

0

The fast query avoids sorting the result by reading in date_recorded index order matching your ORDER BY. It filters the hard way, comparing your conditions in the WHERE clause to rows one by one, but once it gets enough matching rows to satisfy the LIMIT, it stops scanning the table.

Whereas the slow query tries to use indexes on a couple of columns, thinking this will help. But it ends up having to scan hundreds of thousands of rows to find those that match, then finds the set of rows that matched both index searches (that's the Using intersect note), and finally sorts the large set of rows the hard way, using a filesort, just to return 16 rows and discard the rest.

This is a good counter-example case that shows that searching by index isn't always the best strategy. But the MySQL cost-based optimzer is biased to think that searching by index is usually better.

In this case, you might want to use an index hint to make it choose the date_recorded index.

8
  • i found an article saying a lot of the same. Bit of a pain since this is a WordPress plugin that I'd have to mess around with now to manage its queries. I'm just confused as to why one server is doing it this way, and the other server isn't. If I could tweak that setting, whatever it is, that would be best/easiest Apr 6, 2021 at 21:37
  • The cost-based optimizer chooses indexes in part by the statistics it has for data value frequency. The difference in data between the two servers has convinced the optimizer to behave differently. Or else the statistics are in need of a resampling: run ANALYZE TABLE wp_bp_activity in both servers to make sure. Apr 6, 2021 at 21:51
  • Thanks, I tried that earlier, did just again, just says "OK" but no change in anything. Apr 6, 2021 at 21:56
  • Then it has accurate statistics (as accurate as they can be given they are sampled), but the data is in fact different, leading it to choose indexes differently. Apr 6, 2021 at 21:57
  • Was able to (very hackily) shove in a "USE INDEX(date_recorded" into the proper calls, and performance went up big. So that's great. But I notice there are a lot of smaller hits here and there due to similar reasons. It's almost like, it would be nice to give the date_recorded index more power in the index-choosing process - is that a thing, in any way possible? Apr 6, 2021 at 22:42
0

Analysis of GLOBAL VARIABLES between the 10.5.8 and 10.5.9 instances reveals these potential contributors to 10.5.9 being slower.

In your 10.5.9 configuration, my.cnf [mysqld] section consider

key_buffer_size=402653184  # from 134217728
query_cache_size=29360128  # from 1048576 for higher capacity
query_cache_type=ON  # from OFF to enable query cache

REMOVE thread_cache_size to allow auto calculation of default size.

These changes may get your timing close to the 10.5.8 elapsed time to complete.

THere are many more opportunities detectable after additional requested data is posted and analyzed.

And then of course we have not looked at OS limits that would be revealed with ulimit -a reports of each server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.