Thread: Is Diskeeper Automatic Mode safe?

I've got a pair of servers running PostgreSQL 8.0.4 on Windows. We
have several tables that add and delete massive amounts of data in a
single day and are increasingly having a problem with drive
fragmentation and it appears to be giving us a decent performance hit.
This is external fragmentation we are dealing with. We already vacuum
the tables on a regular basis to reduce internal fragmentation as best
as possible.

Currently I shut down the PostgreSQL service every few weeks and
manually run a defragment of the drive, but this is getting tedious.
Diskeeper has an Automatic Mode that runs in the background all the
time to handle this for me. They advertise they are compatible with MS
SQL server, but don't appear to have any specific info on PostgreSQL.

I'm curious if anyone else has used Diskeeper's Automatic Mode in
combination with PostgreSQL to defrag and keep the drive defragged
while PostgreSQL is actually running.

Thanks!

-chris
<www.mythtech.net>

On Mon, Nov 16, 2009 at 12:14 PM, cb <cb@mythtech.net> wrote:
> I've got a pair of servers running PostgreSQL 8.0.4 on Windows. We have
> several tables that add and delete massive amounts of data in a single day
> and are increasingly having a problem with drive fragmentation and it
> appears to be giving us a decent performance hit. This is external
> fragmentation we are dealing with. We already vacuum the tables on a regular
> basis to reduce internal fragmentation as best as possible.
>
> Currently I shut down the PostgreSQL service every few weeks and manually
> run a defragment of the drive, but this is getting tedious. Diskeeper has an
> Automatic Mode that runs in the background all the time to handle this for
> me. They advertise they are compatible with MS SQL server, but don't appear
> to have any specific info on PostgreSQL.
>
> I'm curious if anyone else has used Diskeeper's Automatic Mode in
> combination with PostgreSQL to defrag and keep the drive defragged while
> PostgreSQL is actually running.
>
> Thanks!
>
> -chris
> <www.mythtech.net>

I'm not sure what the answer is to your actual question, but I'd
highly recommend upgrading to 8.3 or 8.4.  The performance is likely
to be a lot better, and 8.0/8.1 are no longer supported on Windows.

...Robert

cb wrote:
> I'm curious if anyone else has used Diskeeper's Automatic Mode in
> combination with PostgreSQL to defrag and keep the drive defragged
> while PostgreSQL is actually running.
>
> Thanks!
>
> -chris
> <www.mythtech.net>
>

I've been a Diskeeper customer for about 10 years now and consider it
'must have' software for Windows machines.  I do not work for them nor
get paid by them, I just find the software incredibly valuable.  I'm
running XP-64bit with 8.4.0 and Diskeeper does a wonderful job of
defragmenting the database tables when they get fragmented.  I just
checked their website and the 2009 version is still listed.  I've been
running the 2010  Enterprise Server version for about a week and I can
tell you that it's great!  (I'm actually running it on 3 servers but
only mine has PG)  The main difference with the 2010 version is
something that they call IntelliWrite.  As everyone knows, one of the
biggest problems with the Windows OS is that it lets fragmentation occur
in the first place.  This new IntelliWrite actually prevents the
fragmentation from occurring in the first place (or at least the vast
majority of it).  The auto defrag takes care of the rest.  I can attest
to this actually working in real life scenarios.  The other thing
Diskeeper has is something they call I-FAAST.  What this does is monitor
file usage and moves the most heavily accessed files to the fastest part
of the drive.  My db is on an Adaptec 52445 with 16 ST373455SS (15K5) in
RAID5 and Diskeeper defrags and moves pretty much everything in
\data\base to the outer part of the drive.  So the short answer is yes,
I have it running with PostgreSQL and have not had any problems.


Bob

On Mon, Nov 16, 2009 at 1:11 PM, Robert Schnabel <schnabelr@missouri.edu> wrote:
> cb wrote:
>>
>> I'm curious if anyone else has used Diskeeper's Automatic Mode in
>>  combination with PostgreSQL to defrag and keep the drive defragged  while
>> PostgreSQL is actually running.
>>
>> Thanks!
>>
>> -chris
>> <www.mythtech.net>
>>
>
> I've been a Diskeeper customer for about 10 years now and consider it 'must
> have' software for Windows machines.  I do not work for them nor get paid by
> them, I just find the software incredibly valuable.  I'm running XP-64bit
> with 8.4.0 and Diskeeper does a wonderful job of defragmenting the database
> tables when they get fragmented.  I just checked their website and the 2009
> version is still listed.  I've been running the 2010  Enterprise Server
> version for about a week and I can tell you that it's great!  (I'm actually
> running it on 3 servers but only mine has PG)  The main difference with the
> 2010 version is something that they call IntelliWrite.  As everyone knows,
> one of the biggest problems with the Windows OS is that it lets
> fragmentation occur in the first place.  This new IntelliWrite actually
> prevents the fragmentation from occurring in the first place (or at least
> the vast majority of it).  The auto defrag takes care of the rest.  I can
> attest to this actually working in real life scenarios.  The other thing
> Diskeeper has is something they call I-FAAST.  What this does is monitor
> file usage and moves the most heavily accessed files to the fastest part of
> the drive.  My db is on an Adaptec 52445 with 16 ST373455SS (15K5) in RAID5
> and Diskeeper defrags and moves pretty much everything in \data\base to the
> outer part of the drive.  So the short answer is yes, I have it running with
> PostgreSQL and have not had any problems.

Have you unplugged the power cord a few times in the middle of heavy
write activity?

...Robert

<br /><blockquote cite="mid:603c8f070911161142j6dd18fb3w4fbb72fa12d01a41@mail.gmail.com" type="cite"><blockquote
type="cite"><prewrap=""> So the short answer is yes, I have it running with
 
PostgreSQL and have not had any problems.   </pre></blockquote><pre wrap="">
Have you unplugged the power cord a few times in the middle of heavy
write activity?

...Robert</pre></blockquote> Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along with a BBU
controller.<br/><br /> Bob<br /><br /> 

On Mon, Nov 16, 2009 at 1:04 PM, Robert Schnabel <schnabelr@missouri.edu> wrote:
>
>  So the short answer is yes, I have it running with
> PostgreSQL and have not had any problems.
>
>
> Have you unplugged the power cord a few times in the middle of heavy
> write activity?
>
> ...Robert
>
> Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along
> with a BBU controller.

BBU controller, yes.  UPS, no.  I've seen more than one multi-million
dollar hosting center go down from something as simple as a piece of
wire flying into a power conditioner, shorting it out, and feeding
back and blowing every single power conditioner and UPS AND the switch
that allowed the diesel to come into the loop.  All failed.  Every
machine lost power.  One database server out of a few dozens came back
up.  In fact there were a lot of different dbm systems running in that
center, and only the pg 7.2 version came back up unscathed.

Because someone insisted on pulling the plug out from the back a dozen
or so times to make sure it would do come back up.  PG saved our
shorts and the asses they contain.  Sad thing is I'm sure the other
servers COULD have come back up if they had been running proper BBUs
and hard drives that didn't lie about fsync, and an OS that enforced
fsync properly, at least for scsi, at the time.

Power supplies / UPSes fail far more often than one might think.  And
a db that doesn't come back up afterwards is not to be placed into
production.

On Mon, Nov 16, 2009 at 1:12 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Power supplies / UPSes fail far more often than one might think.  And
> a db that doesn't come back up afterwards is not to be placed into
> production.

Note that there are uses for databases that can lose everything and
just initdb and be happy.  Session databases are like that.   But I'm
talking persistent databases.

Robert Schnabel wrote:

Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along with a BBU controller.

If you have a UPS *and* a BBU controller, then things are much better--those should have a write cache that insulates you from the worst of the problems.  But just a UPS alone doesn't help you very much:

1) A UPS is built with a consumable (the battery), and they do wear out.  Unless you're proactive about monitoring UPS battery quality and doing tests, you won't find this out until the first time the power goes out and the UPS doesn't work anymore.
2) Do you trust that the UPS integration software will *always* shut the server down before the power goes out?  You shouldn't.
3) Ever had someone trip over the cord between the UPS and the server?  How about accidentally unplugging the wrong server?  These things happen; do you want data corruption when they do?
4) There are all sorts of major electrical problems you can run into (around here it's mainly summer lightening) that will blow out a UPS without giving an opportunity for graceful shutdown.

If there's anyone who thinks a UPS is all you need to be safe from power issues, I know a guy named Murphy you should get introduced to.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

Greg Smith wrote:

Robert Schnabel wrote:

Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along with a BBU controller.

If you have a UPS *and* a BBU controller, then things are much better--those should have a write cache that insulates you from the worst of the problems.  But just a UPS alone doesn't help you very much:

A UPS is just a controlled shutdown device for when things are working and mains power goes off.

Note the "when things are working" qualifier. :)

-- Karl

My reply about server failure was shwoing what could go wrong at the server level assuming a first-class, properly run data center, with fully redundant power, including a server with dual power supplies on separate cords fed by separate UPS'es etc. ....

Unfortunately, correctly configured A/B power is all too rare these days. Some examples of foo that I've seen at professional data centers:

- Allegedly "A/B" power supplied from two phases of the same UPS (which was then taken down due to a tech's error during "hot" maintenance)
- "A/B" power fed through a common switch panel
- A/B power with dual attached servers, with each power feed running a steady 60% load (do the math!)

A classic piece of foo from a manufacturer - Dell supplies their low end dual-power rackmount boxes with a Y shaped IEC cable ... clearly, this is only suitable for non-redundant use but I've seen plenty of them deployed in data centers by less-than-clueful admins.


On Mon, Nov 16, 2009 at 2:12 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Nov 16, 2009 at 1:04 PM, Robert Schnabel <schnabelr@missouri.edu> wrote:
>>
>>  So the short answer is yes, I have it running with
>> PostgreSQL and have not had any problems.
>>
>>
>> Have you unplugged the power cord a few times in the middle of heavy
>> write activity?
>>
>> ...Robert
>>
>> Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along
>> with a BBU controller.
>
> BBU controller, yes.  UPS, no.  I've seen more than one multi-million
> dollar hosting center go down from something as simple as a piece of
> wire flying into a power conditioner, shorting it out, and feeding
> back and blowing every single power conditioner and UPS AND the switch
> that allowed the diesel to come into the loop.  All failed.  Every
> machine lost power.  One database server out of a few dozens came back
> up.  In fact there were a lot of different dbm systems running in that
> center, and only the pg 7.2 version came back up unscathed.
>
> Because someone insisted on pulling the plug out from the back a dozen
> or so times to make sure it would do come back up.  PG saved our
> shorts and the asses they contain.  Sad thing is I'm sure the other
> servers COULD have come back up if they had been running proper BBUs
> and hard drives that didn't lie about fsync, and an OS that enforced
> fsync properly, at least for scsi, at the time.
>
> Power supplies / UPSes fail far more often than one might think.  And
> a db that doesn't come back up afterwards is not to be placed into
> production.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

<br /> Scott Marlowe wrote: <blockquote cite="mid:dcc563d10911161212w3ac11a05l39b32a6d7b691fb9@mail.gmail.com"
type="cite"><prewrap="">On Mon, Nov 16, 2009 at 1:04 PM, Robert Schnabel <a class="moz-txt-link-rfc2396E"
href="mailto:schnabelr@missouri.edu"><schnabelr@missouri.edu></a>wrote: </pre><blockquote type="cite"><pre
wrap=""> Sothe short answer is yes, I have it running with
 
PostgreSQL and have not had any problems.


Have you unplugged the power cord a few times in the middle of heavy
write activity?

...Robert

Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?  Along
with a BBU controller.   </pre></blockquote><pre wrap="">
BBU controller, yes.  UPS, no.  I've seen more than one multi-million
dollar hosting center go down from something as simple as a piece of
wire flying into a power conditioner, shorting it out, and feeding
back and blowing every single power conditioner and UPS AND the switch
that allowed the diesel to come into the loop.  All failed.  Every
machine lost power.  One database server out of a few dozens came back
up.  In fact there were a lot of different dbm systems running in that
center, and only the pg 7.2 version came back up unscathed.

Because someone insisted on pulling the plug out from the back a dozen
or so times to make sure it would do come back up.  PG saved our
shorts and the asses they contain.  Sad thing is I'm sure the other
servers COULD have come back up if they had been running proper BBUs
and hard drives that didn't lie about fsync, and an OS that enforced
fsync properly, at least for scsi, at the time.

Power supplies / UPSes fail far more often than one might think.  And
a db that doesn't come back up afterwards is not to be placed into
production. </pre></blockquote> Ok, so you have sufficiently sparked my curiosity as to whether Diskeeper will in any
waycause Postgres to fail the power chord test.  Unfortunately I have some deadlines to meet so won't be able to test
thisout until later in the week.  I'm in the fortunate position that the only person that uses my db is me myself and I
soI can control what and when it does work.  I also have backup software running that does complete drive imaging so I
shouldbe able to do this fairly safely.  Here is the plan...<br /><br /> 1) Shut down the Diskeeper service, run a
querythat is write heavy and then pull the chord on the box.  Wait a few minutes then plug it back in and see if it
recovers.<br/> 2) Leave Diskeeper running and repeat the above...<br /><br /> Comments/suggestions?  If I'm going to do
thisI'd like to make sure I do it correctly so it will be useful for the group.<br /><br /> I'm using XP 64 bit,
Adaptec52445 + BBU, I have two external drive enclosures (8 each) plus the 8 in the box, pg 8.4.0<br /><br /> Bob<br
/><br/><br /><br /> 

Dave Crooke wrote:
> My reply about server failure was shwoing what could go wrong at the
> server level assuming a first-class, properly run data center, with
> fully redundant power, including a server with dual power supplies on
> separate cords fed by separate UPS'es etc. ....
Never had a motherboard short out either eh?  China makes really GOOD
electrolytic caps these days (I can show you several SERVER CLASS boards
that were on conditioned power and popped 'em, rendering the board dead
instantly.)

Murphy is a bastard.

-- Karl

On Mon, Nov 16, 2009 at 1:32 PM, Robert Schnabel <schnabelr@missouri.edu> wrote:
>
> Ok, so you have sufficiently sparked my curiosity as to whether Diskeeper
> will in any way cause Postgres to fail the power chord test.  Unfortunately
> I have some deadlines to meet so won't be able to test this out until later

Best time is during acceptance testing before deployment.    Failing
that testing it in production on the backup server so you can burn it
to the ground and rebuild it on a saturday.

Note that surviving the power plug being pulled doesn't PROVE your
system will always do that.  You can try to simulate the real mix of
load and even replay queries when pulling the plug, only to find the
one corner case you didnt' test in production when power is lost.  The
power cord plug can prove a system bad, but you're still somewhat
"hoping" it's really good, with a high probability of being right.

Which is why backup is so important.

On Mon, Nov 16, 2009 at 1:32 PM, Karl Denninger <karl@denninger.net> wrote:
> Dave Crooke wrote:
>> My reply about server failure was shwoing what could go wrong at the
>> server level assuming a first-class, properly run data center, with
>> fully redundant power, including a server with dual power supplies on
>> separate cords fed by separate UPS'es etc. ....
> Never had a motherboard short out either eh?  China makes really GOOD
> electrolytic caps these days (I can show you several SERVER CLASS boards
> that were on conditioned power and popped 'em, rendering the board dead
> instantly.)
>
> Murphy is a bastard.

You know about the whole capacitor caper from a few years back, where
this one plant was making corrosive electrolyte and a huge number of
capacitor suppliers were buying from them.  Mobos from that era are
terrible.  Caps that expand and burst after anywhere from a few months
to a few years of use.  ugh.

Scott Marlowe wrote: <blockquote cite="mid:dcc563d10911161250o766faabfyd51dcb6b88ed996@mail.gmail.com"
type="cite"><prewrap="">On Mon, Nov 16, 2009 at 1:32 PM, Robert Schnabel <a class="moz-txt-link-rfc2396E"
href="mailto:schnabelr@missouri.edu"><schnabelr@missouri.edu></a>wrote: </pre><blockquote type="cite"><pre
wrap="">Ok,so you have sufficiently sparked my curiosity as to whether Diskeeper
 
will in any way cause Postgres to fail the power chord test.  Unfortunately
I have some deadlines to meet so won't be able to test this out until later   </pre></blockquote><pre wrap="">
Best time is during acceptance testing before deployment.    Failing
that testing it in production on the backup server so you can burn it
to the ground and rebuild it on a saturday.

Note that surviving the power plug being pulled doesn't PROVE your
system will always do that.  You can try to simulate the real mix of
load and even replay queries when pulling the plug, only to find the
one corner case you didnt' test in production when power is lost.  The
power cord plug can prove a system bad, but you're still somewhat
"hoping" it's really good, with a high probability of being right.

Which is why backup is so important. </pre></blockquote> Granted, but the point of me testing this is to say whether or
notthe Diskeeper service could introduce a problem.  If the system recovers without Diskeeper running but does not
recoverwhile Diskeeper is actively running then we have a problem.  If they both recover then I've answered the
question"Have you unplugged the power cord a few times in the middle of heavy write activity?"  I understand that we
can'tprove that it works but I should be able to at least answer the question asked.<br /><br /> I wouldn't consider my
databasea production one.  I basically use it to store a large amount of genetic data for my lab.  The only time the
databasegets use is when I use it.  Short of frying a piece of hardware by pulling the plug I'm not worried about
losingany data and rebuilding is actually quite a simple process that only takes about 2 hours... been there done that
whenI pulled the wrong SAS connector.<br /><br /> Bob<br /><br /> 

> I also have backup software running that does
> complete drive imaging so I should be able to do this fairly safely.
> Here is the plan...
>
> 1) Shut down the Diskeeper service, run a query that is write heavy and
> then pull the chord on the box.  Wait a few minutes then plug it back in
> and see if it recovers.
> 2) Leave Diskeeper running and repeat the above...
>
> Comments/suggestions?  If I'm going to do this I'd like to make sure I
> do it correctly so it will be useful for the group.

Do it more than once.  This is a highly erratic test that can catch your system at a wide variety of points, some of
whichcause no problems, and some of which can be catastrophic.  If you test and it fails, you know you have a problem.
Ifyou test and it doesn't fail, you don't know much.  It's only when you've tested a number of times without failure
thatyou've gained any real knowledge. 

Craig

Craig James escribió:

> Do it more than once.  This is a highly erratic test that can catch
> your system at a wide variety of points, some of which cause no
> problems, and some of which can be catastrophic.  If you test and it
> fails, you know you have a problem.  If you test and it doesn't fail,
> you don't know much.  It's only when you've tested a number of times
> without failure that you've gained any real knowledge.

Of course, you're only truly safe when you've tested infinite times,
which may take a bit longer than management expects.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Nov 16, 2009 at 03:20:12PM -0500, Greg Smith wrote:
> Robert Schnabel wrote:
> >Nope.  Forgive my ignorance but isn't that what a UPS is for anyway?
> >Along with a BBU controller.
>
> If you have a UPS *and* a BBU controller, then things are much
> better--those should have a write cache that insulates you from the
> worst of the problems.  But just a UPS alone doesn't help you very much:
>
> 1) A UPS is built with a consumable (the battery), and they do wear
> out.  Unless you're proactive about monitoring UPS battery quality and
> doing tests, you won't find this out until the first time the power goes
> out and the UPS doesn't work anymore.

Well the bbu is just another battery (ok some are capacitors but...)
so the same caveats apply for a bbu raid card. We test ours every 6
months and fail them if they are less than a 5 day capacity (failure
over a long weekend 3 days + 1-2 day(s) to fix the issue (replace
power supply, mobo etc.)).

--
                -- rouilj

John Rouillard       System Administrator
Renesys Corporation  603-244-9084 (cell)  603-643-9300 x 111

On Mon, Nov 16, 2009 at 2:04 PM, Robert Schnabel <schnabelr@missouri.edu> wrote:

> Granted, but the point of me testing this is to say whether or not the
> Diskeeper service could introduce a problem.  If the system recovers without
> Diskeeper running but does not recover while Diskeeper is actively running
> then we have a problem.  If they both recover then I've answered the
> question "Have you unplugged the power cord a few times in the middle of
> heavy write activity?"  I understand that we can't prove that it works but I
> should be able to at least answer the question asked.
>
> I wouldn't consider my database a production one.  I basically use it to
> store a large amount of genetic data for my lab.  The only time the database
> gets use is when I use it.  Short of frying a piece of hardware by pulling
> the plug I'm not worried about losing any data and rebuilding is actually
> quite a simple process that only takes about 2 hours... been there done that
> when I pulled the wrong SAS connector.

Be careful, it's not uncommon for a database / app to suddenly become
popular and people start expecting it to be up all the time.  For
things like a corporate intranet, losing a few hours work from
something like a power loss is acceptable.

We have four types of dbs where I work.  session servers can be
configured to have fsync off and don't have to be ultra reliable under
things like power loss.  Search database which gets recreated every
few days as the indexer runs.  Stats database where reliability is
sorta important but not life or death, and the user data database
which has to work and stay up.  So each one is tested differently
because each one would have a much different impact if they crash and
can't come back up without help.

On Nov 16, 2009, at 1:09 PM, Robert Haas wrote:

> I'm not sure what the answer is to your actual question, but I'd
> highly recommend upgrading to 8.3 or 8.4.  The performance is likely
> to be a lot better, and 8.0/8.1 are no longer supported on Windows.


Ugh, yeah, I'd love to upgrade but the powers that get to make that
decision have no interest in upgrading. So I'm stuck on 8.0.4, and
since I really don't do the PG support itself, I don't even get to
voice much of an opinion (I deal really just with making sure the
physical hardware is doing what it needs to do, which is where the
disk defrag comes in to play).

-chris
<www.mythtech.net>

On Nov 16, 2009, at 1:11 PM, Robert Schnabel wrote:

> I've been a Diskeeper customer for about 10 years now and consider
> it 'must have' software for Windows machines.
> <snip>
> So the short answer is yes, I have it running with PostgreSQL and
> have not had any problems.


So that seems to be a definite vote for it should be just fine.

I've read the other posts and I understand the concerns that were
raised. I may try to do some testing myself since other than the one
Yes there isn't anyone else jumping in to say they are doing it
safely. Of course there is also no one saying don't do it, just
statements of caution as it appears to be an unknown and has the
potential to cause problems.

It looks like to be really safe I should do some failure testing on my
end first.

Thanks to everyone for their input!

-chris
<www.mythtech.net>

cb <cb@mythtech.net> writes:
> Ugh, yeah, I'd love to upgrade but the powers that get to make that
> decision have no interest in upgrading. So I'm stuck on 8.0.4,

Make sure you're not in the line of fire when (not if) that version
eats your data.  Particularly on Windows, insisting on not upgrading
that version is unbelievably, irresponsibly stupid.  There are a
*large* number of known bugs.

            regards, tom lane

Tom Lane wrote:

cb <cb@mythtech.net> writes: 

Ugh, yeah, I'd love to upgrade but the powers that get to make that  
decision have no interest in upgrading. So I'm stuck on 8.0.4,   

Make sure you're not in the line of fire when (not if) that version
eats your data.  Particularly on Windows, insisting on not upgrading
that version is unbelievably, irresponsibly stupid.  There are a
*large* number of known bugs. 

Yeah, the prudent thing to do in your situation is to issue a CYA memo that says something like "I think the hardware is OK, but due to large number of bugs in PostgreSQL 8.0.4 on Windows it's easy for the database to become corrupted anyway", point toward http://www.postgresql.org/docs/8.4/static/release.html to support that claim and note that 8.0.22 is the absolutely minimum version anyone should be running, then CC everyone up the management chain.  You're using a version that considers your data quite tasty and would like to make a snack of it at the first opportunity that arises.

-- 
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

On Mon, Nov 16, 2009 at 7:45 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> Tom Lane wrote:
>
> cb <cb@mythtech.net> writes:
>
>
> Ugh, yeah, I'd love to upgrade but the powers that get to make that
> decision have no interest in upgrading. So I'm stuck on 8.0.4,
>
>
> Make sure you're not in the line of fire when (not if) that version
> eats your data.  Particularly on Windows, insisting on not upgrading
> that version is unbelievably, irresponsibly stupid.  There are a
> *large* number of known bugs.
>
>
> Yeah, the prudent thing to do in your situation is to issue a CYA memo that
> says something like "I think the hardware is OK, but due to large number of
> bugs in PostgreSQL 8.0.4 on Windows it's easy for the database to become
> corrupted anyway", point toward
> http://www.postgresql.org/docs/8.4/static/release.html to support that claim
> and note that 8.0.22 is the absolutely minimum version anyone should be
> running, then CC everyone up the management chain.  You're using a version
> that considers your data quite tasty and would like to make a snack of it at
> the first opportunity that arises.

Last job I worked we had pgsql and a Big Commercial Database and the
three other DBAs who worked on mostly that other database were scared
to death of patches to their dbms.  Thank the gods that pgsql updates
are quite possibly the most reliable and easy to apply of any system.
Read release notes, and 99% of the time it's just just shut down, rpm
-Uvh postgres*rpm, start up, and viola you're up to date.

Pg updates are focused on security and bug fixes that don't change
accepted behaviour within a major version.  I agree, not applying them
verges on negligence.  Especially if you haven't read the release
notes to see what was fixed.  Sometimes I read them and don't worry
about it if it's a real esoteric bug.  But when a data loss bug shows
up I upgrade right away.

On Nov 16, 2009, at 8:31 PM, Tom Lane wrote:

> Make sure you're not in the line of fire when (not if) that version
> eats your data.  Particularly on Windows, insisting on not upgrading
> that version is unbelievably, irresponsibly stupid.  There are a
> *large* number of known bugs.


I hear ya, and have agreed with you for a long while. There is a
fairly regular and constant fight in house over the issue of
upgrading. We get hit on a regular basis with problems that as far as
I know are bugs that have been fixed (transaction log rename crashes
that take down PG, as well as queries just vanishing into the aether
at times of heavy load resulting in hung threads in our Tomcat front
end as it waits for something to come back that has disappeared).



On Nov 16, 2009, at 9:45 PM, Greg Smith wrote:

> Yeah, the prudent thing to do in your situation is to issue a CYA
> memo that says something like "I think the hardware is OK, but due
> to large number of bugs in PostgreSQL 8.0.4 on Windows it's easy for
> the database to become corrupted anyway", point toward http://www.postgresql.org/docs/8.4/static/release.html
>  to support that claim and note that 8.0.22 is the absolutely
> minimum version anyone should be running, then CC everyone up the
> management chain.  You're using a version that considers your data
> quite tasty and would like to make a snack of it at the first
> opportunity that arises.

Myself and the other guy responsible for the underlying hardware have
already gone down this route. The big bosses know our stance and know
it isn't us preventing the upgrade. After that, there isn't too much
more I can do except sit back and shake my head each time something
goes wrong and I get sent on a wild goose chase to find any reason for
the failure OTHER than PG.

Really it comes down to the DBMs have a firm stance of nothing
changes, ever. Me, I say bug fixes are released for a reason.

My understanding is, before I joined the company, they did an upgrade
from 7 on Linux to 8 on Windows and got bit by some change in PG that
broke a bunch of code. After that, they have just refused to budge
from the 8.0.4 version we are on and know the code works against. I
don't really have any details beyond that and asking for them tends to
invoke religious wars in house between the Linux/Open Source people
and the Windows/Buy Everything people. So I've given up fighting,
covered my butt, and just do the best I can to keep things running.


Thanks again for the insights!

-chris
<www.mythtech.net>

cb wrote:
> My understanding is, before I joined the company, they did an upgrade
> from 7 on Linux to 8 on Windows and got bit by some change in PG that
> broke a bunch of code. After that, they have just refused to budge
> from the 8.0.4 version we are on and know the code works against.
Yes; that's one of the reasons there was a major version number bump
there.  That's a completely normal and expected issue to run into.  A
similar problem would happen if they tried to upgrade to 8.3 or later
from 8.0--you can expect the app to break due to a large change made in 8.3.

Sounds to me like the app doesn't really work against the version you're
running against now though, from the issues you described.  Which brings
us to the PostgreSQL patching philosophy, which they may not be aware
of.  Upgrades to later 8.0 releases will contain *nothing* but bug and
security fixes.  The basic guideline for changes made as part of the
small version number changes (8.0.1 to 8.0.2 for example) are that the
bug must be more serious than the potential to cause a regression
introduced by messing with things.  You shouldn't get anything by going
to 8.0.22 but fixes to real problems.  A behavior change that broke code
would be quite unexpected--the primary way you might run into one is by
writing code that expects buggy behavior that then breaks.  That's not a
very common situation though, whereas the way they got bit before was
beyond common--as I said, it was expected to happen.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com

cb <cb@mythtech.net> wrote:
> On Nov 16, 2009, at 8:31 PM, Tom Lane wrote:
>
>> Make sure you're not in the line of fire when (not if) that version
>> eats your data.  Particularly on Windows, insisting on not
>> upgrading that version is unbelievably, irresponsibly stupid.
>> There are a *large* number of known bugs.
>
>
> I hear ya, and have agreed with you for a long while. There is a
> fairly regular and constant fight in house over the issue of
> upgrading. We get hit on a regular basis with problems that as far
> as I know are bugs that have been fixed (transaction log rename
> crashes that take down PG, as well as queries just vanishing into
> the aether at times of heavy load resulting in hung threads in our
> Tomcat front end as it waits for something to come back that has
> disappeared).

If you could track down some unmodified 1971 Ford Pintos, you could
give them some perspective by having them drive those until they
upgrade.

-Kevin

On Tue, Nov 17, 2009 at 7:59 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> cb <cb@mythtech.net> wrote:
>> On Nov 16, 2009, at 8:31 PM, Tom Lane wrote:
>>
>>> Make sure you're not in the line of fire when (not if) that version
>>> eats your data.  Particularly on Windows, insisting on not
>>> upgrading that version is unbelievably, irresponsibly stupid.
>>> There are a *large* number of known bugs.
>>
>>
>> I hear ya, and have agreed with you for a long while. There is a
>> fairly regular and constant fight in house over the issue of
>> upgrading. We get hit on a regular basis with problems that as far
>> as I know are bugs that have been fixed (transaction log rename
>> crashes that take down PG, as well as queries just vanishing into
>> the aether at times of heavy load resulting in hung threads in our
>> Tomcat front end as it waits for something to come back that has
>> disappeared).
>
> If you could track down some unmodified 1971 Ford Pintos, you could
> give them some perspective by having them drive those until they
> upgrade.

And they all get 1993 era Pentium 60s with 32 Megs of RAM running
windows 3.11 for workgroups and using the trumpet TCP stack.
Upgrades, who needs 'em?!

Greg Smith wrote:
> cb wrote:
>> My understanding is, before I joined the company, they did an upgrade
>> from 7 on Linux to 8 on Windows and got bit by some change in PG that
>> broke a bunch of code. After that, they have just refused to budge
>> from the 8.0.4 version we are on and know the code works against.
> Yes; that's one of the reasons there was a major version number bump
> there.  That's a completely normal and expected issue to run into.  A
> similar problem would happen if they tried to upgrade to 8.3 or later
> from 8.0--you can expect the app to break due to a large change made in
> 8.3.
>
> Sounds to me like the app doesn't really work against the version you're
> running against now though, from the issues you described.  Which brings
> us to the PostgreSQL patching philosophy, which they may not be aware
> of.  Upgrades to later 8.0 releases will contain *nothing* but bug and
> security fixes.

To elaborate on Greg's point: One of the cool things about Postgres "minor" releases (e.g. everything in the 8.0.*)
series,is that you can backup your software, turn off Postgres, install the new version, and just fire it up again, and
itworks.  Any problems?  Just revert to the old version. 

It's an easy sell to management.  They can try it, confirm that none of the apps have broken, and if there are
problems,you simple say "oops", and revert to the old version.  If it works, you're the hero, if not, it's just a
couplehours of your time. 

Craig

On Mon, 2009-11-16 at 23:57 -0500, cb wrote:
> On Nov 16, 2009, at 8:31 PM, Tom Lane wrote:
> Myself and the other guy responsible for the underlying hardware have
> already gone down this route. The big bosses know our stance and know
> it isn't us preventing the upgrade. After that, there isn't too much
> more I can do except sit back and shake my head each time something
> goes wrong and I get sent on a wild goose chase to find any reason for
> the failure OTHER than PG.


What you need to do is stop the wild goose chases.  If problem is you PG
version, no amount of investigation into other areas is going to change
that.  Your company is simply wasting money by ignoring this and blindly
hoping that the problem will be something else.

It can be a difficult battle, but it can be won.

--
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.