Skip site navigation (1) Skip section navigation (2)

Re: Help request: how to tune performance?

From: Mauri Sahlberg <mauri(dot)sahlberg(at)claymountain(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Help request: how to tune performance?
Date: 2008-09-18 18:00:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

Thanks for the reply and advice.

Scott Marlowe kirjoitti:
>> Version     : 8.1.11                            Vendor: CentOS
> So, you built it its own machine, but you didn't upgrade to at least 8.2?
Now it is: 8.4devel_15092008

The machine was installed by the production team from the standard 
CentOS template. I tried to adhere to the standard and installed the 
standard CentOS binary for Postgresql. I am not  part of production team 
so I try to be extra careful with the "rule book".
> Please post the output of explain analyze as an attachment.  explain
> is only half the answer.
I did what Kenneth Marshall suggested and edited DBIx::Searchbuilder's 
Handle/ I will post the explain analyze for the new query it now 
generates if it becomes necessary.
> Possibly.  explain analyze will help you identify where stats are
> wrong.  sometimes just cranking the stats target on a few columns and
> re-analyzing gets you a noticeable performance boost.  It's cheap and
> easy.
> When the estimated and actual number of rows are fairly close, then
> look for the slowest thing and see if an index can help.
> What have to already done to tune the install?  shared_buffers,
> work_mem, random_page_cost, effective_cache_size.  Is your db bloating
> during the day?
When I upgraded to 8.4 I also checked newer Postgresql manual for the 
memory consumption and found comment by Steven Citron-Pousty and 
increased accordingly:
- shared_buffers to 320MB
- wal_buffers to 8MB
- effective_cache_size to 2048MB
- maintenance_work_mem to 384MB

Sorry, I do not understand what you mean by bloating. The db size is:
rt=# select pg_size_pretty(pg_database_size('rt'));
 350 MB
(1 row)

> Are you running on a single SATA hard drive?    How big's the database
> directory?  I'm guessing from your top output that the db is about 500
> meg or so.  it should all fit in memory.
-bash-3.2$ du --si -s data
524M    data

I don't know what kind of drives there actually are. The machine is 
vmware virtual with two virtual CPU's clocking 2,33GHz, 4 GB ram, 1 GB 
swap. The disk is probably given from either MSA or from EVA. The disk 
shows up as one virtual drive and everything is on it. Filesystem is 
ext3 on lvm. Database data is on /var which is it's own volume.

I have also added 5 more mason processes to the web frontend machine.

For me the results look promising. Opening search builder went from 42 
seconds to 4 seconds and opening one particular long chain takes now 
only 27 seconds. But again I am not from the support team either so I do 
not get to define what is fast enough. The verdict is now in for the 
jury to decide.

Thank you.

In response to


pgsql-admin by date

Next:From: Kenneth MarshallDate: 2008-09-18 18:07:24
Subject: Re: Help request: how to tune performance?
Previous:From: Alvaro HerreraDate: 2008-09-18 17:03:26
Subject: Re: Regaining superuser access

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group