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 likely. >> 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
