From: Jim C. Nasby <jnasby@pervasive.com>
To: PostgreSQL <martin@portant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: What gets cached?
Date: Thu, 27 Oct 2005 17:48:56 -0500
Message-ID: <20051027224855.GS63747@pervasive.com> (raw)
In-Reply-To: <djre16$uqq$1@news.hub.org>
References: <pan.2005.10.21.12.34.29.712700@portant.com>
<1129931087.19971.12.camel@localhost.localdomain>
<33c6269f0510240809k47f91a71t387a0b5ab0339f4c@mail.gmail.com>
<20051024153248.GA24601@samfundet.no>
<djre16$uqq$1@news.hub.org>
Did the patch that allows multiple seqscans to piggyback on each other
make it into 8.1? It might help in this situation.
BTW, if a query requires loading more than a few percent of an index
PostgreSQL will usually go with a sequential scan instead. You should
check explain/explain analyze on your queries and see what's actually
happening. If you've got stats turned on you can also look at
pg_stat_user_indexes to get a better idea of what indexes are and aren't
being used.
On Thu, Oct 27, 2005 at 03:41:10PM -0500, PostgreSQL wrote:
> Thank each of you for your replies. I'm just beginning to understand the
> scope of my opportunities.
>
> Someone (I apologize, I forgot who) recently posted this query:
> SELECT oid::regclass, reltuples, relpages
> FROM pg_class
> ORDER BY 3 DESC
>
> Though the application is a relatively low-volume TP system, it is
> structured a lot like a data warehouse with one primary table that
> everything else hangs off. What the query above shows is that my largest
> table, at 34 million rows, takes almost 1.4 million pages or 10+ Gb if my
> math is good. The same table has 14 indexes, totaling another 12Gb. All
> this is running on a box with 4Gb of memory.
>
> So what I believe I see happening is that almost every query is clearing out
> memory to load the particular index it needs. Hence my "first queries are
> the fastest" observation at the beginning of this thread.
>
> There are certainly design improvements to be done, but I've already started
> the process of getting the memory increased on our production db server. We
> are btw running 8.1 beta 3.
>
> ""Steinar H. Gunderson"" <sgunderson@bigfoot.com> wrote in message
> news:20051024153248.GA24601@samfundet.no...
> > On Mon, Oct 24, 2005 at 11:09:55AM -0400, Alex Turner wrote:
> >> Just to play devils advocate here for as second, but if we have an
> >> algorithm
> >> that is substational better than just plain old LRU, which is what I
> >> believe
> >> the kernel is going to use to cache pages (I'm no kernel hacker), then
> >> why
> >> don't we apply that and have a significantly larger page cache a la
> >> Oracle?
> >
> > There have (AFAIK) been reports of setting huge amounts of shared_buffers
> > (close to the total amount of RAM) performing much better in 8.1 than in
> > earlier versions, so this might actually be okay these days.
> >
> > I haven't heard of anybody reporting increase setting such values, though.
> >
> > /* Steinar */
> > --
> > Homepage: http://www.sesse.net/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, martin@portant.com
Subject: Re: What gets cached?
In-Reply-To: <20051027224855.GS63747@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox