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
6
  • 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 at 1:46
  • Additional information request. From each instance - MariaDB 10.5.8 and 10.5.9 Your frequency of using ANALYZE table_names; for current STATS? 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 C) SHOW GLOBAL VARIABLES; 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 tuning analysis to provide suggestions. – Wilson Hauck Apr 8 at 21:42
  • @WilsonHauck edited the main post, added two pastebins for SHOW GLOBAL VARIABLES of both servers, I can do the rest if still required but i feel like that may have the best shot at figuring this out? – FTLRalph Apr 9 at 19:57
  • 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. – Wilson Hauck Apr 10 at 2:29
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 – FTLRalph Apr 6 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. – Bill Karwin Apr 6 at 21:51
  • Thanks, I tried that earlier, did just again, just says "OK" but no change in anything. – FTLRalph Apr 6 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. – Bill Karwin Apr 6 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? – FTLRalph Apr 6 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, privacy policy and cookie policy

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