temp disk usage on mysql server

  • Resolved kasra6499

    (@kasra6499)


    2 years, 7 months ago

    Below SQL query lead to abnormal temp disk usage on mysql server

    SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_parent, p.post_date_gmt, p.post_modified_gmt, ap.priority, ap.frequencyFROM wp_posts as p LEFT JOIN wp_aioseo_posts as ap ON ap.post_id = p.IDWHERE 1 = 1 AND p.post_status = ‘publish’ AND p.post_type IN ( ‘post’ ) AND ( ap.robots_noindex IS NULL OR ap.robots_default = 1 OR ap.robots_noindex = 0 OR post_id = 0 )ORDER BY ap.priority DESC, p.post_modified_gmt DESC LIMIT 1000, 1000/* 1 = 1 */

    When it runs multiple queries at once will make the mysql temp disk full while my mysql temp size is set to 64G which is big enough

    my wp_posts size is 1.8G and wp_aioseo_posts is just about 400M

    so turning my temp disk fully used only due to these queries is quite weird

    Please kindly check the query and make it more optimized to avoid this high temp disk usage

Viewing 9 replies - 1 through 9 (of 9 total)
  • Plugin Support J Burns

    (@subiewrx)

    2 years, 7 months ago

    Hi @kasra6499,

    I’m sorry you’re experiencing an issue here.

    Could you please check your sitemap settings to see if Enable Sitemap Indexes is set to Enabled? Then recheck your server log to see if you are still seeing abnormal temp disk usage.

    Thanks!

    Thread Starter kasra6499

    (@kasra6499)

    2 years, 6 months ago

    Yes
    It is active
    You can check from the link below
    https://gamefa.com/sitemap.xml

    Thread Starter kasra6499

    (@kasra6499)

    2 years, 6 months ago

    Does it have anything to do with password content?

    Plugin Author arnaudbroes

    (@arnaudbroes)

    2 years, 6 months ago

    Hey @kasra6499,

    I don’t think this has to do with password-protected content. It’s mostly just the sheer amount of posts that you have on your website.

    However, when I inspected your sitemap earlier today, all of the sitemap indexes were loading just fine.

    Is this high temporary disk usage a continious thing or does it happen occassionally?

    You also mentioned “when it runs multiple queries at once”. May I ask how you know multiple queries are running at the same time? I’m wondering if some kind of crawler or bot is loading your sitemap indexes multiple times or all at the same time.

    Thread Starter kasra6499

    (@kasra6499)

    2 years, 6 months ago

    Server faced overloads due to mysql usage and when we monitored queries it was like hundreds or thousands of these queries
    It doesn’t happen very frequently but at least happened couple of times within couple of weeks

    It might be due to unusual sitemaps crawling but we didn’t check the access logs and they are already pruned

    Thread Starter kasra6499

    (@kasra6499)

    2 years, 6 months ago


    11942421 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942423 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942424 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942425 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942427 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942429 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942430 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942431 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942432 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942434 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942435 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942436 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942438 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942439 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942440 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942441 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942442 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare
    11942443 gamefaco_ localhost gamefaco_s 2775 Query SELECT p.ID, p.post_title, p.post_content, p.post_excerpt, p.post_type, p.post_password, p.post_pare

    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    • This reply was modified 2 years, 6 months ago by kasra6499.
    Thread Starter kasra6499

    (@kasra6499)

    2 years, 6 months ago

    Is it related to the crawler of the LiteSpeed cache plugin?
    more information
    https://docs.litespeedtech.com/lscache/lscwp/crawler/

    Plugin Author arnaudbroes

    (@arnaudbroes)

    2 years, 6 months ago

    I can’t say for sure since we’re not familiar with their codebase and don’t know how their crawler exactly works but that’s definitely possible and a great guess.

    I’d ask them about this in their forums, but it might be a good idea to temporarily disable the crawler feature in their settings and see if the load issues go away after that.

    We’ve never had any reports like yours where the sitemap indexes are causing disk usage issues, so it’s likely that a third-party plugin is causing this. Many of our users use their plugin and crawler functionality, but I don’t think many have sites similar to the size of yours.

    Plugin Support Steve M

    (@wpsmort)

    2 years, 6 months ago

    Hi @kasra6499,

    We haven’t heard back from you in a week. I’m going to go ahead and close this thread for now. But if you’d like us to assist, please feel welcome to continue the conversation.

    Thanks!

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘temp disk usage on mysql server’ is closed to new replies.