Results 1 to 9 of 9
  1. #1

    Very high mysql load

    Hello,

    I am getting very high mysql usage on the server.

    The 'mysqladmin proc' commands gives very high number of (SELECT t.*, tt.*,....) queries.

    The alarming thing is these queries run under different cpanel accounts so it is not like one of the account is abusing the mysql, but different accounts are running same kind of queries .

    Code:
    | 2585 | username1_wp663 | localhost | username1_wp663     | Query   | 0    | Opening tables       | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  | 0.000    |
    | 2625 | username2_wp786 | localhost | username2_wp786     | Query   | 0    | Opening tables       | SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHER | 0.000    |
    | 2626 | username3_wp886 | localhost | username3_wp886     | Query   | 0    | Sending data         | SELECT   * FROM wp_posts  WHERE 1=1  AND wp_posts.post_type = 'gal_display_source' AND (wp_posts.pos | 0.000    |
    | 2628 | username4_wp938 | localhost | username4_wp938     | Query   | 0    | Copying to tmp table | SELECT t.*, tt.*, tr.object_id FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id =  | 0.000    |
    Please guide.

  2. #2
    You can try to optimize and repair all MySQL databases on your server.

    Repair of all databases:
    mysqlcheck -rA

    Optimize of all databases:
    mysqlcheck -oA

    Afterwards, restart MySQL.
    Visiba | UK and US based cPanel Web Hosting
    Softaculous Script Installer | Daily Backups | SEO Tools | SSD Servers
    99.9% Uptime Guarantee | 30 Days Money Back Guarantee | 24x7 Support

  3. #3
    Thanks visiba, its a relief. I was thinking someone was able to access the server and was running abusive queries...

    I have run the mentioned queries, hopefully it should fix the issues. However there are a bunch of errors e.g. database engine does not support the repair etc. Should I ignore those.

    Thanks

  4. #4
    Do a search for mysqltuner.pl and run that to view your leaks. Or, install something like New Relic if you prefer pretty graphs to look at

  5. #5
    Join Date
    Dec 2007
    Location
    LocalHost
    Posts
    1,303
    mysqltuner.pl can suggest you best mysql configuration based on your server resources and usage. Search how to install and use it.
    YagHost - Pure SSD Hosting | Since 2007 | Average Response Time: 15 min
    Web Hosting | Reseller Hosting | Managed VPS Hosting
    99.9% Server Uptime Guarantee | 24/7 Rapid Response Tech Support | 30 Day Money Back Guarantee
    LopHost.com - Web Hosting Tutorials

  6. #6
    Join Date
    Jul 2005
    Posts
    489
    Quote Originally Posted by p4kp4k
    However there are a bunch of errors e.g. database engine does not support the repair etc. Should I ignore those.
    That's InnoDB tables which do not support repair. You can ignore the warnings

  7. #7
    Join Date
    May 2012
    Location
    India
    Posts
    1,026

  8. #8
    Join Date
    May 2013
    Location
    India
    Posts
    748
    Optimizing databases and queries are as important as optimizing MySQL. To me, those "select *" commands doesn't looks good and it can generate enough CPU / IO load on a server with a reasonable size database. So whatever portion of wordpress ( perhaps a plugin ) generating those queries, try to tweak it, optimize and repair your MySQL tables as well.

  9. #9
    Quote Originally Posted by nixtree View Post
    Optimizing databases and queries are as important as optimizing MySQL. To me, those "select *" commands doesn't looks good and it can generate enough CPU / IO load on a server with a reasonable size database. So whatever portion of wordpress ( perhaps a plugin ) generating those queries, try to tweak it, optimize and repair your MySQL tables as well.
    Yes but these commands are wordpress's ones, and these are all coming from different cpanel accounts' wordpress installations. Maybe it is not the queries causing the issues but the mysql itself is somehow slowed down.

    After following the optimize / repair tables advise the mysql starts to work fine for good 12 to 24 hours then again it will create I/O wait time of over 90% causing the mysql to crash. Also I noticed the /tmp filling up and then only a server reboot fixes the issue.

    The funny thing is I use cloudlinux with sql governor which should stop abusive clients.

    I have contacted the datacenter support but they check the server running fine after a crash and confirm everything fine.

    Can anyone suggest some experienced person who can fix this issue freelance or a management company?

Similar Threads

  1. high load mysql
    By andalusy in forum Hosting Security and Technology
    Replies: 10
    Last Post: 02-26-2010, 04:05 AM
  2. High Mysql Load :(
    By crzy in forum Hosting Security and Technology
    Replies: 3
    Last Post: 03-15-2008, 11:55 AM
  3. High MySQL load
    By alphix in forum Hosting Security and Technology
    Replies: 7
    Last Post: 02-16-2007, 01:44 PM
  4. HIGH LOAD Because of MySQL
    By rumi in forum Hosting Security and Technology
    Replies: 3
    Last Post: 07-01-2006, 07:46 AM
  5. MySQL high load
    By Im_Goodspeed in forum Hosting Security and Technology
    Replies: 7
    Last Post: 06-20-2005, 06:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •