Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bouYp-0001ts-VK for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 15:44:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bouYp-0000K6-EY for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 15:44:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bouXA-0006o2-8E for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 15:43:04 +0000 Received: from aibo.runbox.com ([91.220.196.211]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bouX6-0003cd-SR for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 15:43:03 +0000 Received: from [10.9.9.210] (helo=mailfront10.runbox.com) by bars.runbox.com with esmtp (Exim 4.71) (envelope-from ) id 1bouX5-0002AR-2G for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 17:42:59 +0200 Received: from rrcs-74-87-24-164.west.biz.rr.com ([74.87.24.164] helo=seasyslap4) by mailfront10.runbox.com with esmtpsa (uid:561468 ) (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.82) id 1bouX1-0003BJ-7E for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 17:42:55 +0200 From: "Mike Sofen" To: "'pgsql-performa.'" References: <002d01d218d1$41bb2160$c5316420$@runbox.com> In-Reply-To: <002d01d218d1$41bb2160$c5316420$@runbox.com> Subject: Re: Millions of tables Date: Tue, 27 Sep 2016 08:42:44 -0700 Message-ID: <003401d218d5$cb900640$62b012c0$@runbox.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0035_01D2189B.1F32B4E0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIGoPHtU7IEjWZl2VDwhNKwCwLMKALCbGTnAfYNMXKf/qTsoA== Content-Language: en-us X-Pg-Spam-Score: -5.6 (-----) 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_0035_01D2189B.1F32B4E0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable From: Mike Sofen Sent: Tuesday, September 27, 2016 8:10 AM From: Greg Spiegelberg Sent: Monday, September 26, 2016 7:25 AM I've gotten more responses than anticipated and have answered some = questions and gotten some insight but my challenge again is what should = I capture along the way to prove or disprove this storage pattern? = Alternatives to the storage pattern aside, I need ideas to test rig, = capture metrics and suggestions to tune it. =20 In the next 24 hours, I will be sending ~1 trillion records to the test = database. Because of time to set up, I'd rather have things set up = properly the first go. =20 Thanks! -Greg=20 --------------------- Greg, I ran another quick test on a wider table than you=E2=80=99ve = described, but this time with 80 million rows, with core counts, ram and = ssd storage similar to what you=E2=80=99d have on that AWS EC2 instance. = This table had 7 columns (3 integers, 3 text, 1 timestamptz) with an = average width of 157 chars, one btree index on the pk int column. Using = explain analyze, I picked one id value out of the 80m and ran a select * = where id =3D x. It did an index scan, had a planning time of 0.077ms, = and an execution time of 0.254 seconds. I ran the query for a variety = of widely spaced values (so the data was uncached) and the timing never = changed. This has been mirroring my general experience with PG =E2=80=93 = very fast reads on indexed queries.=20 =20 Summary: I think your buckets can be WAY bigger than you are = envisioning for the simple table design you=E2=80=99ve described. = I=E2=80=99m betting you can easily do 500 million rows per bucket before = approaching anything close to the 30ms max query time. =20 Mike Sofen (Synthetic Genomics) =20 Totally typo=E2=80=99d the execution time: it was 0.254 MILLISECONDS, = not SECONDS. Thus my comment about going up 10x in bucket size instead = of appearing to be right at the limit. Sorry! =20 Mike ------=_NextPart_000_0035_01D2189B.1F32B4E0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

From:<= /b> = Mike Sofen=C2=A0=C2=A0 Sent: Tuesday, September 27, 2016 8:10 = AM

From:<= /b> = Greg Spiegelberg   Sent: Monday, September 26, 2016 = 7:25 AM
I've gotten more responses than anticipated and have = answered some questions and gotten some insight but my challenge again = is what should I capture along the way to prove or disprove this storage = pattern?  Alternatives to the storage pattern aside, I need ideas = to test rig, capture metrics and suggestions to tune = it.

 

In the next 24 hours, I = will be sending ~1 trillion records to the test database.  Because = of time to set up, I'd rather have things set up properly the first = go.

 

Thanks!

-Greg 

---------------------

Greg, I ran = another quick test on a wider table than you=E2=80=99ve described, but = this time with 80 million rows, with core counts, ram and ssd storage = similar to what you=E2=80=99d have on that AWS EC2 instance.  This = table had 7 columns (3 integers, 3 text, 1 timestamptz) with an average = width of 157 chars, one btree index on the pk int column.  Using = explain analyze, I picked one id value out of the 80m and ran a select * = where id =3D x.  It did an index scan, had a planning time of = 0.077ms, and an execution time of 0.254 seconds.  I ran the query = for a variety of widely spaced values (so the data was uncached) and the = timing never changed. This has been mirroring my general experience with = PG =E2=80=93 very fast reads on indexed queries. =

 

Summary: = ; I think your buckets can be WAY bigger than you are envisioning for = the simple table design you=E2=80=99ve described.  I=E2=80=99m = betting you can easily do 500 million rows per bucket before approaching = anything close to the 30ms max query time.

 

Mike Sofen = (Synthetic Genomics)

 

Totally = typo=E2=80=99d the execution time:=C2=A0 it was 0.254 MILLISECONDS, not = SECONDS.=C2=A0 Thus my comment about going up 10x in bucket size instead = of appearing to be right at the limit.=C2=A0 = Sorry!

 

Mike

------=_NextPart_000_0035_01D2189B.1F32B4E0--