database cause high load server

Hello,
My site is WP 3.6.1 , plugins & ClassiPress theme are latest.
Hostgator don't allow my site connect with database.
This is message that hostgator sent to me: "The database was found to be consuming an inordinate amount of processor time, to the point of degrading overall system performance. While we do limit each account to no more than 25% of a system's CPU in our terms of service, we do not actively disable accounts until they greatly exceed that number, which is what happened in this case. Requests to this database may become degraded by limiting the maximum number of queries or connections for a limited amount of time, or if there are sustained issues, ultimately we may be forced to block access to this database until the issue has been resolved.
Resolving this situation may be as simple as adding additional indexes to your database, optimizing the queries used, or something equally easy. If not, it may simply be a matter of moving this database to dedicated services, as it may have outgrown a shared environment.

If you believe you have a solution to this overuse, we are happy to discuss the situation with you and possibly reinstate the database on the server. Otherwise, we will be happy to assist you with the upgrade process if a dedicated server is the most appropriate solution. Thank you, and we look forward to hearing from you shortly.

~~~
Excessive MySQL activity is caused by (a) a long-running process that locks a table, causing other queries to back up, (b) a query that is not optimized ][example: select all from ... and involving a large or complex query], (c) huge table copies/maintenance during peak hours. "
The error_log file increase to 100MB within 10 days

I also tried to migrate my site to VPS with 4 cores & 4GB RAM dedicated, but mysql load very high and slow down all node.
My site's database is about 1GB , about 1000visitors / day .
Please help me how to fix the problem.
Thanks

This is 1 record of error_log file:
[16-Sep-2013 06:11:29] Database of WordPress error User 'webraova_wpusr' has exceeded the 'max_questions' resource (current value: 1) with query SELECT SQL_CALC_FOUND_ROWS dl_posts.ID FROM dl_posts INNER JOIN dl_postmeta ON (dl_posts.ID = dl_postmeta.post_id) WHERE 1=1 AND dl_posts.post_type = 'page' AND (dl_posts.post_status = 'publish' OR dl_posts.post_status = 'tr_pending' OR dl_posts.post_status = 'tr_failed' OR dl_posts.post_status = 'tr_completed' OR dl_posts.post_status = 'tr_activated') AND ( (dl_postmeta.meta_key = '_wp_page_template' AND CAST(dl_postmeta.meta_value AS CHAR) = 'tpl-registration.php') ) GROUP BY dl_posts.ID ORDER BY dl_posts.post_date DESC LIMIT 0, 1 require by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/classipress/wrapper.php'), get_header, locate_template, load_template, require_once('/themes/classipress/header.php'), cp_login_head, appthemes_get_registration_url, APP_Registration::get_id, APP_View_Page::_get_id, WP_Query->__construct, WP_Query->query, WP_Query->get_posts

  • lol
    • The Incredible Code Injector

    Hello Tonyle,

    Whether it's a shared hosting or VPS, resources are shared. You will always have problems with the host who will complain about your consumption of resources...

    1GB is not small ... 1000 hits either!

    For a site of this size (size of database and number of visits) I would choose a good dedicated hosting!

    Regards,
    Laurent.

  • tonyle
    • The Incredible Code Injector

    Hostgator's guy said that : "In order to address this issue we would suggest that the row count of the following tables be reduced, either via the removal of non critical data or out of date data. Another option would be the addition of indexes in order to help reduce the overall number of rows read. The tables in question along with the current row count ofr each table can be seen below:

    | webraova_dlwp | dl_cp_ad_pop_daily | 1526631 |
    | webraova_dlwp | dl_postmeta | 624625 |
    | webraova_dlwp | dl_term_relationships | 202795 |
    | webraova_dlwp | dl_usermeta | 125228 | "

    He help me :
    "Upon review of the MySQL activities on the server we have implemented a few transparent performance enhancements for your account. The actions taken pertain to the implementation of MySQL indexing for the resource intensive SQL queries. As stated, the implementation should be transparent to the database driven software applications (WordPress, Joomla, etc.) operating on your account. The only significant change should be to the performance and response time of the SQL queries indexed and their associated page load times. The following link provides detailed information regarding the use of MySQL indexing and is provided for your reference: http://use-the-index-luke.com/sql/preface "

    And my site was decreased load on the MySQL and worked .
    Thanks

  • Mariana
    • New Recruit

    Hi! I have the same issue and the hosting provider just suspended my account, they say that the database where my wp installation is placed is making a lot of queries that blocked the server. Any idea of how can i solve this, it began to happen when i installed hummingbird to optimize my site, but don't know what is exactly, the minification, the compression...i feel stuck with this situation, please help!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.