max-connectionsMySQLunix
The documentation does not specify if max_connections apply only to TCP connections.
The way it is said, it looks like it does apply for any kind of connection, but I'd like to be sure because otherwise, I could use it when the "Too many connection" problem arises, even if the (last) SUPER connection slot is already in use.
Please notice the makeup of mysql.user:
mysql> show create table mysql.user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
`User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
`Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
`ssl_cipher` blob NOT NULL,
`x509_issuer` blob NOT NULL,
`x509_subject` blob NOT NULL,
`max_questions` int(11) unsigned NOT NULL DEFAULT '0',
`max_updates` int(11) unsigned NOT NULL DEFAULT '0',
`max_connections` int(11) unsigned NOT NULL DEFAULT '0',
`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
`plugin` char(64) COLLATE utf8_bin DEFAULT '',
`authentication_string` text COLLATE utf8_bin,
PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'
1 row in set (0.00 sec)
Notice the column max_user_connections. It puts a moratorium on the number of connections a user may make per hour. Check and make sure the user '557574_prod' isn't connecting to mysql a ton of times within less than an hour.
This is used in conjunction with the option max_user_connections that you can set in /etc/my.cnf
Please change either max_user_connections in /etc/my.cnf or, if the individual user has this column set, that column mysql.user.max_user_connections. If you have no config control over my.cnf and you cannot do SQL against the mysql schema, then you beg and implore your hosting company to raise those limits for you.
I believe you can set the max_user_connections as a session variable, but you may need certain privileges, probably SUPER.
As for establish connections, you need to keep those connections open. Perhaps sending a heartbeat of some kind by doing a SELECT. But guess what ??? The db columns max_questions monitor SELECT queries per hour and max_updates monitors INSERTs, UPDATEs, DELETEs per hour. YOu may need to check these columns in mysql.user. You can quickly check those number with:
SHOW GRANTS;
I am not sure if the the user's per-hour connection limits are per thread or per user. You could answer that quickly by connecting to the DB aggressively using 2 or 3 DB connecitons at the same time and the error message should manifest itself. It is up you how to count the number of connections, perhaps "SHOW STATUS LIKE 'Connections';" It should reach either the max limits of the connection within the one connections or it could be the sum of all connections' connection status value.
Please also check the wait_timeout and interactive_timeout values. The default is 28800 in a standalone server. They may be set already. You cannot set them within a current connection. You must be able to set them as follows: (for new conenctions coming)
SET GLOBAL interactive_timeout = 86400;
SET GLOBAL wait_timeout = 86400;
If you cannot run even these commands, my condolences !!! :(
Three points:
I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.
Some suggestions:
With regard "too many connections", try the following:
Set max_connections to some reasonable value, like 300, throwing around a number.
Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files.
Do you have some connection pool which holds many open (idle) connections, with some keepalive?
How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set:
table_open_cache=2048
table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion.
The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.
Best Answer
This limit applies to the total TCP socket connections and Unix socket connections, combined, so connecting via the Unix socket won't help you if your connection count reaches the configured max.
However, the slot reserved for a user with
SUPERprivilege is not a single slot in the way your question implies. Any time the server's current client thread count is equal to the value ofmax_connections, one more user with theSUPERprivilege will be allowed to connect, regardless of how manySUPERconnections are currently already established.So the max+1 allowance refers not to a single slot that can be occupied and whose occupancy is remembered, but rather to an exception in the code that bypasses the logic that denies new connections connections when current == max and new attempt comes from a user with super, each time that condition is encountered.
This is one good reason, among others, that your application should never use an account with
SUPER.