We have a development database server with Percona-server 5.7.15-9 on it. It replicates two schemas from two different production servers using multi-source GTID replication. Lets call these schemas alice
and bob
.
On the dev server we clone this replicas of production databases to get databases for development. They are called 1_alice
, 1_bob
, 2_alice
, 2_bob
, etc. All of them use the same instance of MySQL.
For fast cloning we use Percona XtraBackup as described here https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/restoring_individual_tables_ibk.html
In the past there were only one replica (alice
) and we used binary log position for replication instead of GTID. These times everything worked fine and fast. One day (I'm not sure when exactly) it became broken.
Now when I perform ALTER TABLE 2_alice.access_group IMPORT TABLESPACE
query, it hangs in the 'System lock' state. And could be hanging in this state from 1 min 'till 1 hour and more (then it works). There are no more active connections instead of two replicas, but they doesn't use 2_alice
schema.
Why is IMPORT TABLESPACE
query hanging and how could I debug this case?
Best Answer
I think your table must be very big. I have recently encountered the same problem when I trying to import a 700G table.
Check the error log, I found that the import is in progress stage 1
Check the official documentation. https://dev.mysql.com/doc/refman/8.0/en/tablespace-copying.html
When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:
Each tablespace page is checked for corruption.
The space ID and log sequence numbers (LSNs) on each page are updated
Flags are validated and LSN updated for the header page.
Btree pages are updated.
The page state is set to dirty so that it is written to disk.
I think it takes a lot of time to update the LSN of each page.
check the MySQL work log https://dev.mysql.com/worklog/task/?id=5522