Re: DB is slow until DB is reloaded - Mailing list pgsql-performance

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