Does Python MySQLdb Make You Wait?
Recently while writing a data loading application for a customer, we’ve come into a problematic situation with the Python MySQLdb module that can be installed with base RHEL repository or DVD. As a little background, this application uses an HA architecture where a Virtual IP can be assigned to different servers during a failover scenario. However, during failover, as long as the VIPs are not re-assigned, the application could hang waiting for query response. A quick strace reveals something like:
sendto(5, "W\0\0\0\3SELECT * FROM dataloader.bo"..., 91, 0, NULL, 0) = 91 recvfrom(5,
Further test reveals that this problem will not timeout until after about 15 minutes which matches the default tcp_retries2 value on the system (see man 7 tcp):
[root@node1 ~]# cat /proc/sys/net/ipv4/tcp_retries2 15
It turns out that this version of MySQLdb module has client net read and write timeouts set to 0 allowing the TCP setting to kick in instead at the maximum of ~15mins. Another problem, the same version does not support setting the timeout values, you’d have to upgrade to >= 1.2.4 to get this support within your MySQLdb.connect calls.
So, I see a few things we can do here:
- Set default read and write timeouts to the module. I’ve opened an issue on GitHub via https://github.com/farcepest/MySQLdb1/issues/86 as this is only 2 lines change on the code. Hopefully this makes next release!
- Of course, if you do decide to use the current release, make sure to set read_timeout and write_timeout values appropriate to your application!
I try to switch everything from MySQLdb to mysql-connector-python as that supports python3 and supports more of the newer features in MySQL.
Leave a Comment