0

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:

  1. Time of partition updating was increased 5 times. (before average time was about 2 sec, now it is about 10-11 sec.)
  2. 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

enter image description here

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?

Rick James
  • 135,179
  • 13
  • 127
  • 222
Maksym Fedorov
  • 6,383
  • 2
  • 11
  • 31

0 Answers0