Mysql got 400% cpu usage

Question

Mysql at my ubuntu server got this output

14312 mysql     20   0 2127664 219892   7540 S 409.0  1.3   2041:15 mysqld

It use 409% of cpu usage, and only 1.3% of memory

How can I optimise my sql code to make it use lesser cpu usage.

I am running a lot of code to insert data into mysql.

I saw that website mention using "procedure"

Now i am using statement like

insert into table (col1,col2) values (val1,val2)

select statement with join tables

My database is quite big, with over 1million row, what should I do to reduce cpu usage. sorry for asking this but I tried look around and

this is the output for "free"

   total       used       free     shared    buffers     cached
Mem:      16376692    9126016    7250676     245324     294300    6995448
-/+ buffers/cache:    1836268   14540424
Swap:        93180      27864      65316

Any idea if I can tune my.cnf to improve the situation ?

I am running INNODB

Thanks for helping

 PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND             
14312 mysql     20   0 2127664 218224   7540 S 592.3  1.3   2057:29 mysqld              
15419 root      20   0   48348   5424   2752 S   1.0  0.0   2:26.95 ssh                 
15424 root      20   0 1050752 206128    560 S   0.7  1.3   2:06.20 sshfs               
  202 root      20   0       0      0      0 S   0.3  0.0   1:38.71 jbd2/sda5-8         
  857 unbound   20   0   51020   6960   1640 S   0.3  0.0   1:27.65 unbound             
 6182 root      20   0       0      0      0 S   0.3  0.0   0:51.75 kworker/1:2         
 7894 root      20   0  238120  14056   7184 S   0.3  0.1   0:01.47 php                 
10251 root      20   0  237868  13820   7184 S   0.3  0.1   0:08.76 php                 
14990 root      20   0  237856  13764   7184 S   0.3  0.1   0:03.24 php                 
22098 root      20   0  239256  15616   7608 S   0.3  0.1   0:04.80 php                 
    1 root      20   0   33388   2452   1392 S   0.0  0.0   0:07.84 init                
    2 root      20   0       0      0      0 S   0.0  0.0   0:00.02 kthreadd            
    3 root      20   0       0      0      0 S   0.0  0.0   0:00.42 ksoftirqd/0

@mozziemd i know its funny, haha I updated my question u can see — Manga Black, Aug 31 '15 at 16:27
i think that's a bug, it must be 59.23% for sure, or even 5.923% — MozzieMD, Aug 31 '15 at 16:28
I once had the same. But that was because there was an error in my time checking clausel which always turned true and so inserted always a few thousand rows (100k+) everytime the cronjob ran (every minute). Because of that MySQL always had to do something (making indexes, fill table, etc...). — Charlotte Dunois, Aug 31 '15 at 16:30
If you had indexes, yes you could improve it, maybe bring it down to 210% — Drew, Aug 31 '15 at 16:33
Check this question [MySQL high CPU usage](http://stackoverflow.com/questions/1282232/mysql-high-cpu-usage?rq=1) — MozzieMD, Aug 31 '15 at 16:33
@Drew how can you use 210% of cpu if it can give you only 100% ? ) — MozzieMD, Aug 31 '15 at 16:35
The usage is relative to a single core. If it's reporting 400%, its occupying 4 cores. More: http://superuser.com/questions/174660/why-is-the-cpu-usage-reported-by-top-in-linux-over-100 — nickb, Aug 31 '15 at 16:38
@nickb okay . but any idea how to get things fixed . will a more core server help? — Manga Black, Aug 31 '15 at 16:42

Mysql got 400% cpu usage

0 Answers0