If you run a large Squirro cluster, you might run into MySQL connection issue.
These can show like this:
UnknownError: (500, u"(OperationalError) (1040, 'Too many connections') None None")
Locate the cluster leader
You first want to investigate the leader and set the new value there.
curl -s http://localhost:81/service/cluster/v0/leader/cluster -H "Accept: application/json" | python -mjson.tool | grep electionLeader
Log into that machine.
How many connections are allowed currently?
As root, run:
echo "SHOW VARIABLES" | mysql | grep max_connections
Default value is:
max_connections 151
Show the 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
Set new Max Connection value
Create a new file called /etc/mysql/conf.d/connections.cnf
Add:
[mysqld] set-variable=max_connections=251
Then restart mysqld
service mysqld restart
Check how much RAM the MySQL server might consume
Each connection consumes RAM. Don't go crazy on the numbers. This nifty statement will print out the max. ammount 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 | +------------------+
Set the same parameter 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