02-22-2005, 06:39 AM
|
#1 (permalink)
|
| Active Forumer
Join Date: Feb 2005 Location: Scotland
Posts: 253
Rep Power: 4  fBuck$: 537.0 Bank: 1.0 Total fBuck$: 538.0 My Forumer
My Country:
 |  Too many connections!
This is an error which we are all getting from time to time.
It can be fixed: Quote:
If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other clients.
The number of connections allowed is controlled by the max_connections system variable. Its default value is 100. If you need to support more connections, you should restart mysqld with a larger value for this variable.
mysqld actually allows max_connections+1 clients to connect. The extra connection is reserved for use by accounts that have the SUPER privilege. By granting the SUPER privilege to administrators and not to normal users (who should not need it), an administrator can connect to the server and use SHOW PROCESSLIST to diagnose problems even if the maximum number of unprivileged clients are connected. See Section 13.5.4.15, “SHOW PROCESSLIST Syntax”.
The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform. Linux or Solaris should be able to support 500-1000 simultaneous connections, depending on how much RAM you have and what your clients are doing. Static Linux binaries provided by MySQL AB can support up to 4000 connections.
SHOW [FULL] PROCESSLIST
SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist statement. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, “KILL Syntax”. If you don't use the FULL keyword, only the first 100 characters of each query are shown.
Starting from MySQL 4.0.12, the statement reports the hostname for TCP/IP connections in host_name:client_port format to make it easier to determine which client is doing what.
This statement is very useful if you get the "too many connections" error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users).
Some states commonly seen in the output from SHOW PROCESSLIST:
Checking table
The thread is performing (automatic) checking of the table.
Closing tables
Means that the thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, then you should verify that you don't have a full disk and that the disk is not in very heavy use.
Connect Out
Slave connecting to master.
Copying to tmp table on disk
The temporary result set was larger than tmp_table_size and the thread is changing the temporary table from in-memory to disk-based format to save memory.
Creating tmp table
The thread is creating a temporary table to hold a part of the result for the query.
deleting from main table
The server is executing the first part of a multiple-table delete and deleting only from the first table.
deleting from reference tables
The server is executing the second part of a multiple-table delete and deleting the matched rows from the other tables.
Flushing tables
The thread is executing FLUSH TABLES and is waiting for all threads to close their tables.
Killed
Someone has sent a kill to the thread and it should abort next time it checks the kill flag. The flag is checked in each major loop in MySQL, but in some cases it might still take a short time for the thread to die. If the thread is locked by some other thread, the kill takes effect as soon as the other thread releases its lock.
Locked
The query is locked by another query.
Sending data
The thread is processing rows for a SELECT statement and also is sending data to the client.
Sorting for group
The thread is doing a sort to satisfy a GROUP BY.
Sorting for order
The thread is doing a sort to satisfy a ORDER BY.
Opening tables
The thread is trying to open a table. This is should be very fast procedure, unless something prevents opening. For example, an ALTER TABLE or a LOCK TABLE statement can prevent opening a table until the statement is finished.
Removing duplicates
The query was using SELECT DISTINCT in such a way that MySQL couldn't optimize away the distinct operation at an early stage. Because of this, MySQL requires an extra stage to remove all duplicated rows before sending the result to the client.
Reopen table
The thread got a lock for the table, but noticed after getting the lock that the underlying table structure changed. It has freed the lock, closed the table, and is trying to reopen it.
Repair by sorting
The repair code is using sorting to create indexes.
Repair with keycache
The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.
Searching rows for update
The thread is doing a first phase to find all matching rows before updating them. This has to be done if the UPDATE is changing the index that is used to find the involved rows.
Sleeping
The thread is waiting for the client to send a new statement to it.
System lock
The thread is waiting to get an external system lock for the table. If you are not using multiple mysqld servers that are accessing the same tables, you can disable system locks with the --skip-external-locking option.
Upgrading lock
The INSERT DELAYED handler is trying to get a lock for the table to insert rows.
Updating
The thread is searching for rows to update and updating them.
User Lock
The thread is waiting on a GET_LOCK().
Waiting for tables
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to be able to reopen the table, it must wait until all other threads have closed the table in question.
This notification happens if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
waiting for handler insert
The INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.
Most states correspond to very quick operations. If a thread stays in any of these states for many seconds, there might be a problem that needs to be investigated.
There are some other states that are not mentioned in the preceding list, but many of them are useful only for finding bugs in the server.
| blindman |
| |
|  |
Similar Threads 
Linear Mode
