You really could do with updating that; 8.1.4 is very old. 8.1.15 is
the latest in the 8.1 series and has lots of bug fixes.
Will update increase speed ?
Server is running for approx 4 years now and I havent encountered any bugs.
Db size is 862 MB
Bigger tables:
1 1214 pg_shdepend 775 MB
2 1232 pg_shdepend_depender_index 285 MB
5 1233 pg_shdepend_reference_index 156 MB
those look scary, scary big to me. Have you been running without
autovacuum for a while and creating *lots* of tables or something?
Log file shows many messages
autovacuum: processing database "mydb" every day.
So I expect it is running.
After VACUUM ANALYZE I ran
VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb
after that I got
1 1214 pg_shdepend 440 MB
2 1232 pg_shdepend_depender_index 285 MB
3 1233 pg_shdepend_reference_index 155 MB
4 19701 rid 103 MB
5 19301 bilkaib 93 MB
6 19335 dok 46 MB
Your database looks quite bloated; if you can afford the downtime I'd be
tempted to do a full backup and restore. This will reduce bloat a lot
and also provide a good opportunity to update PG. The good thing about
doing it from a restore is that you don't have to go through REINDEXing
everything by hand and potentially miss lots of things out. If things
are going to shrink a lot, restoring is normally quicker as well.
A good way to test would be to do a backup and see how big the resulting
file is. I'd expect the database to be three or four times the size of
the plain text backup (depending on table design and index use it can
vary quite a bit either way), so if the dump is less than a hundred MB
you're probably better off doing a restore.
I have acces to this db only from port 5432
Thus Text backup takes a lot of time and server upgrade is not possible.
I ran
VACUUM FULL; REINDEX DATABASE mydb;REINDEX SYSTEM mydb
and hope this produces the same results and backup/restore.
Andrus.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
