Re: DB is slow until DB is reloaded

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux