haproxy-troubleshooting

## Descrizione

mysql saturates its nproc limit (4k) with idle connections
thanks for your support.  We're organizing a call and checking your comments.

To reduce the impacts of this unbalanced distribution, can we configure mariadb to use thread_handling=pool-of-threads ?

From SHOW PROCESSLIST we got that many connections and threads.

count(id)       user    substring_index(host,':',1)
1183    neutron 10.20.105.31
848     nova    10.20.105.31
480     heat    10.20.105.31
406     keystone        10.20.105.31
241     nova_api        10.20.105.31
216     gnocchi 10.20.105.31
3       aodh    10.20.105.31
378     cinder  10.20.105.31
42      glance  10.20.105.31
1       root    localhost
2       system user


It seems that:
- the applications don't do connection pooling (eg. neutron has >1k connections)
- mysql saturates its nproc with idle threads



## Analisi

Elencando il numero di processi connessi a mysql troviamo che haproxy ha circa 8k connessioni aperte verso mysql.

[root@c1f-ops-ctlp00 ~]# ss -twnp | grep 3306 | awk -F'"' '{d[$2]++; } END { for (k in d) {print k, d[k]}}' | sort -nk2
 131
beam.smp 1
cinder-schedule 1
nova-consoleaut 1
sshd 1
/usr/bin/python 1
aodh-evaluator  2
nova-scheduler 2
glance-api 6
cinder-volume 7
cinder-api 46
gnocchi-metricd 54
httpd 154
heat-engine 160
nova-conductor 164
neutron-server 388
nova-api 443
mysqld 4096
haproxy 8192

Ecco la configurazione del server.
MariaDB [(none)]> show variables like '%connect%';
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| character_set_connection | utf8            |
| collation_connection     | utf8_general_ci |
| connect_timeout          | 10              |
| extra_max_connections    | 1               |
| init_connect             |                 |
| max_connect_errors       | 10              |
| max_connections          | 8192            |
| max_user_connections     | 0               |
+--------------------------+-----------------+
MariaDB [(none)]> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| aria_repair_threads                     | 1                         |
| binlog_optimize_thread_scheduling       | ON                        |
| debug_no_thread_alarm                   | OFF                       |
| innodb_purge_threads                    | 1                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_concurrency_timer_based   | OFF                       |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| pseudo_thread_id                        | 3429284                   |
| thread_cache_size                       | 8                         |
| thread_concurrency                      | 10                        |
| thread_handling                         | one-thread-per-connection |
| thread_pool_idle_timeout                | 60                        |
| thread_pool_max_threads                 | 500                       |
| thread_pool_oversubscribe               | 3                         |
| thread_pool_size                        | 80                        |
| thread_pool_stall_limit                 | 500                       |
| thread_stack                            | 262144                    |
| wsrep_slave_threads                     | 1                         |
+-----------------------------------------+---------------------------+
25 rows in set (0.00 sec)