Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 5F3D19FB29A for ; Mon, 25 Sep 2006 17:34:03 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 05910-04 for ; Mon, 25 Sep 2006 20:33:58 +0000 (UTC) X-Greylist: delayed 00:24:18.561081 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id E49289FB378 for ; Mon, 25 Sep 2006 17:33:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from gw.sandvine.com (sandvine.com [199.243.201.138]) by svr4.postgresql.org (Postfix) with ESMTP id EF5DB5AF8E1 for ; Mon, 25 Sep 2006 20:09:36 +0000 (GMT) Received: from mailserver.sandvine.com ([192.168.1.10]) by gw.sandvine.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 25 Sep 2006 16:09:34 -0400 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6E0DE.84CB47FA" Subject: Decreasing BLKSZ Date: Mon, 25 Sep 2006 16:09:33 -0400 Message-ID: <2BCEB9A37A4D354AA276774EE13FB8C201130680@mailserver.sandvine.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Decreasing BLKSZ Thread-Index: Acbg3oSRf3yRG5vNQvWLB69OO7nL7g== From: "Marc Morin" To: X-OriginalArrivalTime: 25 Sep 2006 20:09:34.0747 (UTC) FILETIME=[85200EB0:01C6E0DE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200609/330 X-Sequence-Number: 21016 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6E0DE.84CB47FA Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Our application has a number of inserters posting rows of network statistics into a database. This is occuring continously. The following is an example of a stats table (simplified but maintains key concepts). =20 =20 CREATE TABLE stats=20 ( logtime timestamptz, key int, stat1 bigint, stat2 bigint, stat3 bigint, PRIMARY KEY (key,logtime) ); CREATE INDEX x ON stats(logtime); =20 There are on the order of 1M unique values for "key" and a new row for each key value will be inserted say every 15 minutes. These rows are divided up between a number of different inserting elements, but that isn't relevant. =20 The problem is, the insert pattern has low correlation with the (key,logtime) index. In this case, would need >1M blocks in my shared_buffer space to prevent a read-modify-write type of pattern happening during the inserts (given a large enough database). =20 Wondering about lowering the BLKSZ value so that the total working set of blocks required can be maintained in my shared buffers. Our database only has 8G of memory and likely need to reduce BLKSZ to 512.... =20 Any comment on other affects or gotchas with lowering the size of BLKSZ? Currently, our database is thrashing its cache of blocks we we're getting only ~100 inserts/second, every insert results in a evict-read-modify operation. =20 =20 Ideally, like to keep the entire working set of blocks in memory across insert periods so that the i/o looks more like write full blocks.... =20 Thanks Marc =20 =20 ------_=_NextPart_001_01C6E0DE.84CB47FA Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Our = application has=20 a number of inserters posting rows of network statistics into a = database. =20 This is occuring continously.  The following is an example of a = stats table=20 (simplified but maintains key concepts).
 
 
CREATE = TABLE stats=20
(
      logtime = timestamptz,
      key    =20 int,
      stat1  =20 bigint,
      stat2  =20 bigint,
      stat3  =20 bigint,
      PRIMARY KEY=20 (key,logtime)
);
CREATE = INDEX x ON=20 stats(logtime);
 
There = are on the=20 order of 1M unique values for "key" and a new row for each key value = will be=20 inserted say every 15 minutes.  These rows are divided up between a = number=20 of different inserting elements, but that isn't = relevant.
 
The = problem is, the=20 insert pattern has low correlation with the (key,logtime) = index.   In=20 this case, would need >1M blocks in my shared_buffer space to prevent = a=20 read-modify-write type of pattern happening during the inserts (given a = large=20 enough database).
 
Wondering about=20 lowering the BLKSZ value so that the total working set of blocks = required can be=20 maintained in my shared buffers.  Our database only has 8G of = memory and=20 likely need to reduce BLKSZ to 512....
 
Any = comment on other=20 affects or gotchas with lowering the size of BLKSZ?  Currently, our = database is thrashing its cache of blocks we we're getting only ~100=20 inserts/second, every insert results in a evict-read-modify=20 operation.
 
 
Ideally, like to=20 keep the entire working set of blocks in memory across insert periods so = that=20 the i/o looks more like write full blocks....
 
Thanks
Marc
 
 
------_=_NextPart_001_01C6E0DE.84CB47FA--