MySQL Query Issues

[PhillipHuynh]

2504 wordpress localhost wordpress 168 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2517 wordpress localhost wordpress 166 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2523 wordpress localhost wordpress 165 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2545 wordpress localhost wordpress 163 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2546 wordpress localhost wordpress 162 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2566 wordpress localhost wordpress 161 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2639 wordpress localhost wordpress 149 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2648 wordpress localhost wordpress 148 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2660 wordpress localhost wordpress 144 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2687 wordpress localhost wordpress 143 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2765 wordpress localhost wordpress 132 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2785 wordpress localhost wordpress 129 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2790 wordpress localhost wordpress 127 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2819 wordpress localhost wordpress 123 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2837 wordpress localhost wordpress 118 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2861 wordpress localhost wordpress 115 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2891 wordpress localhost wordpress 112 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2883 wordpress localhost wordpress 110 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2886 wordpress localhost wordpress 107 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2929 wordpress localhost wordpress 105 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2933 wordpress localhost wordpress 105 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2971 wordpress localhost wordpress 98 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2980 wordpress localhost wordpress 96 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM w 2991 wordpress localhost wordpress 95 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROMw 2999 wordpress localhost wordpress 94 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FROM `w 3009 wordpress localhost wordpress 90 Query Sending SELECT p.ID AS id, p.post_title AS title, p.post_author AS uid, p.post_date AS date, v.pageviews FRO

I am running into issue where the plugin is overloading the database and it's locking it up. These queries are not going away.

[cabrerahector]

Hi @PhillipHuynh,

Have you read this yet? WordPress Popular Posts - Wiki - Performance.

[PhillipHuynh]

@cabrerahector Yes, I am using both your Pageviews cache (Beta) and Data logging.

[cabrerahector]

Ah, I remember now. You're the guy from the other day! Sorry, I'm not great with (user)names.

Anyways, I'm not really sure what's going on to be honest. The log you shared doesn't say a lot.

Data Caching might help as it will reduce the number of database reads performed by the plugin, and since your site seems to be a high traffic one you might want to enable it as well. A 1 minute cache should suffice but play around with the options and see what works for you the best.

Also, although I haven't personally tested this, you could try using hinters to limit the execution time of the query:

/**
 * Modifies the SQL query performed by WordPress Popular Posts,
 * limiting its execution time to 1000ms to reduce the chances of database locks.
 *
 * @param string
 * @param array
 * @return string
 */
function wpp_limit_query_execution_time($fields, $options){
    return '/*+ MAX_EXECUTION_TIME(1000) */ ' . $fields;
}
add_filter('wpp_query_fields', 'wpp_limit_query_execution_time', 10, 2);

If the query takes longer than 1000 ms (1 second) to execute, the database server will terminate it automatically (AFAIK by default queries are allowed to run as long as they need to which might cause database locks in extreme cases). Note that to use this you'll need MySQL 7.0 or greater as this feature (Optimizer Hints) isn't available for older MySQL versions.

(And in case you're wondering why WPP doesn't use this out of the box, it's because WordPress itself continues to support ancient MySQL versions. Once the minimum required MySQL version jumps from 5.0 to 5.7 or greater, I'll add this hinter to the query.)

Please give those a shot a report back what the results are.

P.S.: You might need to restart your server to kill those queries, but do try without rebooting first and see how it goes.

[PhillipHuynh]

@cabrerahector no problem! The performance guide was helpful and it did help with our server resource issues.

I know the log was limited, I am using "mytop" in command line and monitoring the queries in our MySQL database. I saw many WPP related queries going over 100 seconds, which shouldn't be the case obviously.

Our website gets over 600,000+ pageviews a month, I'm actually not sure what constitutes a high traffic website to be honest. But I will go ahead and set caching for 5 minutes as a start? I have it currently running every 30 minutes.

Great! I will go ahead and add that to our functions.php file then. Should the query not being taking longer than a 1 second? I feel like 1 second might be too low? Maybe 5 or 10 seconds? I'm not too sure. You're the better expert in this area. Also, you meant MySQL 5.7 or greater correct? I believe there isn't a MySQL 7.0 available for the public.

I've already restarted the MySQL server about 3 times in the past 10 minutes because of the deadlocks. And I have turned off the plugin for now. Once you respond back with more information, I will go ahead and add that snippet of code and re-enable the plugin.

[PhillipHuynh]

@cabrerahector Also, I appreciate you providing that snippet of code. I was about to install Percona Toolkit on the server and just have it auto kill any queries over 60 seconds.

[cabrerahector]

Our website gets over 600,000+ pageviews a month, I'm actually not sure what constitutes a high traffic website to be honest. But I will go ahead and set caching for 5 minutes as a start? I have it currently running every 30 minutes.

That doesn't sound too bad. If my memory serves me right, your server should be able to handle that much. There might be something else going on but let's try the suggestions from above first and see how things go from there.

Should the query not being taking longer than a 1 second? I feel like 1 second might be too low? Maybe 5 or 10 seconds? I'm not too sure. You're the better expert in this area.

Well, I'm not really an expert. Far from it. I feel I still have lots to learn.

Anyways, in my experience most SELECT queries (like the one from your log) usually execute in less than a second (eg. 200-800 ms depending on traffic, server setup, query complexity, etc). Anything longer than that normally indicates that there's something slowing down the database server (traffic peaks, tables needing optimization, suboptimized queries, etc). I might be wrong though so don't take my word for it.

You might want to raise the execution time limit to 5 seconds instead and tweak from there if/when needed. It can't hurt to try and it'll be way better than having queries running for over 100 seconds piling up :P

Also, you meant MySQL 5.7 or greater correct? I believe there isn't a MySQL 7.0 available for the public.

Correct, MySQL 7 or greater (or MariaDB 10.2 or greater).

Also, I appreciate you providing that snippet of code. I was about to install Percona Toolkit on the server and just have it auto kill any queries over 60 seconds.

Don't mention it. I actually had forgotten all about that option until now. I didn't even document it anywhere, your two issues so far have been a nice refresher so thank you for testing the plugin so thoroughly.

[PhillipHuynh]

@cabrerahector Everything is looking good so far. I haven't seen any slowdowns as of late, I will close this and create a new case for a different problem I have.

[cabrerahector]

Fantastic! Which of the suggestions did the trick?

[PhillipHuynh]

@cabrerahector I went ahead and added that snippet of code and had it kill after 5 seconds. I also decreased the cache time from 30 minutes to 5 minutes.