MySQL CPU usage 128%?
I backed up my site using an all in one WordPress migration plugin from shared hosting and restored on VPS hosting server. Everything worked fine except for 1 MySQL task eating up whole CPU usage.. I have read tons of techniques on different blogs but am unable to work for me because maybe I am not good with SQL yet. My server has 4GB RAM and 6 CPU cores. It's a WordPress video site using Linux. I have root permission. Using the TOP command I can see this:
top - 10:54:08 up 4:37, 2 users, load average: 147.65, 92.12, 80.78
Tasks: 459 total, 132 running, 327 sleeping, 0 stopped, 0 zombie
%Cpu(s): 88.6 us, 10.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.4 si, 0.4 st
KiB Mem : 12138208 total, 1139584 free, 4309540 used, 6689084 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 1533244 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1689 mysql 20 0 2406108 232064 6004 S 90.0 1.9 8:02.25 mysqld
3338 desi 20 0 307364 92600 60344 R 3.0 0.8 0:02.00 php-cgi
3839 desi 20 0 291948 75540 54316 R 3.0 0.6 0:01.21 php-cgi
3865 desi 20 0 290868 70440 50432 R 3.0 0.6 0:01.10 php-cgi
3889 desi 20 0 290092 64220 45164 R 3.0 0.5 0:00.96 php-cgi
3950 desi 20 0 285448 53004 38268 R 3.0 0.4 0:00.69 php-cgi
4018 desi 20 0 285020 45136 30932 R 3.0 0.4 0:00.51 php-cgi
3351 desi 20 0 307108 92492 60344 R 2.7 0.8 0:01.84 php-cgi
3368 desi 20 0 234228 35228 10016 R 2.7 0.3 0:01.82 php-cgi
3369 desi 20 0 307364 92608 60348 R 2.7 0.8 0:01.90 php-cgi
3380 desi 20 0 307464 92444 60276 R 2.7 0.8 0:01.76 php-cgi
3721 desi 20 0 302284 87640 60284 R 2.7 0.7 0:01.63 php-cgi
3805 desi 20 0 298336 81684 58128 R 2.7 0.7 0:01.38 php-cgi
3821 desi 20 0 292904 77348 55424 R 2.7 0.6 0:01.26 php-cgi
3834 desi 20 0 292052 74856 53956 R 2.7 0.6 0:01.23 php-cgi
3835 desi 20 0 290880 72508 52452 R 2.7 0.6 0:01.17 php-cgi
3894 desi 20 0 290420 65052 45868 R 2.7 0.5 0:00.97 php-cgi
3910 desi 20 0 290188 63656 44600 R 2.7 0.5 0:00.89 php-cgi
3943 desi 20 0 285448 55328 40568 R 2.7 0.5 0:00.74 php-cgi
3957 desi 20 0 285432 51500 36772 R 2.7 0.4 0:00.66 php-cgi
4034 desi 20 0 283688 40428 27976 R 2.7 0.3 0:00.42 php-cgi
4041 desi 20 0 283220 39336 27028 R 2.7 0.3 0:00.39 php-cgi
4046 desi 20 0 283104 39316 27192 R 2.7 0.3 0:00.38 php-cgi
4082 desi 20 0 282040 32044 20612 R 2.7 0.3 0:00.23 php-cgi
As you can see in the first row, MySQL is using much CPU. I killed it but it opened again.. I used WP optimize plugin but again the same. I optimized and repaired inside PHPMyAdmin but still the same high CPU. I changed the process priority using a command but again the same...
Can any one tell what to do here? Which command can I paste in root to fix this?
mysql optimization linux
edited Nov 2 '18 at 9:01
ibennetch
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 3 more comments
I backed up my site using an all in one WordPress migration plugin from shared hosting and restored on VPS hosting server. Everything worked fine except for 1 MySQL task eating up whole CPU usage.. I have read tons of techniques on different blogs but am unable to work for me because maybe I am not good with SQL yet. My server has 4GB RAM and 6 CPU cores. It's a WordPress video site using Linux. I have root permission. Using the TOP command I can see this:
top - 10:54:08 up 4:37, 2 users, load average: 147.65, 92.12, 80.78
Tasks: 459 total, 132 running, 327 sleeping, 0 stopped, 0 zombie
%Cpu(s): 88.6 us, 10.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.4 si, 0.4 st
KiB Mem : 12138208 total, 1139584 free, 4309540 used, 6689084 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 1533244 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1689 mysql 20 0 2406108 232064 6004 S 90.0 1.9 8:02.25 mysqld
3338 desi 20 0 307364 92600 60344 R 3.0 0.8 0:02.00 php-cgi
3839 desi 20 0 291948 75540 54316 R 3.0 0.6 0:01.21 php-cgi
3865 desi 20 0 290868 70440 50432 R 3.0 0.6 0:01.10 php-cgi
3889 desi 20 0 290092 64220 45164 R 3.0 0.5 0:00.96 php-cgi
3950 desi 20 0 285448 53004 38268 R 3.0 0.4 0:00.69 php-cgi
4018 desi 20 0 285020 45136 30932 R 3.0 0.4 0:00.51 php-cgi
3351 desi 20 0 307108 92492 60344 R 2.7 0.8 0:01.84 php-cgi
3368 desi 20 0 234228 35228 10016 R 2.7 0.3 0:01.82 php-cgi
3369 desi 20 0 307364 92608 60348 R 2.7 0.8 0:01.90 php-cgi
3380 desi 20 0 307464 92444 60276 R 2.7 0.8 0:01.76 php-cgi
3721 desi 20 0 302284 87640 60284 R 2.7 0.7 0:01.63 php-cgi
3805 desi 20 0 298336 81684 58128 R 2.7 0.7 0:01.38 php-cgi
3821 desi 20 0 292904 77348 55424 R 2.7 0.6 0:01.26 php-cgi
3834 desi 20 0 292052 74856 53956 R 2.7 0.6 0:01.23 php-cgi
3835 desi 20 0 290880 72508 52452 R 2.7 0.6 0:01.17 php-cgi
3894 desi 20 0 290420 65052 45868 R 2.7 0.5 0:00.97 php-cgi
3910 desi 20 0 290188 63656 44600 R 2.7 0.5 0:00.89 php-cgi
3943 desi 20 0 285448 55328 40568 R 2.7 0.5 0:00.74 php-cgi
3957 desi 20 0 285432 51500 36772 R 2.7 0.4 0:00.66 php-cgi
4034 desi 20 0 283688 40428 27976 R 2.7 0.3 0:00.42 php-cgi
4041 desi 20 0 283220 39336 27028 R 2.7 0.3 0:00.39 php-cgi
4046 desi 20 0 283104 39316 27192 R 2.7 0.3 0:00.38 php-cgi
4082 desi 20 0 282040 32044 20612 R 2.7 0.3 0:00.23 php-cgi
As you can see in the first row, MySQL is using much CPU. I killed it but it opened again.. I used WP optimize plugin but again the same. I optimized and repaired inside PHPMyAdmin but still the same high CPU. I changed the process priority using a command but again the same...
Can any one tell what to do here? Which command can I paste in root to fix this?
mysql optimization linux
edited Nov 2 '18 at 9:01
ibennetch
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune itsmax_connectionsdown to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.
– danblack
Sep 23 '18 at 22:02
1
SHOW GLOBAL STATUSandSHOW ENGINE INNODB STATUScan also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settingsSHOW GLOBAL VARIABLES.
– danblack
Sep 23 '18 at 22:25
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
1
High CPU means a naughty query. You must find it. Even a simpleSHOW FULL PROCESSLIST;may catch it.
– Rick James
Oct 10 '18 at 1:29
|
show 3 more comments
I backed up my site using an all in one WordPress migration plugin from shared hosting and restored on VPS hosting server. Everything worked fine except for 1 MySQL task eating up whole CPU usage.. I have read tons of techniques on different blogs but am unable to work for me because maybe I am not good with SQL yet. My server has 4GB RAM and 6 CPU cores. It's a WordPress video site using Linux. I have root permission. Using the TOP command I can see this:
top - 10:54:08 up 4:37, 2 users, load average: 147.65, 92.12, 80.78
Tasks: 459 total, 132 running, 327 sleeping, 0 stopped, 0 zombie
%Cpu(s): 88.6 us, 10.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.4 si, 0.4 st
KiB Mem : 12138208 total, 1139584 free, 4309540 used, 6689084 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 1533244 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1689 mysql 20 0 2406108 232064 6004 S 90.0 1.9 8:02.25 mysqld
3338 desi 20 0 307364 92600 60344 R 3.0 0.8 0:02.00 php-cgi
3839 desi 20 0 291948 75540 54316 R 3.0 0.6 0:01.21 php-cgi
3865 desi 20 0 290868 70440 50432 R 3.0 0.6 0:01.10 php-cgi
3889 desi 20 0 290092 64220 45164 R 3.0 0.5 0:00.96 php-cgi
3950 desi 20 0 285448 53004 38268 R 3.0 0.4 0:00.69 php-cgi
4018 desi 20 0 285020 45136 30932 R 3.0 0.4 0:00.51 php-cgi
3351 desi 20 0 307108 92492 60344 R 2.7 0.8 0:01.84 php-cgi
3368 desi 20 0 234228 35228 10016 R 2.7 0.3 0:01.82 php-cgi
3369 desi 20 0 307364 92608 60348 R 2.7 0.8 0:01.90 php-cgi
3380 desi 20 0 307464 92444 60276 R 2.7 0.8 0:01.76 php-cgi
3721 desi 20 0 302284 87640 60284 R 2.7 0.7 0:01.63 php-cgi
3805 desi 20 0 298336 81684 58128 R 2.7 0.7 0:01.38 php-cgi
3821 desi 20 0 292904 77348 55424 R 2.7 0.6 0:01.26 php-cgi
3834 desi 20 0 292052 74856 53956 R 2.7 0.6 0:01.23 php-cgi
3835 desi 20 0 290880 72508 52452 R 2.7 0.6 0:01.17 php-cgi
3894 desi 20 0 290420 65052 45868 R 2.7 0.5 0:00.97 php-cgi
3910 desi 20 0 290188 63656 44600 R 2.7 0.5 0:00.89 php-cgi
3943 desi 20 0 285448 55328 40568 R 2.7 0.5 0:00.74 php-cgi
3957 desi 20 0 285432 51500 36772 R 2.7 0.4 0:00.66 php-cgi
4034 desi 20 0 283688 40428 27976 R 2.7 0.3 0:00.42 php-cgi
4041 desi 20 0 283220 39336 27028 R 2.7 0.3 0:00.39 php-cgi
4046 desi 20 0 283104 39316 27192 R 2.7 0.3 0:00.38 php-cgi
4082 desi 20 0 282040 32044 20612 R 2.7 0.3 0:00.23 php-cgi
As you can see in the first row, MySQL is using much CPU. I killed it but it opened again.. I used WP optimize plugin but again the same. I optimized and repaired inside PHPMyAdmin but still the same high CPU. I changed the process priority using a command but again the same...
Can any one tell what to do here? Which command can I paste in root to fix this?
mysql optimization linux
edited Nov 2 '18 at 9:01
ibennetch
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
I backed up my site using an all in one WordPress migration plugin from shared hosting and restored on VPS hosting server. Everything worked fine except for 1 MySQL task eating up whole CPU usage.. I have read tons of techniques on different blogs but am unable to work for me because maybe I am not good with SQL yet. My server has 4GB RAM and 6 CPU cores. It's a WordPress video site using Linux. I have root permission. Using the TOP command I can see this:
top - 10:54:08 up 4:37, 2 users, load average: 147.65, 92.12, 80.78
Tasks: 459 total, 132 running, 327 sleeping, 0 stopped, 0 zombie
%Cpu(s): 88.6 us, 10.6 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.4 si, 0.4 st
KiB Mem : 12138208 total, 1139584 free, 4309540 used, 6689084 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 1533244 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1689 mysql 20 0 2406108 232064 6004 S 90.0 1.9 8:02.25 mysqld
3338 desi 20 0 307364 92600 60344 R 3.0 0.8 0:02.00 php-cgi
3839 desi 20 0 291948 75540 54316 R 3.0 0.6 0:01.21 php-cgi
3865 desi 20 0 290868 70440 50432 R 3.0 0.6 0:01.10 php-cgi
3889 desi 20 0 290092 64220 45164 R 3.0 0.5 0:00.96 php-cgi
3950 desi 20 0 285448 53004 38268 R 3.0 0.4 0:00.69 php-cgi
4018 desi 20 0 285020 45136 30932 R 3.0 0.4 0:00.51 php-cgi
3351 desi 20 0 307108 92492 60344 R 2.7 0.8 0:01.84 php-cgi
3368 desi 20 0 234228 35228 10016 R 2.7 0.3 0:01.82 php-cgi
3369 desi 20 0 307364 92608 60348 R 2.7 0.8 0:01.90 php-cgi
3380 desi 20 0 307464 92444 60276 R 2.7 0.8 0:01.76 php-cgi
3721 desi 20 0 302284 87640 60284 R 2.7 0.7 0:01.63 php-cgi
3805 desi 20 0 298336 81684 58128 R 2.7 0.7 0:01.38 php-cgi
3821 desi 20 0 292904 77348 55424 R 2.7 0.6 0:01.26 php-cgi
3834 desi 20 0 292052 74856 53956 R 2.7 0.6 0:01.23 php-cgi
3835 desi 20 0 290880 72508 52452 R 2.7 0.6 0:01.17 php-cgi
3894 desi 20 0 290420 65052 45868 R 2.7 0.5 0:00.97 php-cgi
3910 desi 20 0 290188 63656 44600 R 2.7 0.5 0:00.89 php-cgi
3943 desi 20 0 285448 55328 40568 R 2.7 0.5 0:00.74 php-cgi
3957 desi 20 0 285432 51500 36772 R 2.7 0.4 0:00.66 php-cgi
4034 desi 20 0 283688 40428 27976 R 2.7 0.3 0:00.42 php-cgi
4041 desi 20 0 283220 39336 27028 R 2.7 0.3 0:00.39 php-cgi
4046 desi 20 0 283104 39316 27192 R 2.7 0.3 0:00.38 php-cgi
4082 desi 20 0 282040 32044 20612 R 2.7 0.3 0:00.23 php-cgi
As you can see in the first row, MySQL is using much CPU. I killed it but it opened again.. I used WP optimize plugin but again the same. I optimized and repaired inside PHPMyAdmin but still the same high CPU. I changed the process priority using a command but again the same...
Can any one tell what to do here? Which command can I paste in root to fix this?
mysql optimization linux
mysql optimization linux
edited Nov 2 '18 at 9:01
ibennetch
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
edited Nov 2 '18 at 9:01
ibennetch
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
edited Nov 2 '18 at 9:01
ibennetch
45826
edited Nov 2 '18 at 9:01
ibennetch
45826
edited Nov 2 '18 at 9:01
ibennetch
45826
45826
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
asked Sep 23 '18 at 18:03
now 2daysnow 2days
11
11
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 7 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
2
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune itsmax_connectionsdown to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.
– danblack
Sep 23 '18 at 22:02
1
SHOW GLOBAL STATUSandSHOW ENGINE INNODB STATUScan also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settingsSHOW GLOBAL VARIABLES.
– danblack
Sep 23 '18 at 22:25
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
1
High CPU means a naughty query. You must find it. Even a simpleSHOW FULL PROCESSLIST;may catch it.
– Rick James
Oct 10 '18 at 1:29
|
show 3 more comments
2
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune itsmax_connectionsdown to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.
– danblack
Sep 23 '18 at 22:02
1
SHOW GLOBAL STATUSandSHOW ENGINE INNODB STATUScan also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settingsSHOW GLOBAL VARIABLES.
– danblack
Sep 23 '18 at 22:25
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
1
High CPU means a naughty query. You must find it. Even a simpleSHOW FULL PROCESSLIST;may catch it.
– Rick James
Oct 10 '18 at 1:29
2
2
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune its
max_connections down to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.– danblack
Sep 23 '18 at 22:02
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune its
max_connections down to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.– danblack
Sep 23 '18 at 22:02
1
1
SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS can also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settings SHOW GLOBAL VARIABLES.– danblack
Sep 23 '18 at 22:25
SHOW GLOBAL STATUS and SHOW ENGINE INNODB STATUS can also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settings SHOW GLOBAL VARIABLES.– danblack
Sep 23 '18 at 22:25
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
1
1
High CPU means a naughty query. You must find it. Even a simple
SHOW FULL PROCESSLIST; may catch it.– Rick James
Oct 10 '18 at 1:29
High CPU means a naughty query. You must find it. Even a simple
SHOW FULL PROCESSLIST; may catch it.– Rick James
Oct 10 '18 at 1:29
|
show 3 more comments
1 Answer
1
active
oldest
votes
Threads_cache=0 / Threads_created=~5k so set the thread_cache_size. (uptime=38852). 50 might be a good figure considering max_used_connections=60.
Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total - mostly free and your SHOW ENGINE INNODB STATUS is showing no activity.
I suspect all of tables aren't innodb and hence causing a concurrency problem. So for each table x, ALTER TABLE x ENGINE=innodb. The table will be unaccessable for read/writing during this time.
com_update (number of UPDATE) queries is about 10% of com_select which is probably enough to cause some contention especially if the update is a slow query. A MyISAM table cannot be accessed while an update on that table is occurring.
Handler_read_rnd_next is 300x the value of Handler_read_rnd. This would indicate a large number of incorrectly indexed tabled. Enable your slow query log, set log-slow-verbosity=query_plan,explain (if using mariadb-10.0 or greater), and set long_query_time to 0.1. pt-query-digest can help identify the the common slow queries so you can prioritize work. When seeking help of this, show the EXPLAIN {query} output (will be in the raw log), the query itself, and the SHOW CREATE TABLE {tablename} for each table in the query.
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
|
show 5 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218360%2fmysql-cpu-usage-128%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Threads_cache=0 / Threads_created=~5k so set the thread_cache_size. (uptime=38852). 50 might be a good figure considering max_used_connections=60.
Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total - mostly free and your SHOW ENGINE INNODB STATUS is showing no activity.
I suspect all of tables aren't innodb and hence causing a concurrency problem. So for each table x, ALTER TABLE x ENGINE=innodb. The table will be unaccessable for read/writing during this time.
com_update (number of UPDATE) queries is about 10% of com_select which is probably enough to cause some contention especially if the update is a slow query. A MyISAM table cannot be accessed while an update on that table is occurring.
Handler_read_rnd_next is 300x the value of Handler_read_rnd. This would indicate a large number of incorrectly indexed tabled. Enable your slow query log, set log-slow-verbosity=query_plan,explain (if using mariadb-10.0 or greater), and set long_query_time to 0.1. pt-query-digest can help identify the the common slow queries so you can prioritize work. When seeking help of this, show the EXPLAIN {query} output (will be in the raw log), the query itself, and the SHOW CREATE TABLE {tablename} for each table in the query.
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
|
show 5 more comments
Threads_cache=0 / Threads_created=~5k so set the thread_cache_size. (uptime=38852). 50 might be a good figure considering max_used_connections=60.
Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total - mostly free and your SHOW ENGINE INNODB STATUS is showing no activity.
I suspect all of tables aren't innodb and hence causing a concurrency problem. So for each table x, ALTER TABLE x ENGINE=innodb. The table will be unaccessable for read/writing during this time.
com_update (number of UPDATE) queries is about 10% of com_select which is probably enough to cause some contention especially if the update is a slow query. A MyISAM table cannot be accessed while an update on that table is occurring.
Handler_read_rnd_next is 300x the value of Handler_read_rnd. This would indicate a large number of incorrectly indexed tabled. Enable your slow query log, set log-slow-verbosity=query_plan,explain (if using mariadb-10.0 or greater), and set long_query_time to 0.1. pt-query-digest can help identify the the common slow queries so you can prioritize work. When seeking help of this, show the EXPLAIN {query} output (will be in the raw log), the query itself, and the SHOW CREATE TABLE {tablename} for each table in the query.
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
|
show 5 more comments
Threads_cache=0 / Threads_created=~5k so set the thread_cache_size. (uptime=38852). 50 might be a good figure considering max_used_connections=60.
Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total - mostly free and your SHOW ENGINE INNODB STATUS is showing no activity.
I suspect all of tables aren't innodb and hence causing a concurrency problem. So for each table x, ALTER TABLE x ENGINE=innodb. The table will be unaccessable for read/writing during this time.
com_update (number of UPDATE) queries is about 10% of com_select which is probably enough to cause some contention especially if the update is a slow query. A MyISAM table cannot be accessed while an update on that table is occurring.
Handler_read_rnd_next is 300x the value of Handler_read_rnd. This would indicate a large number of incorrectly indexed tabled. Enable your slow query log, set log-slow-verbosity=query_plan,explain (if using mariadb-10.0 or greater), and set long_query_time to 0.1. pt-query-digest can help identify the the common slow queries so you can prioritize work. When seeking help of this, show the EXPLAIN {query} output (will be in the raw log), the query itself, and the SHOW CREATE TABLE {tablename} for each table in the query.
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
Threads_cache=0 / Threads_created=~5k so set the thread_cache_size. (uptime=38852). 50 might be a good figure considering max_used_connections=60.
Innodb_buffer_pool_pages_free/Innodb_buffer_pool_pages_total - mostly free and your SHOW ENGINE INNODB STATUS is showing no activity.
I suspect all of tables aren't innodb and hence causing a concurrency problem. So for each table x, ALTER TABLE x ENGINE=innodb. The table will be unaccessable for read/writing during this time.
com_update (number of UPDATE) queries is about 10% of com_select which is probably enough to cause some contention especially if the update is a slow query. A MyISAM table cannot be accessed while an update on that table is occurring.
Handler_read_rnd_next is 300x the value of Handler_read_rnd. This would indicate a large number of incorrectly indexed tabled. Enable your slow query log, set log-slow-verbosity=query_plan,explain (if using mariadb-10.0 or greater), and set long_query_time to 0.1. pt-query-digest can help identify the the common slow queries so you can prioritize work. When seeking help of this, show the EXPLAIN {query} output (will be in the raw log), the query itself, and the SHOW CREATE TABLE {tablename} for each table in the query.
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
edited Sep 25 '18 at 20:52
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
answered Sep 25 '18 at 0:50
danblackdanblack
1,5521212
1,5521212
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
|
show 5 more comments
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
SHOW GLOBAL STATUS LIKE 'Connections'; gives.....value 143512. SHOW GLOBAL STATUS LIKE 'max_used_connections'; 152 SHOW GLOBAL STATUS LIKE 'Threads_created'; 143444
– now 2days
Sep 25 '18 at 17:42
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
For ALTER TABLE x ENGINE=innodb --------MySQL said: #1146 - Table 'wp82.x' doesn't exist
– now 2days
Sep 25 '18 at 17:49
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
You're going to have to read and think. Change all tables to innodb
– danblack
Sep 25 '18 at 19:31
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
I read this and applied but still the same case... I checked this commad log-error=/var/log/mysqld.log and it gave me long data Pastebin. i am Stuck at cpu... hosting server asking $60 for this work resolution.
– now 2days
Sep 26 '18 at 17:05
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
with innodb_buffer pool size of 128M how did you run out of RAM? Sounds like cheap resolution if you can't work it out.
– danblack
Sep 26 '18 at 21:11
|
show 5 more comments
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218360%2fmysql-cpu-usage-128%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Don't kill off mysql processes. You'll only end up in a data recovery scenario. Enable the slow query log, look at its output and see what can be improved in queries/indexes (you can't rely on WP optimize plugin obviously). If your database doesn't support a large number of connections, tune its
max_connectionsdown to a sane number. Please provide my.cnf configuration. When you have common entries in the slow query log, use 'EXPLAIN {query}', and 'SHOW CREATE TABLE {tblname}', and we'll see what can be improved.– danblack
Sep 23 '18 at 22:02
1
SHOW GLOBAL STATUSandSHOW ENGINE INNODB STATUScan also show some information as to what is going on. Share them if you need help. Obviously there is no single root command that can fix this. You're going to have to learn a bit of MySQL. Based on the assumption your previous shared hosting wasn't terrible, do you have access to their database configuration file (my.cnf) or settingsSHOW GLOBAL VARIABLES.– danblack
Sep 23 '18 at 22:25
Thank you for the reply. i have added the required info here at Link . Kindly check please.
– now 2days
Sep 24 '18 at 15:24
Additional information request, please. Post on pastebin.com or here. Text results of: C) SHOW GLOBAL VARIABLES; D) complete MySQLTuner report AND Optional very helpful information, if available includes - htop OR top OR mytop for most active apps, ulimit -a for a linux/unix list of limits, iostat -x when system is busy for an idea of IOPS by device, df -h for a linux/unix free space list by device, includes hda & sda (SSD) filesystem type clues. ffor server tuning analysis.
– Wilson Hauck
Oct 3 '18 at 17:23
1
High CPU means a naughty query. You must find it. Even a simple
SHOW FULL PROCESSLIST;may catch it.– Rick James
Oct 10 '18 at 1:29