Too Many Connections

MySQL & MariaDB Other MySQL® Questions
1

I’m trying to troubleshoot a server that just keeps cracking up.

We get errors to the tune of “Could not select database;
Could not connect: Too many connections” printing to the screen, but when I go off and
show proccesses
the problem has cleared up. I’m not even sure how to tell where the problem is coming from.

I was ready to pin the blame on our Phorum forums, but I limited the phorum’s mysql user to 5 connections and it is still happening.

Can I tweak my logs at all to see where these scrums are coming from?

And: the timeouts of 3600 seem crazy long to me, but they were set by a predecessor.

Thanks!
Amanda

my my.cnf is at [URL]Phpfi.com is for sale | HugeDomains or below.

[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1#ABH Edit 2008-01-09log-slow-queries = /var/log/mysql/mysql-slow.loglong_query_time = 5# Rackspace edit per #060815-01133key_buffer = 384Mmax_allowed_packet = 1Mtable_cache = 512sort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 8Mmyisam_sort_buffer_size = 64Mthread_cache_size = 8query_cache_size = 32M# Try number of CPU’s*2 for thread_concurrencythread_concurrency = 8wait_timeout = 3600interactive_timeout = 3600# End Rackspace edit[mysql.server]user=mysqlbasedir=/var/libmax_connections=200[mysqld_safe]#ABH Edit 2008-01-09#err-log=/var/log/mysqld.logerr-log=/var/log/mysql/err-mysqld.logpid-file=/var/run/mysqld/mysqld.pid## ABH EDIT 2008-4-16# The following can be used as easy to replay backup logs or for replication.#server-id = 1log_bin = /var/log/mysql/mysql-bin.log# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!expire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name

2

I’m not sure I entirely understand the situation you’re describing, but it sounds a little like you’ve got something that is constantly trying to establish new connections instead of reusing the connections it previously created. If that’s the case, when you run SHOW PROCESSLIST, then process id’s should get higher and higher every time you run it, instead of staying fixed. Also, if you want to know where connections are coming from, try running netstat on any of the machines where you’re running a client or server. In order to get refused by the server, the client has to make a TCP connection and even if it closes that connection immediately, it will be shown in the output of netstat for a little while. Netstat will show you which process made the connection, so that should help you hunt things down if you have some misbehaving program constantly making connections.

3

The problem is that I don’t understand the problem. I think that something is opening many connections and leaving them open. I haven’t found any instances of pconnect().

Almost all of our connections are localhost, which means netstat won’t help, right?

4

Netstat will show connections over loopback (127.0.0.1) and unix sockets, which is what the MySQL client library will use if you specify “localhost”. Unfortunately, the unix sockets will disappear from netstat as soon as their connections are closed.

You could try disabling unix sockets on the server, and reconfiguring your code to connect using 127.0.0.1. This will let you use netstat or even tcpdump to figure out what’s going on.