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

Re: Very long deletion time on a 200 GB database

From: Lew <noone(at)lewscanon(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Very long deletion time on a 200 GB database
Date: 2012-02-27 17:07:04
Message-ID: jigd7q$jae$1@news.albasani.net (view raw or flat)
Thread:
Lists: pgsql-performance
On 02/27/2012 07:14 AM, Shaun Thomas wrote:
> 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. ;)

There is more than one parable here.

For the client - don't be a damn fool. When you go to a doctor for a broken 
arm, you don't refuse the splint and insist on using just aspirin to manage 
the problem.

For the consultant/employee - stop buying into the bullshit. This is a common 
situation, where you tell your client, "You need X" and they refuse the 
advice. You need to be crystal clear with them that they are therefore NOT 
solving their problem.

I stopped giving in to the client's bullshit in this regard years ago when a 
customer tried to withhold over eight thousand dollars because I agreed to my 
manager's refusal to normalize a database and thus didn't fix a performance 
problem.  I got paid when their programmer whom I'd secretly informed of the 
problem and how to fix it took over as the project manager, after using my 
advice to become the hero. The lesson I took is not to gloss over real 
problems because the client is recalcitrant. They don't win, you don't win, 
nobody wins. (Unless you use a workaround as I did, but politics is the court 
of last resort for an engineer.)

I'd rather have my bosses think I'm a little snarky (as long as I'm not fired 
for it), than have them hate me and try not to pay me. I am just loud about 
what is correct and what the consequences of incorrect are; then when they get 
those consequences I make sure to draw the connection.

I'm not there to make friends, I'm there to make solutions. It is fiduciary 
irresponsibility to let your clients go down in flames without at least 
informing them of the alternative.

-- 
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

In response to

pgsql-performance by date

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

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