From: Joe McClintock <joe.mcclintock@antics.com>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Database Optimization and Peformance
Date: Wed, 30 Aug 2006 15:13:49 -0700
Message-ID: <44F60D9D.7080309@antics.com> (raw)
In-Reply-To: <Pine.LNX.4.64.0608301141330.23923@glacier.frostconsultingllc.com>
References: <44F5D91E.8000806@antics.com>
<Pine.LNX.4.64.0608301141330.23923@glacier.frostconsultingllc.com>
Thanks Jeff,
Obviously I have a bit to learn about Postgres admin.
> How are your FSM settings in the conf file?
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?
>When you loaded the new data did you delete or update old data or was
it just a straight insert?
As far as the database load went, it was a straight insert, no updates
were made.
>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
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_9187147_index" now contains 0 row versions in 1 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: free space map: 1000 relations, 1111 pages stored; 19648 total
pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 223 kB
shared memory.
VACUUM
erm_slow=>
Thanks again for your quick response
Regards
--Joe
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?
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: joe.mcclintock@antics.com, jeff@frostconsultingllc.com
Subject: Re: Database Optimization and Peformance
In-Reply-To: <44F60D9D.7080309@antics.com>
References: <44F5D91E.8000806@antics.com> <Pine.LNX.4.64.0608301141330.23923@glacier.frostconsultingllc.com> <44F60D9D.7080309@antics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox