pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Conway <neilc@samurai.com>
To: swampler@noao.edu
Cc: Postgres-performance <pgsql-performance@postgresql.org>
Subject: Re: Help tracking down problem with inserts slowing
Date: Fri, 05 Dec 2003 18:38:47 -0500
Message-ID: <87fzfy96ns.fsf@mailbox.samurai.com> (raw)
In-Reply-To: <1070661108.20063.86.camel@weaver.tuc.noao.edu>
References: <1070661108.20063.86.camel@weaver.tuc.noao.edu>

Steve Wampler <swampler@noao.edu> writes:
> PG: 7.2.3  (RedHat 8.0)

You're using PG 7.2.3 with the PG 7.1 JDBC driver; FWIW, upgrading to
newer software is highly recommended.

> The two sites were performing at comparable speeds until a few days
> ago, when we deleted several million records from each database and
> then did a vacuum full; analyze on both.  Now inserts at Site B are
> several orders of magnitude slower than at Site A.

Two thoughts:

    (1) Can you confirm that the VACUUM FULL on site B actually
        removed all the tuples you intended it to remove? Concurrent
        transactions can limit the amount of data that VACUUM FULL is
        able to reclaim. If you run contrib/pgstattuple (or compare
        the database's disk consumption with the number of live rows
        in it), you should be able to tell.

    (2) Look at the EXPLAIN for the SELECTs generated by the ON INSERT
        trigger -- is there any difference between site A and B?

-Neil




view thread (9+ messages)  latest in thread

Message-ID: <87fzfy96ns.fsf@mailbox.samurai.com>
Permalink:  ../87fzfy96ns.fsf@mailbox.samurai.com/
Also on:    postgresql.org/message-id/87fzfy96ns.fsf@mailbox.samurai.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: neilc@samurai.com, swampler@noao.edu
  Subject: Re: Help tracking down problem with inserts slowing
  In-Reply-To: <87fzfy96ns.fsf@mailbox.samurai.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox