Using 100% CPU while creating indexes after enabling Index for Speed plugins

[petergerard]

We added both Index WP MYSQL for Speed and Index WP Users for Speed to our woocommerce site, which had become slow due to very large usermeta (1.6MM rows) and postmeta (3.8MM rows) tables. (~25K customers with ~130K orders)

Initially we were very happy with the plugins since postmeta and usermeta join queries that were taking 10-20s are now < 1s.

However, less than a week later our site is frequently reaching 100% CPU. Our host said there were lots of slow queries running and likely causing this:

| Command | Time | State | Info | Progress |
| Query | 721 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 6 OFFSET 4751 | 0.000 |
| Query | 500 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 6 OFFSET 4751 | 0.000 |
| Query | 429 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 4 OFFSET 4751 | 0.000 |
| Query | 228 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 4 OFFSET 4751 | 0.000 |
| Query | 22 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 4 OFFSET 4751 | 0.000 |
| Query | 691 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 16384 OFFSET | 0.000 |
| Query | 716 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 12288 OFFSET | 0.000 |
| Query | 596 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 12288 OFFSET | 0.000 |
| Query | 615 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 8192 OFFSET 5 | 0.000 |
| Query | 491 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 8192 OFFSET 5 | 0.000 |
| Query | 535 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 6144 OFFSET 4 | 0.000 |
| Query | 421 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 6144 OFFSET 4 | 0.000 |
| Query | 205 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 4096 OFFSET 2 | 0.000 |
| Query | 244 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 3072 OFFSET 2 | 0.000 |
| Query | 141 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 3072 OFFSET 2 | 0.000 |
| Query | 17 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 3072 OFFSET 2 | 0.000 |
| Query | 185 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 2048 OFFSET 1 | 0.000 |
| Query | 22 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 2048 OFFSET 1 | 0.000 |
| Query | 434 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 2048 OFFSET 1 | 0.000 |
| Query | 67 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1536 OFFSET 1 | 0.000 |
| Query | 311 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1536 OFFSET 9 | 0.000 |
| Query | 288 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1536 OFFSET 9 | 0.000 |
| Query | 10 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1024 OFFSET 7 | 0.000 |
| Query | 337 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1024 OFFSET 6 | 0.000 |
| Query | 244 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1024 OFFSET 6 | 0.000 |
| Query | 70 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 768 OFFSET 53 | 0.000 |
| Query | 14 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 768 OFFSET 53 | 0.000 |
| Query | 138 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 512 OFFSET 35 | 0.000 |
| Query | 97 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 512 OFFSET 35 | 0.000 |
| Query | 264 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 512 OFFSET 30 | 0.000 |
| Query | 184 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 384 OFFSET 26 | 0.000 |
| Query | 160 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 384 OFFSET 26 | 0.000 |
| Query | 49 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 384 OFFSET 26 | 0.000 |
| Query | 131 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 96 OFFSET 115 | 0.000 |
| Query | 67 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 96 OFFSET 115 | 0.000 |
| Query | 12 | Creating sort index | SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 96 OFFSET 115 | 0.000 |

We had the User Indexes set to rebuild daily at 12:25am, so I wondered if this was related (even though the CPU was hitting 100% at more like 2pm).

I uploaded metadata with this ID: VCsh5YMX

Any tips on how to avoid maxing CPU with these index plugins would be gratefully received.

[rjasdf]

What is the purpose of    SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 6144 OFFSET 4Perhaps that query was truncated? Why is it walking through the entire table? Please provide SHOW CREATE TABLE wp_usermeta` so we check that the index was correctly applied by the plugin. Rick Jamesemail: @.***

On Tuesday, September 13, 2022 at 02:21:14 PM PDT, Peter ***@***.***> wrote:  

We added both Index WP MYSQL for Speed and Index WP Users for Speed to our woocommerce site, which had become slow due to very large usermeta (1.6MM rows) and postmeta (3.8MM rows) tables. (~25K customers with ~130K orders)

Initially we were very happy with the plugins since postmeta and usermeta join queries that were taking 10-20s are now < 1s.

However, less than a week later our site is frequently reaching 100% CPU. Our host said there were lots of slow queries running and likely causing this: | Command | Time | State | Info | Progress | | Query | 721 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 6 OFFSET 4751 | 0.000 | | Query | 500 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 6 OFFSET 4751 | 0.000 | | Query | 429 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 4 OFFSET 4751 | 0.000 | | Query | 228 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 4 OFFSET 4751 | 0.000 | | Query | 22 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 4 OFFSET 4751 | 0.000 | | Query | 691 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 16384 OFFSET | 0.000 | | Query | 716 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 12288 OFFSET | 0.000 | | Query | 596 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 12288 OFFSET | 0.000 | | Query | 615 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 8192 OFFSET 5 | 0.000 | | Query | 491 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 8192 OFFSET 5 | 0.000 | | Query | 535 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 6144 OFFSET 4 | 0.000 | | Query | 421 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 6144 OFFSET 4 | 0.000 | | Query | 205 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 4096 OFFSET 2 | 0.000 | | Query | 244 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 3072 OFFSET 2 | 0.000 | | Query | 141 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 3072 OFFSET 2 | 0.000 | | Query | 17 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 3072 OFFSET 2 | 0.000 | | Query | 185 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 2048 OFFSET 1 | 0.000 | | Query | 22 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 2048 OFFSET 1 | 0.000 | | Query | 434 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 2048 OFFSET 1 | 0.000 | | Query | 67 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1536 OFFSET 1 | 0.000 | | Query | 311 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1536 OFFSET 9 | 0.000 | | Query | 288 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1536 OFFSET 9 | 0.000 | | Query | 10 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1024 OFFSET 7 | 0.000 | | Query | 337 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1024 OFFSET 6 | 0.000 | | Query | 244 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 1024 OFFSET 6 | 0.000 | | Query | 70 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 768 OFFSET 53 | 0.000 | | Query | 14 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 768 OFFSET 53 | 0.000 | | Query | 138 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 512 OFFSET 35 | 0.000 | | Query | 97 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 512 OFFSET 35 | 0.000 | | Query | 264 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 512 OFFSET 30 | 0.000 | | Query | 184 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 384 OFFSET 26 | 0.000 | | Query | 160 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 384 OFFSET 26 | 0.000 | | Query | 49 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 384 OFFSET 26 | 0.000 | | Query | 131 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 96 OFFSET 115 | 0.000 | | Query | 67 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 96 OFFSET 115 | 0.000 | | Query | 12 | Creating sort index | SELECT * from wp_usermeta ORDER BY umeta_id,user_id,meta_key LIMIT 96 OFFSET 115 | 0.000 |

We had the User Indexes set to rebuild daily at 12:25am, so I wondered if this was related (even though the CPU was hitting 100% at more like 2pm).

I uploaded metadata with this ID: VCsh5YMX

Any tips on how to avoid maxing CPU with these index plugins would be gratefully received.

— Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: @.***>

[petergerard]

Thanks. I don't know the purpose of these queries. I assumed they weren't truncated because they ended with order, limit and offset. And I wondered if "Creating sort index" meant it was related to the indexing.

Here's the create statement:

CREATE TABLE `wp_usermeta` (   `umeta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `user_id` bigint(20) unsigned NOT NULL DEFAULT 0,   `meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL,   `meta_value` longtext COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,   PRIMARY KEY (`user_id`,`meta_key`,`umeta_id`),   UNIQUE KEY `umeta_id` (`umeta_id`),   KEY `meta_key` (`meta_key`,`meta_value`(32),`user_id`,`umeta_id`),   KEY `meta_value` (`meta_value`(32),`umeta_id`) ) ENGINE=InnoDB AUTO_INCREMENT=2215442 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

[OllieJones]

Thanks for the report! Your uploaded metadata says that you've used Index WP Mysql to add the high-performance keys to all your tables, and that you use MariaDB 10.4. All good. It says you use wp_3p848kys1x_ in place of wp_ as your table name prefix. That's fine too.

"Creating Sort Index" means your MariaDB server is accumulating rows so it can sort them. ("Using filesort" is another way this sometimes appears.)

You can remediate this problem in your own site by issuing this MariaDB query (to create a site specific key. I gave it a site-specific name but you can use something different if you prefer.

ALTER TABLE wp_3p848kys1x_usermeta ADD KEY petergerards_key (umeta_id, user_id, meta_key);

You can do this from phpmyadmin or some other SQL client. Or, you can use wp-cli like so:

wp db query "ALTER TABLE wp_3p848kys1x_usermeta ADD KEY petergerards_key (umeta_id, user_id, meta_key);"

These queries are a bit faster with WordPress standard indexes. In my 100K user test site they take 0.5 sec with standard keys and 1.7 sec with "high-performance" keys. Neither of those speeds is acceptable in my view. But with the new index they take 1.5 milliseconds, which is reasonable.

What is going on?

I do not know the source of these

SELECT * from `wp_usermeta` ORDER BY `umeta_id`,`user_id`,`meta_key` LIMIT 1024 OFFSET 6

queries. Why would somebody want 1K usermeta records starting with the sixth one, with no WHERE filters? Doesn't make much sense. They're not from either Index WP Mysql or Index WP Users. And nothing much useful shows up in https://wpdirectory.net/, the code search engine for open-source plugins.

I wonder if you can identify what code is running those queries? Using the Query Monitor plugin would be a way to do that. Also, using Index WP MySQL's monitor feature might capture and locate the queries.

Thanks in advance for helping to troubleshoot this problem, and for taking the time to report it.

[gerzok]

I have the same problem, as soon as I activate the plugin and add the wp_postmeta table the CPU goes to heaven.

Is there any way to solve this?