Jerry Stuckle wrote:
> toby wrote:
> > Jerry Stuckle wrote:
> >
> >>toby wrote:
> >>
> >>>Jerry Stuckle wrote:
> >>>
> >>>
> >>>>Hi, Alex,
> >>>>
> >>>>Comments below.
> >>>>
> >>>>Axel Schwenke wrote:
> >>>>
> >>>>
> >>>>>Jerry Stuckle <jstucklex@attglobal.net> wrote:
> >>>>>
> >>>>>
> >>>>>
> >>>>>>Axel Schwenke wrote:
> >>>>>>... 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.
> >>>
> >>
> >>Depends on your definition of "otherwise unmodified". That sounds like
> >>something different than "unmodified", doesn't it? "Otherwise
> >>unmodified" indicates *something* has changed.
> >>
> >>Now - if you just say "MySQL never reads an unmodified record and
> >>rewrites it somewhere else", I will agree.
> >
> >
> > I think that's exactly what Axel meant, yes.
> >
> >
> >>>>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.
> >>>
> >>
> >>Nope, and I never said it did. But this has nothing to do with
> >>transactions. It has to do with a single row - or even a single column
> >>in one row - being corrupted.
> >>
> >>Transactional has to do with multiple operations (generally including
> >>modification of the data) in which all or none must complete. That's
> >>not the case here.
> >
> >
> > The problem you describe is solved by transactional engines.
> >
>
> Yes, it is solved by by "transactional engines". But you don't
> necessarily need to explicitly use transactions for it. For instance,
> INNODB can protect against that, even if you are using autocommit
> (effectively otherwise negating transactional operations).

An Autocommited statement is no different from any other transaction,
so it benefits from the same machinery, yes.

>
> >
> >>>>>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.
> >>>
> >>
> >>Could be. But that's not what he said. He said "not written to...".
> >>
> >>Now - if he means data which was not overwritten (or in the progress of
> >>being overwritten), then I will agree.
> >
> >
> > Again, I think that's what he meant.
> >
>
> It could be. I can only go by what he said. And sometimes English is
> not the best language, especially when discussing technical topics.

You apparently had more trouble deciphering his intended meaning than I
did.

>
> >
> >>>>>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?
> >>>
> >>
> >>It goes back to the crux of the original poster's problem. He wants to
> >>use an access method which is not crash-safe and is trying to ensure the
> >>integrity of his data - or at least a major portion of it.
> >
> >
> > I guess you/Axel have covered some of the points where this just isn't
> > possible. OP really ought to consider a different engine, no?
> >
>
> I agree completely.
>
> Of course, with the additional integrity comes additional overhead.
> TANSTAAFL.

Well, each of the engines has a different sweet spot (BDB, Solid, PBXT,
Falcon) and we don't even know if the OP has a performance problem. I
think he only mentioned an integrity problem?

>
> >
> >>>>...
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>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
> >>==================
> >
> >
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================