CODEWITHSUNDEEP
mysqllinuxdatabase-connectionscalabilitymariadb
Sample1
Sample1

Reputation: 41

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

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?

Upvotes: 4

Views: 13176

Answers (3)

Jean Claveau
Jean Claveau

Reputation: 1461

In a more recent environment, I had the same error message when trying to access MariaDB in a Docker container behind a Traefik 2.0-beta reverse proxy.

The error message has no sense compared to what occurred for real that's why I add my 2 cents.

And the solution I found was to use the name of the Docker service as host instead of the local domain name I gave to my MariaDB server with Traefik.

Some new inputs may be available here https://github.com/jclaveau/docker-standalone-webdev-ci-stack/tree/master/examples/docker-example-lamp

Upvotes: 1

VasaraBharat
VasaraBharat

Reputation: 81

you can use sleep mechanism to solve this issue.

import time

time.sleep(2) ## sleep for two seconds.

this will reduce the number of processes in wait state.

Upvotes: 0

Hartmut Holzgraefe
Hartmut Holzgraefe

Reputation: 2765

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

Upvotes: 13

Related Questions