pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tv@fuzzy.cz
To: Decibel! <decibel@decibel.org>
Cc: tv@fuzzy.cz, "Tobias Brox" <tobias@nordicbet.com>
Cc: Gábor Farkas <gabor@nekomancer.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum: recommended?
Date: Mon, 19 Nov 2007 10:53:08 +0100 (CET)
Message-ID: <20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz> (raw)
In-Reply-To: <7DA6747D-E739-4E25-9D5C-0516A6A97F1C@decibel.org>
References: <473D659B.1070008@nekomancer.net>
	<20071116111355.GB1195@mail.nordicbet.com>
	<61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz>
	<7DA6747D-E739-4E25-9D5C-0516A6A97F1C@decibel.org>

> FWIW, 20k rows isn't all that big, so I'm assuming that the
> descriptions make the table very wide. Unless those descriptions are
> what's being updated frequently, I suggest you put those in a
> separate table (vertical partitioning). That will make the main table
> much easier to vacuum, as well as reducing the impact of the high
> churn rate.

Yes, you're right - the table is quite wide, as it's a catalogue of a
pharmacy along with all the detailed descriptions and additional info etc.
So I guess it's 50 MB of data or something like that. That may not seem
bad, but as I already said the table grew to about 12x the size during the
day (so about 500MB of data, 450MB being dead rows). This is the 'central'
table of the system, and there are other quite heavily used databases as
well. Add some really stupid queries on this table (for example LIKE
searches on the table) and you easily end up with 100MB of permanent I/O
during the day.

The vertical partitioning would be overengineering in this case - we
considered even that, but proper optimization of the update process
(updating only those rows that really changed), along with a little bit of
autovacuum tuning solved all the performance issues.

Tomas




view thread (18+ messages)  latest in thread

Message-ID: <20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz>
Permalink:  ../20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz/
Also on:    postgresql.org/message-id/20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz

 · 

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: tv@fuzzy.cz, decibel@decibel.org, tobias@nordicbet.com, gabor@nekomancer.net
  Subject: Re: autovacuum: recommended?
  In-Reply-To: <20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz>
  References: <473D659B.1070008@nekomancer.net> <20071116111355.GB1195@mail.nordicbet.com> <61575.212.24.144.68.1195220303.squirrel@mail.fuzzy.cz> <7DA6747D-E739-4E25-9D5C-0516A6A97F1C@decibel.org> <20746.212.24.144.68.1195465988.squirrel@mail.fuzzy.cz>

* 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