Re: DB is slow until DB is reloaded

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