MySQL Forums
Forum List  »  Newbie

Re: SQLSTATE[08004] [1040] Too many connections
Posted by: Adam Donnison
Date: August 31, 2011 08:15PM

To find out what connections are in play you need to use the MySQL command line tool and issue the command:
SHOW PROCESSLIST;

You need a user with SYSTEM privilege for this. This should show what users are connecting from where and to what database. Threads in Sleep are those that have been opened but currently aren't doing any useful work. The Time field tells you how long they've been in this state.

You can also look at what connections you have and how many are being used:
SHOW GLOBAL VARIABLES LIKE '%connections';

The max_connections value tells how many have been configured

SHOW STATUS LIKE '%connect%';

In this there are a number of stats. The most important one is the Max_used_connections which tells you how close to the max_connections you've been. The next one is 'Threads_connected' which tells you how many connections you currently have.

Once you eliminate bad queries and dangling connections you can use the usage figures to tell you what to tune max_connections to. If you are getting a lot of long-standing connections in Sleep, you may want to also change the wait_timeout value down to something like 5 minutes instead of the default (which is 28800 seconds if I remember correctly).

These values should be in my.cnf in the [mysqld] section:
[mysqld]
max_connections = 1000
wait_timeout = 300

Webmaster - http://www.SkySQL.com/

Options: ReplyQuote


Subject
Written By
Posted
Re: SQLSTATE[08004] [1040] Too many connections
August 31, 2011 08:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.