Problem
After upgrading Percona XtraDB cluster from 5 version to 8 version (8.0.28-19.1) I have some problems with the cluster in prod environment:
- Time of partition updating was increased 5 times. (before average time was about 2 sec, now it is about 10-11 sec.)
- When partition updating is starting all active transactions are crushed with an error "Deadlock found when trying to get lock; try restarting transaction"
An example to repeat the deadlock problem
// create a table to test the problem
CREATE TABLE test_table
(
id BIGINT UNSIGNED AUTO_INCREMENT,
value BIGINT NOT NULL,
created_at DATETIME NOT NULL,
primary key (id, created_at)
)
COLLATE = utf8_unicode_ci PARTITION BY RANGE COLUMNS (`created_at`) (
PARTITION p20221105 VALUES LESS THAN ('2022-11-06 00:00:00'),
PARTITION p20221106 VALUES LESS THAN ('2022-11-07 00:00:00'),
PARTITION p20221107 VALUES LESS THAN ('2022-11-08 00:00:00'),
PARTITION p20221108 VALUES LESS THAN ('2022-11-09 00:00:00'),
PARTITION p20221109 VALUES LESS THAN ('2022-11-10 00:00:00')
);
CREATE INDEX idx_created_at ON test_table (created_at);
In one session run a transaction with sleep to do transaction execution and partition updating at the same time
START TRANSACTION;
INSERT INTO test_table (value, created_at) VALUES (1, '2022-11-09 13:00:00');
SELECT SLEEP(30);
COMMIT;
In another session run a partition updating - drop the old partition and add a new one
ALTER TABLE test_table DROP PARTITION p20221105;
ALTER TABLE test_table ADD PARTITION (partition p20221110 values less than ('2022-11-11 00:00:00'));
After running a query to add/drop the partition, the transaction instantly fails with a deadlock error.
Structure of the cluster
wsrep_cluster_address for each node has the same values gcomm://node1,node2,node3
Does somebody have any ideas about what causes those problems and how to fix them?