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