Host tells me “database is generating heavy queries” Site down a lot as of late

  • archjedi

    (@archjedi)


    16 years ago

    Siteground is my host.

    Version release dates
    2.6.1 August 15, 2008
    2.6.2 September 8, 2008
    2.6.3 October 23, 2008
    I usually upgrade the day it is available via the upgrade plugin.

    My problem started on Oct 16th.. Site went down, I consult my host.
    I was told because of the “popularity of my my site” and the traffic increase, it was causing more CPU usage than a shared server could support. (I get only 1,000 visitors/day), and for the last 2 months it’s been a fairly gradual increase from 500/day to that 1k/day.
    Here is a traffic graph from g. analytics from sep 1st to present. If traffic were the problem, then I would have gotten bogged down on sep23 when I hit 3700 visitors that day (thx to a nice backlink). Nope. No problems until 3 weeks later well after traffic has calmed down.

    https://conspiracyrealitytv.com/images/1.gif

    My account was suspended and was told I needed to upgrade to a dedicated server (basically from 9 bucks a month to 99 a month) which I DID. Was back up and running by Oct 18 or so.

    So for the last week and a half I’ve been having serious issues.
    These are the 5 reasons given to me each time my site was down – starting with the oldest

    Reason #1 : 2008-11-03
    There has been a minor issue with the firewall of your VPS because of which your application has not been able to connect to the remote rss server to fetch its information. This has caused your script to fail during the execution time.
    I have restarted the firewall and fixed the issue. As far as I can see everything works properly now. Please clear your browser cache and cookies and check it on your end. Thank you in advance.

    #2 : 2008-11-04
    I have checked your website and it seems that the issue was caused because your database is generating heavy queries:
    [03-Nov-2008 15:28:18] WordPress database error MySQL server has gone away for query SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts WHERE 1=1 AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) ORDER BY wp_posts.post_date DESC LIMIT 0, 1 made by get_posts
    [03-Nov-2008 15:28:18] WordPress database error MySQL server has gone away for query SELECT FOUND_ROWS() made by get_posts
    I have reconfigured the MySQL services on your VPS to use more resources in order for the above queries to be executed completely. Note that it is recommended to have your database optimized because such queries may cause overload on your server. In order to have the database of your WordPress optimized you may contact an expert local web developer. S/He should be able to apply all the necessary changes for you. Unfortunately we do not offer such development services at this time.
    However your website should function correctly with the new MySQL service.

    #3: 2008-11-06
    I have carefully investigated your case and found that due to certain low values set for MySQL buffer sizes, your MySQL server was failing to execute queries properly.
    I have tweaked the buffer size settings, allowing larger amount of memory to be used by the database server and your website is fully accessible again.

    #4 : 2008-11-13
    I have carefully reviewed your case and lifted up your VPS memory limits available for your scripts to:

    max_execution_time 90
    max_input_nesting_level 64
    max_input_time 90 90
    memory_limit 196M
    However, this did not resolve the issue right away. This is why I have then repaired your website’s databases from:
    cPanel -> mySQL Databases -> Repair button for the corresponding database.
    This has fixed the issue and your website is currently up and running. I have also double checked it and everything seems to be working properly.

    #5 : 2008-11-13
    I reviewed your ticketing history carefully and noticed that this re-occurring MySQL problem is caused by the slow queries that the website generates and the large number of visits towards the website. The MySQL service was restarted and the website is up and running normally, but I advise you to perform further optimization of the database such as adding indexes in each table of the database for example, which will improve the performance and the stability of the site. For more detailed information about optimizing a MySQL database, please refer to the following article and consult with a developer which is familiar with the database structure of your website if necessary: https://www.developer.com/db/article.php/3667831

    Any help I can pass on to the tech support would be much appreciated. I don’t think I needed a dedicated server. But either way the problems still persist.

Viewing 4 replies - 1 through 4 (of 4 total)
  • MichaelH

    (@michaelh)

    Have you looked at using a caching plugin such as SuperCache?

    Also for your review WordPress_Optimization.

    Thread Starter archjedi

    (@archjedi)

    Yea that’s been installed for months.

    supernovia

    (@supernovia)

    You might ask your host to set up a slow query log for you, with a high threshold like 25 seconds or so. The “Gone Away” error can mean a lot of things, but a slow query log can help you pinpoint which queries are eating up the most resources, and then you can continue from there…

    wpsensei

    (@wpsensei)

    Whether VPS or Dedicated, MySQL is going to crawl unless properly optimized for use via MySQL’s my.cnf configuration file.

    The configuration of my.cnf would depend on CPU & RAM availability. With a VPS, both of these items are going to be significantly less than with a Dedicated Server though, if you have at least 512MB RAM on the VPS, you should be able to perform some minor tweaking.

    I would suggest installing xCache for PHP which will allow you to further increase your caching ability beyond that of WP Super Cache. WPSC is client-side caching, xCache allows you to perform server-side caching to RAM. They should both be enabled.

    Once xCache has been configured, the following is an updated version of a xCache variable cache plugin. It is dependent on xCache, so it will need to be installed after xCache has been installed.

    https://dougal.gunters.org/blog/2008/08/29/xcache-object-cache-plugin-for-wordpress-25

    To-Do

    – Fully Optimize my.cnf based on CPU & RAM Availability
    – Install and Configure xCache & xCache Plugin

    Resources

    – Installing xCache (Requires Root SSH Access)
    https://www.vbseo.com/f34/installing-xcache-your-server-18498/

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Host tells me “database is generating heavy queries” Site down a lot as of late’ is closed to new replies.