Thread: db corruption pg vs mysql

Hi. Im running one of my apps on mysql/myisam. db size: 7mil records, 5.8Gb.
We've had some corruption problems, somehow the mysqld service failed corrupted the db.

For many reasons, but for this reason alone, im considering switching to pgsql sooner
then planned. My question is... will this same problem happen in pgsql?
Is there a way to prevent or minimize corruption due to service or hardware failure?

also, will the use of Transactional queries prevent corruption, or is that a different issue?

thanks.

--
Tim H
Berkeley, CA

In response to "tim h" <timh@vyew.com>:

> Hi. Im running one of my apps on mysql/myisam. db size: 7mil records, 5.8Gb.
> We've had some corruption problems, somehow the mysqld service failed
> corrupted the db.
>
> For many reasons, but for this reason alone, im considering switching to
> pgsql sooner
> then planned. My question is... will this same problem happen in pgsql?

No.

> Is there a way to prevent or minimize corruption due to service or hardware
> failure?

The design of PostgreSQL considers your data extremely valuable.  As a result,
the system is very good about avoiding corruption, even in the event of
hardware failure.  The default configuration settings favor data
consistency.  Sometimes you will be encouraged to alter these settings for
a few % of performance improvement, but I don't recommend that you do so.

> also, will the use of Transactional queries prevent corruption, or is that a
> different issue?

Transactions prevent corruption because in the event of a hardware failure,
any half-committed transactions will be rolled back during recovery.  This
prevents the system from mangling your data.

An important thing to understand about the transaction model is that data
_loss_ may occur, but data corruption won't.

--
Bill Moran
http://www.potentialtech.com

tim h wrote:
> Hi. Im running one of my apps on mysql/myisam. db size: 7mil records, 5.8Gb.
> We've had some corruption problems, somehow the mysqld service failed
> corrupted the db.
>
> For many reasons, but for this reason alone, im considering switching to
> pgsql sooner
> then planned. My question is... will this same problem happen in pgsql?

No. PostgreSQL is ACID compliant.

> Is there a way to prevent or minimize corruption due to service or
> hardware failure?

Well sure, backups, UPS, Raid battery backup...

>
> also, will the use of Transactional queries prevent corruption, or is
> that a different issue?

Transactional queries is a different issue. Read this:

http://www.fredosaurus.com/notes-db/transactions/acid.html

This is what Pg has, that MySQL (at least with myisam) does not.

Joshua D. Drake


>
> thanks.
>
> --
> Tim H
> Berkeley, CA
>


--

       === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
              http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

On Tue, May 22, 2007 at 11:52:40AM -0700, tim h wrote:

> Is there a way to prevent or minimize corruption due to service or hardware
> failure?

This partly depends on what is causing the corruption.  If your disk
controller breaks and writes garbage all over the disk, or your
operating systems fsck is broken and moves the whole data area in to
/lost+found on reboot, there's not much PostgreSQL or any other
database system can do.  But in the event of bog-standard failures,
PostgreSQL is extremely reliable in how it handles your data.  Note
that some hard drives lie about completing fsync, and in that case
your data is indeed subject to corruption on failure.  Again, no
database can be reliable when the hardware lies about what it has
done.  (Buy better hardware, in that case :)  To the best of my
knowledge, I have never had data corruption under Postgres that
turned out to be a Postgres problem (I have had it happen from both a
bad drive controller and from bad operating systems).

> also, will the use of Transactional queries prevent corruption, or is that a
> different issue?

To the extent a COMMIT means "the data is really actually on the
disk", PostgreSQL's care with transactions helps avoid this problem.
But the bigger problem with MyISAM's lack of transactions is that
multi-statement events don't all happen at once (you've probably
heard of ACID, and this is part of it).  So you can end up in a
situation where one table had a portion of the data added, but
another table _didn't_ get the data.  In other words, your data is
inconsistent.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
The plural of anecdote is not data.
        --Roger Brinner