pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Martin Marques <martin@bugs.unl.edu.ar>
To: Rick Gigger <rick@alpinenetworking.com>
To: PgSQL General ML <pgsql-general@postgresql.org>
Subject: Re: performance problem
Date: Tue, 18 Nov 2003 18:50:14 -0300
Message-ID: <200311181850.14802.martin@bugs.unl.edu.ar> (raw)
In-Reply-To: <01b201c3ae14$92bd3c00$0700a8c0@trogdor>
References: <1069164075.986.239.camel@bbking.uio.no>
	<20031118205057.K606@hermes.hilbert.loc>
	<01b201c3ae14$92bd3c00$0700a8c0@trogdor>

El Mar 18 Nov 2003 17:43, Rick Gigger escribió:
> I am currently trying to import a text data file without about 45,000
> records.  At the end of the import it does an update on each of the 45,000
> records.  Doing all of the inserts completes in a fairly short amount of
> time (about 2 1/2 minutes).  Once it gets to the the updates though it slows
> to a craw.  After about 10 minutes it's only done about 3000 records.

Thats not a big amount of rows. It shouldn't be making that amount of trouble, 
unless you have something wrong with the update query.

Try inserting all the 45K rows and then run an explain analyze to the update 
query to see what's wrong (reply the output of explain analyze to the list). 
The update query itself would help alot, too.

> Is that normal?  Is it because it's inside such a large transaction?  Is
> there anything I can do to speed that up.  It seems awfully slow to me.

Not sure, but I have had lot's of big transactions with heady load, and never 
had a problem like the one you describe.

> I didn't think that giving it more shared buffers would help but I tried
> anyway.  It didn't help.
> 
> I tried doing a analyze full on it (vacuumdb -z -f) and it cleaned up a lot
> of stuff but it didn't speed up the updates at all.
> 
> I am using a dual 800mhz xeon box with 2 gb of ram.  I've tried anywhere
> from about 16,000 to 65000 shared buffers.

How's memory performace while you're running the updates? What does free say 
(if you are in some unix environment).

-- 
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-----------------------------------------------------------------
Martín Marqués                  |        mmarques@unl.edu.ar
Programador, Administrador, DBA |       Centro de Telemática
                       Universidad Nacional
                            del Litoral
-----------------------------------------------------------------




view thread (37+ messages)  latest in thread

Message-ID: <200311181850.14802.martin@bugs.unl.edu.ar>
Permalink:  ../200311181850.14802.martin@bugs.unl.edu.ar/
Also on:    postgresql.org/message-id/200311181850.14802.martin@bugs.unl.edu.ar

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: martin@bugs.unl.edu.ar, rick@alpinenetworking.com, pgsql-general@postgresql.org
  Subject: Re: performance problem
  In-Reply-To: <200311181850.14802.martin@bugs.unl.edu.ar>
  References: <1069164075.986.239.camel@bbking.uio.no> <20031118205057.K606@hermes.hilbert.loc> <01b201c3ae14$92bd3c00$0700a8c0@trogdor> <200311181850.14802.martin@bugs.unl.edu.ar>

* 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