Write lock on tables when executing backup

nekoanz26 · December 30, 2020, 4:21pm

Hi,

I am currently doing a case study regarding the usage of xtrabackup.

When I am executing the backup it seems that reading from the tables is possible, but when the system tries to execute writes to the tables it seems that it is not allowed for a lock is currently on-going.

Can anyone enlighten me regarding this one? and how can I get around with this?

Appreciate your help.

Thanks in advance.

mixa · December 30, 2020, 4:30pm

Assuming that all tables are InnoDB:
Do you see any errors trying to write to DB? What in error-log?
Can you paste here output?

nekoanz26 · December 30, 2020, 4:30pm

i don’t think there are errors

whats happening is whenever the backup is running, when I am trying to access the application it leads me to a timeout error on the browser

that is why I assume that the application cannot write on the tables

for whenever I visit pages that only does reads from the table it can load the page but when I visit pages that will write data on the tables that is when the timeout occur.

mirfan · December 30, 2020, 4:30pm

Locking is only done for MyISAM and non-InnoDB tables. So probably your application database tables includes MyISAM tables as well. Check how innobackupex works for further details.

nekoanz26 · December 30, 2020, 4:30pm

Hi,

But upon checking all my tables are InnoDB and no table is on MyISAM.

Is it possible that the entire database is on MyISAM?

We have tried using mysqldump with --single-transaction parameter it works well without locking any table and we can still execute writes to the tables. How can we achieve this in percona?

Thanks in advance.

przemek · December 30, 2020, 4:30pm

Hi,

No, the storage engine is per table, not per database.

By default, innobackupex first copies all InnoDB data and then acquires global read lock with “flush tables with read lock” before it starts to copy all the non-transactional files, so MyISAM tables and indexes, .frm table definition files, etc.
If all your tables are InnoDB, then this stage when the global lock is active should be very short, however if you have many tables, then copying the .frm files can take considerable time.
You can use the --no-lock option if you want to skip the global read lock. But it’s safe only for InnoDB tables and if no tables are changed during the backup (no ALTER, DROP, etc.).
See more details: http://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/innobackupex_option_reference.html#cmdoption-innobackupex–no-lock