Queries slower (after reset)

Thanks for the question, and the upload.

According to the upload, and as designed, the plugin reverted your database tables to the standard keys when it failed to add the high-performance ones (because MariaDb blew out /tmp storage during that operation). So, your keys are normal now.

Your monitor does show some slow operations. It is possible you experienced a temporary slowdown because the rekeying operation changed the contents of your innodb buffer pool. When I tried your site this morning I didn’t see that kind of slowdown. Or, as you mentioned, something else could have happened.

Suggestion: consider adopting a persistent object cache. It helps performance and responsiveness a lot on busy commerce sites.

Hi @olliejones,

I’m sorry for the slow reply – never got an email with your answer. I apologize.

As for the slowdown; things are totally and utterly running crazy on the site. Every get_orders query will most of the time, just timeout. Even after reverting the keys on the post_meta and deactivating the plugin; the database queries are going crazy.

Any advice/suggestions are appreciated – Not sure if we should get /tmp bufffed and then go back to SQL Index – the site is having a fair amount of issues these days (even after moving to a huge cloud-server – where we still time out; while only using 10-15% of the capacity on the server)

Something’s going on with your site beyond the effects of table indexing.

If you upload your database metadata (from the plugin’s About tab) and maybe another monitor, I can take a closer look. If you record a monitor, please be sure to do a few of the unacceptably slow operations while you’re recording it. Also, what are the specs of your virtual machines? How many vCPUs? How much RAM? Is there a limit on provisioned IOPS between CPU and HDD / SSD storage (for your site code, databases, media library, all that stuff)? Do you use just one VM for both web server and MariaDB, or do you have a separate database VM?

Systemwise, I’m an old-school guy, so if it were my site I’d start by looking at the state of my virtual machine or machines. Is something sucking up all the CPU? RAM? If you’re on Linux you can say:

sudo install htop
htop

to see a nice display of what your virtual machine is doing. It shows RAM and CPU usage, and shows what processes are active. Is somethiing running amok? Could be.

If I had any doubts about what htop shows, I would reboot the database and web server virtual machines.

We can improve this situation.

• This reply was modified 8 months, 3 weeks ago by OllieJones.

I appreciate the fast reply!

Update: Monitor ID: k8C6ZZam

I am very much -not- a server-guy. I think thats faily cliche by now; but let me try to answer the questions as well as i can.

We bought “out the box” VHM Pro/cPanel cloud hosting with 16 CPU’s @ 64 GB. (Running MySQL & the site on the same server). We went massive overboard on the server, cause we assumed it would help. (Its a large subscription-company where we run a fairly large amount of orders every week – which also makes it _very_ hard to run staging and larger tests)

Average usage: 120% CPU and 13% memory (8 / 64)
Peak usage: 450% CPU and 21% memory (13 / 64)

IOPS limit 10240
NPROC limit 1000
Entry processes 200
I/O Usage 10G/s

The time-out issue is both before and after we changed hosting from a small normal 4GB ram setup. It happened out of nowhere and the only changes made was running this plugin (Thereby most definitely not saying the plugin made an error – cause reverting things still provides the same issue – so it might be something that has just shown up now)

I upgraded to VHM Pro to gain access to PHP X-Ray which always shows the timeouts at 50-60 seconds with 90%+ of total duration. Example of the query that times out is below (And its almost always this query – And also getting the same delay/timeout running similar queries directly in phpmyadmin). In short: Any query that looks up inside post_meta, will take 60 seconds and then timeout the whole site for the duration.

(If it was just the loading of the current call that timed out, the issue wouldnt be as big – but the whole site goes belly-up for 60 seconds on every call made – making very little sense; because its been the same issue on both servers, and we are nowhere near cap/peak on the servers; yet it stalls all over)

(Ill upload more queries – we just ran the payments manually the last 15 hours (normally takes 2-3 hours). But i’ll find a query that stalls and add it to the monitor-send)

  SELECT   wp_posts.ID      FROM wp_posts  INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )      WHERE 1=1  AND (    (      ( wp_postmeta.meta_key = '_xxxxxxx_xxxxx' AND wp_postmeta.meta_value IN ('xxxxxxxxxxxxxx0xxxxxx.xxx') )      OR      ( wp_postmeta.meta_key = '_xxxxxxxx_xxxx' AND wp_postmeta.meta_value IN ('00000') )   ) ) AND wp_posts.post_type = 'xxxx_xxxxx' AND ((wp_posts.post_status = 'xx-xxxxxxxxxx' OR wp_posts.post_status = 'xx-xxxxxxxxx'))      GROUP BY wp_posts.ID      ORDER...

• This reply was modified 8 months, 2 weeks ago by Patrick Thagaard.

I’m emailing you on your contact email on your personal / agency web site.

*Reads. Raises hand.*

Please, never, ever, ever, ever post any email addresses on this site again. Not even [at] ones. That’s not appropriate and just leads to more spam and spam email harvesting.

I archived both of your replies.

Thanks, Jan. Sorry for the mistake.