Re: Database Optimization and Peformance

I'm an ex-ora guy, also.  We will see more and more of us here
as oracle corp becomes less cooperative in their pricing demands.
What I'm saying here is directly from my oracle performance tuning
history and Milsap and Holt.

With all due respect (seriously), I'm playing catch up here with postgresql,
and I consider any help a benefit.  However, during the 1990s it was very popular
to "tune" oracle db performance as you have described.  People who
know cool knobs to turn were considered wizards and knocked down
big consulting bucks in this process:

see slow app
change stuff (how did they know about that knob?)
run app again
repeat as needed

I'm not saying what you advise will not help performance.
Lots of ad hoc kinds of changes "might" be helpful.  However, we
learned years ago from great db engineering minds a better method.
The wisdom goes something like this:

"Reliable problem diagnosis cannot proceed unless the data collection
phase produces response time data for exactly the right time scope
and exactly the right action scope." - Millsap, Holt

and

"If you can't measure it, you can't manage it. - Peter Drucker

That said, I've yet to find a trace tool within pg that provides system
wide response time data, ala the oracle wait interface.  I don't
know if the internal instrumentation is included in the pg kernel.
A. Kretschmer was kind enough to point to "explain analyze," that is
like ora explain plan including "total elapsed time expended within each
plan node."  Better than the nothing I had last week.  Far from
what I've been spoiled by.

Perhaps there is some way to hook strace into the db kernel, but
the results,  how you would profile that is way beyond me.
If on solaris 10, dtrace has great os level instrumentation
that might come in handy.

One underlying concept in the method is:

"Work first to reduce the biggest response time component of a business' most
important user action."  - Millsap, Holt

Any help on how to accomplish the detail of this method would be greatly
appreciated.

Thanks.


On Wed, Aug 30, 2006 at 11:45:06AM -0700, 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     <jeff@frostconsultingllc.com>
> Frost Consulting, LLC     http://www.frostconsultingllc.com/
> Phone: 650-780-7908    FAX: 650-649-1954
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match