Date: Thu, 18 Sep 2008 13:07:24 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Mauri Sahlberg <mauri.sahlberg@claymountain.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Help request: how to tune performance?
Message-ID: <20080918180724.GG22468@it.is.rice.edu>
References: <1221553908.3410.11.camel@fault.in.tuolla.net>
	<dcc563d10809161010j4a04a9f9ufefab82abf2dce08@mail.gmail.com>
	<48D2972E.9000604@claymountain.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <48D2972E.9000604@claymountain.com>
User-Agent: Mutt/1.5.16 (2007-06-09)

Hi,

The only other thing to check is what indexes are defined for
your schema. You can look at a previous post about PostgreSQL
indexing for RT to see what we are using here at Rice. Let me
know if you have any questions.

Cheers,
Ken

On Thu, Sep 18, 2008 at 09:00:14PM +0300, Mauri Sahlberg wrote:
> Hi,
>
> 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/Pg.pm. 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'));
> pg_size_pretty
> ----------------
> 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.
>
>
> -- 
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>