Database backup freezes with deadlock

MySQL & MariaDB Percona XtraDB Cluster 8.x
, ,
1

Hello,

we recently wanted to switch from a single MariaDB server to a replicated XtraDB instance. For this purpose I set up an XtraDB cluster in our kubernetes cluster with 7 nodes distributed across four geographical zones (US, EU, India, AP).

I then wanted to import one of our backups from our old database into the new cluster.
The .sql file for that table is 2.1GB big and consists of roughly 4.5 Million rows.
I tried importing it using

mysql -h mysql-prod -u root -p my_database < backup_file.sql

This will prompt me for a password, begin running and insert rows. However, after some time, I get the following error:

ERROR 1213 (40001) at line 93582: Deadlock found when trying to get lock; try restarting transaction

The given line is a regular part of an INSERT INTO statement that inserts the data into the database.

I’m not sure if this is an issue with xtradb or an issue with my configuration of it.
Maybe someone knows whats wrong?

2

Hello @Tobias_Grether ,

when you say you run on Kubernetes - do you use Percona Operator?

As for deadlock issue:
Try looking at: https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks - then you will be able to find deadlocks in the error log.

Another good tool that can help you is pt-deadlock-logger.

3

Hello @Sergey_Pronin,

yeah i’m using the Percona Operator.
I already enabled innodb_print_all_deadlocks using

SET GLOBAL innodb_print_all_deadlocks=1;

However none of the nodes actually seem to print relevant data.
I tried running pt-deadlock-logger, I’m not sure about its usage though - do I need to point it to the specific node in the cluster that receives the request or can I point it to any node in the cluster?

4

You checked MySQL error log on each node? That’s were the info would be written.

5

Yeah I checked for each one of them, however it doesn’t seem to state anything related to deadlocks

6

I might be wrong and this might be totally offtopic, but tightly coupled database cluster with more than 1 hop is a no no.
Take a look at this excellent post:

And here more info how to setup this: MySQL High Availability On-Premises: A Geographically Distributed Scenario

7

Hmm yeah thats actually a good link, thank you. I knew that this might be an issue but still wanted to try it either way.

Thanks guys and have a good one