blog
HAProxy Connections vs MySQL Connections – What You Should Know
Having a load balancer or reverse proxy in front of your MySQL or MariaDB server does add a little bit of complexity to your database setup, which might lead to some, things behaving differently. Theoretically, a load balancer which sits in front of MySQL servers (for example an HAProxy in front of a Galera Cluster) should just act like a connection manager and distribute the connections to the backend servers according to some balancing algorithm. MySQL, on the other hand, has its own way of managing client connections. Ideally, we would need to configure these two components together so as to avoid unexpected behaviours, and narrow down the troubleshooting surface when debugging issues.
If you have such setup, it is important to understand these components as they can impact the overall performance of your database service. In this blog post, we will dive into MySQL’s max_connections and HAProxy maxconn options respectively. Note that timeout is another important parameter that we should know, but we are going to cover that in a separate post.
MySQL’s Max Connections
The number of connections permitted to a MySQL server is controlled by the max_connections system variable. The default value is 151 (MySQL 5.7).
To determine a good number for max_connections, the basic formulas are:
Where,
**Variable innodb_additional_mem_pool_size is removed in MySQL 5.7.4+. If you are running in the older version, take this variable into account.
And,
By using the above formulas, we can calculate a suitable max_connections value for this particular MySQL server. To start the process, stop all connections from clients and restart the MySQL server. Ensure you only have the minimum number of processes running at that particular moment. You can use ‘mysqladmin’ or ‘SHOW PROCESSLIST’ for this purpose:
$ mysqladmin -uroot -p processlist
+--------+------+-----------+------+---------+------+-------+------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+------+-----------+------+---------+------+-------+------------------+----------+
| 232172 | root | localhost | NULL | Query | 0 | NULL | show processlist | 0.000 |
+--------+------+-----------+------+---------+------+-------+------------------+----------+
1 row in set (0.00 sec)From the above output, we can tell that only one user is connected to the MySQL server which is root. Then, retrieve the available RAM (in MB) of the host (look under ‘available’ column):
$ free -m
total used free shared buff/cache available
Mem: 3778 1427 508 148 1842 1928
Swap: 2047 4 2043Just for the info, the ‘available’ column gives an estimate of how much memory is available for starting new applications, without swapping (only available in kernel 3.14+).
Then, specify the available memory, 1928 MB in the following statement:
mysql> SELECT ROUND((1928 - (ROUND((@@innodb_buffer_pool_size + @@innodb_log_buffer_size + @@query_cache_size + @@tmp_table_size + @@key_buffer_size) / 1024 / 1024))) / (ROUND(@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@thread_stack + @@join_buffer_size + @@binlog_cache_size) / 1024 / 1024)) AS 'Possible Max Connections';
+--------------------------+
| Possible Max Connections |
+--------------------------+
| 265 |
+--------------------------+**Variable innodb_additional_mem_pool_size is removed in MySQL 5.7.4+. If you are running in the older version, take this variable into account.
From this example, we can have up to 265 MySQL connections simultaneously according to the available RAM the host has. It doesn’t make sense to configure a higher value than that. Then, append the following line inside MySQL configuration file, under the [mysqld] directive:
max_connections = 265Restart the MySQL service to apply the change. When the total simultaneous connections reaches 265, you would get a “Too many connections” error when trying to connect to the mysqld server. This means that all available connections are in use by other clients. MySQL actually permits max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. So if you face this error, you should try to access the server as a root user (or any other SUPER user) and look at the processlist to start the troubleshooting.
HAProxy’s Max Connections
HAProxy has 3 types of max connections (maxconn) – global, defaults/listen and default-server. Assume an HAProxy instance configured with two listeners, one for multi-writer listening on port 3307 (connections are distributed to all backend MySQL servers) and another one is single-writer on port 3308 (connections are forwarded to a single MySQL server):
global
...
maxconn 2000 #[a]
...
defaults
...
maxconn 3 #[b]
...
listen mysql_3307
...
maxconn 8 #[c]
balance leastconn
default-server port 9200 maxqueue 10 weight 10 maxconn 4 #[d]
server db1 192.168.55.171 check
server db2 192.168.55.172 check
server db3 192.168.55.173 check
listen mysql_3308
...
default-server port 9200 maxqueue 10 weight 10 maxconn 5 #[e]
server db1 192.168.55.171 check
server db2 192.168.55.172 check backup #[f]Let’s look at the meaning of some of the configuration lines:
global.maxconn [a]
The total number of concurrent connections that are allowed to connect to this HAProxy instance. Usually, this value is the highest value of all. In this case, HAProxy will accept a maximum of 2000 connections at a time and distribute them to all listeners defined in the HAProxy process, or worker (you can run multiple HAProxy processes using nbproc option).
HAProxy will stop accepting connections when this limit is reached. The “ulimit-n” parameter is automatically adjusted to this value. Since sockets are considered equivalent to files from the system perspective, the default file descriptors limit is rather small. You will probably want to raise the default limit by tuning the kernel for file descriptors.
defaults.maxconn [b]
Defaults maximum connections value for all listeners. It doesn’t make sense if this value is higher than global.maxconn.
If “maxconn” line is missing under the “listen” stanza (listen.maxconn), the listener will obey this value. In this case, mysql_3308 listener will get maximum of 3 connections at a time. To be safe, set this value equal to global.maxconn, divided by the number of listeners. However, if you would like to prioritize other listeners to have more connections, use listen.maxconn instead.
listen.maxconn [c]
The maximum connections allowed for the corresponding listener. The listener takes precedence over defaults.maxconn if specified. It doesn’t make sense if this value is higher than global.maxconn.
For a fair distribution of connections to backend servers like in the case of a multi-writer listener (mysql_3307), set this value as listen.default-server.maxconn multiply by the number of backend servers. In this example, a better value should be 12 instead of 8 [c]. If we chose to stick with this configuration, db1 and db2 are expected to receive a maximum of 3 connections each, while db3 will receive a maximum of 2 connections (due to leastconn balancing), which amounts to 8 connections in total. It won’t hit the limit as specified in [d].
For single-writer listener (mysql_3308) where connections should be allocated to one and only one backend server at a time, set this value to be the same or higher than listen.default-server.maxconn.
listen.default-server.maxconn [d][e]
This is the maximum number of connections that every backend server can receive at a time. It doesn’t make sense if this value is higher than listen.maxconn or defaults.maxconn. This value should be lower or equal to MySQL’s max_connections variable. Otherwise, you risk exhausting the connections to the backend MySQL server, especially when MySQL’s timeout variables are configured lower than HAProxy’s timeouts.
In this example, we’ve set each MySQL server to only get a maximum of 4 connections at a time for multi-writer Galera nodes [d]. While the single-writer Galera node will get a maximum of 3 connections at a time, due to the limit that applies from [b]. Since we specified “backup” [f] to the other node, the active node will at once get all 3 connections allocated to this listener.
The above explanation can be illustrated in the following diagram:
To sum up the connections distribution, db1 is expected to get a maximum number of 6 connections (3 from 3307 + 3 from 3308). The db2 will get 3 connections (unless if db1 goes down, where it will get additional 3) and db3 will stick to 2 connections regardless of topology changes in the cluster.
Connection Monitoring with ClusterControl
With ClusterControl, you can monitor MySQL and HAProxy connection usage from the UI. The following screenshot provides a summary of the MySQL connection advisor (ClusterControl -> Performance -> Advisors) where it monitors the current and ever used MySQL connections for every server in the cluster:
For HAProxy, ClusterControl integrates with HAProxy stats page to collect metrics. These are presented under the Nodes tab:
From the above screenshot, we can tell that each backend server on multi-writer listener gets a maximum of 8 connections. 4 concurrent sessions are running. These are highlighted in the top red square, while the single-writer listener is serving 2 connections and forwarding them to a single node respectively.
Conclusion
Configuring the maximum connections for HAProxy and MySQL server is important to ensure good load distribution to our database servers, and protect the MySQL servers from overloading or exhausting its connections.