Processlist state field: wsrep: replicating and certifying write set(-1) on 8.0.31, 8.0.32-24.2

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

After upgrading 8.0.29 to 8.0.31, on stresstesting most of sesions stuck in state: “wsrep: replicating and certifying write set(-1)”
We upgraded to latest 8.0.32-24.2, but bug is still here.

show processlist
| 311151 | isac_is_core     | 10.10.117.14:58428 | isac_is_core       | Query   |   385 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   384964 |         0 |             0 |
| 311155 | isac_is_core     | 10.10.117.14:58440 | isac_is_core       | Query   |   384 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_error` (`id`, `data`, `type`, `error_msg`, `trace`, `logs_api_id`, `logs_api_reque |   383663 |         0 |             0 |
| 311322 | isac_is_core     | 10.10.117.14:45436 | isac_is_core       | Sleep   |   326 |                                                 | NULL                                                                                                 |   326071 |         0 |        611354 |
| 311326 | isac_is_core     | 10.10.117.14:45446 | isac_is_core       | Query   |   325 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   324419 |         0 |             0 |
| 311496 | isac_is_core     | 10.10.117.14:43818 | isac_is_core       | Query   |   265 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   265200 |         0 |             0 |
| 311501 | isac_is_core     | 10.10.117.13:42188 | isac_is_core       | Query   |   264 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   263748 |         0 |             0 |
| 311665 | isac_is_core     | 10.10.117.14:35772 | isac_is_core       | Sleep   |   206 |                                                 | NULL                                                                                                 |   206307 |         0 |             0 |
| 311671 | isac_is_core     | 10.10.117.14:35778 | isac_is_core       | Query   |   205 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   204949 |         0 |             0 |
| 311836 | isac_is_core     | 10.10.117.14:35650 | isac_is_core       | Sleep   |   147 |                                                 | NULL                                                                                                 |   146913 |         0 |        611354 |
| 311842 | isac_is_core     | 10.10.117.14:35652 | isac_is_core       | Query   |   146 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |   145775 |         0 |             0 |
| 312010 | isac_is_core     | 10.10.117.14:54620 | isac_is_core       | Sleep   |    87 |                                                 | NULL                                                                                                 |    86622 |         0 |        611354 |
| 312183 | isac_is_core     | 10.10.117.14:54354 | isac_is_core       | Sleep   |    26 |                                                 | NULL                                                                                                 |    26313 |         0 |       2749841 |
| 312184 | isac_is_core     | 10.10.117.14:54368 | isac_is_core       | Query   |    26 | wsrep: replicating and certifying write set(-1) | INSERT INTO `logs_command_execution` (`id`, `logs_error_id`, `name`, `started_at`, `finished_at`, `d |    26304 |         0 |             0 |
| 312259 | clustercheckuser | localhost          | NULL               | Query   |     0 | executing                                       | SHOW VARIABLES LIKE 'pxc_maint_mode'                                                                 |        4 |         0 |             0 |
| 312260 | clustercheckuser | localhost          | NULL               | Query   |     0 | Sending to client                               | SHOW STATUS LIKE 'wsrep_local_state'                                                                 |        2 |         1 |             1 |
+--------+------------------+--------------------+--------------------+---------+-------+-------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+-----------+---------------+
610 rows in set (0.00 sec)

I checkes data locked, but nothing special:

mysql> SELECT ENGINE_TRANSACTION_ID as eng_tr_id, THREAD_ID, OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA FROM performance_schema.data_locks order by THREAD_ID;
+-----------+-----------+---------------+------------------------+---------------------+-----------+---------------+-------------+-------------------------------------+
| eng_tr_id | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME            | INDEX_NAME          | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA                           |
+-----------+-----------+---------------+------------------------+---------------------+-----------+---------------+-------------+-------------------------------------+
|  63093971 |    250138 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093981 |    250151 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093973 |    250154 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093994 |    250164 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093976 |    250166 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093980 |    250169 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094003 |    250170 | isac_is_core  | cards_holders          | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63093988 |    250171 | isac_is_core  | logs_error             | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
...
|  63094471 |    307517 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094475 |    307520 | isac_is_admin | db_sync                | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094475 |    307520 | isac_is_admin | db_sync                | PRIMARY             | RECORD    | X,REC_NOT_GAP | GRANTED     | 162                                 |
|  63094473 |    307521 | isac_is_core  | logs_error             | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094474 |    307527 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094477 |    307881 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094478 |    307939 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094479 |    308055 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094480 |    308226 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
...
|  63094520 |    314175 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
|  63094521 |    314200 | isac_is_core  | logs_command_execution | NULL                | TABLE     | IX            | GRANTED     | NULL                                |
+-----------+-----------+---------------+------------------------+---------------------+-----------+---------------+-------------+-------------------------------------+
537 rows in set (0.01 sec)

Also there is a lot of locked tables, but i cant find transaction which locks everything:
i switched GTID mode off

mysql> select @@GTID_MODE, @@ENFORCE_GTID_CONSISTENCY;
+-------------+----------------------------+
| @@GTID_MODE | @@ENFORCE_GTID_CONSISTENCY |
+-------------+----------------------------+
| OFF         | OFF                        |
+-------------+----------------------------+
1 row in set (0.00 sec)
2

Mostly certification session is stucked on INSERT into logs_* tables, they have one or two indexes.

mysql> show open tables where In_use > 0;
+--------------+----------------------------------------+--------+-------------+
| Database     | Table                                  | In_use | Name_locked |
+--------------+----------------------------------------+--------+-------------+
| isac_is_core | logs_external_request                  |     93 |           0 |
| isac_is_core | logs_external_response                 |     10 |           0 |
| isac_is_core | cards                                  |      6 |           0 |
| isac_is_core | transactions                           |      9 |           0 |
| isac_is_core | logs_history                           |     36 |           0 |
| isac_is_core | logs_error                             |     55 |           0 |
| isac_is_core | currency_rates                         |      2 |           0 |
| isac_is_core | logs_internal_transactions             |      7 |           0 |
| isac_is_core | cards_accounts                         |      7 |           0 |
| isac_is_core | logs_command_execution                 |    386 |           0 |
| isac_is_core | cards_accounts_balance_snapshots_daily |     10 |           0 |
| isac_is_core | cards_accounts2cards                   |      1 |           0 |
| isac_is_core | logs_transaction_data                  |     13 |           0 |
| isac_is_core | logs_card_account_balance              |      6 |           0 |
| isac_is_core | fees_groups                            |      1 |           0 |
| isac_is_core | fees                                   |      1 |           0 |
| isac_is_core | cards_fees                             |      1 |           0 |
+--------------+----------------------------------------+--------+-------------+
17 rows in set (0.00 sec)

I dropped indexes on five table and now stresstest is OK. But this is simple insert!!! Maybe there is some tuning parameter? for inserts.