Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Mysql running very slow with high CPU usage and High load average, Please Help
Posted on 2014-02-10
Hi,
I have running a website with database mysql on RHEL 6 but mysql using 90-95% CPU and very high load average. It slow down with every connection for website. Please help
here is my my.cnf
[mysqld]
datadir=/test
socket=/var/lib/mysql/mysq
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=500
max_allowed_packet=2G
wait_timeout=1500
long_query_time = 5
query_cache_size=536870912
query_cache_limit=8388608
max_allowed_packet=3355443
sort_buffer_size=16777216
read_buffer_size=16777216
read_rnd_buffer_size=33554
key_buffer_size=134217728
myisam_sort_buffer_size=53
join_buffer_size=4194304
tmp_table_size=5343543296
max_heap_table_size=319605
#log-slow-queries = /var/log/mysql/mysql-slow.
#long_query_time = 1
[mysqld_safe]
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
EOF-----------------------
Here is the process list
mysql> show full processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 89085
Current database: *** NONE ***
+-------+------+----------
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+----------
| 89026 | remote | 10.59.7.7:45606 | remote_db | Execute | 7 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89030 | remote | 10.59.7.7:45607 | remote_db | Execute | 31 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89033 | remote | 10.59.7.7:45608 | remote_db | Sleep | 0 | | NULL |
| 89047 | remote | 10.59.7.6:50608 | remote_db | Execute | 10 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89048 | remote | 10.59.7.6:50610 | remote_db | Execute | 1 | preparing | SELECT site.site_id ,SIT_DISPLAY_NAME ,SIT_LOGO_PATH, site.company, site.SIT_ACCOUNT_NUMBER ,site.country FROM site_accounts site where site.SIT_ACCOUNT_STATUS='A
| 89049 | remote | 10.59.7.6:50633 | remote_db | Sleep | 6 | | NULL |
| 89050 | remote | 10.59.7.6:50643 | remote_db | Sleep | 2 | | NULL |
| 89051 | remote | 10.59.7.7:45610 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89052 | remote | 10.59.7.6:50647 | remote_db | Sleep | 1 | | NULL |
| 89053 | remote | 10.59.7.7:45611 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89054 | remote | 10.59.7.6:50648 | remote_db | Sleep | 79 | | NULL |
| 89055 | remote | 10.59.7.7:45613 | remote_db | Execute | 35 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89056 | remote | 10.59.7.6:50653 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89057 | remote | 10.59.7.6:50654 | remote_db | Execute | 24 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89058 | remote | 10.59.7.6:50655 | remote_db | Sleep | 0 | | NULL |
| 89060 | remote | 10.59.7.6:50657 | remote_db | Execute | 5 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89061 | remote | 10.59.7.6:50659 | remote_db | Execute | 36 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89062 | remote | 10.59.7.7:45614 | remote_db | Sleep | 0 | | NULL |
| 89065 | remote | 10.59.7.7:45616 | remote_db | Execute | 30 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89066 | remote | 10.59.7.6:50676 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89070 | remote | 10.59.7.7:45617 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89071 | remote | 10.59.7.7:45619 | remote_db | Execute | 54 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89072 | remote | 10.59.7.7:45620 | remote_db | Execute | 29 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89073 | remote | 10.59.7.7:45621 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89074 | remote | 10.59.7.7:45622 | remote_db | Execute | 33 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89075 | remote | 10.59.7.7:45623 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89080 | remote | 10.59.7.7:45625 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89081 | remote | 10.59.7.7:45626 | remote_db | Execute | 14 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89082 | remote | 10.59.7.7:45627 | remote_db | Sleep | 4 | | NULL |
| 89083 | remote | 10.59.7.7:45628 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate, remlic.enddate, lic_types.name,CASE WHEN remlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_acce
| 89084 | remote | 10.59.7.7:45629 | remote_db | Execute | 1 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
+-------+------+----------
32 rows in set (0.03 sec)
I have running a website with database mysql on RHEL 6 but mysql using 90-95% CPU and very high load average. It slow down with every connection for website. Please help
here is my my.cnf
[mysqld]
datadir=/test
socket=/var/lib/mysql/mysq
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections=500
max_allowed_packet=2G
wait_timeout=1500
long_query_time = 5
query_cache_size=536870912
query_cache_limit=8388608
max_allowed_packet=3355443
sort_buffer_size=16777216
read_buffer_size=16777216
read_rnd_buffer_size=33554
key_buffer_size=134217728
myisam_sort_buffer_size=53
join_buffer_size=4194304
tmp_table_size=5343543296
max_heap_table_size=319605
#log-slow-queries = /var/log/mysql/mysql-slow.
#long_query_time = 1
[mysqld_safe]
log-error=/var/log/mysqld.
pid-file=/var/run/mysqld/m
EOF-----------------------
Here is the process list
mysql> show full processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 89085
Current database: *** NONE ***
+-------+------+----------
| Id | User | Host | db | Command | Time | State | Info |
+-------+------+----------
| 89026 | remote | 10.59.7.7:45606 | remote_db | Execute | 7 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89030 | remote | 10.59.7.7:45607 | remote_db | Execute | 31 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89033 | remote | 10.59.7.7:45608 | remote_db | Sleep | 0 | | NULL |
| 89047 | remote | 10.59.7.6:50608 | remote_db | Execute | 10 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89048 | remote | 10.59.7.6:50610 | remote_db | Execute | 1 | preparing | SELECT site.site_id ,SIT_DISPLAY_NAME ,SIT_LOGO_PATH, site.company, site.SIT_ACCOUNT_NUMBER ,site.country FROM site_accounts site where site.SIT_ACCOUNT_STATUS='A
| 89049 | remote | 10.59.7.6:50633 | remote_db | Sleep | 6 | | NULL |
| 89050 | remote | 10.59.7.6:50643 | remote_db | Sleep | 2 | | NULL |
| 89051 | remote | 10.59.7.7:45610 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89052 | remote | 10.59.7.6:50647 | remote_db | Sleep | 1 | | NULL |
| 89053 | remote | 10.59.7.7:45611 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89054 | remote | 10.59.7.6:50648 | remote_db | Sleep | 79 | | NULL |
| 89055 | remote | 10.59.7.7:45613 | remote_db | Execute | 35 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89056 | remote | 10.59.7.6:50653 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89057 | remote | 10.59.7.6:50654 | remote_db | Execute | 24 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89058 | remote | 10.59.7.6:50655 | remote_db | Sleep | 0 | | NULL |
| 89060 | remote | 10.59.7.6:50657 | remote_db | Execute | 5 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89061 | remote | 10.59.7.6:50659 | remote_db | Execute | 36 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89062 | remote | 10.59.7.7:45614 | remote_db | Sleep | 0 | | NULL |
| 89065 | remote | 10.59.7.7:45616 | remote_db | Execute | 30 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89066 | remote | 10.59.7.6:50676 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89070 | remote | 10.59.7.7:45617 | remote_db | Execute | 21 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89071 | remote | 10.59.7.7:45619 | remote_db | Execute | 54 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89072 | remote | 10.59.7.7:45620 | remote_db | Execute | 29 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89073 | remote | 10.59.7.7:45621 | remote_db | Execute | 17 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89074 | remote | 10.59.7.7:45622 | remote_db | Execute | 33 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89075 | remote | 10.59.7.7:45623 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89080 | remote | 10.59.7.7:45625 | remote_db | Execute | 23 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89081 | remote | 10.59.7.7:45626 | remote_db | Execute | 14 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
| 89082 | remote | 10.59.7.7:45627 | remote_db | Sleep | 4 | | NULL |
| 89083 | remote | 10.59.7.7:45628 | remote_db | Execute | 13 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate, remlic.enddate, lic_types.name,CASE WHEN remlic.cont_accessfrom IS NULL THEN '1900' ELSE SUBSTRING(remlic.cont_acce
| 89084 | remote | 10.59.7.7:45629 | remote_db | Execute | 1 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, remlic.startdate,remlic.en
+-------+------+----------
32 rows in set (0.03 sec)
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2-
2
4 Comments
Are your tables indexed?
If so, how often do you have an ANALYZE TABLE issued to update the index statistics?
Can you get an explain plan for that query that shows up over and over in that output you provided?
If so, how often do you have an ANALYZE TABLE issued to update the index statistics?
Can you get an explain plan for that query that shows up over and over in that output you provided?
Hi,
thanks for your reply. i have checked tables are indexed, but update statistics is not done from past 3 months.
Please suggest.
Thanks
thanks for your reply. i have checked tables are indexed, but update statistics is not done from past 3 months.
Please suggest.
Thanks
Three months is way too old. Use the analyze table command and set up a weekly or more often scheduled job to run it.
http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Note that there is a table lock while this operation is performed; normally it is scheduled for when low activity is expected. In your case, it sounds like an amergency so run it asap but then schedule it for slow times.
http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
Note that there is a table lock while this operation is performed; normally it is scheduled for when low activity is expected. In your case, it sounds like an amergency so run it asap but then schedule it for slow times.
Hi,
We have updated the index now.
But when the load increases it is showing the same issue:
603 | apps | 10.59.7.7:51677 | Reg_data | Sleep | 18 | | NULL |
| 616 | apps | 10.59.7.7:51798 | Reg_data | Sleep | 18 | | NULL |
| 617 | apps | 10.59.7.7:51801 | Reg_data | Sleep | 112 | | NULL |
| 620 | apps | 10.59.7.7:51804 | Reg_data | Sleep | 112 | | NULL |
| 621 | apps | 10.59.7.7:51806 | Reg_data | Sleep | 18 | | NULL |
| 622 | apps | 10.59.7.7:51807 | Reg_data | Sleep | 18 | | NULL |
| 623 | apps | 10.59.7.7:51808 | Reg_data | Sleep | 18 | | NULL |
| 624 | apps | 10.31.1.7:59201 | Reg_data | Sleep | 10 | | NULL |
| 625 | apps | 10.31.1.7:59202 | Reg_data | Execute | 7 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, licenses.startdate, licenses.enddate, |
| 630 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 632 | apps | 10.31.1.7:59214 | Reg_data | Sleep | 1 | | NULL |
| 633 | apps | 10.31.1.7:59215 | Reg_data | Sleep | 0 | | NULL |
| 634 | apps | 10.31.1.7:59216 | Reg_data | Sleep | 1 | | NULL |
We have updated the index now.
But when the load increases it is showing the same issue:
603 | apps | 10.59.7.7:51677 | Reg_data | Sleep | 18 | | NULL |
| 616 | apps | 10.59.7.7:51798 | Reg_data | Sleep | 18 | | NULL |
| 617 | apps | 10.59.7.7:51801 | Reg_data | Sleep | 112 | | NULL |
| 620 | apps | 10.59.7.7:51804 | Reg_data | Sleep | 112 | | NULL |
| 621 | apps | 10.59.7.7:51806 | Reg_data | Sleep | 18 | | NULL |
| 622 | apps | 10.59.7.7:51807 | Reg_data | Sleep | 18 | | NULL |
| 623 | apps | 10.59.7.7:51808 | Reg_data | Sleep | 18 | | NULL |
| 624 | apps | 10.31.1.7:59201 | Reg_data | Sleep | 10 | | NULL |
| 625 | apps | 10.31.1.7:59202 | Reg_data | Execute | 7 | Copying to tmp table | select distinct p.product_id, p.product_code, p.product_desc, licenses.startdate, licenses.enddate, |
| 630 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 632 | apps | 10.31.1.7:59214 | Reg_data | Sleep | 1 | | NULL |
| 633 | apps | 10.31.1.7:59215 | Reg_data | Sleep | 0 | | NULL |
| 634 | apps | 10.31.1.7:59216 | Reg_data | Sleep | 1 | | NULL |
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times.
For those of us that don't have the luxury of using all transact…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special.
It's completely agentless, but does let you create an agent, if you desire.
It offers powerful scalability …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 10 hours left to enroll
632 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.