# PXC8.0.37 Very high thread activity during replication **URL:** \ **Category:** Percona XtraDB Cluster 8.x\ **Tags:** mysql, percona\ **Created:** [February 21, 2025, 12:44pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850 "2025-02-21T12:44:43Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [February 21, 2025, 12:44pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/1 "2025-02-21T12:44:43Z")
Hello! There is a cluster 8.0.37 of 3 nodes: > Server version: 8.0.37-29.1 Percona XtraDB Cluster (GPL), Release rel29, Revision d29a325, WSREP version 26.1.4.3 In front of it is ProxySQL with balancing rules: read/write. Write always on 1 node, read from 2 nodes. PXC bare-metal servers: > OS: Ubuntu 22.04.5 LTS > CPU: Intel W-2255 (10x3.7 GHz HT) > RAM: 256 GB — 4 × 64 GB DDR4 ECC Reg > Disk: > 2 × 480 GB SSD SATA Enterprise - for OS > 2000 GB SSD NVMe M.2 - for database PXC mysqld.cnf: > **Summary** > > ```auto > # Ansible managed > > #check_point > # Template my.cnf for PXC > # Edit to your requirements. > [client] > socket = /var/run/mysqld/mysqld.sock > > [mysqld] > datadir = /nvme/mysql > log-error = /var/log/mysql/error.log > pid-file = /var/run/mysqld/mysqld.pid > server-id = 22 > socket = /var/run/mysqld/mysqld.sock > > authentication_policy = mysql_native_password # mysql_native_password - deprecated > > # Binary log expiration period is 604800 seconds, which equals 7 days > binlog_expire_logs_seconds = 28800 > > ######## wsrep ############### > # Path to Galera library > wsrep_provider = /usr/lib/libgalera_smm.so > > # Cluster connection URL contains IPs of nodes > #If no IP is found, this implies that a new cluster needs to be created, > #in order to do that you need to bootstrap this node > wsrep_cluster_address = gcomm://X.X.X.X,X.X.X,X.X.X.X > # In order for Galera to work correctly binlog format should be ROW > # binlog_format = ROW # deprecated > > # Slave thread to use > wsrep_applier_threads = 16 > > wsrep_log_conflicts > > # This changes how InnoDB autoincrement locks are managed and is a requirement for Galera > innodb_autoinc_lock_mode = 2 > > # Node IP address > wsrep_node_address = X.X.X.X > # Cluster name > wsrep_cluster_name = pxc-cluster > > #If wsrep_node_name is not specified, then system hostname will be used > #wsrep_node_name=pxc-cluster-node-1 > > #pxc_strict_mode allowed values: DISABLED,PERMISSIVE,ENFORCING,MASTER > pxc_strict_mode = ENFORCING > > # SST method > wsrep_sst_method = xtrabackup-v2 > #encrypt > pxc-encrypt-cluster-traffic = OFF > > pxc_maint_mode = DISABLED > pxc_maint_transition_period = 10 > wsrep_applier_FK_checks = ON > wsrep_applier_UK_checks = OFF > wsrep_auto_increment_control = ON > wsrep_certify_nonPK = ON > wsrep_debug = NONE > wsrep_dirty_reads = OFF > wsrep_load_data_splitting = ON > wsrep_max_ws_rows = 800000 > wsrep_max_ws_size = 2147483648 > wsrep_provider_options = "pc.weight=1;gmcast.segment=1;socket.ssl_compression=no;gcache.keep_pages_size=4;gcache.page_size=32M;gcache.size=1024M;evs.send_window=512;evs.user_send_window=512;evs.use_aggregate = true;gcs.fc_factor = 0.9;gcs.fc_limit = 5000;gcs.fc_single_primary = no;gcs.max_packet_size = 131072;gcs.fc_debug = 0;evs.inactive_check_period = PT1S;evs.inactive_timeout = PT20S;evs.suspect_timeout = PT5S;evs.stats_report_period = PT1M;" > wsrep_recover = OFF > wsrep_replicate_myisam = OFF > wsrep_restart_replica = OFF > wsrep_retry_autocommit = 3 > wsrep_sst_receive_address = AUTO > wsrep_sync_wait = 0 > > ##################INNODB and other options##################### > connect_timeout = 60 > group_concat_max_len = 2048 > innodb_adaptive_flushing_lwm = 20 > innodb_adaptive_hash_index = OFF > innodb_buffer_pool_instances = 16 > innodb_buffer_pool_size = 214748364800 > innodb_flush_log_at_trx_commit = 2 > innodb_flush_method = O_DIRECT > innodb_io_capacity = 2000 > innodb_io_capacity_max = 5000 > innodb_print_all_deadlocks = OFF > innodb_redo_log_capacity = 5G # innodb_log_file_size deprecated > interactive_timeout = 1200 > log_error_suppression_list = MY-013360 > log_error_verbosity = 3 > long_query_time = 1 > max_allowed_packet = 512M > max_connect_errors = 99999999999999 > max_connections = 3000 > max_prepared_stmt_count = 131072 > range_optimizer_max_mem_size = 44040191 > skip_name_resolve = 1 > slow_query_log = ON > slow_query_log_file = /var/log/mysql/mysql-slow.log > sql_mode = 'NO_ENGINE_SUBSTITUTION' > sync_binlog = 2 > table_open_cache = 8192 > thread_cache_size = 256 > userstat = ON > wait_timeout = 1200 > > [sst] > streamfmt = xbstream > #compressor='zstd -1 -T2' > #decompressor='zstd -d -T2' > > [xtrabackup] > parallel = 16 > rebuild-threads = 16 > > ``` There is a problem when data is written/modified on the writing node. As a result, a strong burst of connections is visible on ProxySQL: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/2/52a92ec56664d3b7cbc5c681c56d13059459adaa.png) Which is not typical for us. The traffic has increased on the reading nodes, these are 2 nodes. Due to the fact that the load on them has increased significantly, requests have become very slow to execute and there is a queue from services. We look at metrics on MySQL/MySQL Table Details charts: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/0/c/0c64faee16d3842f5e77f4cf408c6c8a18fc4185.png) Here you can see a large number of data changes over time from one of the services. MySQL/MySQL Instances Compare: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/6/a6d0e4ab3a6286ac2e4fa88e1b97b31bc68c670a.png) The graphs show interruptions in connections on 2 nodes for reading. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/0/1/01d94b0d2d0d7ebd7571d48e2dca723ac066c204.png) The graphs show a very high number of Threads on 2 nodes for reading. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/4/4/448bacd0e061c66b541c45d56932341d2d00ae89.png) The graphs show a very high number of slow queries on 2 nodes for reading. This is not typical for them, as there are practically no slow queries even during the whole day. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/8/b/8b957e1610ab1dfec87baf158d81b8b0d7e9cfab.png) And also graphs for Transaction Handlers and Process States. There are no problems with resource usage: CPU, Memory or Disk. node1 (writer): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/7/a/7ad6edd8f24e9e37968c2212b79d04ebc3adbde8.png) node2 (reader): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/d/cdff8919e2fe5582af02e581d8dbe9ea2785ef00.png) node3 (reader): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/0/4/04c9b3a281904d5d0c617bba90c802239acec08f.png) And here are the graphs for Galera. MySQL/PXC/Galera Nodes Compare ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/8/d/8dc05cd25c799b0f8ed5f898b1206a52a35fcd52.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/4/5457a5f5a5106431fd28960de61c498c830d86d2.png) And also from interesting graphics MySQL/MySQL InnoDB Details node1: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/1/a/1a76c6b2a60a51fbf2679766b8adeb7e6b76c22f.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/d/5/d55e1cb04763eeea1f47c83e996e991845533f21.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/8/a86a8f385de1963b015a173983de36fc16e611d1.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/e/ce9bd2abc16261f8718f4e85b06ce8b1a05e4340.png) node2: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/6/8/687561d19f304bd5e70a532d3a7eecb117291ae9.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/d/ad8ba45c9f0beac77d046a0fa7c1671ea83ae34b.png) Increased activity of InnoDB Log IO and InnoDB FSyncs is visible. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/c/ac03aa11f1bfc09e8bd402d8550edafa7b9ccb00.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/7/57b5e499159409224377ea0cb3cf03ddfd630b2d.png) Too large on InnoDB Row Lock Wait Load. node3: ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/4/a/4a95560480cef26f1281508f61bbd5bb78ccf9dc.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/f/8/f86b3a82527e1d0148576965a5c9994956d44cd1.png) Increased activity of InnoDB Log IO and InnoDB FSyncs is visible. ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/5/5/55c6a433318f25f2baabca1550158ac7d8cedd91.png) ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/5/c5964852a8c611d4f074098c9bc07acdff7e5e1b.png) Too large on InnoDB Row Lock Wait Load. Help me understand the situation, what is the impact due to changes in the DB that caused a very large queue during replication and led to a very large number of threads? And this is not always the case. What else can I look at, do? ---
**Author:** ![Abhinav\_Gupta](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/abhinav_gupta/32/6262_2.png) [@Abhinav\_Gupta](https://forums.percona.com/u/Abhinav_Gupta)\ **Post date:** [February 21, 2025, 3:29pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/2 "2025-02-21T15:29:54Z")
These graphs indicate a significant write event during the issue, with a peak of 12.24MB/s for `wait/io/file/innodb/innodb_log_file`. This resulted in excessive flushing to disk, leading to increased I/O activity and contributing to the problem. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [February 24, 2025, 7:23am UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/3 "2025-02-24T07:23:46Z")
Thanks for the answer! Is there any recommendation, solution? Maybe it makes sense to increase `innodb_redo_log_capacity ` (deprecated `innodb_log_file_size`)? Current `innodb_redo_log_capacity=5G` (`innodb_log_file_size`=50331648 bytes). For my part, I notified the developers to reduce the load and distribute the changes in this task with a limit and by time. ---
**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:** [February 24, 2025, 6:09pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/4 "2025-02-24T18:09:40Z")
> [@shigaev.s](#): > > innodb\_redo\_log\_capacity @shigaev.s I would increase innodb\_redo\_log\_capacity to 8G, and also set innodb\_flush\_log\_at\_trx\_commit=2, and if you have binlogs enabled, set sync\_binlog=1000. These will lessen IO impact. ---
**Author:** ![ROHIT\_KHURANA](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rohit_khurana/32/2760_2.png) [@ROHIT\_KHURANA](https://forums.percona.com/u/ROHIT_KHURANA)\ **Post date:** [February 25, 2025, 5:14am UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/5 "2025-02-25T05:14:31Z")
PXC works on synchronous based replication so whenever your writes will increase it’ll impact on majority of nodes at same time and disk IOPs will increase. In addition, **innodb\_doublewrite** can be disabled for saving disk IOPs. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [February 25, 2025, 7:37am UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/6 "2025-02-25T07:37:15Z")
Thanks, I’ll increase `innodb_redo_log_capacity` to 8G. And `innodb_flush_log_at_trx_commit` is already set to 2, `sync_binlog = 2`. I’ll check the work after changing the settings. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [February 25, 2025, 7:37am UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/7 "2025-02-25T07:37:46Z")
> [@ROHIT\_KHURANA](#): > > innodb\_doublewrite Thank you, I’ll look at this parameter. ---
**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:** [February 25, 2025, 12:49pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/8 "2025-02-25T12:49:52Z")
> [@ROHIT\_KHURANA](#): > > innodb\_doublewrite Be sure to understand the risk with disabling dblwrite buffer. The only way InnoDB protects itself against partial page writes is using this buffer. However, you do have a 3-node PXC, and the likelihood that all 3 nodes partially write a page on flush would be very rare. sync\_binlog=2 isn’t saving much. Try 500 or 1000. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [February 25, 2025, 12:54pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/9 "2025-02-25T12:54:45Z")
I read about the `innodb_doublewrite` parameter, I won’t disable it. But I think `sync_binlog` can be increased. Thanks. ---
**Author:** ![ROHIT\_KHURANA](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/rohit_khurana/32/2760_2.png) [@ROHIT\_KHURANA](https://forums.percona.com/u/ROHIT_KHURANA)\ **Post date:** [February 25, 2025, 9:19pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/10 "2025-02-25T21:19:41Z")
I agree, but as they have 3 nodes setup or you can say no single-point failure, I recommended this option. and if they are using PXC cluster why they have binlog enable and need to play with ` **sync_binlog** ` ---
**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:** [February 25, 2025, 9:42pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/11 "2025-02-25T21:42:12Z")
> [@ROHIT\_KHURANA](#): > > and if they are using PXC cluster why they have binlog enable Just like “RAID is not a backup”, a cluster does not protect against disaster. You still need to do regular backups. In order to have point-in-time-recovery, binary logs are required. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [April 23, 2025, 8:55am UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/12 "2025-04-23T08:55:10Z")
Configured in stage `innodb_redo_log_capacity=8G` and `sync_binlog=500`. I’ll check how it works with the new values ​​and configure it in production. ---
**Author:** ![shigaev.s](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@shigaev.s](https://forums.percona.com/u/shigaev.s)\ **Post date:** [April 23, 2025, 1:00pm UTC](https://forums.percona.com/t/pxc8-0-37-very-high-thread-activity-during-replication/36850/13 "2025-04-23T13:00:02Z")
So, here are the results after changing the settings in the stage cluster. Configuration changed from 11:27. **Rows Changed:** ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/7/c/7c5bda0aa461fa160c3c97dfdd34feb35b4e0c25.png) Every hour there is a task that makes changes. ## MySQL Client Thread Activity pxc-stage-01 (writer in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/8/8/8809c3f1d3db859c033acd40a265f19c69fbcd3f.png) pxc-stage-02 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/2/5/25d661b8b700e54ad289df45875dd24881bdc1f7.png) pxc-stage-03 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/c/8/c8d4b16191d567abce64a17e55ffafcf3ded6101.png) ## Galera Replication Queues ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/3/c/3cb6f8701836e4e7c3d9d674b11c2db6e1297d76.png) There is no longer a replication queue. ## InnoDB Disk IO pxc-stage-01 (writer in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/6/a/6aad3380c3c7db76a1512a6dc9aea823fa99bbd4.png) pxc-stage-02 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/a/5/a5ca49ffb57c478d2d69a293c1c37b81109d7b1e.png) pxc-stage-03 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/b/b/bbf774f3b93dcceab80e2ac373c7f29b6a125727.png) As you can see from the graphs, Log Writte increased after setting `innodb_redo_log_capacity=8G`. But there is no decrease in performance. ## Disk Details pxc-stage-01 (writer in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/f/5/f542989538863e05bc1757c2f58197c0e7e59e68.png) pxc-stage-02 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/b/d/bd99de9d5ab09a25a922058caa4e0263d75dab48.png) pxc-stage-03 (reader in ProxySQL): ![image](https://us1.discourse-cdn.com/flex019/uploads/percona1/original/3X/3/5/35633b3905ac641dd230ab874b60f2e1be3b8d4c.png) From the graphs you can see a significant decrease in disk and I/O utilization. The new parameter values ​​helped improve performance and significantly reduce disk utilization. I will update the parameter values ​​in the production environment.