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
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
orF5
to see the hierarchy.See also Why Does
htop
Show More Processes thanps
?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.
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 ?