2003: Can't connect to MySQL server on '127.0.0.1:3306' (99 Cannot assign requested address)

Question

I am running a python2.7 application that performs "inserts" into a single mysql/mariadb instance on a multi-core 64 bit CentOS(or ubuntu) machine. as soon as the parallel processes/cores exceed 4 or maybe 6, I see this error. (at different points in the execution) 2003: Can't connect to MySQL server on '127.0.0.1:3306' (99 Cannot assign requested address)

I am running the application on CentOS6.5, mariadb 10.1 I have also tried with Ubuntu 14.04 (64 bit), mysql resulting in the same problem.

I tried making the following changes:

In my.cnf file:
[mysqld]
interactive_timeout=1
wait-timeout = 1
thread_cache_size = 800
max_connections = 5000
#max_user_connections = 5000
max_connect_errors = 150


In sysctl.conf file:
fs.file-max = 65536

In limits.confg file:
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

I am inclined to think that this is a configuration issue, because the code runs just fine on 2 core Mac. Can someone suggest some configuration tweaks or any easy way to reuse connections?

Answer 1

You're probably connecting/disconnecting mysqld on a pretty high rate? And when hitting error 99 you're probably seeing a lot of connections in TIME_WAIT state in netstat -nt output?

Problem most likely is that you are running out of client ports pretty quick due to frequent reconnects and the TIME_WAIT delay. This would also explain why you are more likely to run into this the higher your number of parallel clients is.

The TL;DR solution may be to set net.ipv4.tcp_tw_reuse to 1, e.g. using

echo 1 > /proc/sys/net/ipv4/tcp_tw_reuse

or, as you have clients and mysql server on the same machine anyway, you could use UNIX domain socket connections instead of TCP. This may be as simple as connecting to verbatim "localhost" host name instead of 127.0.0.1, but I don't know about the various Python connectors and how these handle this ...

For more detailed tips and explanations see

http://www.fromdual.com/huge-amount-of-time-wait-connections

and

http://vincent.bernat.im/en/blog/2014-tcp-time-wait-state-linux.html