Re: Bundling postgreSQL with my Java application

[Saurabh Dave <saurabhdave@xxxxxxxxx>]

Thanks all for your valuable comments, as I gather, what I need to do is to check the queries that are slow and do a vacuum analyze and share the results along with postgresql.conf being used.

I will work on that.

Thanks again,
Saurabh

On Tue, Jul 7, 2009 at 4:46 AM, justin <justin@xxxxxxxxxxxxxxx> wrote:

Saurabh Dave wrote:

>No offense intended - but have you looked at the documentation for postgresql.conf?

>If you are going to include PostgreSQL in your application, I'd highly recommend you >understand what you are including. :-)

I had a look into the documentation of postgres.conf, and tried a lot with changing paramters I thought would improve the performance, but in vain.
Autovaccum is enabled by default in 8.3.7 , but i reduced the nap time so that it happens more frequently.

As others have pointed tuning is not a caned answer  hence all the config options to start with.  But to change the configuration to something a bench mark must be made.  The only way to do that is identify the common SQL commands sent to the server then run explain analyze  so you know what the server is doing.  Then post the the results along with Config file and we can make suggestions

There is  http://wiki.postgresql.org/wiki/Performance_Optimization

Greg Smith is working on a tuner  http://notemagnet.blogspot.com/2008/11/automating-initial-postgresqlconf.html

But thats a monumental undertaking as one configuration setting for one type of work load can be ruinousness to another work load.

The one common theme is know the workload so the configuration matches. 

My personal opinion is that certain parameters in postgres.conf are simply too technical in nature for a application developer like me, it becomes more of a trial and error kind of frustrating process.

This boils down to know the  work load.
different kinds of work loads: 
    A: more writing with very few  reads.
    B: more reads that are simple queries and few complex quiers with very few writes.  There is a ratio to look at in my case 10000 reads occur before next write So we have lots of indexes aimed at those common queries. 
    C: Complex queries taking minutes to hours to run on data warehouse covering  millions of records.
    D: equal work load between writes and reads. 

There are many kinds of workloads requiring different configurations. 

If there a utility that understands the system specification on which postgres is going to run and change the paramters accordingly, that would help.

Thanks,
Saurabh

<snip>