Jerry Stuckle wrote:
> Hi, Alex,
>
> Comments below.
>
> Axel Schwenke wrote:
> > Jerry Stuckle <jstucklex@attglobal.net> wrote:
> >
> >>Axel Schwenke wrote:
> >>
> >>>Jerry Stuckle <jstucklex@attglobal.net> wrote:
> >>>
> >>>
> >>>>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.
> >
> >
> > Agreed. But Alf worried he could lose whole tables aka files.
> >
> >
> >>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.
> >
> >
> > Sorry, I didn't express myself clear here: MyISAM never touches the
> > metadata for a data file. The file itself is created with CREATE TABLE.
> > Later on there is data appended to the file or some block inside the
> > file is modified. But the file itself stays there and there is
> > virtually no chance to lose it. So indeed there is no gain from using
> > a filesystem with metadata journaling (in fact most "journaling"
> > filesystems use the journal only for metadata).
> >
> >
> >>And it is constantly rewriting the files to disk.
> >
> > ...
> >
> >>Yes, I know exactly how MySQL works. Yep, and it has to rewrite a
> >>portion of the file to do all of this.
> >
> >
> > What do you call "rewrite"?
> >
> > Of cource MySQL writes modified data. MySQL never reads an otherwise
> > unmodified record and rewrites it somewhere else.
> >
>
> Just what you are calling it. It reads in a block of data and writes it
> back out to disk.

Note the words "otherwise unmodified" - i.e. not affected by current
operation.

>
> Even in variable length rows where the new row is longer than the old
> one and MySQL appends it to the end of the file, MySQL has to go back
> and rewrite the original row to mark it as invalid.
>
> >
> >>>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.
> >>
> >>... what happens if the row spans a disk and the
> >>system crashes between writes, for instance? ...
> >
> >
> > Agreed. But then again I don't know how *exactly* MyISAM does those
> > nonatomic writes. ...
> >
>
> Part of it is MyISAM. But part of it is the OS, also. For instance,
> what happens if the row spans two physical blocks of data which are not
> contiguous? In that case the OS has to write the first block, seek to
> the next one and write that one.
>
> There isn't anything Monty can do about that, unfortunately.
>

MyISAM doesn't claim to be transactional.

> > However, there is only very little chance to lose data that was not
> > written to at the time of the crash.
> >
>
> Actually, you would lose all data which wasn't written to the disk.

Axel means, data *already* written which is not being changed, i.e.
other records.

>
> > Dynamic vs. fixed format: Dynamic row format is susceptible to the
> > following problem: ...
> > Having the whole system crashing is also hard for "real" database
> > engines. I remember several passages in the InnoDB manual about
> > certain operating systems ignoring O_DIRECT for the tx log. Also
> > there may be "hidden" caches in disk controllers and in the disks.
> >
> Agreed it's a problem. Most databases handle this with a log/journal
> which writes directly to the file system and doesn't return until the
> record is written. Once that is done, the real data is written
> asynchronously to the tables.

Yes, but how is this relevant to MyISAM?

> ...
>
>
> >
> > 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
> ==================