Axel Schwenke wrote:
> Jerry Stuckle <jstucklex@attglobal.net> wrote:
>
>>alf wrote:
>>
>>>Not sure I agree. ext3 enables a quick recovery because there is a
>>>trxlog of the file system itself. In ext2 you can lose files. So there
>>>is a small step froward.
>>
>>So? If the file itself is corrupted, all it will do is recover a
>>corrupted file. What's the gain there?
>
>
> The gain is, that you have a chance to recover at all. With no files,
> there is *no* way to recover.
>

What you don't get it that it's not the presence or absence of the files
- it's the CONTENTS of the files that matters. There is very little
chance you will lose the files completely in the case of a crash. There
is a much bigger (although admittedly still small) that the files will
be corrupted. And a huge chance if you have more than one table your
database will be inconsistent.

> However, thats not a real problem. MySQL never touches the datafile
> itself once it is created. Only exception: REPAIR TABLE. This will
> recreate the datafile (as new file with extension .TMD) and then
> rename files.
>

Excuse me? MySQL ALWAYS touches the data file. That's where the
information is stored! And it is constantly rewriting the files to disk.

> DELETE just marks a record as deleted (1 bit). INSERT writes a new
> record at the end of the datafile (or into a hole, if one exists).
> UPDATE is done either in place or as INSERT + DELETE.
>

Yes, I know exactly how MySQL works. Yep, and it has to rewrite a
portion of the file to do all of this.

> Most file operations on MyISAM tables are easier, faster and less
> risky, if the table uses fixed length records. Then there is no need to
> collapse adjacent unused records into one, UPDATE can be done in place,
> there will be no fragmentation and such.
>

No, no fragmentation. But what happens if the row spans a disk and the
system crashes between writes, for instance? Depending on exactly where
the block was split, you could completely screw up that row, bug be very
difficult to detect. Sure, it's only one row. But data corruption like
this can be much worse than just losing a row. The latter is easier to
determine.

> The MyISAM engine is quite simple. Data and index are held in separate
> files. Data is structured in records. Whenever a record is modified,
> it's written to disk immediately (however the operation system might
> cache this). MyISAM never touches records without need. So if mysqld
> goes down while in normal operation, only those records can be damaged
> that were in use by active UPDATE, DELETE or INSERT operations.
>

But the caching is all too important. It's not unusual to have hundreds
of MB of disk cache in a busy system. That's a lot of data which can be
lost.

> There are two exceptions: REPAIR TABLE and OPTIMIZE TABLE. Both
> recreate the datafile with new name and then switch by renaming.
> There is still no chance to lose *both* files.
>

True - but these are so seldom used it's almost not worth talking about.
And even then it's a good idea to backup the database before repairing
or optimizing it.

> Indexes are different, though. Indexes are organized in pages and
> heavily cached. You can even instruct mysqld to never flush modified
> index pages to disk (except at shutdown or cache restructuring).
> However indexes can be rebuilt from scratch, without losing data.
> The only thing lost is the time needed for recovery.
>

True. But that's not a big concern, is it?

>
> HTH, XL
> --
> Axel Schwenke, Senior Software Developer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================