Re: Parameters for PostgreSQL

On Mon, Aug 1, 2011 at 7:09 AM, Jayadevan M
<Jayadevan(dot)Maymala(at)ibsplc(dot)com> wrote:
> Hello all,
> We are planning to test one of our products, which works with Oracle, on
> PostgreSQL.  The database size is about 100 GB. It is a product with a
> not-so-high load ( about 10 tps - mostly read). My doubts are about
> PostgreSQL settings. For Oracle, we give about 4 GB SGA (shared buffer) and
> 1.5 GB PGA (sum of session-specific memory). The machine configuration is
> Opteron 2CPU * 4cores @ 2.3GHz
> 16GB  RAM
> OS Solaris10 x64
>
> So far I have changed the following settings in postgresql.conf
>
> shared_buffers = 2GB
> temp_buffers = 8MB
> work_mem = 16MB
> maintenance_work_mem = 32MB
> wal_level = archive
> checkpoint_segments = 10
> checkpoint_completion_target = 0.7
> archive_mode = on
> effective_cache_size = 6GB
> log_destination = 'csvlog'
> logging_collector = on
> log_directory = '/backup/datapump/pgdata/log'
> log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
> log_rotation_age = 1d
> client_min_messages = notice
> log_min_messages = warning
> log_min_duration_statement = 3000
>
> Could you please let me know the parameters I should pay attention to? Do
> the settings mentioned above look OK?

The settings above look ok. I would consider raising
maintenance_work_mem much higher, say to 1gb. I personally don't like
the timestamp encoded into the log filename and do something much
simpler, like:
log_filename = 'postgresql-%d.log'

and set the logs to truncate on rotation.

> We are suing weblogic. Should we let weblogic manage the connection pool or
> try something else?

Don't have a experience with weblogic, but at 10 tps, it doesn't
matter a whole lot. I'd consider sticking with what you've got unless
you have a good reason to change it.

merlin