Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UA1Ui-00078u-LR for pgsql-performance@arkaria.postgresql.org; Mon, 25 Feb 2013 17:05:40 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UA1Ui-00082l-4K for pgsql-performance@arkaria.postgresql.org; Mon, 25 Feb 2013 17:05:40 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UA1Ug-00082g-Ls for pgsql-performance@postgresql.org; Mon, 25 Feb 2013 17:05:38 +0000 Received: from toroondcbmts07-srv.bellnexxia.net ([207.236.237.41]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UA1Uc-0003DU-Op for pgsql-performance@postgresql.org; Mon, 25 Feb 2013 17:05:37 +0000 Received: from toip57-bus.srvr.bell.ca ([67.69.240.184]) by toroondcbmts07-srv.bellnexxia.net (InterMail vM.8.00.01.00 201-2244-105-20090324) with ESMTP id <20130225170532.TNVU15122.toroondcbmts07-srv.bellnexxia.net@toip57-bus.srvr.bell.ca>; Mon, 25 Feb 2013 12:05:32 -0500 X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AhEFAHaWK1FGMVM9/2dsb2JhbABFgkODI7tvgQUWc4IgAQUIAiNMDQMCBgMNORk+AgQeBYgDt0OHeI8OB4NAA4Ehh0iFK45Bik2DJQ X-IronPort-AV: E=Sophos;i="4.84,736,1355115600"; d="scan'208,217";a="305600301" Received: from unknown (HELO CAPRICA) ([70.49.83.61]) by toip57-bus.srvr.bell.ca with ESMTP; 25 Feb 2013 12:04:22 -0500 From: "Carlo Stonebanks" To: "'Jeff Janes'" Cc: References: <005701ce0fb5$6f841f30$4e8c5d90$@sympatico.ca> <001501ce1054$7b518380$71f48a80$@sympatico.ca> <006201ce1125$2c554150$84ffc3f0$@sympatico.ca> <009001ce1137$f9c31bf0$ed4953d0$@sympatico.ca> In-Reply-To: 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> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0107_01CE1350.3D963A50" X-Mailer: Microsoft Outlook 14.0 Thread-Index: AQHaXprYpEPEIaXdLmMZOCaAwfiQhwMAB23aAo8fOP4CRcCoxQGZrFXnAfFz5MABrTXs1wKK8Tywl/RX9VA= Content-Language: en-ca X-Pg-Spam-Score: -0.5 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0107_01CE1350.3D963A50 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0107_01CE1350.3D963A50 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

------=_NextPart_000_0107_01CE1350.3D963A50--