MOS version: 6.1 Release, Ubuntu+HA
Basically on 200 nodes lab we got in situation when under the stable number of objects in cloud (7k VM, 7k Users,7k Tenants, 7k Networks, 7k Subnetwork) we go out of mysql connections limit.
Let me describe briefly.
We have Galera cluster and haproxy in front of it. Just to escape putting SQL inserts and updates to the different nodes of mysql-galera cluster we have active/backup/backup config in haproxy settings for galera so effectively we have only one mysql server serving the cloud. But it looks like number of connections in config (4096) allowed isn't enough to server 200 nodes cluster.
Here is example of keystone and other service reaction on that:
root@node-663:~# keystone tenant-list
An unexpected error prevented the server from fulfilling your request: (OperationalError) (1040, 'Too many connections') None None (original cause: OperationalError: (OperationalError) (1040, 'Too many connections') None None) u'SELECT 1' [] (Disable debug mode to suppress these details.) (HTTP 500)
root@node-663:~# keystone tenant-list
Authorization Failed: An unexpected error prevented the server from fulfilling your request: (OperationalError) (1040, 'Too many connections') None None (Disable debug mode to suppress these details.) (HTTP 500)
Here is netstat stats to the haproxy across three controllers:
[root@fuel ~]# for i in 57 58 59;do ssh 10.20.2.$i "netstat -an |grep -c 3306";done
Warning: Permanently added '10.20.2.57' (RSA) to the list of known hosts.
1642
Warning: Permanently added '10.20.2.58' (RSA) to the list of known hosts.
1776
Warning: Permanently added '10.20.2.59' (RSA) to the list of known hosts.
1629
So we could see that we just get to the limit.
Fixes(two of them):
1. Change number of connections on all of the nodes keeping in mind that only one will service requests(still).
2. Allow using of whole galera cluster so connections would be distributed across all the controllers/servers with mysql-galera.
Second fix should be tested on high load and also with failure testing because previously galera didn't behave stable enough and only because of that we switched to the active/backup/backup config. At the Openstack summit I had conversation with galera representative and they said that all the problems which we probably had was fixed. Anyway, we should test 3xACTIVE config before proceed with this fix to upstream.
Quick workaround:
1. Change /etc/mysql/conf.d/wsrep.cnf in section [mysqld] max_connections from 4096 to 16384. Number should be relevant to the hardware used. Beware. Don't use very high numbers on slow hardware. That could lead in very aggressive CPU usage by MySQL-galera.
2. Make online change of max_connections using shell command:
mysql -e "SET GLOBAL max_connections = 16384;"
AFAIK, we have the following connections limit
grep files /proc/$(pidof mysqld)/limits
Max open files 102400 102400 files
which if far from 4096, as you descrived, Alexandr.
This requires elaboration.
Do you also think, mysql cluster should just be scaled horizontally (cells) instead of vertically (connection limits) ?