Message-ID: <4B42638B.4000103@alteeve.com>
Date: Mon, 04 Jan 2010 16:54:19 -0500
From: Madison Kelly <linux@alteeve.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB is slow until DB is reloaded
References: <4B423D22.7040803@alteeve.com>
	<4B424215.70605@pinpointresearch.com> <4B424FF6.40207@alteeve.com>
	<4B4260E6.7050502@pinpointresearch.com>
In-Reply-To: <4B4260E6.7050502@pinpointresearch.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Steve Crawford wrote:
> Madison Kelly wrote:
>> Steve Crawford wrote:
>>> Madison Kelly wrote:
>>>> Hi all,
>>>>
>>>>   I've got a fairly small DB...
>>>>
>>>>   It slows down over time and I can't seem to find a way to get the
>>>> performance to return without doing a dump and reload of the 
>>>> database...
>>>
>> Yup, I even tried manually running 'VACUUM FULL' and it didn't help.
> That's because VACUUM reclaims space (er, actually marks space that is 
> available for reuse) while ANALYZE refreshes the statistics that the 
> planner uses.
> 
>> As for upgrading;
>>
>> a) I am trying to find a way around the dump/reload. I am doing it as 
>> a "last resort" only.
> Agreed - it is the last resort. But since you were doing it I was just 
> suggesting that you could combine with a upgrade and get more benefits.
>> b) I want to keep the version in CentOS' repo.
> Depends on reasoning. If you absolutely require a fully vanilla 
> particular version of CentOS for some reason then fine. But telling 
> CentOS to use the PostgreSQL Development Group pre-built releases for 
> CentOS is a very easy one-time process (it's what I do on my CentOS 
> machines). From memory (but read to end for warnings):
> 
> Download the setup rpm:
> wget http://yum.pgsqlrpms.org/reporpms/8.4/pgdg-centos-8.4-1.noarch.rpm
> 
> Install it:
> rpm -i pgdg-centos-8.4-1.noarch.rpm
> 
> Note: This does not install PostgreSQL - it just updates your repository 
> list to add the repository containing PostgreSQL binaries. Now make sure 
> that you get your updates from PostgreSQL, not CentOS:
> 
> Edit /etc/yum.repos.d/CentOS-Base.repo and add "exclude=postgresql*" to 
> the  [base] and [updates] sections.
> 
> Now you can use "yum" as normal and you will get PostgreSQL 8.4 and 
> updates thereto rather than using 8.1.
> 
> BUT!! I have only done this on new installs. I have not tried it on an 
> already running machine. As always, test first on a dev machine and do 
> your pre-update dump using the new version of the pg_dump utilities, not 
> the old ones.
> 
> Cheers,
> Steve
> 
>>
>>
>> I'd not tried simply updating the stats via ANALYZE... I'll keep an 
>> eye on performance and if it starts to slip again, I will run ANALYZE 
>> and see if that helps. If there is a way to run ANALYZE against a 
>> query that I am missing, please let me know.
> If you stick with 8.1x, you may want to edit postgresql.conf and change 
> default_statistics_target to 100 if it is still at the previous default 
> of 10. 100 is the new default setting as testing indicates that it tends 
> to yield better query plans with minimal additional overhead.
> 
> Cheers,
> Steve

I think for now, I will stick with 8.1, but I will certainly try out 
your repo edit above on a test machine and see how that works out. I am 
always reticent to change something as fundamental as postgres without 
"good reason". I guess I am a fan of "if it ain't broke...". :)

As for the edit to postgresql.conf, I've made the change. Thanks for the 
detailed input on that.

Madi