3

I have setup a master-slave replication and added a haproxy before them.

#HA-Proxy version 1.4.24 global log /dev/log local0 log /dev/log local1 notice chroot /var/lib/haproxy user haproxy group haproxy daemon defaults log global mode http option httplog option dontlognull contimeout 5000 clitimeout 50000 srvtimeout 50000 errorfile 400 /etc/haproxy/errors/400.http errorfile 403 /etc/haproxy/errors/403.http errorfile 408 /etc/haproxy/errors/408.http errorfile 500 /etc/haproxy/errors/500.http errorfile 502 /etc/haproxy/errors/502.http errorfile 503 /etc/haproxy/errors/503.http errorfile 504 /etc/haproxy/errors/504.http frontend ft_readonly_mysql mode tcp timeout client 50000 option clitcpka bind *:3308 default_backend bk_readonly_mysql backend bk_readonly_mysql mode tcp option srvtcpka timeout server 50000 option mysql-check user haproxy_check server mysql1 127.0.0.1:3306 check server mysql2 172.16.19.250:3306 check

If my pymysql client connect to mysql via 3306 directly, it works well. If connect to mysql via haproxy 3308 port, it will throw OperationalError: (OperationalError) (2013, 'Lost connection to MySQL server during query') every several minutes.

I doubted it was due to haproxy config so that I have added clitcpka, srvtcpka, timeout client, timeout server. But it still throws Lost connection.

Update 0:

mysql> show global variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +----------------------------+----------+
Improve this question
0

1 Answer 1

Reset to default
1

TL;DR: Keep the timeouts in HAProxy and MySQL / MariaDB the same.

I've run into this issue on and off when I fire some long running quries. I'm posting the relevant section from an article here that has worked well for me:

MySQL Server has gone away

This is usually caused when HAProxy has closed the connection due to timeout or the connection is closed on the server side. Sometimes, you could see this when the server is restarting or the connection has reached one of the following timeouts.

Our recommendation is to configure the @net_read_timeout@ and @net_write_timeout@ value in MySQL / MariaDB with the same value as for @timeout client@ and @timeout server@ in HAProxy configuration file.

Based on the above, your configuration should look like this for HAProxy:

# haproxy.cfg ... defaults timeout client 3600 timeout server 3600 ...

You can also add the timeouts to a specific frontend or backend configuration in the HAProxy config

And like this for MySQL / MariaDB:

# my.cnf [mysqld] ... net_read_timeout = 3600 net_write_timeout = 3600 ...

Here is an excerpt from another article questioning the same recommendation:

Please keep in mind that there's no magic number which is the best setting for given variable. In one case longer timeouts may work better, in other case you would prefer shorter ones. This is up to you to decide what is the best setting for your environment, application and workload. It doesn't have to me 10800 seconds. It may be 30, 60, 3600 or anything else.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.