| Excerpt |
|---|
If you run a large Squirro cluster, you might run into MySQL connection issue. |
These can show like this:
| Code Block |
|---|
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.
| Code Block |
|---|
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:
| Code Block |
|---|
echo "SHOW VARIABLES" | mysql | grep max_connections |
Default value is:
| Code Block |
|---|
max_connections 151 |
Show the active connections
As root, run:
| Code Block |
|---|
echo "show processlist" | mysql |
Sample output:
| Code Block |
|---|
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:
| Code Block |
|---|
[mysqld]
set-variable=max_connections=251 |
Then restart mysqld
| Code Block |
|---|
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.
| Code Block |
|---|
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:
| Code Block |
|---|
+------------------+
| @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:
...
| language | bash |
|---|
...
This page can now be found at Too Many Connections (MySQL) on the Squirro Docs site.