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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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