# Increased I/O Wait and CPU Load after Upgrading MySQL from 5.7 to 8.0 **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Tags:** troubleshooting, mysql\ **Created:** [March 28, 2024, 1:19pm UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423 "2024-03-28T13:19:55Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![cesanek](https://avatars.discourse-cdn.com/v4/letter/c/b2d939/32.png) [@cesanek](https://forums.percona.com/u/cesanek)\ **Post date:** [March 28, 2024, 1:19pm UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/1 "2024-03-28T13:19:55Z")
Hello, I’m dealing with an issue where immediately after upgrading MySQL from version 5.7 to 8.0, there’s been a significant increase in I/O wait and CPU load. Please refer to the attached PPM graph. I have a cluster consisting of 3 nodes, with 2 nodes designated for read-only and one node for read-write operations. The CPU load on the read-write node is exceeding the threshold value. Is there any configuration adjustment, different from version 5.7, that can alleviate the load on these nodes? Thank you for any advice. These nodes are running as VPS on Proxmox virtualization with configuration: 4x core (Intel(R) Xeon(R) Gold 6138 CPU) , 24GB RAM , SSD Samsung SSD 870 EVO Below is the my.cnf configuration: [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking skip-name-resolve bind-address = 0.0.0.0 #key\_buffer = 16M max\_allowed\_packet = 32M thread\_stack = 2048K thread\_cache\_size = 8 log\_error = /var/log/mysql/error.log #percona SETTINGS server-id=1 innodb\_file\_per\_table=1 innodb\_redo\_log\_capacity=640M innodb\_buffer\_pool\_size=16G innodb\_buffer\_pool\_instances=16 innodb\_buffer\_pool\_chunk\_size=128M innodb\_flush\_method=O\_DIRECT innodb\_thread\_concurrency=15 innodb\_flush\_log\_at\_trx\_commit=0 wsrep\_sst\_method=xtrabackup-v2 wsrep\_provider=/usr/lib/libgalera\_smm.so wsrep\_provider\_options=“gcache.size = 5G;cert.log\_conflicts=YES;gmcast.peer\_timeout=PT10S” pxc-encrypt-cluster-traffic=OFF wsrep\_log\_conflicts=ON wsrep\_cluster\_name=xxx wsrep\_cluster\_address=gcomm://x.x.x.x.x.x.x.x.x.x.x.x.x.x..x wsrep\_slave\_threads=8 wsrep\_node\_name=x.x.x.x wsrep\_node\_address=x.x.x.x wsrep\_sst\_donor=x.x.x.x wsrep\_retry\_autocommit=5 pxc\_strict\_mode=ENFORCING sql\_mode=“NO\_ZERO\_IN\_DATE,NO\_ZERO\_DATE,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_ENGINE\_SUBSTITUTION” log\_timestamps=SYSTEM group\_concat\_max\_len = 64M tmp\_table\_size = 128M sort\_buffer\_size = 2M join\_buffer\_size = 2M table\_open\_cache=2048 max\_connections=500 max\_heap\_table\_size = 512M innodb\_lru\_scan\_depth = 256 binlog\_format=ROW default\_storage\_engine=InnoDB innodb\_autoinc\_lock\_mode=2 [mysqldump] quick quote-names max\_allowed\_packet = 64M [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld\_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 service\_startup\_timeout = 2880 # PPM userstat=ON Screenshot of increse load and i/o wait from PPM The upgrade took place on 03/25, coinciding with a noticeable increase ![pmm_load](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/6/68c1d8876842ac207063dd5d32eb36476734648a.png) ---
**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:** [March 29, 2024, 4:01am UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/2 "2024-03-29T04:01:42Z")
Hello @cesanek, It’s PMM, not PPM. 🙂 Anyways, is there anything notable in MySQL’s error log? Have you looked at the performance schema waits tables in PMM to see what the IO is waiting on? `innodb_lru_scan_depth` I would revert this back to the default, and instead opt to set `innodb_flush_neighbors=0` instead. ---
**Author:** ![cesanek](https://avatars.discourse-cdn.com/v4/letter/c/b2d939/32.png) [@cesanek](https://forums.percona.com/u/cesanek)\ **Post date:** [April 3, 2024, 9:03am UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/3 "2024-04-03T09:03:03Z")
Thanks for your reply! I haven’t found anything relevant in the MySQL’s error log. I adjusted `innodb_lru_scan_depth` and `innodb_flush_neighbors` as you suggested, but unfortunately, these changes haven’t had a significant impact on CPU load or I/O waits. There was a change in these parameters around 14:45 according to the graph. ![io_waits](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/3/32c23f41f2489b36b0ddec2aa736663188dec395.png) ---
**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:** [April 3, 2024, 3:51pm UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/4 "2024-04-03T15:51:23Z")
Can you please show that Perf Schema waits graph using the same time range as your CPU graph above? ---
**Author:** ![cesanek](https://avatars.discourse-cdn.com/v4/letter/c/b2d939/32.png) [@cesanek](https://forums.percona.com/u/cesanek)\ **Post date:** [April 4, 2024, 10:48am UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/5 "2024-04-04T10:48:18Z")
Sure! ![psw](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/2X/e/e36b77539aa16c058bd655bfd8ce2ecfd54effe3.png) ---
**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:** [April 4, 2024, 12:25pm UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/6 "2024-04-04T12:25:56Z")
On the Waits(Load), yea, there’s an overall increase in everything, and your iowait increases as well. Maybe try `sync_binlog=1000`, or `innodb_thread_concurrency=0` Side note, tmp\_table\_size and max\_heap\_table\_size should be the same, as the lower of the two is the actually used value. The `nice=0` i’ve never seen before; I would remove that. ---
**Author:** ![cesanek](https://avatars.discourse-cdn.com/v4/letter/c/b2d939/32.png) [@cesanek](https://forums.percona.com/u/cesanek)\ **Post date:** [April 5, 2024, 8:12am UTC](https://forums.percona.com/t/increased-i-o-wait-and-cpu-load-after-upgrading-mysql-from-5-7-to-8-0/29423/7 "2024-04-05T08:12:54Z")
My bad, binary logs are enabled by default since MySQL version 8.X. However, we don’t need them because we don’t use master-slave replication, so I disabled binary logs completely. This, however, did not reduce the high CPU load. So, following your recommendation, I tried to set the parameter ‘innodb\_thread\_concurrency=0’, but this also had no noticeable effect on CPU load. Do you think there are any other options? And thank you for mentioning other additional incorrect settings. BTW: I will add that the problem only occurs on the node that is designated as the write node using a proxy. The cluster is configured with 2 nodes for reading and 1 node for writing only.