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