mysqld is using too much CPU

by Derp Dee Derp   Last Updated September 11, 2019 18:00 PM

I run this code bellow in the browser console, to test if i'm vulnerable to DoS, and i realized that mysqld is using ~90% of CPU and 9.3 of RAM for about 5 seconds when i run the code, and the server does not respond till it finishes the requests. Inicially the nginx use 50% of CPU, also the mysqld do the same, after 2 seconds the mysqld start using 90% of CPU.

I want to know if it is normal, or if there's a problem, i mean, it looks like there's a problem.

var i;
for(i = 0; i < 50; i++){
    $.ajax({url:'https://example.com'})
}

Result from the command top when i run the js:

top - 10:05:51 up 3 days, 16:40,  0 users,  load average: 0.25, 0.07, 0.02                              
Tasks:  35 total,   1 running,  34 sleeping,   0 stopped,   0 zombie                                    
%Cpu(s): 94.7 us,  5.0 sy,  0.0 ni,  0.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st                         
KiB Mem :  2097152 total,   635476 free,   640476 used,   821200 buff/cache                             
KiB Swap:  2097152 total,  2067536 free,    29616 used.  1320984 avail Mem                              

  PID USER        PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                             
10515 mysql       20   0  696732 197796   9132 S 93.0  9.4   4:15.96 mysqld                               
 7339 nobody      20   0  259664  38300   4100 S  1.7  1.8   0:21.65 nginx                                
15184 myusername  20   0  367452   9232   6052 S  0.7  0.4   0:00.10 php-fpm                              
15185 myusername  20   0  367452   9244   6052 S  0.7  0.4   0:00.10 php-fpm                              
15186 myusername  20   0  364188   5676   4004 S  0.7  0.3   0:00.08 php-fpm                              
15187 myusername  20   0  364188   5676   4004 S  0.7  0.3   0:00.08 php-fpm                              
15188 myusername  20   0  364188   5676   4004 S  0.7  0.3   0:00.08 php-fpm                              
15189 myusername  20   0  364188   5680   4008 S  0.7  0.3   0:00.08 php-fpm                              
  402 root        20   0  117760   2464    940 S  0.3  0.1   0:51.09 supervisord                          
15182 myusername  20   0  367452   9332   6124 S  0.3  0.4   0:00.11 php-fpm                              
15183 myusername  20   0  364188   5684   4008 S  0.3  0.3   0:00.08 php-fpm                              
    1 root        20   0  178412   3080   2100 S  0.0  0.1   0:14.00 systemd                              
    2 root        20   0       0      0      0 S  0.0  0.0   0:00.00 kthreadd/2867                        
    3 root        20   0       0      0      0 S  0.0  0.0   0:00.05 khelper/2867                         
   64 root        20   0   47272   6636   6504 S  0.0  0.3   0:03.09 systemd-journal                      
   66 root        20   0   42768    952    948 S  0.0  0.0   0:00.00 systemd-udevd     

I don't know if it's helpful, but my php-fpm.conf is here: https://pastebin.com/raw/iyNR55Ek

I enabled the slow_query_log (for 1 second), and there's no slow queries. This is the query that is executed:

SELECT title, slug FROM table_name WHERE slug = ? AND type = ? LIMIT 1, 30

This is the result from the EXPLAIN:

id | select_type |  table     |  type        |  possible_keys | key       | key_len | ref  | rows | Extra   
1  |    SIMPLE   | table_name | index_merge  |  slug,type     | slug,type | 63,36   | NULL | 760  | Using intersect(slug,type); Using where

I have two indexes, one for the column slug and other for type. I used OPTMIZE in my table but there's no change.

NOTE: I don't have any custom configuration in the file my.cnf. And it's blank.

This is my server config:

CPU: 1 core
RAM: 2 GB
SSD: 15GB
Nginx: 1.16
PHP-FPM 7.3.8
Centos 7

Maybe my server config is low (for the amount of requests that i did in the js)? I have a score of 97/100 on google pagespeed, the only problem i have is with TTFB (which is ~750ms).

If the problem is with my server config, then 2 CPU cores would handle it? Any help is welcome.

Tags : mysql vps database


Answers 1


Add INDEX(slug, type) (in either order). It will be better than using "index_merge".

Lower long_query_time to 0.1 or perhaps 0. The DOS attack (or password cracking) will be lots of fast queries. Then use pt-query-digest to summarize the sloglog.

Rick James
Rick James
September 11, 2019 17:08 PM

Related Questions


Updated February 21, 2017 11:00 AM

Updated December 01, 2015 18:00 PM

Updated June 05, 2015 04:00 AM

Updated December 04, 2015 20:00 PM

Updated February 21, 2017 13:00 PM