Slow query causes server to crash

This is a count query and it should not be slow. There should be 1 query not many queries of same. This query also cached so it will not again query until no messages is sent/received by that user.

Can you please let me know which page you were loading? How many queries it shows?

Also make sure you do not cache message page (where [front-end-pm] shortcode is).

@shamim51

The number of registered users of the website reached 500,000, with an average of 70,000 visitors per day.

In order to reduce the server load, even the pages accessed by the logged in user are cached. Only a small percentage of access is bypassed because the cache has not yet been generated.

But the front-end-pm page is not cached.

Once a new article is published, the home page and category page cache will be cleaned up.

I tested to remove the cache cleanup code, even if I didn’t clean up the cache. Publishing an article will still cause the server to crash.

I have turned on the redis object cache, and under normal circumstances the load is only about 3. Once the server crashes, the CPU will reach 100% use.

I am not very clear about what caused it. I only know that everything is fine after closing the plugin. And there are a lot of such queries in the database slow query.

SELECT COUNT(*) FROM wp_fep_messages INNER JOIN wp_fep_participants ON ( wp_fep_messages.mgs_id = wp_fep_participants.mgs_id ) WHERE 1=1 AND wp_fep_messages.mgs_parent = 0 AND wp_fep_messages.mgs_type = 'message' AND wp_fep_messages.mgs_status = 'publish' AND ((wp_fep_participants.mgs_participant = <strong>userID</strong> AND wp_fep_participants.mgs_deleted = 0));
The userID is constantly changing.

I am using nginx fastcgi_cache as a cache.
And used the parameters:

Fastcgi_cache_lock on;
Fastcgi_cache_lock_age 60s;
Fastcgi_cache_lock_timeout 60s;

This means that the current page has only one access to the backend before the cache is generated. All subsequent accesses are blocked in the cache layer.

This query can be called max one in a page load (only for current user) and cached. So it will not be called until he send/receive a message. So publishing a article should not have any impact in this query.

Can you please go to Front End PM > Settings > Misc > Notification and uncheck all notification related settings. Then test and let me know.

Also can you DM me on slack so that we can work together closely in this?

@shamim51
Thank you very much for your reply!
How can I contact you?
Notification settings have always been turned off.
I re-discovered the website issue and it was indeed caused by the PM plugin.
I got some new slow queries, and these query commands are frequent.

# User@Host: darkanddark[darkanddark] @ localhost []
# Thread_id: 47789270  Schema: darkanddark  QC_hit: No
# Query_time: 1.278143  Lock_time: 0.045469  Rows_sent: 1  Rows_examined: 12212
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1552314448;
SELECT COUNT(*) FROM wp_fep_messages INNER JOIN wp_fep_participants ON ( wp_fep_messages.mgs_id = wp_fep_participants.mgs_id ) WHERE 1=1 AND wp_fep_messages.mgs_parent = 0 AND wp_fep_messages.mgs_type = 'message' AND wp_fep_messages.mgs_status = 'publish' AND ((wp_fep_participants.mgs_participant = 305727 AND wp_fep_participants.mgs_deleted = 0));

The website has a total of 16524 letters.

Slow query log screenshot
https://cdnimg.acgget.com/images/201903/85f196f4e2f5f230.jpg

server configuration
Processor E5-2670 @ 2.60GHz *2
Ram 32 GiB

• This reply was modified 5 years, 8 months ago by fantasts.

You can follow https://make.www.ads-software.com/chat/
Then DM me there (@shamim51)