To: Mark Kirkwood <markir@paradise.net.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: planner with index scan cost way off actual cost,
	advices to tweak cost constants?
References: <87ek11gz69.fsf@meuh.mnc.lan> <441CDAAA.20609@paradise.net.nz>
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 20 Mar 2006 09:35:14 +0100
In-Reply-To: <441CDAAA.20609@paradise.net.nz>
Message-ID: <87d5ghms3h.fsf@meuh.mnc.lan>
Lines: 63
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi Mark,

Thanks for your reply.

> Guillaume Cottenceau wrote:

[...]

> > Btw, I use postgres 7.4.5 with -B 1000 -N 500 and all
> > postgresql.conf default values except timezone = 'UTC', on an
> > ext3 partition with data=ordered, and run Linux 2.6.12.
> 
> I didn't see any mention of how much memory is on your server, but
> provided you have say 1G, and are using the box solely for a database
> server, I would increase both shared_buffers and effective_cache size.

This test machine has 1G of (real) memory, servers often have 2G
or 4G. The thing is that the application runs on the same
machine, and as it is a java application, it takes up a little
memory too (we can say half of it should go to java and half to
postgres, I guess). Determining the best memory "plan" is not so
easy, though your information is priceless and will help a lot!
 
> shared_buffer = 12000
> effective_cache_size = 25000
> 
> This would mean you are reserving 100M for Postgres to cache relation
> pages, and informing the planner that it can expect ~200M available
> from the disk buffer cache. To give a better recommendation, we need

Ok, thanks. I wanted to investigate this field, but as the
application is multithreaded and uses a lot of postgres clients,
I wanted to make sure the shared_buffers values is globally for
postgres, not just per (TCP) connection to postgres, before
increasing the value, fearing to take the whole server down.

On a server with 235 connections and -N 512 -B 1024, reading
http://virtualthreads.blogspot.com/2006/02/understanding-memory-usage-on-linux.html
I came up with the following figure:

for i in `pidof postmaster`; do pmap -d $i | grep -i writeable ; done | perl -MMDK::Common -ne 'do { push @a, $1; $tot += $1 } if /writeable.private: (\d+)K/; END { print "total postgres private memory: ${tot}K\nmin: " . min(@a) . "K\nmax: " . max(@a) . "K\n"; }'
total postgres private memory: 432080K
min: 936K
max: 4216K

As the server has 2G of memory, I was reluctant to increase the
amount of shared memory since overall postgres memory use seems
already quite high - though 100M more would not kill the server,
obviously. Btw, can you comment on the upper figures?

> to know more about your server and workload (e.g server memory
> configuration and usage plus how close you get to 500 connections).

Depending on the server, it can have 200, up to around 400
connections open. As of workload, I am not sure what metrics are
suitable. Typically postgres can be seen in the top processes but
most queries are quick and average load average reported by the
linux kernel is nearly always below 0.3, and often 0.1. These are
single or dual xeon 2.8 GHz machines with hardware raid (megaraid
or percraid driver) with reasonable performance.

-- 
Guillaume Cottenceau