From: Carlo Stonebanks <stonec.register@sympatico.ca>
To: 'Jeff Janes' <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Are bitmap index scans slow to start?
Date: Mon, 25 Feb 2013 12:04:19 -0500
Message-ID: <010601ce137a$266b7f00$73427d00$@sympatico.ca> (raw)
In-Reply-To: <CAMkU=1z-UOitt2mtnjn=XQ8kk2cL2nAnd2MfycLwju__qC49wQ@mail.gmail.com>
References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca>
<001501ce1054$7b518380$71f48a80$@sympatico.ca>
<CAMkU=1yG884Nak4zPoNHwDGpC7w2nrKH6_xztPw4b7P9am0wdg@mail.gmail.com>
<B6F6FD62F2624C4C9916AC0175D56D8801B17F@jenmbs01.ad.intershop.net>
<006201ce1125$2c554150$84ffc3f0$@sympatico.ca>
<CAPmjWd1On_uAp6suqYoHuh3ksKD0xn6tOhOM_Z2vaweO-vexSA@mail.gmail.com>
<009001ce1137$f9c31bf0$ed4953d0$@sympatico.ca>
<CAMkU=1z-UOitt2mtnjn=XQ8kk2cL2nAnd2MfycLwju__qC49wQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi Jeff, thanks for the insight.
<< And then the next question would be, once they are in the cache, why
don't they stay there? For that you would have to know what other types of
activities are going on that might be driving the data out of the cache.
>>
To give you an idea of the activity level, each physical machine hosts
multiple DB's with the same structure - one DB per client.
We run automated ETL processes which digests client feeds (E) normalizes
them (T) and then stores them in our DB (L).
Looking at the stats from our audit log, the average feed load is 4 hours,
divided up into 14 client sessions. Each session averages about 50 write
(update, insert, no deletes) operations per second, representing 700 write
operations per second. The ratio of reads per write is pretty high as the
system goes through the transformation process.
Since I don't know how this compares to other PG installations, the question
of using periodic REINDEX and CLUSTER brings up these questions:
1) Because we are hosting multiple DB's, what is the impact on OS and
disk caches?
2) Is there an automated CLUSTER and REINDEX strategy that will not
interfere with normal operations?
3) By PG standards, is this a busy DB - and does explain why the
general caches expire?
Thanks,
Carlo
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: stonec.register@sympatico.ca, jeff.janes@gmail.com
Subject: Re: Are bitmap index scans slow to start?
In-Reply-To: <010601ce137a$266b7f00$73427d00$@sympatico.ca>
* 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