Our VM configuration (hosted on Vmware)
# cat /proc/cpuinfo |grep "cpu cores" | awk -F: '{ num+=$2 } END{ print "cpu cores", num }'
cpu cores 32
# free -h
total used free shared buffers cached
Mem: 62G 23G 39G 500K 349M 10G
-/+ buffers/cache: 12G 50G
Swap: 50G 0B 50G
I got from pt-variable-advisor a warning about max_connections
:
pt-variable-advisor h=localhost,u=root,p=Quule0juqu7aifohvo2Ahratit --socket /var/vcap/sys/run/mysql/mysqld.sock
(...)
# WARN max_connections: If the server ever really has more than a thousand threads running, then the system is likely to spend more time scheduling threads than really doing useful work.
(...)
Why? Any details?
Configuration in my.cnf
max_connections = 15360
settings from prd db cluster (MariaDB 10.1.x and Galera)
MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 718 |
+-------------------+-------+
1 row in set (0.01 sec)
MariaDB [(none)]> SHOW STATUS WHERE `variable_name` = 'Max_used_connections';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| Max_used_connections | 924 |
+----------------------+-------+
1 row in set (0.02 sec)
I found a MySQL manual and an (very) old similar question.
The default value is 151 to improve performance
The number of connections permitted is controlled by the max_connections system variable. The default value is 151 to improve performance when MySQL is used with the Apache Web server. (Previously, the default was 100.) If you need to support more connections, you should set a larger value for this variable.
and
The maximum number of connections MySQL supports depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at least 500 to 1000 simultaneous connections routinely and as many as 10,000 connections
We currently have 460 users and every user is allowed to do 100 max_connections
. This would be the max value. Is the 100 max_connections
per user and database too high? With modern connection pooling we can set this to 20? How we should configure that not risking overloading our server with context switching? Is it possible that one web app use one connection (every statement over same connection)?