Re: Strange performance hit upgrading from 8.0.9 to 8.2.1

From: "Gregory S(dot) Williamson" <gsw(at)globexplorer(dot)com>
To: "David F(dot) Skoll" <dfs(at)roaringpenguin(dot)com>, <pgsql-admin(at)postgresql(dot)org>
Subject: Re: Strange performance hit upgrading from 8.0.9 to 8.2.1
Date: 2007-01-19 03:34:42
Message-ID: 71E37EF6B7DCC1499CEA0316A256832802B3EC28@loki.wc.globexplorer.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Have you tried using a TRUNCATE instead of a DELETE ? I've found it to be *way* faster and has some better aspects with vacuuming the table afterwards.

I am not sure why you would see such a change from 8.0.9 though (we mostly skipped to 8.1 and now 8.2).

Do you know if the aggregation part has slowed, the delete part, or both ? If they were to take much longer than they used to I would expect that the attaching processes would experience unhappiness. Can you do an explain analyze on both the old and new systems ?

HTH,

Greg Williamson
DBA
GlobeXPlorer LLC, a DigitalGLobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

-----Original Message-----
From: pgsql-admin-owner(at)postgresql(dot)org on behalf of David F. Skoll
Sent: Thu 1/18/2007 5:37 PM
To: pgsql-admin(at)postgresql(dot)org
Cc:
Subject: [ADMIN] Strange performance hit upgrading from 8.0.9 to 8.2.1

Hello,

We have a customer running a rather large installation. There are
about 15 machines talking to a PostgreSQL database server. At any
given time, each machine has between around 20 to 120 connections to
the PG server, and the aggregate query rate probably hovers at over
1000 per second.

Our database has a table that looks like this:

database=> \d low_contention_stats
Table "public.low_contention_stats"
Column | Type | Modifiers
-----------+---------+-----------------------------
date | date | default ('now'::text)::date
key | text |
increment | integer | default 1

There are no indexes on that table. Anywhere from 40 to 100
times per second, we insert something like this into the table:

INSERT INTO low_contention_stats(key, increment) VALUES('ACCEPTED', 1);

The INSERTs run outside of any transaction (ie, in their own self-contained
transaction.) The "key" data being inserted is short -- under 25 characters.

We periodically do something like this:

BEGIN;
-- Aggregate the low_contention data into another table
-- and then...
DELETE FROM low_contention_stats;
COMMIT;

and it used to work fine. However, on the upgrade from 8.0.9 to
8.2.1, we suddenly started seeing terrible performance on the INSERTs.
(The aggregation process was disabled -- we were only doing the
INSERTs at the time.) What would happen is that hundreds of back-ends
would acquire locks on the table and it would take many seconds for
all the INSERTs to finish. Things would settle down for a few seconds
to a couple of minutes, and then again - WHAM! Huge contention.
Disabling the INSERTs completely made everything happy, even though
they formed only a small portion of the overall queries. While this
is OK as a stopgap, it's not really a long-term solution because it
disables a fairly useful feature of our software.

Unfortunately, I have no idea how to duplicate the problem in our
lab with the hardware we have access to. :-( Any PostgreSQL gurus
have any insights?

Regards,

David.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

-------------------------------------------------------
Click link below if it is SPAM gsw(at)globexplorer(dot)com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45b026ea317331465134470&user=gsw(at)globexplorer(dot)com&retrain=spam&template=history&history_page=1"
!DSPAM:45b026ea317331465134470!
-------------------------------------------------------

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Milen A. Radev 2007-01-19 08:22:37 Re: System Date
Previous Message Stephan Szabo 2007-01-19 03:16:26 Re: Error in trigger