xtraBackup does block my database

[wpichler]

hi all,

i am currently in the progress of implementing xtraBackup - to be able to set up database replication.

The database is very small at time - ibdata1 file is only around 300MB.

Load is not that high - around 170 queries per second, 30% update, 10% insert.

Database is a 5.5.29-MariaDB-mariadb1~precise-log - on an ubuntu 12.04 64bit Xen VM - with 16GB RAM and 24 E5-2620 CPU’s. Using built in XtraDB engine.

So, starting from this i thought it would be absolut no problem to start XtraBackup - it should be able to complete in a few seconds.

But as soon as i start it - and as soon as it comes to scaning the logs - my database starts to block queries - because of a table level lock.

I do have one special table in my database - it is a memory table - for statistical updates.
The queries which does start to block because the have to wait - do write entries in this table - and some external scripts are reading from there and updating there…

Could the memory table cause the problem ?

I have also already tried the --no-lock option - does have no affect

Any Ideas ?

[gmouse]

innobackupex without --no-lock runs FLUSH TABLES WITH READ LOCK:
Quote:

Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLES to release the lock.

With a READ LOCK, other threads can only read the data but not modify it. Then the MYI and MYD files are copied, then the lock gets released, then ibdata1 gets copied. So, the lock should be released quickly, before copying the ibdata1 file.

With --no-lock I am surprised you still see locking occur.

Maybe use pt-online-schema-change to convert the memory table to innodb (which is also held in memory if you have sufficient memory, but with additional writes to the harddisk to ensure consistency even after a crash).

[wpichler]

i do have already converted the memory table into a normal innodb table (it is no problem - it does not hold that much data - it is only heavily inserted / deleted / updated). And now i can run the backup script for the first time. It does work long - but sometimes also my database starts to block queries. I now have reworked some internals - to avoid some stored procedures. And i will try it later when i have done some more rewriting…

But the memory table was definitly a problem