Skip site navigation (1) Skip section navigation (2)

Re: Very long deletion time on a 200 GB database

From: Shaun Thomas <sthomas(at)peak6(dot)com>
To: "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-27 15:14:13
Message-ID: 4F4B9DC5.70108@peak6.com (view raw or flat)
Thread:
Lists: pgsql-performance
On 02/27/2012 08:59 AM, Reuven M. Lerner wrote:

> From what I understand, the issue isn't one of current disk space,
> but rather of how quickly the disk space is being used up.

Noted. Just keep in mind that dead rows are not free. In the case of 
sequence scans, the rows still have to be read from disk and then 
ignored by the engine. Vacuums also act as sequence scans, so the more 
data they're reading, the longer that takes. This is especially true on 
an overloaded system.

> I wouldn't be surprised if we end up doing a CLUSTER at some point.
> The problem is basically that this machine is in 24/7 operation at
> high-speed manufacturing plants, and the best-case scenario is for a
> 4-hour maintenance window.

The best case scenario is for them to buy a second server. If operation 
of this app stack really is critical to business, they need to spend the 
money to keep it working, or they'll end up paying much more for it when 
it fails. You also said that server has other stuff running on it, and 
it already has very little memory. That tells me they have no DR node. 
I'm afraid to even ask how they're doing backups. That one machine is a 
giant, red, flashing single point of failure. I really hope they 
understand that.

> I've suggested that we might be able to help the situation somewhat
> by attaching a portable USB-based hard disk, and adding a new
> tablespace that'll let us keep running while we divide up the work
> that the disk is doing, but they've made it clear that the current
> hardware configuration cannot and will not change. Period.

And that's it, then. You have yourself a bad client. If it were me, I'd 
get through this contract and never do business with them again. They 
have a system that's basically 100% guaranteed to fail some time in the 
future (and yet is critical for operation!) and are putting Band-Aids on 
it. I think there's a parable somewhere about eggs and baskets, but I 
can't recall it at this moment. ;)

-- 
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-444-8534
sthomas(at)peak6(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2012-02-27 15:25:31
Subject: Re: Joining tables by UUID field - very slow
Previous:From: Shaun ThomasDate: 2012-02-27 15:01:13
Subject: Re: Very long deletion time on a 200 GB database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group