SQL Queries Stuck in "Statistics"

KWH

• October 23, 2017 03:58

Hi, I have a WordPress website on a cPanel server with: [LIST]

8 cores

8GB RAM

4GB Swap

PHP-FPM enabled (default settings)

MySQL 5.6.37-log (MyIsam default storage)

PHP7.0

wp-posts is a MyIsam table The website offers users multiple search options for filtering custom posts. 99% of the time it all works fine, but occasionally we are experiencing the following problem: [LIST]

User selects search options (website delivers live search results, with an optional "search" button).

Website hangs indefinitely (hours, if unattended).

No one else can use the site until the server is rebooted (restarting MySQL doesn't resolve it). I've checked the server status and the System Information is never more than this: [LIST]

Server load: roughly 1 out of 8 cores

Memory used: 25%

Swap used: 23% My SQL Processes show: 49 root localhost [Database Name] Query 321 Waiting for table level lock UPDATE wp_posts SET post_password = '10af72916b219c715494', post_modified_gmt = '[Date and Time] Statistics SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_ I've attached a screenshot of the complete process screen the last time this happened. My developers are looking into improving the SQL query, but I thought I'd through it out there to see if anyone has any other comment or advice regarding the server set up and SQL query etc. Many thanks in advance!

• 

  cPanelMichael

  • October 25, 2017 16:40

  Hello, Do you notice any output to the MySQL error log (/var/lib/mysql/$hostname.err) when this happens? Thank you.

  0
• 

  KWH

  • November 07, 2017 09:26

  Hi, No, nothing is being recorded there when it happens. Thanks.

  0

Please sign in to leave a comment.