Re: Query help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks for the response kevin.

DB size is about 30G. Bloat could have been due to recent load testing
that was done. Autovaccum wasn't aggressive enough to catch up with load
testing. I will rebuild those indexes if possible reload the table
itself as they are bloated too.

Sure I will collect necessary stats on the next occurrence of the slow
query. 

Stalin

-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@xxxxxxxxxxxx] 
Sent: Tuesday, August 04, 2009 8:57 AM
To: Subbiah Stalin-XCGF84; pgsql-performance@xxxxxxxxxxxxxx
Subject: RE:  Query help

"Subbiah Stalin-XCGF84" <SSubbiah@xxxxxxxxxxxx> wrote:
 
> Server has 32G memory and it's a dedicated to run PG and no other 
> application is sharing this database.
 
It's not likely to help with this particular problem, but it's generally
best to start from a position of letting the optimizer know what it's
really got for resources.  An effective cache size of somewhere around
30GB would probably be best here.
 
> Given the nature of the ix_objects_type_lastmodified index, wondering 
> if the index requires rebuilt. I tested rebuilding it in another db, 
> and it came to 2500 pages as opposed to 38640 pages.
 
That's pretty serious bloat.  Any idea how that happened?  Have you had
long running database transaction which might have prevented normal
maintenance from working?  If not, you may need more aggressive settings
for autovacuum.  Anyway, sure, try this with the index rebuilt.  If you
don't want downtime, use CREATE INDEX CONCURRENTLY and then drop the old
index.  (You could then rename the new index to match the old, if
needed.)
 
> The puzzle being why the same query with same filters, runs most of 
> times faster but at times runs 5+ mintues and it switches back to fast

> mode.
 
It is likely either that something has pushed the relevant data out of
cache before the slow runs, or there is blocking.  How big is this
database?  Can you get a list of pg_stat_activity and pg_locks during an
episode of slow run time?
 
> If it had used a different execution plan than the above, how do I 
> list all execution plans executed for a given SQL.
 
It's unlikely that the slow runs are because of a different plan being
chosen.  I was wondering if a better plan might be available, but this
one looks pretty good with your current indexes.  I can think of an
indexing change or two which *might* cause the optimizer to pick a
different plan, but that is far from certain, and without knowing the
cause of the occasional slow runs, it's hard to be sure that the new
plan wouldn't get stalled for the same reasons.
 
If it's possible to gather more data during an episode of a slow run,
particularly the pg_stat_activity and pg_locks lists, run as the
database superuser, it would help pin down the cause.  A vmstat during
such an episode, to compare to a "normal" one, might also be
instructive.
 
-Kevin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux