# MySQL server performance degradation after multiple connects and disconnects of clients **URL:** \ **Category:** Percona Distribution for MySQL\ **Created:** [August 17, 2021, 9:32am UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815 "2021-08-17T09:32:06Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Nikolay](https://avatars.discourse-cdn.com/v4/letter/n/a698b9/32.png) [@Nikolay](https://forums.percona.com/u/Nikolay)\ **Post date:** [August 17, 2021, 9:32am UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/1 "2021-08-17T09:32:06Z")
Hello, We faced some issue which causes noticeable performance degradation of mysql server. The change is significant - we saw 3 times slower performance than before and it remains at its low level until its full restart. As far as we can tell from all our tests, the issue is related to establishing and closing mysql connections to the server (all technical details and tests are below). If anyone can provide any useful information, it would be very nice. Software: MySQL Percona Server versions 5.6.32 and 5.7.20 Environment: initially discovered on production server (5.6), later reproduced on test servers (5.6 and 5.7) Recent Changes: no changes in software or hardware before its occurrence Relevant Files (for 5.7): /etc/my.cnf: [client] port = 3306 socket = /tmp/mysql.sock [mysqld\_safe] log-error=/var/10.8.107.143/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqld] datadir = /var/10.8.107.143/mysql port = 3306 socket = /tmp/mysql.sock skip-external-locking key\_buffer\_size = 64M max\_allowed\_packet = 32M relay-log = relay-bin sort\_buffer\_size = 2M read\_buffer\_size = 2M read\_rnd\_buffer\_size = 8M myisam\_sort\_buffer\_size = 32M thread\_cache\_size = 8 query\_cache\_size = 64M default-storage-engine = InnoDB innodb\_buffer\_pool\_size = 640M innodb\_log\_file\_size = 100M innodb\_log\_buffer\_size = 8M innodb\_flush\_log\_at\_trx\_commit = 1 innodb\_lock\_wait\_timeout = 50 log-bin-trust-function-creators = 1 innodb\_stats\_on\_metadata = 0 [mysqldump] quick max\_allowed\_packet = 32M [mysql] default-character-set=utf8 [isamchk] key\_buffer = 64M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M [myisamchk] key\_buffer = 256M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M [mysqlhotcopy] interactive-timeout !include /var/10.8.107.143/mysql/my.cnf /var/10.8.107.143/mysql/my.cnf: [mysqld\_safe] innodb\_sched\_priority\_cleaner = 20 [mysqld] bind-address = 10.8.107.143 port = 3306 server-id = 2021072113 tmpdir = /var/10.8.107.143/mysql-tmpdir max\_connections = 10000 log-bin = mysql-bin binlog\_format = ROW slave\_type\_conversions=ALL\_NON\_LOSSY sql\_mode=NO\_ENGINE\_SUBSTITUTION expire\_logs\_days=5 userstat = ON thread\_statistics = OFF performance\_schema = ON event\_scheduler = ON key\_buffer\_size = 64M read\_rnd\_buffer\_size = 8M read\_buffer\_size = 2M sort\_buffer\_size = 2M max\_heap\_table\_size = 128M tmp\_table\_size = 128M query\_cache\_type = OFF query\_cache\_size = 1M innodb\_file\_format = barracuda innodb\_strict\_mode = ON expand\_fast\_index\_creation = ON innodb\_stats\_auto\_recalc = ON innodb\_stats\_persistent\_sample\_pages = 40 innodb\_log\_buffer\_size = 8M innodb\_log\_file\_size = 100M innodb\_buffer\_pool\_size = 44G innodb\_buffer\_pool\_instances = 8 innodb\_buffer\_pool\_load\_at\_startup = OFF innodb\_buffer\_pool\_dump\_at\_shutdown = OFF innodb\_flush\_method = O\_DIRECT innodb\_flush\_log\_at\_trx\_commit = 1 innodb\_flush\_neighbors = 1 innodb\_thread\_concurrency = 24 innodb\_commit\_concurrency = 0 table\_open\_cache = 8000 table\_open\_cache\_instances = 8 metadata\_locks\_hash\_instances = 8 innodb\_adaptive\_hash\_index = ON innodb\_io\_capacity = 200 innodb\_write\_io\_threads = 4 innodb\_read\_io\_threads = 4 innodb\_purge\_threads = 2 innodb\_checksum\_algorithm = innodb binlog\_checksum = NONE log\_bin\_use\_v1\_row\_events = ON sync\_binlog = 0 innodb\_data\_file\_path = ibdata1:10G:autoextend max\_heap\_table\_size=128M tmp\_table\_size=128M log-slave-updates replicate-do-db=db1 replicate-do-db=db2 replicate-do-db=db3 replicate-do-db=db4 replicate-do-db=db5 replicate-do-db=db6 replicate-ignore-table = db1.Session\_Deposits replicate-ignore-table = db1.Session\_Connect\_Attempts replicate-ignore-table = db1.Active\_Calls replicate-ignore-table = db2.active\_nodes replicate-ignore-table = db2.shared\_cookies innodb\_log\_file\_size=400M innodb\_lock\_wait\_timeout=300 skip-slave-start report-host=psh No errors or warnings in mysqld logs. All our steps for reproducing it (not in 100% of cases, but pretty frequent): - restart mysqld - check its initial performance. We just started some number of parallel mysql clients running a simple select query on an empty table (Create test DB/table if they are absent): mysql -u root -h 10.8.107.143 -e “create database if not exists ls\_performance\_test”; mysql -u root -h 10.8.107.143 ls\_performance\_test -e “create table if not exists ls\_performance\_test (p int(10) unsigned not null auto\_increment, k int(10) unsigned not null, v varchar(100) default null, primary key (p), key (k)) engine innodb default charset=latin1”; for i in `seq 1 48`; do sleep 0.5; while true; do echo “select \* from ls\_performance\_test where k = 27;”; done | mysql -u root -h 10.8.107.143 ls\_performance\_test & done then checked values of the status variable “Queries” now and in a couple of minutes and calculated number of queries processed per minute - stop all clients started above - run a loop making connects and disconnects: for i in `seq 1 30`; do for i in `seq 1 1024`; do timeout 8 mysql -u root -h 10.8.107.143 -A ls\_performance\_test & done; sleep 10; done - when everything finishes, check server performance again. Most likely you will get noticeably lower performance. We were able to lower it from ~90000 queries per second to ~30000 queries per second one time Two interesting things we noticed: - performance changes affect new connections only, already existing connections were not affected in our tests - we could not reproduce it after changing thread\_cache\_size to 2048, however we do not know whether it just masks the issue somehow (e.g., already existing threads remain in the “good” state, so, it is harder to notice the issue) or really affects its root cause. However, its increasing does not look like a proper fix ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 17, 2021, 3:51pm UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/2 "2021-08-17T15:51:39Z")
I would disable `userstat`. That’s a heavy hitting metric. innodb\_checksum\_algorithm=innodb is slow; Switch to crc32. If on ssd, innodb\_flush\_neighbors=0. innodb\_log\_file\_size looks pretty low. You guys are never writing more than 100M/hr? event\_scheduler=off unless you are using it. ---
**Author:** ![Nikolay](https://avatars.discourse-cdn.com/v4/letter/n/a698b9/32.png) [@Nikolay](https://forums.percona.com/u/Nikolay)\ **Post date:** [August 17, 2021, 5:21pm UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/3 "2021-08-17T17:21:33Z")
Thank you for your suggestions. We tried userstat = OFF; innodb\_checksum\_algorithm = crc32; event\_scheduler = OFF, however the issue is still there. We did not touch innodb\_flush\_neighbors (we are using HDDs) and innodb\_log\_file\_size (we do not have any writing activity there during our tests). ---
**Author:** ![Nikolay](https://avatars.discourse-cdn.com/v4/letter/n/a698b9/32.png) [@Nikolay](https://forums.percona.com/u/Nikolay)\ **Post date:** [August 18, 2021, 8:29pm UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/4 "2021-08-18T20:29:11Z")
One more thing we noticed - when we ran tests for measuring its performance (48 parallel mysql clients with simple query on empty table), they had random states in mysql processlist (sleep/sending/etc.) when everything was okay. However, when the same test was done when the server was in the bad state, majority of them were shown in the state “statistics”. According to documentation it usually means slow disk operations, however disk activity was close to 0 during these periods (0% disk load; 0 KB/s disk read; 0-3 KB/s disk write) ---
**Author:** ![Ivan\_Groenewold](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ivan_groenewold/32/6299_2.png) [@Ivan\_Groenewold](https://forums.percona.com/u/Ivan_Groenewold)\ **Post date:** [August 19, 2021, 12:18pm UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/5 "2021-08-19T12:18:09Z")
Can you try also query\_cache\_size=0? I suggest you install PMM to see the metrics and identify the problem more easily ---
**Author:** ![Nikolay](https://avatars.discourse-cdn.com/v4/letter/n/a698b9/32.png) [@Nikolay](https://forums.percona.com/u/Nikolay)\ **Post date:** [August 20, 2021, 7:50am UTC](https://forums.percona.com/t/mysql-server-performance-degradation-after-multiple-connects-and-disconnects-of-clients/11815/6 "2021-08-20T07:50:13Z")
Thank you. query\_cache\_size=0 did not change anything. Regarding PMM - we have it. Is there anything specific? Basically, it shows very expected picture - number of queries (and related metrics) are 2 times lower after these multiple reconnects. Process states graphs show already described change - increased number of “statistics” states (from ~4 to ~30 in our last test). Perhaps the only new information we found there is “Client thread activity” - average threads running is ~12 for “good” periods and ~40 for “bad” periods for the same test conditions.