SEO

Hi,

is it possible that due to OS crash or mysql itself crash or some e.g.
SCSI failure to lose all the data stored in the table (let's say million
of 1KB rows). In other words what is the worst case scenario for MyISAM
backend?


Also is it possible to not to lose data but get them corrupted?


Thx, Andy

>is it possible that due to OS crash or mysql itself crash or some e.g.
>SCSI failure to lose all the data stored in the table (let's say million
>of 1KB rows).

It is always possible that your computer system will catch fire and
lose all data EVEN IF IT'S POWERED OFF. And the same nuclear attack
might take up all your backups, too. And you and all your employees.
Or the whole thing could just be stolen.

Managing to smash just one sector, the sector containing the data
file inode, or worse, the sector containing the data file, index
file, AND table definition inodes, could pretty well kill a table.
I have had the experience of a hard disk controller that sometimes
flipped some bits in the sectors before writing them. It took weeks
to discover this.

>In other words what is the worst case scenario for MyISAM
>backend?

Probably, total loss of data and hardware.

>Also is it possible to not to lose data but get them corrupted?

I call that 'lost'. But yes, it is possible to end up with a bunch
of data that's bad and you don't realize it until things have gotten
much worse.

Gordon Burditt wrote:
>>is it possible that due to OS crash or mysql itself crash or some e.g.
>>SCSI failure to lose all the data stored in the table (let's say million
>>of 1KB rows).
>
> Managing to smash just one sector, the sector containing the data
> file inode, or worse, the sector containing the data file, index
> file, AND table definition inodes, could pretty well kill a table.
> I have had the experience of a hard disk controller that sometimes
> flipped some bits in the sectors before writing them. It took weeks
> to discover this.
>
>
>>In other words what is the worst case scenario for MyISAM
>>backend?
>
>
> Probably, total loss of data and hardware.
>

well, let's narrow it down to the mysql bug causing it to crash. Or
better to the all situations where trx's capabilities of InnoDB can
easily take care of a recovery (to the last committed trx).

I wonder if there is a possibility due to internal structure of MyISAM
backend to lose entire table where even recovery tools give up.

Would using ext3 help?


Thx in advance, Andy

alf wrote:
> Gordon Burditt wrote:
>
>>> is it possible that due to OS crash or mysql itself crash or some e.g.
>>> SCSI failure to lose all the data stored in the table (let's say million
>>> of 1KB rows).
>>
>>
>> Managing to smash just one sector, the sector containing the data
>> file inode, or worse, the sector containing the data file, index
>> file, AND table definition inodes, could pretty well kill a table.
>> I have had the experience of a hard disk controller that sometimes
>> flipped some bits in the sectors before writing them. It took weeks
>> to discover this.
>>
>>
>>> In other words what is the worst case scenario for MyISAM
>>> backend?
>>
>>
>>
>> Probably, total loss of data and hardware.
>>
>
> well, let's narrow it down to the mysql bug causing it to crash. Or
> better to the all situations where trx's capabilities of InnoDB can
> easily take care of a recovery (to the last committed trx).
>
> I wonder if there is a possibility due to internal structure of MyISAM
> backend to lose entire table where even recovery tools give up.
>
> Would using ext3 help?
>
>
> Thx in advance, Andy

As Gordon said - anything's possible.

I don't see why ext3 would help. It knows nothing about the internal
format of the tables, and that's what is most likely to get screwed up
in a database crash. I would think it would be almost impossible to
recover to a consistent point in the database unless you have a very
detailed knowledge of the internal format of the files. And even then
it might be impossible if your system is very busy.

The best strategy is to keep regular backups of the database.

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

Gordon Burditt wrote:
> >is it possible that due to OS crash or mysql itself crash or some e.g.
> >SCSI failure to lose all the data stored in the table (let's say million
> >of 1KB rows).
>
> It is always possible that your computer system will catch fire and
> lose all data EVEN IF IT'S POWERED OFF. And the same nuclear attack
> might take up all your backups, too. And you and all your employees.
> Or the whole thing could just be stolen.
>
> Managing to smash just one sector, the sector containing the data
> file inode, or worse, the sector containing the data file, index
> file, AND table definition inodes, could pretty well kill a table.
> I have had the experience of a hard disk controller that sometimes
> flipped some bits in the sectors before writing them. It took weeks
> to discover this.

I spent weeks on a similar problem too - turned out to be bad RAM. The
only filesystem that I know of which can handle such hardware failures
is Sun's ZFS:
http://blogs.sun.com/bonwick/entry/zfs_end_to_end_data

>
> >In other words what is the worst case scenario for MyISAM
> >backend?
>
> Probably, total loss of data and hardware.
>
> >Also is it possible to not to lose data but get them corrupted?
>
> I call that 'lost'. But yes, it is possible to end up with a bunch
> of data that's bad and you don't realize it until things have gotten
> much worse.

Jerry Stuckle wrote:

> I don't see why ext3 would help.


only to not to get the file system corrupted.



> It knows nothing about the internal
> format of the tables, and that's what is most likely to get screwed up
> in a database crash. I would think it would be almost impossible to
> recover to a consistent point in the database unless you have a very
> detailed knowledge of the internal format of the files.


Well, mysql recovery procedures does have that knowledge. There are
different levels of disaster. My assumption is that the file system
survives.


>
> The best strategy is to keep regular backups of the database.
>

in my case it is a bit different. There are millions of rows which get
inserted, live for a few minutes or hours and then they get deleted. the
backup is not even feasible. While I can afford some (1-5%) data loss
due to crash, I still must not lose entire table. Wonder if mysql
recovery procedures can ensure that.

--
alf

alf wrote:
> Jerry Stuckle wrote:
>
>> I don't see why ext3 would help.
>
>
>
> only to not to get the file system corrupted.
>
>

That doesn't mean the tables themselves can't be corrupted. For instance
if MySQL crashes in the middle of large write operation. Nothing the
file system can do to prevent that from happening. And you would have
to know exactly where to stop the file system restore to recover the
data - which would require a good knowledge of MySQL table structure.

>
>> It knows nothing about the internal format of the tables, and that's
>> what is most likely to get screwed up in a database crash. I would
>> think it would be almost impossible to recover to a consistent point
>> in the database unless you have a very detailed knowledge of the
>> internal format of the files.
>
>
>
> Well, mysql recovery procedures does have that knowledge. There are
> different levels of disaster. My assumption is that the file system
> survives.
>

Yes, it does. That's its job, after all. But if the tables themselves
are corrupted, nothing the file system will do will help that. And if
MySQL can't recover the data because of this, which file system you use
doesn't make any difference.
>
>>
>> The best strategy is to keep regular backups of the database.
>>
>
> in my case it is a bit different. There are millions of rows which get
> inserted, live for a few minutes or hours and then they get deleted. the
> backup is not even feasible. While I can afford some (1-5%) data loss
> due to crash, I still must not lose entire table. Wonder if mysql
> recovery procedures can ensure that.
>

Backups are ALWAYS feasible. And critical if you want to keep your data
safe. There is no replacement.

You can get some help by using INNODB tables and enabling the binary
log. That will allow MySQL to recover from the last good backup by
rolling the logs forward. There should be little or no loss of data.

But you still need the backups. There's no way to feasibly roll forward
a year's worth of data, for instance.

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

Jerry Stuckle wrote:

> That doesn't mean the tables themselves can't be corrupted. For instance
> if MySQL crashes in the middle of large write operation. Nothing the
> file system can do to prevent that from happening. And you would have
> to know exactly where to stop the file system restore to recover the
> data - which would require a good knowledge of MySQL table structure.

I understand that.


> Yes, it does. That's its job, after all. But if the tables themselves
> are corrupted, nothing the file system will do will help that. And if
> MySQL can't recover the data because of this, which file system you use
> doesn't make any difference.

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.


>
> Backups are ALWAYS feasible. And critical if you want to keep your data
> safe. There is no replacement.

In my case backups get outdated every minute or so. There is a lot of
data coming into DB and leaving it. Also losing the data from last
minute or so is not as critical (as opposed to banking systems).
Critical is losing like 5%. I know the system is just different.


> You can get some help by using INNODB tables and enabling the binary
> log. That will allow MySQL to recover from the last good backup by
> rolling the logs forward. There should be little or no loss of data.


For some other reasons INNODB is not an option. My job is to find out
if crashing the mysql or the actual hardware the mysql is running on can
lead that significant amount of data (more then 5%) is lost. From what
I understand from here it is.

Thx a lot, A.

alf wrote:
> Jerry Stuckle wrote:
>
>> That doesn't mean the tables themselves can't be corrupted. For
>> instance if MySQL crashes in the middle of large write operation.
>> Nothing the file system can do to prevent that from happening. And
>> you would have to know exactly where to stop the file system restore
>> to recover the data - which would require a good knowledge of MySQL
>> table structure.
>
>
> I understand that.
>
>
>> Yes, it does. That's its job, after all. But if the tables
>> themselves are corrupted, nothing the file system will do will help
>> that. And if MySQL can't recover the data because of this, which file
>> system you use doesn't make any difference.
>
>
> 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?

>
>>
>> Backups are ALWAYS feasible. And critical if you want to keep your
>> data safe. There is no replacement.
>
>
> In my case backups get outdated every minute or so. There is a lot of
> data coming into DB and leaving it. Also losing the data from last
> minute or so is not as critical (as opposed to banking systems).
> Critical is losing like 5%. I know the system is just different.
>

Without backups or logs/journals, I don't think ANY RDB can provide the
recovery you want.

>
>> You can get some help by using INNODB tables and enabling the binary
>> log. That will allow MySQL to recover from the last good backup by
>> rolling the logs forward. There should be little or no loss of data.
>
>
>
> For some other reasons INNODB is not an option. My job is to find out if
> crashing the mysql or the actual hardware the mysql is running on can
> lead that significant amount of data (more then 5%) is lost. From what
> I understand from here it is.
>
> Thx a lot, A.
>
>

You have a problem. The file system will be able to recover a file, but
it won't be able to fix a corrupted file. And without backups and
logs/journals, neither MySQL nor any other RDB will be able to guarantee
even 1% recovery - much less 95%.

Let's say MySQL starts to completely rewrite a 100Mb table. 10 bytes
into it, MySQL crashes. Your file system will see a 10 byte file and
recover that much. The other 99.99999MB will be lost. And without a
backup and binary logs, MySQL will not be able to recover.

Sure, you might be able to roll forward the file system journal. But
you'll have to know *exactly* where to stop or your database will be
inconsistent. And even if you do figure out *exactly* where to stop,
the database may still not be consistent.

You have the wrong answer to your problem. The RDB must do the
logging/journaling. For MySQL that means INNODB. MSSQL, Oracle, DB2,
etc. all have their versions of logging/journaling, also. And they
still require a backup to start.

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

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.

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.

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.

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.

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.

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.

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.


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/