Re: Memory usage - indexes - Mailing list pgsql-performance

Tobias,

Consult pg_statio_user_indexes to see which indexes have been used and how much.  Indexes with comparitively low usages
ratesaren't helping you much and are candidates for elimination.  Also, partitioning large tables can help, since the
indexeson each partition are smaller than one huge index on the original table. 

Good luck!

Bob Lunney

--- On Thu, 9/23/10, Tobias Brox <tobixen@gmail.com> wrote:

> From: Tobias Brox <tobixen@gmail.com>
> Subject: [PERFORM] Memory usage - indexes
> To: pgsql-performance@postgresql.org
> Date: Thursday, September 23, 2010, 5:50 PM
> We've come to a tipping point with
> one of our database servers, it's
> generally quite loaded but up until recently it was
> handling the load
> well - but now we're seeing that it struggles to process
> all the
> selects fast enough.  Sometimes we're observing some
> weird lock-like
> behaviour (see my other post on that), but most of the time
> the
> database server is just not capable of handling the load
> fast enough
> (causing the queries to pile up in the
> pg_stat_activity-view).
>
> My main hypothesis is that all the important indexes would
> fit snuggly
> into the memory before, and now they don't.  We'll
> eventually get the
> server moved over to new and improved hardware, but while
> waiting for
> that to happen we need to do focus on reducing the memory
> footprint of
> the database.  I have some general questions now ...
>
> 1) Are there any good ways to verify my hypothesis? 
> Some months ago I
> thought of running some small memory-gobbling program on
> the database
> server just to see how much memory I could remove before we
> would see
> indications of the database being overloaded.  It
> seems a bit radical,
> but I think the information learned from such an experiment
> would be
> very useful ... and we never managed to set up any testing
> environment
> that faithfully replicates production traffic. 
> Anyway, it's sort of
> too late now that we're already observing performance
> problems even
> without the memory gobbling script running.
>
> 2) I've seen it discussed earlier on this list ...
> shared_buffers vs
> OS caches.  Some claims that it has very little effect
> to adjust the
> size of the shared buffers.  Anyway, isn't it a risk
> that memory is
> wasted because important data is stored both in the OS
> cache and the
> shared buffers?  What would happen if using almost all
> the available
> memory for shared buffers?  Or turn it down to a bare
> minimum and let
> the OS do almost all the cache handling?
>
> 3) We're discussing to drop some overlapping indexes ...
> i.e. to drop
> one out of two indexes looking like this:
>
> some_table(a)
> some_table(a,b)
>
> Would the query "select * from some_table where a=?" run
> slower if we
> drop the first index?  Significantly?
>
> (in our situation I found that the number of distinct b's
> for each a
> is low and that the usage stats on the second index is
> quite low
> compared with the first one, so I think we'll drop the
> second index).
>
> 4) We're discussing to drop other indexes.  Does it
> make sense at all
> as long as we're not experiencing problems with
> inserts/updates?  I
> suppose that if the index isn't used it will remain on disk
> and won't
> affect the memory usage ... but what if the index is rarely
> used ...
> wouldn't it be better to do a seqscan on a table that is
> frequently
> accessed and mostly in memory than to consult an index that
> is stored
> on the disk?
>
> Sorry for all the stupid questions ;-)
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>