From: Madison Kelly <linux@alteeve.com>
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB is slow until DB is reloaded
Date: Mon, 04 Jan 2010 16:54:19 -0500
Message-ID: <4B42638B.4000103@alteeve.com> (raw)
In-Reply-To: <4B4260E6.7050502@pinpointresearch.com>
References: <4B423D22.7040803@alteeve.com>
<4B424215.70605@pinpointresearch.com>
<4B424FF6.40207@alteeve.com>
<4B4260E6.7050502@pinpointresearch.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: linux@alteeve.com, scrawford@pinpointresearch.com
Subject: Re: DB is slow until DB is reloaded
In-Reply-To: <4B42638B.4000103@alteeve.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox