Re: How to debug performance problems

Поиск
Список
Период
Сортировка
От Ray Stell
Тема Re: How to debug performance problems
Дата
Msg-id 20070221154520.GA25149@cns.vt.edu
обсуждение исходный текст
Ответ на Re: How to debug performance problems  ("Craig A. James" <cjames@modgraph-usa.com>)
Ответы Re: How to debug performance problems  ("Craig A. James" <cjames@modgraph-usa.com>)
Re: How to debug performance problems  (Mark Stosberg <mark@summersault.com>)
Список pgsql-performance
I'd like to have a toolbox prepared for when performance goes south.
I'm clueless.  Would someone mind providing some detail about how to
measure these four items Craig listed:

1. The first thing is to find out which query is taking a lot of time.

2. A long-running transaction keeps vacuum from working.

3. A table grows just enough to pass a threshold in the
   planner and a drastically different plan is generated.

4. An index has become bloated and/or corrupted, and you
   need to run the REINDEX command.

Thx.





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







--

On Mon, Feb 19, 2007 at 10:02:46AM -0800, Craig A. James wrote:
> Andreas Tille wrote:
> >My web application was running fine for years without any problem
> >and the performance was satisfying.  Some months ago I added a
> >table containing 4500000 data rows ...
> >
> >Since about two weeks the application became *drastically* slower
> >and I urgently have to bring back the old performance.  As I said
> >I'm talking about functions accessing tables that did not increased
> >over several years and should behave more or less the same.
>
> Don't assume that the big table you added is the source of the problem.  It
> might be, but more likely it's something else entirely.  You indicated that
> the problem didn't coincide with creating the large table.
>
> There are a number of recurring themes on this discussion group:
>
>  * A long-running transaction keeps vacuum from working.
>
>  * A table grows just enough to pass a threshold in the
>    planner and a drastically different plan is generated.
>
>  * An index has become bloated and/or corrupted, and you
>    need to run the REINDEX command.
>
> And several other common problems.
>
> The first thing is to find out which query is taking a lot of time.  I'm no
> expert, but there have been several explanations on this forum recently how
> to find your top time-consuming queries.  Once you find them, then EXPLAIN
> ANALYZE should get you started
> Craig
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
You have no chance to survive make your time.

В списке pgsql-performance по дате отправления:

Предыдущее
От: Bill Moran
Дата:
Сообщение: Re: Postgres performance Linux vs FreeBSD
Следующее
От: "Craig A. James"
Дата:
Сообщение: Re: How to debug performance problems