OllieJones

Well, this is quite strange. The usual reason for this error — your autoincrement number growing too large for its column — isn’t in play. Try restarting your MySQL or MariaDB database server.

It looks like you’re using my other plugin, Index WP MySQL for Speed. Could you go to Tools -> Index MySQL and then to the About tab. Then upload your metadata and reply to this topic with the upload id. And I’ll take another look.

Sorry for the inconvenience, and thanks for your patience.

Thanks for the question, and my apologies for not noticing it sooner.

You’re getting the database error Failed to read auto-increment value from storage engine from your DBMS. That can happen when your current autoincrementing meta_id value gets bigger than the space allocated for it in your table. There are also a couple of old MySQL bugs that can cause it too.

If you could do something like this:

wp db query "show create table wp_usermeta;"

and reply to this topic with the table definition you get back I might be able to troubleshoot further.

Thanks for the excellent question. It’s all about the performance advantage of clustered indexes, a feature of InnoDB. Here’s the brief explanation.

The clustered index is the index supporting the table’s primary key. It contains all the table’s data, that’s what clustered means. In fact, the clustered index is the table. So, table lookups which can exploit the primary key have immediate access to all the table’s data, without the CPU and potential IO cost of looking elsewhere for the data. It’s a little faster.

I didn’t know about mydumper’s need for an autoincrementing integer primary key. As a workaround, you can keep that primary key and still get the compound index on (post_id, meta_key) by using this data definition language for wp_postmeta.

SET @@sql_mode := REPLACE(@@sql_mode, 'NO_ZERO_DATE', '');

ALTER TABLE wp_postmeta 
  DROP KEY post_id,
  ADD KEY post_id (post_id, meta_key), 
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key, meta_value(32), post_id),
  ADD KEY meta_value (meta_value(32), meta_id);

I hope this helps.

I really don’t want to guess about where the connections are coming from. I have so little information you’d be better off with a Ouija board guess. If your site is busy, it’s possible all the database traffic is actual work for your audience and customers.

If I were investigating it, I would start by logging in to MySQL as “root” and giving this SQL command;

https://dev.mysql.com/doc/refman/8.0/en/show-processlist.html

“Process” here means “connection”, close as makes no difference. I would eyeball the processes and try to figure out some kind of pattern. Let me know if I can help further.

Thanks for the report. It is good to hear from a user who knows about database keys. Sorry for the inconvenience!

You are correct about this plugin removing other indexes. I did that because some early users had messy indexes that needed to be cleaned up.

We have a way built-in to ignore certain indexes. If you give your index a name starting with index_wp_mysql_protect_ this plugin will not touch it. The same goes for index names starting with woo_, crp_ , and yarpp_ .

Alternatively, if you want to manage your own indexes you can get this plugin to show you the data-definition-language commands to change the keys. Use wp-cli and say this to get it to emit the commands and not actually run them.

wp index-mysql enable --all --dryrun

What index have you added? If you think it’s widely useful, maybe we could add it to this plugin? Please let me know.

Thanks for the upload. I took a look. I don’t see anything in particular amiss. As is very common in WPML sites,. that localization software is database intensive.

Your MySQL 8.0.39 server is very busy. 278 connections per second and 37K queries per second were handled in the 15 min of your monitor. (Assuming that workload is legitimate and not some silly runaway reporting process or something) it looks to me like your CPU usage is high because you’re doing useful work with it.

Did it go up a lot after you added the high performance keys? If so, please let me know.

A quick tip for you. The ubuntu apt package for redis, as installed, puts no limit on its RAM growth. I’ve had users where that default redis started hogging RAM and destabilizing MySQL (bad. bad.). You may want to put a couple of lines in /etc/redis/redis.conf . I use these settings on my 16GiB RAM development VM. You may want to allocate a smaller amount in maxmemory.

maxmemory 256mb 
maxmemory-policy allkeys-lfu

Thanks for the question!

If you “convert” it will change the keys to this plugin’s high-performance keys.

If you “revert” it will put them back to WordPress’s standard key set.

You can choose to do neither if you prefer.

If you go to this plugin’s About tab you can upload diagnostic data. I’ll be happy to take a look at your info if you do that, then reply to this topic with the upload id.

Version 1.1.7 now shows user display names and login names. That is the way it works without the plugin.

Thanks for the pointer (and the translation!). I made this request.

When you say “use with redis” I assume you mean “use with the Redis Object Cache plugin by Till Krüss.”

Yes. In fact, the high-performance keys are designed to work best with a persistent object cache, although they work fine without one.

This is great. Thanks!

It looks like we must wait for a transation editor to approve your work.

This plugin does not cause order search operations to “cast a wider net” than they would without it. (At least, that’s the way it’s designed.) Precisely the same search criteria that WooCommerce uses without this plugin are still applied to all searches. This plugin adds an efficient way of winnowing out irrelevant results before applying WooCommerce’s search criteria.

The order search function is designed, by WooCommerce, to work similarly to Google searches in this respect: it returns items that might match. It’s much more likely to return false positives (items it found that are not needed) than it is to return false negatives (not find items it needs).

Thanks for these suggestions!

I’m not sure about the order count warning or performance prediction. Does the feature’s usefulness justify its complexity? We’d have to run the tests and announce the results to the user. And, they’re in a language (backend database performance) unknown to many WordPress site operators.

This plugin works fine on small sites. It doesn’t help much but it doesn’t hurt either. The trigram lookup complexity is O(log n), even when n is small.

Workaround for missing feature: To regenerate the trigrams, use wp-cli to deactivate and then activate the plugin. Activation regenerates them, while deactivation drops the table.

Notice that it’s necessary, when the toplevel php file mentions * WC Requires at least, to implement an action hook that declares the plugin to support HPOS. That will be in the next release.

• This reply was modified 2 months ago by OllieJones.

Thanks for the question. I fear that’s the way caches work. Whatever overhead is required to fill the cache happens the first time. Then, the cache makes subsequent times faster.

It’s possible a redis or memcached client-server cache would take less time to fill.

I’ll investigate the 100% notification banner issue.

The plugin maintains the trigram table automatically as new orders come in and orders change. But it’s possible the maintenance falls behind, especially on a busy site.

The banner gets displayed whenever the plugin detects an order id ( === post id) bigger than the largest one indexed.

It should not be necessary to regenerate the trigram table. You do it, if it is needed, by deactivating and reactivating the plugin. Please let me know if it is needed.

Thanks for the defect report and the kind words, Maxime.