Re: Need help on parameters and their values to tune the postgresql database

Bebarta, Simanchala wrote:
> I am doing a performance benchmarking test by using benchmarkSQL tool on
> postgresql 8.2.4.I need to tune the parameters to achieve an optimal
> performance of the postgresql database.
>
> I have installed postgresql 8.2.4 on RHEL AS4. It is a DELL Optiplex
> GX620 PC with 4GB RAM.
>
> Please suggest me which parameters I need to tune and what would be the
> possible values for the parameters.
>
> Appreciate if I would get a quick response.

Why are you doing the benchmark? That's going to have a big impact on
how you should tune. For example, how much do you value data integrity,
in case of a crash or power outage?

How many warehouses are you planning to use? The fact that you didn't
mention anything about your I/O hardware suggests that you're planning
to do a test that fits entirely in cache. If that's the case, you're
going to be limited by the rate you can fsync the commit WAL records to
disk. If you don't care about data integrity, you can turn fsync=off, to
eliminate that bottleneck. In that case you'll also want to turn
full_page_writes=off; that'll reduce the CPU overhead somewhat.

Another important factor is how long you're going to run the test. If
you run it for more than say 10 minutes, you'll have to run vacuum to
keep the table sizes in check. If it's all in memory, as I presume,
autovacuum might be enough for the task.

PS. You might want to talk to these guys from Unisys:
http://www.pgcon.org/2007/schedule/events/16.en.html

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com