Simple Download Monitor

Thanks for the upload. The first two queries in the monitor you showed me are related to that Simple Download Monitor. The first one reads all the rows (!!! why?) in the wp_sdm_downloads table, and takes almost a quarter-second each time it runs. The second one reads all the records for a particular post, and takes 0.066 seconds each time. Is it possible your site has logged a great many downloads, particularly for post_id 4443? Is it possible you should delete old historical records from that table? (It is still sloppy programming on the part of the plugin developer to write SELECT * FROM table queries, and probably means they didn’t anticipate working with a site as busy as yours.)

XXXX Please see the next post. You could delete historical records over a year old from that table with wp-cli:

xxxx wp db query "DELETE FROM NHQ2wp_sdm_downloads WHERE date_time < NOW() - INTERVAL 1 YEAR"

There aren’t any indexes on that table. The second query would speed up a bit if you added an index on post_id. To do this with wp-cli:

wp db query "ALTER TABLE NHQ2wp_sdm_downloads ADD KEY post_id(post_id)"

Other queries seem to be ordinary WordPress category searches and so forth.

I think it’s possible a persistent object cache will help make things faster? Worth a try! https://developer.www.ads-software.com/reference/classes/wp_object_cache/#persistent-cache-plugins

I didn’t add any indexes to wp_term_taxonomy or wp_term_relationships because the standard indexes on those tables looked to me like they were the best possible. I’d be delighted if you put some of your slow-query-log queries in here, so I can take another look at that decision.

• This reply was modified 6 months, 2 weeks ago by OllieJones.

Oh wait, forget the wp db query "DELETE .... suggestion. You can do this directly from the plugin.

On Downloads -> Logs, there’s an option to Delete entries older than [ ] days.

I haven’t heard back from you. Please don’t hesitate to start another support topic if I can help you further.

Sorry, missed this. The post 4443 doesn’t have many downloads. But I just added an index to post_id and it seems help. No more long time query from sdm_downloads table. Thanks a lot.

The reason of select all is because the site owner wants to show the total downloads.

Now, there are several long time queries related to postmeta table. Can you take a look at the new upload, please?

Thank you.

Your monitor shows some queries that look up postmeta records for a really long list of posts, 437 of them to be exact. They also come from Simple Download Monitor, for what it’s worth. The average time of the those queries is about 45ms and the 95th-percentile is about 85ms. That isn’t too bad.

They look like this:

SELECT post_id, meta_key, meta_value FROM NHQ2wp_postmeta WHERE post_id IN (1300,2566,2582,2729,1682,1325,1799, — 430 more post_id values — ) ORDER BY meta_id ASC”,

This plugin’s keys on that table are optimal for queries like that. Unfortunately that kind of query is hard for MySQL to optimize beyond what it already does.

• A persistent object cache reduces the incidence of this kind of query a lot.
• You might consider increasing your database server’s innodb_buffer_pool_size. Right now it’s set to 2GiB and it is running almost full. You might (or might not) get improved performance if you increased it. Try 4GiB. https://mariadb.com/kb/en/innodb-buffer-pool/#innodb_buffer_pool_size

Thanks a lot for the info. I forgot to check the MariaDB optimization. Have increased innodb_buffer_pool_size and turned on Redis. Let’s see if the performance is better ??

Thanks again.