pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Francisco Reyes <lists@stringsutils.com>
To: Karl Wright <kwright@metacarta.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Wed, 20 Jun 2007 13:53:07 -0400
Message-ID: <cone.1182361987.297492.1867.5001@35st.simplicato.com> (raw)
References: <4676DD57.7070304@metacarta.com>
	<14291.1182221811@sss.pgh.pa.us>
	<4677B831.8060704@metacarta.com>
	<4677D298.2010109@metacarta.com>
	<20070619132643.GC4265@alvh.no-ip.org>
	<4677DC06.3000300@metacarta.com>
	<20070619134637.GG4265@alvh.no-ip.org>
	<cone.1182267949.154012.90929.5001@35st.simplicato.com>
	<467963A8.2060703@metacarta.com>

Karl Wright writes:

> Okay - I started a VACUUM with the 8.1 database yesterday morning, 
> having the database remain under load.  As of 12:30 today (~27 hours), 
> the original VACUUM was still running.  At that point:

I don't recall if you said it already, but what is your 
maintenance_work_mem?
  
> (a) I had to shut it down anyway because I needed to do another 
> experiment having to do with database export/import performance, and

Do you know which tables change the most often?
Have you tried to do vacuum of those one at a time and see how long they 
take?

> (b) the performance of individual queries had already degraded 
> significantly in the same manner as what I'd seen before.

If you have a lot of inserts perhaps you can do analyze more often also. 
 
> So, I guess this means that there's no way I can keep the database 
> adequately vacuumed with my anticipated load and hardware.

It is a possibility, but you could consider other strategies.. totally 
dependant on the programs accessing the data..

For example:
do you have any historical data that never changes?
Could that be moved to a different database in that same machine or another 
machine? That would decrease your vacuum times.
Also partitioning the data so data that never changes is in separate 
tables may also help (but I am not sure of this).

Given the sizes you sent to the list, it may be simply that it is more than 
the hardware can handle.



view thread (56+ messages)  latest in thread

Message-ID: <cone.1182361987.297492.1867.5001@35st.simplicato.com>
Permalink:  ../cone.1182361987.297492.1867.5001@35st.simplicato.com/
Also on:    postgresql.org/message-id/cone.1182361987.297492.1867.5001@35st.simplicato.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 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-performance@postgresql.org
  Cc: lists@stringsutils.com, kwright@metacarta.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us
  Subject: Re: Performance query about large tables, lots of concurrent access
  In-Reply-To: <cone.1182361987.297492.1867.5001@35st.simplicato.com>
  References: <4676DD57.7070304@metacarta.com> <14291.1182221811@sss.pgh.pa.us> <4677B831.8060704@metacarta.com> <4677D298.2010109@metacarta.com> <20070619132643.GC4265@alvh.no-ip.org> <4677DC06.3000300@metacarta.com> <20070619134637.GG4265@alvh.no-ip.org> <cone.1182267949.154012.90929.5001@35st.simplicato.com> <467963A8.2060703@metacarta.com> <cone.1182361987.297492.1867.5001@35st.simplicato.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