On Wed, 30 Aug 2006, Joe McClintock wrote:
Looking at the postgresql.config the lines for max_fsm_pages and
max_fsm_relations are commented out so they have the default 20000 pages and
1000 relations. Should we up these defaults?
From the vacuum verbose output below, it looks like you're on the very edge of
those settings, but not yet over of course I presume that was just for this
DB, so if you have more than one, you might be over. You probably want to
double them as they don't take that many resources.
Can you run VACUUM VERBOSE and send us the last 10 or so lines of output?
I ran VACUUM FULL VERBOSE on the old production DB and I see we need to run
VACUUM FULL on a regular basis. I took about hour and a half to complete but
afterwards the problem tables and queries are running well. Query time is
back to 1.08 mill sec.
Here are the last 10 lines of VACUUM FULL VERBOSE
Actually, you should not have to run VACUUM FULL ever if you vacuum often
enough, that's why I suggest you use pg_autovacuum. You can find it in the
contrib modules for 8.0.x or builtin to the 8.1.x server.
Jeff Frost wrote:
On Wed, 30 Aug 2006, Joe McClintock wrote:
I ran a vacuum, analyze and reindex on the database with no change in
performance, query time was still 37+ sec, a little worse. On our test
system I found that a db_dump from production and then restore brought the
database back to full performance. So in desperation I shut down the
production application, backed up the production database, rename the
production db, create a new empty production db and restored the
production backup to the empty db. After a successful db restore and
restart of the web application, everything was then up and running like a
top.
Joe,
I would guess that since the dump/restore yielded good performance once
again, a VACUUM FULL would have also fixed the problem. How are your FSM
settings in the conf file? Can you run VACUUM VERBOSE and send us the last
10 or so lines of output?
A good article on FSM settings can be found here:
http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10087&cNode=5K1C3W
You probably should consider setting up autovacuum and definitely should
upgrade to at least 8.0.8 if not 8.1.4 when you get the chance.
When you loaded the new data did you delete or update old data or was it
just a straight insert?
--
Jeff Frost, Owner <[EMAIL PROTECTED]>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
