MySQL CPU usage 128%?












0















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?










share|improve this question
















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 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





    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











  • 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
















0















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?










share|improve this question
















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 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





    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











  • 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














0












0








0








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?










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 2 '18 at 9:01









ibennetch

45826




45826










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 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





    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











  • 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














  • 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






  • 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











  • 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








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










1 Answer
1






active

oldest

votes


















0














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.






share|improve this answer


























  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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.






share|improve this answer


























  • 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
















0














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.






share|improve this answer


























  • 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














0












0








0







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Sep 25 '18 at 20:52

























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 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


















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh