First you need to do is run this query:
SELECT user,host FROM mysql.user
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
This will list all users that have SUPER privilege. Most users that do application-related DB processing do not require this privilege. According to the MySQL Documentation, those with SUPER privilege can do the following:
- Run CHANGE MASTER TO for controlling replication coordinates
- KILL or
mysqladmin kill
to kill threads belonging to other accounts
- PURGE BINARY LOGS to systemically delete binary logs
- Make configuration changes using SET GLOBAL to modify global system variables
- mysqladmin debug command
- enabling or disabling logging
- performing updates even if the *read_only* system variable is enabled
- starting and stopping replication on slave servers
- specification of any account in the DEFINER attribute of stored programs and views
- HERE IS THE MOST IMPORTANT ONE FOR YOUR PROBLEM: : Enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.
You will need to login as root@localhost and revoke SUPER privilege as follows:
UPDATE mysql.user SET super_priv='N'
WHERE super_priv='Y' AND
CONCAT(user,'@',host) <> 'root@localhost';
FLUSH PRIVILEGES;
Once you do this, whenever all users flood mysql connections, only root@localhost
can login. After all, if everybody and his grandmother had SUPER privilege, this would bar root@localhost
from ever connecting ahead of everybody else. If max_connections is at 200 and you need to raise it to 300 without having to restart mysqld, you can dynamically increase the max_connections with this command:
mysql> SET GLOBAL max_connections = 300;
That will allow more connections effective immediately, but don't just arbitrarily increase the number on whim. You have to make sure mysql has enough RAM to accommodate the increase.
CAVEAT : If you change max_connections dynamically to 300, please put it in /etc/my.cnf
[mysqld]
max_connections=300
You can run mysqltuner.pl on your MySQL DB Server. If you do not have it, then run the following:
cd
wget mysqltuner.pl
perl mysqltuner.pl
The 3rd line under Performance Metrics has this
-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 20h 46m 22s (8M q [10.711 qps], 129K conn, TX: 90B, RX: 19B)
[--] Reads / Writes: 4% / 96%
[--] Total buffers: 2.1G global + 5.4M per thread (2000 max threads)
[OK] Maximum possible memory usage: 12.6G (80% of installed RAM)
See the 5.4M per thread? That is multipled by max_connections. In this example, that would be a maximum of about 10.8G of RAM. Therefore, each time you bump up max_connections, you should run mysqltuner.pl and check if you are pressing the OS for too much memory.
In any case, limiting who has SUPER privileges give such users opportunity to mitigate flooding mysqld with DB Connections.
Do:
netstat -an|grep 3306 | grep LISTEN
If something similar to the following line is returned:
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
.. it means that it's listening on all interfaces.
If something similar to the following line is returned, and no other lines:
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN
.. it's already configured to only listen on localhost
.
If there are lines with other IP addresses before the :3306
, it means that it's listening on those interfaces.
To change MySQL to only listen on localhost
, edit your configuration file (usually /etc/my.cnf
), add the following:
bind-address = 127.0.0.1
Restart the service and voila!
Best Answer
Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.
Why do I say regardless of who issued each query ?
There are queries that are launched internally by mysqld. Here is a simple example:
Please note that issuing a simple query like
show global status like 'Questions';
is a query even if your are just requesting internal data from mysqld.Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.
I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"
Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run
netstat
and look for DB Connections to mysql that have the TIME_WAIT status.There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP: