MySQL Too Many Connections

MySQL Too Many Connections#

If you run a large Squirro cluster, you might run into MySQL connection issue.

These can show as follows:

UnknownError: (500, u"(OperationalError) (1040, 'Too many connections') None None")

Locate the Cluster Leader#

To correct the issue, you’ll first want to investigate the leader and set the new value.

curl -s http://localhost:81/service/cluster/v0/leader/cluster -H "Accept: application/json"    | python -mjson.tool    | grep electionLeader

Log in to that machine.

Number of Allowable Collections#

As root, run:

echo "SHOW VARIABLES"    | mysql    | grep max_connections

The default value is:

max_connections 151

Showing Active Connections#

As root, run:

echo "show processlist"    | mysql

Sample output:

Id      User    Host    db      Command Time    State   Info
8       repl    192.168.100.18:46496    NULL    Binlog Dump     1024493 Has sent all binlog to slave; waiting for binlog to be updated  NULL
4728    repl    192.168.100.16:44756    NULL    Binlog Dump     240336  Has sent all binlog to slave; waiting for binlog to be updated  NULL
5878    filtering       192.168.100.16:52150    filtering       Sleep   6764        NULL
5879    filtering       192.168.100.16:52152    filtering       Sleep   6737        NULL
5880    filtering       192.168.100.16:52154    filtering       Sleep   6765        NULL
5892    configuration   192.168.100.16:52180    configuration   Sleep   15947       NULL
5907    fingerprint     192.168.100.16:52684    fingerprint     Sleep   15918       NULL
5908    fingerprint     192.168.100.16:52688    fingerprint     Sleep   15915       NULL
5909    fingerprint     192.168.100.16:52692    fingerprint     Sleep   15913       NULL
5910    fingerprint     192.168.100.16:52784    fingerprint     Sleep   15918       NULL
5911    fingerprint     192.168.100.16:52788    fingerprint     Sleep   15918       NULL
5912    filtering       192.168.100.16:53224    filtering       Sleep   6737        NULL
5913    filtering       192.168.100.16:53254    filtering       Sleep   6737        NULL
5951    user    192.168.100.16:55732    user    Sleep   6737        NULL
5952    user    192.168.100.16:55750    user    Sleep   6736        NULL
5953    user    192.168.100.16:55752    user    Sleep   6736        NULL
5954    user    192.168.100.16:55762    user    Sleep   6737        NULL
5955    user    192.168.100.16:55772    user    Sleep   6810        NULL
5956    topic   192.168.100.16:55774    topic   Sleep   6810        NULL
5957    user    192.168.100.16:55786    user    Sleep   6737        NULL
5958    user    192.168.100.16:55790    user    Sleep   6810        NULL
5959    topic   192.168.100.16:55796    topic   Sleep   6737        NULL
5960    topic   192.168.100.16:55826    topic   Sleep   6737        NULL
5961    topic   192.168.100.16:55828    topic   Sleep   6737        NULL
5962    topic   192.168.100.16:55842    topic   Sleep   6737        NULL
5963    topic   192.168.100.16:55872    topic   Sleep   6736        NULL
5964    topic   192.168.100.16:55888    topic   Sleep   6736        NULL
5981    filtering       192.168.100.18:49246    filtering       Sleep   653         NULL
5982    filtering       192.168.100.18:49248    filtering       Sleep   1357        NULL
5983    filtering       192.168.100.17:57760    filtering       Sleep   655         NULL
5984    sourcer 192.168.100.16:56482    sourcer Sleep   2665        NULL
5985    filtering       192.168.100.18:49274    filtering       Sleep   653         NULL
5986    sourcer 192.168.100.18:49300    sourcer Sleep   2056        NULL
5987    sourcer 192.168.100.17:57808    sourcer Sleep   1999        NULL
5988    feed    192.168.100.16:56484    feed    Sleep   1634        NULL
5989    scheduler       192.168.100.16:56486    scheduler       Sleep   1634        NULL
5990    scheduler       192.168.100.17:57824    scheduler       Sleep   1601        NULL
5991    filtering       192.168.100.17:57828    filtering       Sleep   655         NULL
5992    feed    192.168.100.18:49324    feed    Sleep   1391        NULL
5993    feed    192.168.100.17:57836    feed    Sleep   1389        NULL
5994    filtering       192.168.100.17:57840    filtering       Sleep   1305        NULL
5995    scheduler       192.168.100.18:49332    scheduler       Sleep   1211        NULL
5996    filtering       192.168.100.16:56488    filtering       Sleep   688         NULL
5997    root    localhost       NULL    Query   0       NULL    show processlist

Setting New Max Connection Values#

Create a new file called /etc/mysql/conf.d/connections.cnf

Add:

[mysqld]
set-variable=max_connections=251

Then restart mysqld

service mysqld restart

Checking How Much RAM the MySQL Server May Consume#

Each connection consumes RAM. Don’t use excessive amounts. This nifty statement will print out the max amount of GB RAM the service can use with the current setting.

use mysql;
DELIMITER //
CREATE PROCEDURE sproc_show_max_memory ( OUT max_memory DECIMAL(7,4))
BEGIN
SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size + @@innodb_additional_mem_pool_size + @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size + @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / 1073741824 AS MAX_MEMORY_GB INTO max_memory;
END//
DELIMITER ;
CALL sproc_show_max_memory(@show_max_memory);
SELECT @show_max_memory;

In this example the server will not use more than 443 MB:

+---------------------+
| @show_max_memory    |
+---------------------+
|           0.4433    |
+---------------------+

Setting the Same Parameters on All Remaining Hosts#

Repeat the config file setup and reload on all remaining cluster followers.

If you need to restart all services, use this command:

for i in /etc/init.d/sq*;
do
    serv=`basename $i`;
    if [[ "$serv" != "sqclusterd" ]];
    then
        /sbin/service $serv restart;
    fi
done
Contents