DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Q7fzzuB1US9r9Xg6QWtih+W/QEawxg/YtF9Ur3nGB9wEbZ2vFYaOY5LfJGDPGgMP9Mzt/m7O+Phb8LFpGaOmvPN4k1xg/b/snQ6NWe1G0gzK8D2kJ1kMxJ/98Tf2fXdylX/EuksVWSawSTqxTMSdIAgzqFbYpzYJHZrmRfB8oFQ=;
Message-ID: <865426.21884.qm@web39701.mail.mud.yahoo.com>
Date: Fri, 24 Sep 2010 09:23:49 -0700 (PDT)
From: Bob Lunney <bob_lunney@yahoo.com>
Subject: Re: Memory usage - indexes
To: pgsql-performance@postgresql.org, Tobias Brox <tobixen@gmail.com>
In-Reply-To: <AANLkTimd0Y7KhnwkSXz4FYjsxC8ocDeRRH=PXvU1a3vv@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Tobias,=0A=0AConsult pg_statio_user_indexes to see which indexes have been =
used and how much.  Indexes with comparitively low usages rates aren't help=
ing you much and are candidates for elimination.  Also, partitioning large =
tables can help, since the indexes on each partition are smaller than one h=
uge index on the original table.=0A=0AGood luck!=0A=0ABob Lunney=0A=0A--- O=
n Thu, 9/23/10, Tobias Brox <tobixen@gmail.com> wrote:=0A=0A> From: Tobias =
Brox <tobixen@gmail.com>=0A> Subject: [PERFORM] Memory usage - indexes=0A> =
To: pgsql-performance@postgresql.org=0A> Date: Thursday, September 23, 2010=
, 5:50 PM=0A> We've come to a tipping point with=0A> one of our database se=
rvers, it's=0A> generally quite loaded but up until recently it was=0A> han=
dling the load=0A> well - but now we're seeing that it struggles to process=
=0A> all the=0A> selects fast enough.=A0 Sometimes we're observing some=0A>=
 weird lock-like=0A> behaviour (see my other post on that), but most of the=
 time=0A> the=0A> database server is just not capable of handling the load=
=0A> fast enough=0A> (causing the queries to pile up in the=0A> pg_stat_act=
ivity-view).=0A> =0A> My main hypothesis is that all the important indexes =
would=0A> fit snuggly=0A> into the memory before, and now they don't.=A0 We=
'll=0A> eventually get the=0A> server moved over to new and improved hardwa=
re, but while=0A> waiting for=0A> that to happen we need to do focus on red=
ucing the memory=0A> footprint of=0A> the database.=A0 I have some general =
questions now ...=0A> =0A> 1) Are there any good ways to verify my hypothes=
is?=A0=0A> Some months ago I=0A> thought of running some small memory-gobbl=
ing program on=0A> the database=0A> server just to see how much memory I co=
uld remove before we=0A> would see=0A> indications of the database being ov=
erloaded.=A0 It=0A> seems a bit radical,=0A> but I think the information le=
arned from such an experiment=0A> would be=0A> very useful ... and we never=
 managed to set up any testing=0A> environment=0A> that faithfully replicat=
es production traffic.=A0=0A> Anyway, it's sort of=0A> too late now that we=
're already observing performance=0A> problems even=0A> without the memory =
gobbling script running.=0A> =0A> 2) I've seen it discussed earlier on this=
 list ...=0A> shared_buffers vs=0A> OS caches.=A0 Some claims that it has v=
ery little effect=0A> to adjust the=0A> size of the shared buffers.=A0 Anyw=
ay, isn't it a risk=0A> that memory is=0A> wasted because important data is=
 stored both in the OS=0A> cache and the=0A> shared buffers?=A0 What would =
happen if using almost all=0A> the available=0A> memory for shared buffers?=
=A0 Or turn it down to a bare=0A> minimum and let=0A> the OS do almost all =
the cache handling?=0A> =0A> 3) We're discussing to drop some overlapping i=
ndexes ...=0A> i.e. to drop=0A> one out of two indexes looking like this:=
=0A> =0A> some_table(a)=0A> some_table(a,b)=0A> =0A> Would the query "selec=
t * from some_table where a=3D?" run=0A> slower if we=0A> drop the first in=
dex?=A0 Significantly?=0A> =0A> (in our situation I found that the number o=
f distinct b's=0A> for each a=0A> is low and that the usage stats on the se=
cond index is=0A> quite low=0A> compared with the first one, so I think we'=
ll drop the=0A> second index).=0A> =0A> 4) We're discussing to drop other i=
ndexes.=A0 Does it=0A> make sense at all=0A> as long as we're not experienc=
ing problems with=0A> inserts/updates?=A0 I=0A> suppose that if the index i=
sn't used it will remain on disk=0A> and won't=0A> affect the memory usage =
... but what if the index is rarely=0A> used ...=0A> wouldn't it be better =
to do a seqscan on a table that is=0A> frequently=0A> accessed and mostly i=
n memory than to consult an index that=0A> is stored=0A> on the disk?=0A> =
=0A> Sorry for all the stupid questions ;-)=0A> =0A> -- =0A> Sent via pgsql=
-performance mailing list (pgsql-performance@postgresql.org)=0A> To make ch=
anges to your subscription:=0A> http://www.postgresql.org/mailpref/pgsql-pe=
rformance=0A> =0A=0A=0A