From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Tuning PostgreSQL for very large database |
Date: | 2011-11-06 19:00:08 |
Message-ID: | 4EB6D938.40606@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/06/11 8:51 AM, René Fournier wrote:
> Just wondering what I can do to squeeze out more performance of my
> database application? Here's my configuration:
>
>
> - Mac mini server
>
> - Core i7 quad-core at 2GHz
>
> - 16GB memory
> - Dedicated fast SSD (two SSDs in the server)
> - Mac OS X 10.7.2 (*not* using OS X Server)
>
> - PostgreSQL 9.05
> - PostGIS 1.5.3
> - Tiger Geocoder 2010 database (from build scripts from
> http://svn.osgeo.org/postgis/trunk/extras/tiger_geocoder/tiger_2010/)
> - Database size: ~90GB
>
> I should say, this box does more than PostgreSQL
> geocoding/reverse-geocoding, so reasonably only half of the memory
> should be allotted to PostgreSQL.
>
> Coming from MySQL, I would normally play with the my.cnf, using
> my-huge.cnf as a start. But I'm new to PostgreSQL and PostGIS (w/ a
> big database), so I was wondering if anyone had suggestions on tuning
> parameters (also, which files, etc.) Thanks!
postgresql.conf in the postgres 'data' directory is the only postgresql
file you should have to touch. you -will- also need to increase the
OSX "kernel.shmmax" and 'kernel.shmall' parameters (I'd set these to 4
gigabytes each, note that in most 'nix systems shmall is NOT in bytes),
I can not help you do this as I only know how to do it on
linux/solaris/aix...
in postgresql.org, given what you've said above, and assuming your
application uses relatively few concurrent connections (say, no more
than a few dozen), I'd try something like...
shared_buffers = 1024mb
maintenance_work_mem = 512MB
work_mem = 128MB
effective_cache_size = 4096MB
if you expect 100s of concurrent connections, reduce work_mem accordingly.
I'm assuming your database workload is read-mostly, and that you're not
going to be doing a high rate of transactional operations with
updates/inserts. if you /are/ getting into 100s/1000s of write
transactions/second, then you'll want to watch your postgres logfiles
and increase...
checkpoint_segments = **
such that ** is large enough that you no longer get any
checkpoints-too-frequent warnings. one heavy OLTP transaction server
recently, I had to increase the default 3 to like 100 to get to a happy
place. Increasing wal_buffers is probably a good idea too in these
cases, but I'm suspecting this doesn't apply to you.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2011-11-06 19:58:56 | How to force some char type columns to be stored in uppercase |
Previous Message | stephaneg | 2011-11-06 17:11:10 | Re: installation problems on OSX Lion |