Using 100% CPU while creating indexes after enabling Index for Speed plugins #48
Comments
|
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: ***@***.***>
|
|
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: |
|
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 "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. You can do this from phpmyadmin or some other SQL client. Or, you can use wp-cli like so: 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 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. |
|
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?
|
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:
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.
The text was updated successfully, but these errors were encountered: