From: Dave Cramer <pg@fastcrypt.com>
To: John Parnefjord <John.Parnefjord@kib.ki.se>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Tuning
Date: Fri, 26 Jan 2007 09:07:21 -0500
Message-ID: <55B1AD7B-DCC1-4129-AE14-1EDF0EF786FD@fastcrypt.com> (raw)
In-Reply-To: <76C73999206F3145A2E3046A5C32A103F02A5A@kibmail.kib.local>
References: <76C73999206F3145A2E3046A5C32A103F02A5A@kibmail.kib.local>
On 26-Jan-07, at 6:28 AM, John Parnefjord wrote:
>
> Hi!
>
> I'm planning to move from mysql to postgresql as I believe the latter
> performs better when it comes to complex queries. The mysql database
> that I'm running is about 150 GB in size, with 300 million rows in the
> largest table. We do quite a lot of statistical analysis on the data
> which means heavy queries that run for days. Now that I've got two new
> servers with 32GB of ram I'm eager to switch to postgresql to improve
> perfomance. One database is to be an analysis server and the other an
> OLTP server feeding a web site with pages.
>
> I'm setting for Postgresql 8.1 as it is available as a package in
> Debian
> Etch AMD64.
>
> As I'm new to postgresql I've googled to find some tips and found some
> interesting links how configure and tune the database manager. Among
> others I've found the PowerPostgresql pages with a performance
> checklist
> and annotated guide to postgresql.conf
> [http://www.powerpostgresql.com/]. And of course the postgresql site
> itself is a good way to start. RevSys have a short guide as well
> [http://www.revsys.com/writings/postgresql-performance.html]
>
> I just wonder if someone on this list have some tips from the real
> world
> how to tune postgresql and what is to be avoided. AFAIK the following
> parameters seems important to adjust to start with are:
>
> -work_mem
> -maintenance_work_mem - 50% of the largest table?
Isn't it possible for this to be larger than memory ?
> -shared_buffers - max value 50000
Where does this shared buffers maximum come from ? It's wrong it
should be 1/4 of available memory (8G) to start and tuned from there
> -effective_cache_size - max 2/3 of available ram, ie 24GB on the
> hardware described above
> -shmmax - how large dare I set this value on dedicated postgres
> servers?
as big as required by shared buffer setting above
> -checkpoint_segments - this is crucial as one of the server is
> transaction heavy
> -vacuum_cost_delay
>
> Of course some values can only be estimated after database has been
> feed
> data and queries have been run in a production like manner.
>
> Cheers
> // John
>
> Ps. I sent to list before but the messages where withheld as I'm
> not "a
> member of any of the restrict_post groups". This is perhaps due to the
> fact that we have changed email address a few weeks ago and there
> was a
> mismatch between addresses. So I apologize if any similar messages
> show
> up from me, just ignore them.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
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: pg@fastcrypt.com, John.Parnefjord@kib.ki.se
Subject: Re: Tuning
In-Reply-To: <55B1AD7B-DCC1-4129-AE14-1EDF0EF786FD@fastcrypt.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