Got timeout reading communication packets on /var/log/mysql/error.log about 50 per second

[Zhanglei Mao]

The /var/log/mysql/error.log would print below about 50 per second:
..
2019-03-29T08:47:49.273204Z 3481 [Note] Aborted connection 3481 to db: 'nova_api' user: 'nova' host: '185.168.3.66' (Got timeout reading communication packets)
..
(also four neutron
2019-03-29 00:08:02.050 249626 ERROR oslo_db.sqlalchemy.engines
2019-03-29 00:08:06.820 249627 ERROR oslo_db.sqlalchemy.engines [req-e89ea255-fab7-45fe-85cb-67bc1c0b821f - - - - -] Database connection was found disconnected; reconnecting: DBConnectionError: (pymysql.err.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: u'SELECT 1']
2019-03-29 00:08:06.820 249627 ERROR oslo_db.sqlalchemy.engines Traceback (most recent call last):

[Zhanglei Mao (zhanglei-mao)]

ping to mysql cluster vip didn't find any packet lost. The iperf3 testing looks really good from nova-cloud-controller/0 to this vip too as:

ubuntu@juju-98c8d1-3-lxd-5:~$ iperf3 -c 185.168.3.34
Connecting to host 185.168.3.34, port 5201
[ 4] local 185.168.3.244 port 54338 connected to 185.168.3.34 port 5201
[ ID] Interval Transfer Bandwidth Retr Cwnd
[ 4] 0.00-1.00 sec 120 MBytes 1.01 Gbits/sec 90 773 KBytes
[ 4] 1.00-2.00 sec 112 MBytes 944 Mbits/sec 0 880 KBytes
[ 4] 2.00-3.00 sec 111 MBytes 934 Mbits/sec 0 973 KBytes
[ 4] 3.00-4.00 sec 112 MBytes 943 Mbits/sec 45 827 KBytes
[ 4] 4.00-5.00 sec 112 MBytes 944 Mbits/sec 0 846 KBytes
[ 4] 5.00-6.00 sec 112 MBytes 944 Mbits/sec 0 942 KBytes
[ 4] 6.00-7.00 sec 111 MBytes 933 Mbits/sec 45 776 KBytes
[ 4] 7.00-8.00 sec 112 MBytes 944 Mbits/sec 0 881 KBytes
[ 4] 8.00-9.00 sec 112 MBytes 944 Mbits/sec 0 974 KBytes
[ 4] 9.00-10.00 sec 112 MBytes 944 Mbits/sec 45 790 KBytes
- - - - - - - - - - - - - - - - - - - - - - - - -
[ ID] Interval Transfer Bandwidth Retr
[ 4] 0.00-10.00 sec 1.10 GBytes 948 Mbits/sec 225 sender
[ 4] 0.00-10.00 sec 1.10 GBytes 941 Mbits/sec receiver

iperf Done.
ubuntu@juju-98c8d1-3-lxd-5:~$

[Zhanglei Mao (zhanglei-mao)]

lost connection report was only from nova-api, also for other services of neutron, gnocchi, keystone etc. too.

[Zhanglei Mao (zhanglei-mao)]

To adjust percona cluster charm wait_timeout (from 180 to 3600 seconds) and innodb-buffer-pool-size (from 512M to 2G) can reduces this timeout error/waring about 90%. To reduces work-multiplier to reduces would reduce db clients connect too (it would run less process for example neutron-server).

[Zhanglei Mao (zhanglei-mao)]

It might better to set as 3630 to wait more 30s for client close.