Hi! Inline, again.
On Jun 9, 2011, at 4:58 AM, Johan De Meersman wrote:
> True, but I have never seen an application that checks for inconsistency in it's
> tables. Making sure all users have stopped using the app ensures no in-flight
> transactions, and then you have a consistent database - save application crashes, of
> course, as you mention. MyISAM was never designed for data consistency, so it is pretty
> hard to get, indeed. The original question was asking for consistent backups, so I'm
> trying to give the best there is :-)
> Like the physics jokes go, "assume a perfectly flat surface without friction..."
Ha, I remember that one about friction :) What I meant to say about internal application
consistency checks, the applications are known to skip that, but they better have a plan
about what to do if say the application goes down. Because we have the client/server
model, it kind of implies the N:1 relationship between the applications and the database.
The inconsistency can be created by one of the N application pieces, so it is quite
>> very curious to know if there exists a product that does NOT do the
>> following at the basic technical level:
>> - FLUSH TABLES WITH READ LOCK; <-- locks all tables
>> - take backup
> You only need to FLUSH TABLES if you want the datafiles instead of an SQL dump. In
> the latter case, you can just lock the tables you will backup. A read lock will give you
> an unchanging view of the locked tables, both on MyISAM and InnoDB. On MyISAM, that read
> lock will by default prevent writes, with the exception of inserts if there are no holes
> in the table; for InnoDB a read lock wil simply give a view at the then-current SCN and
> allow further writes to simply go on.
> If the database was in a consistent state at the time of the lock, you can take a
> consistent backup at that point.
FLUSH TABLES really is a way to take the lock on all tables (with an added bonus of
flushing the stuff to disk). How else would you lock the tables, list them in the LOCK
TABLES statement? That could be a lot of tables.
About the datafiles, it is a valid point. However, the value of dumping versus copying
files goes down with the increase of data volume. If we have a large table, recovery
from a dump would mean reinserting all data, that is, redoing all the insertion work since
the application was created. This may take a while. There is also index maintenance
which could take quite a lot of time. The file copy thing will actually not work with
InnoDB at all, so ibbackup really is the only way to go about that.
>> The result will be the locks taken per table, which is equally bad
>> compared to the normal access pattern for MyISAM tables.
> Yeps. Which is why you try to
> * take a full backup of the db in a consistent state (say, once a month) and switch
> the binary logs;
> * then switch the binary logs at a point where the database is in a consistent state,
> and copy all but the newly active one.
> You can then restore the full snapshot, and be sure that at the end of each
> sequential binlog set you apply, it is again consistent.
> As I indicated, ZRM is one product which does exactly that - save for ensuring the DB
> is consistent, of course - that's up to you.
What is described here, is a simple recovery plan. That could be done relatively easy
with scripts (if it matters, that is what I did). Perhaps the ZRM does something else
that qualifies it as a product.
As we know it, taking a full backup while the database is in consistent state may lock
everybody out for a very long time. What placing a lock on the tables will do is this:
- lock tables T1, T2, T3
- wait until the table T4 is unlocked (possibly a while), then place a lock on it
- continue with the rest of tables until all are locked.
This means that if there is something big going on with T4, the lock will take quite a
long time. Meanwhile, everybody will wait. The wait will also have to continue until we
took a snapshot of all tables, of course. So it is the time of placing the lock plus the
time of taking the snapshot.
What I described before, is the procedure based on taking individual table locks, avoiding
the need to lock the whole database and experiencing the large wait.
>> If we think about it, a MyISAM table by definition is a table, the
>> consistency of which is based on whole table locks. Considering
> A single table is always consistent. Data inconsistency occurs in sets of
> interrelated tables, in other words, on the database level.
I would not be so sure about "always". If someone fires an update of every row in the
table and aborts it half way, half of the records in the table will be updated and half of
them will not be. That's something called "statement-level consistency", which also
could render the table inconsistent within itself. MyISAM table, that is.
Personally, to me the using of MyISAM tables means: I do not care about table consistency,
I am prepared to lose data, I do not expect heavy concurrent access, I do not expect
handling no-downtime operation, and so forth.