skip to Main Content

I’m using centos7 as my server, when running a php page with mysql query i find that mysql is running about 4 processes using htop,knowing my thread concurrency is 1, and only one group should run according to my.conf too, but the server runs at least two instances using that cpu processing power, it sometimes reaches 2400% cpu power

my.conf

    [client]                                    
port            = 3306                      
socket          = /var/lib/mysql/mysql.sock 


[mysqld_safe]                               
innodb_buffer_pool_size=3000M               
innodb_additional_mem_pool_size=8500K       
innodb_log_buffer_size=20G                  
innodb_thread_concurrency=10                
innodb_file_per_table=1                     

[mysqld]                                    

log-slow-queries = /var/log/mysql-slow.log  
long_query_time = 1                         

local-infile=0                              
datadir=/var/lib/mysql                      
user=mysql                                  
symbolic-links=0                            

max_connections = 400                       
wait_timeout = 60                           
query-cache-type = 0                        
query-cache-size = 128M                     
query_cache_size = 0                        
query_cache_limit = 0                       
thread_cache_size = 128                     
tmp_table_size = 2048M                      
max_heap_table_size = 2048M                 
join_buffer_size = 40M                      
innodb_file_per_table=ON                    
table_open_cache = 1024                     

port            = 3306                      
socket          = /var/lib/mysql/mysql.sock 
skip-external-locking                       
key_buffer_size = 1024M                     
max_allowed_packet = 268435456              
sort_buffer_size = 2M                       
read_buffer_size = 2M                       
read_rnd_buffer_size = 8M                   
myisam_sort_buffer_size = 512M
query_cache_size = 0
thread_concurrency = 1

innodb_buffer_pool_size = 3024M
innodb_additional_mem_pool_size = 40M
innodb_log_buffer_size = 320M

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout


table_cache             = 1536
table_definition_cache  = 1536

htop output

 1743 eseed      20   0  500M 21448 12208 S  0.0  0.1  0:00.15 php-fpm: pool x.com
 1412 mysql      20   0 5578M  815M 10132 S  0.0  2.5  4:26.02 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a 1582 mysql      20   0 5578M  815M 10132 S  0.0  2.5  1:45.77 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a 1518 apache     20   0 2205M  9472  2632 S  0.0  0.0  0:00.20 /usr/sbin/httpd -DFOREGROUND
 1545 apache     20   0 2205M  9472  2632 S  0.0  0.0  0:00.16 /usr/sbin/httpd -DFOREGROUND
  720 nginx      20   0 38032  3732  1316 S  0.0  0.0  0:00.06 nginx: worker process
 1105 apache     20   0  989M  9668  2952 S  0.0  0.0  0:00.16 /usr/sbin/httpd -DFOREGROUND
 1065 apache     20   0 2205M  9432  2628 S  0.0  0.0  0:00.17 /usr/sbin/httpd -DFOREGROUND
  382 root       20   0  380M 13424  8004 S  0.0  0.0  0:00.09 php-fpm: master process (/etc/php-fpm.conf)
 1058 apache     20   0 1373M 10056  3044 S  0.0  0.0  0:00.18 /usr/sbin/httpd -DFOREGROUND
 1146 apache     20   0 2205M  9432  2628 S  0.0  0.0  0:00.15 /usr/sbin/httpd -DFOREGROUND
 1427 mysql      20   0 5578M  815M 10132 S  0.0  2.5  0:00.02 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a 1461 mysql      20   0 5578M  815M 10132 S  0.0  2.5  0:44.38 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a 1742 eseed      20   0  381M  9248  2908 S  0.0  0.0  0:00.10 php-fpm: pool dev.x.com
  952 root       20   0 3835M 10824  3584 S  0.0  0.0  0:00.18 Passenger core
 1432 mysql      20   0 5578M  815M 10132 S  0.0  2.5  0:00.02 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a
  381 root       20   0  498M 25160 19552 S  0.0  0.1  0:00.11 php-fpm: master process (/opt/plesk/php/7.0/etc/php-fpm.conf)
 1425 mysql      20   0 5578M  815M 10132 S  0.0  2.5  0:00.02 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a 1426 mysql      20   0 5578M  815M 10132 S  0.0  2.5  0:00.02 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/lnw2-g8hk.a

2

Answers


  1. You are misinterpreting htop. Those are threads, not processes. Each connection typically has a thread, in addition to the internal threads the server creates for various operational and housekeeping reasons. Try pressing t or F5 to see the hierarchy.

    See also Why Does htop Show More Processes than ps?

    thread_concurrency is a no-op, except on very old Solaris systems, where it is only a hint to the thread scheduler.

    The amount of CPU consumed by MySQL is directly related to the amount of workload it’s being offered. Nothing in the configuration causes this to be any more or less, to any meaningful extent.

    Login or Signup to reply.
  2. This suggestion will HELP also. To allow DEFAULTS to support your effort, REMOVE the following list from [mysqld] section of my.cnf/.ini sort_buffer_size read_buffer_size read_rnd_buffer_size join_buffer_size — to improve(reduce) response time. Why would you intentionally read 8M of read_rnd_buffer_size data when you could get the data you need with the DEFAULT of 256K ?

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search