MySQL ODBC Connector - power loss scenario

Author

power loss scenario

Florin Andrei

2005-03-30, 9:41 am

Again the logging server i mentioned before: it's like syslog logging
to a DB, lots of INSERTs, perhaps a few SELECTs every now and then,
the tables are append-only and are rotated about once a day.
For reasons that i am not going to discuss here, the machine has no
uninterruptible power supply. Therefore, if the power goes down, bad
things might happen to the database.
Also, i don't have money for funky solutions such as solid-state
disks. In fact, the disks will most likely be IDE (not even SCSI).

What are the techniques that work best in such a situation to increase
the chances for the database to survive a crash in a consistent state?
Loosing a few recent INSERTs is not a problem (since some data will
not be logged anyway while the server is down), but the DB in an
inconsistent state is a big problem (the system has to boot up again
unattended).

I do not want to do such extreme things like turning off the write
cache on the disk, because that would probably kill the performance.
But how about Ext3 with data=journal?
Using InnoDB would be better than MyISAM?
How about raw partitions?
Any other tips?

--
Florin Andrei

http://florin.myip.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Brent Baisley

2005-03-30, 9:41 am

Wow, you are asking a lot, especially since an inexpensive UPS could be
had for less than $50. You don't need one to keep the system up for a
long time, just long enough for writes to finish. A few minutes should
be plenty.

I don't see a problem with IDE drives. Your drive access patterns are
pretty straight forward. SCSI's big advantage is command queueing,
which may not even come into play with your access patterns.

Whatever file system you use, I would most definitely use journaling.
First and foremost you need the system in a good state, then the DB.
Journaling in the file system will also help in keeping the database
intact. Raw partitions would buy you so little performance gain, it's
really not worth the hassle. On the flip side, software mirroring of
two IDE drives would give you such a little performance hit, it would
be worth it for safety. And you'll get better read performance to boot.

InnoDB would probably be better than MyISAM since InndoDB supports
transactions.

On Mar 30, 2005, at 2:47 AM, Florin Andrei wrote:

> Again the logging server i mentioned before: it's like syslog logging
> to a DB, lots of INSERTs, perhaps a few SELECTs every now and then,
> the tables are append-only and are rotated about once a day.
> For reasons that i am not going to discuss here, the machine has no
> uninterruptible power supply. Therefore, if the power goes down, bad
> things might happen to the database.
> Also, i don't have money for funky solutions such as solid-state
> disks. In fact, the disks will most likely be IDE (not even SCSI).
>
> What are the techniques that work best in such a situation to increase
> the chances for the database to survive a crash in a consistent state?
> Loosing a few recent INSERTs is not a problem (since some data will
> not be logged anyway while the server is down), but the DB in an
> inconsistent state is a big problem (the system has to boot up again
> unattended).
>
> I do not want to do such extreme things like turning off the write
> cache on the disk, because that would probably kill the performance.
> But how about Ext3 with data=journal?
> Using InnoDB would be better than MyISAM?
> How about raw partitions?
> Any other tips?
>
> --
> Florin Andrei
>
> http://florin.myip.org/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql? unsub...over
.com
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Renato Golin

2005-03-30, 9:41 am

On Wednesday 30 March 2005 10:49, Brent Baisley wrote:
> Wow, you are asking a lot, especially since an inexpensive UPS could be
> had for less than $50. You don't need one to keep the system up for a
> long time, just long enough for writes to finish. A few minutes should
> be plenty.

Yeah, remember to put only the DB below UPS to force the logging hardware stop
before DB.

--rengolin

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Florin Andrei

2005-03-30, 7:04 pm

On Wed, 30 Mar 2005 08:49:13 -0500, Brent Baisley <brent@landover.com> wrote:
> Wow, you are asking a lot, especially since an inexpensive UPS could be
> had for less than $50. You don't need one to keep the system up for a
> long time, just long enough for writes to finish. A few minutes should
> be plenty.

I know it's weird. It's not about technical issues or money. It's just
one of those "different" situations.

> Whatever file system you use, I would most definitely use journaling.
> First and foremost you need the system in a good state, then the DB.

There are two disks in the system, one with the OS, the other with the database.
The OS drive is, i believe, almost fail-proof: the write cache is
turned off, plus it's Ext3 with data=journal. Those settings bring a
major performance hit, but that's ok on the OS drive, which is
sparsely used.
But i cannot force the same settings on the DB drive without risking
the performance to drop through the floor. Well, maybe data=journal (i
have to experiment).

> Journaling in the file system will also help in keeping the database
> intact. Raw partitions would buy you so little performance gain, it's
> really not worth the hassle.

Wouldn't raw partitions fail less often if the power is yanked, just
because there are fewer components to fail?
I mean, if the database is on top of a FS, it's the database and the
FS that can fail. On a raw partition, it's just the database.
Or am i missing something?

--
Florin Andrei

http://florin.myip.org/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Brent Baisley

2005-03-30, 7:04 pm

If the power is "yanked" a journaled file system knows exactly what it
was doing at the time of failure, what didn't finish, and can recover
from any errors caused by the failure.
A non-journaled file system would need to run a check to see if
everything is ok. This could take a long time on a big drive.

How could you even tell if something was wrong on a raw partition?
There isn't a whole lot of metadata to check for problems against like
there is in a filesystem. It's up to the application to recover from
errors.

Raw partitions used to be used for performance, not for safety.
Hardware has gotten so fast, that there really is no difference in
performance between a file system and a raw partition. hardware fails,
software has bugs.

On Mar 30, 2005, at 1:09 PM, Florin Andrei wrote:

> Wouldn't raw partitions fail less often if the power is yanked, just
> because there are fewer components to fail?
> I mean, if the database is on top of a FS, it's the database and the
> FS that can fail. On a raw partition, it's just the database.
> Or am i missing something?
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links