Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bovEU-0003t2-5M for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 16:27:50 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bovET-0004l9-Jl for pgsql-performance@arkaria.postgresql.org; Tue, 27 Sep 2016 16:27:49 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bovES-0004l2-Ub for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 16:27:49 +0000 Received: from mail-it0-x22e.google.com ([2607:f8b0:4001:c0b::22e]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bovEP-0005Ou-NZ for pgsql-performance@postgresql.org; Tue, 27 Sep 2016 16:27:47 +0000 Received: by mail-it0-x22e.google.com with SMTP id u134so46736001itb.1 for ; Tue, 27 Sep 2016 09:27:45 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=acDFG/7QKFnjQGqPoIMslZxyf7MseBga4vcnauPk8ec=; b=c8bazslNq54UsoW/fqpioYu72LNOR8lPkV6xOaRYVNC58DWaNWZq+/ozaxpuuzePFt 9L7FoEHkNxoNxQI2Vkm0XBdT0NIDcBumtIViNhjqylGAC0tF/a6tOY9s2TeUymUC6Gfz dI7gy2/xxv3Lgsos7+kmfqo2VAGP8ZI3tTiH57LfmhbIZn2KrlqoPiSCGlzjtphtC0HM uQ5ruq81RvLRfA0qTX6U2pLav8nToP6amndH6VbB9NlFgztHrTLrrtRcFc/qc7jmsM/i DPLZ8J6+oWD5wQ3qDLkxisT1j12Jklm1IU8qsWWJk5p1d58LjtCZdHyq22Maf7+8rJCG VzDA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=acDFG/7QKFnjQGqPoIMslZxyf7MseBga4vcnauPk8ec=; b=e7xMjiWDwuWoucaSKNGvMABqVQedSLWg7ccs6+pG0dMHmieAsqH29n36q0rYfkdE8P 1XZMTOfzBvMnobnNwf/lxc9hPELw3QXHNaURI/stLCR23Owf/n09rdzdETRjpIDOKxDu /zcEzqFk603Xs40H/0bDFg4jl4iy2nfn6TPdh58TgHVDGmDShUwlz8IUUPyJXObKJSCf bgabPrPWbOrH8v7e8gI8ejUZAb7naVNG95WMiZFZzCMt4CtDWqK5Q2Ii35WM/GYuGP/J EyGC5fN27osga3P5mnS5icIGAAvxNMQOOUUoE5jybu6DlhSQdwji9wCw/OeBsanTxk13 5QRQ== X-Gm-Message-State: AA6/9Rm2wQcyuW16QstSGqw7oIx+gC7Uf7pSW9++fm3fNlcsv2bRYoQWZc5gJEOYyeSDGAOwRHjHIaGKgmwOSA== X-Received: by 10.36.2.1 with SMTP id 1mr2315324itu.34.1474993664636; Tue, 27 Sep 2016 09:27:44 -0700 (PDT) MIME-Version: 1.0 Received: by 10.107.18.15 with HTTP; Tue, 27 Sep 2016 09:27:43 -0700 (PDT) In-Reply-To: References: From: Greg Spiegelberg Date: Tue, 27 Sep 2016 10:27:43 -0600 Message-ID: Subject: Re: Millions of tables To: Terry Schmitt Cc: "pgsql-performa." Content-Type: multipart/alternative; boundary=001a1143d5ca6d3f5c053d7fb911 X-Pg-Spam-Score: -2.7 (--) 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 --001a1143d5ca6d3f5c053d7fb911 Content-Type: text/plain; charset=UTF-8 On Tue, Sep 27, 2016 at 10:15 AM, Terry Schmitt wrote: > > > On Sun, Sep 25, 2016 at 7:50 PM, Greg Spiegelberg > wrote: > >> Hey all, >> >> Obviously everyone who's been in PostgreSQL or almost any RDBMS for a >> time has said not to have millions of tables. I too have long believed it >> until recently. >> >> AWS d2.8xlarge instance with 9.5 is my test rig using XFS on EBS (io1) >> for PGDATA. Over the weekend, I created 8M tables with 16M indexes on >> those tables. Table creation initially took 0.018031 secs, average >> 0.027467 and after tossing out outliers (qty 5) the maximum creation time >> found was 0.66139 seconds. Total time 30 hours, 31 minutes and 8.435049 >> seconds. Tables were created by a single process. Do note that table >> creation is done via plpgsql function as there are other housekeeping tasks >> necessary though minimal. >> >> No system tuning but here is a list of PostgreSQL knobs and switches: >> shared_buffers = 2GB >> work_mem = 48 MB >> max_stack_depth = 4 MB >> synchronous_commit = off >> effective_cache_size = 200 GB >> pg_xlog is on it's own file system >> >> There are some still obvious problems. General DBA functions such as >> VACUUM and ANALYZE should not be done. Each will run forever and cause >> much grief. Backups are problematic in the traditional pg_dump and PITR >> space. Large JOIN's by VIEW, SELECT or via table inheritance (I am abusing >> it in my test case) are no-no's. A system or database crash could take >> potentially hours to days to recover. There are likely other issues ahead. >> >> You may wonder, "why is Greg attempting such a thing?" I looked at >> DynamoDB, BigTable, and Cassandra. I like Greenplum but, let's face it, >> it's antiquated and don't get me started on "Hadoop". I looked at many >> others and ultimately the recommended use of each vendor was to have one >> table for all data. That overcomes the millions of tables problem, right? >> >> Problem with the "one big table" solution is I anticipate 1,200 trillion >> records. Random access is expected and the customer expects <30ms reads >> for a single record fetch. >> >> No data is loaded... yet Table and index creation only. I am interested >> in the opinions of all including tests I may perform. If you had this >> setup, what would you capture / analyze? I have a job running preparing >> data. I did this on a much smaller scale (50k tables) and data load via >> function allowed close to 6,000 records/second. The schema has been >> simplified since and last test reach just over 20,000 records/second with >> 300k tables. >> >> I'm not looking for alternatives yet but input to my test. Takers? >> >> I can't promise immediate feedback but will do my best to respond with >> results. >> >> TIA, >> -Greg >> > > I have not seen any mention of transaction ID wraparound mentioned in this > thread yet. With the numbers that you are looking at, I could see this as a > major issue. > > T > Thank you Terry. You get the gold star. :) I was waiting for that to come up. Success means handling this condition. A whole database vacuum and dump-restore is out of the question. Can a properly tuned autovacuum prevent the situation? -Greg --001a1143d5ca6d3f5c053d7fb911 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On T= ue, Sep 27, 2016 at 10:15 AM, Terry Schmitt <tschmitt@schmittwor= ks.com> wrote:


On Sun, Sep 25, 2016 at 7:50 PM, Greg Spiegelberg <gspiegelb= erg@gmail.com> wrote:
Hey all,

Obvio= usly everyone who's been in PostgreSQL or almost any RDBMS for a time h= as said not to have millions of tables.=C2=A0 I too have long believed it u= ntil recently.

AWS d2.8xlarge instance with 9.5 is= my test rig using XFS on EBS (io1) for PGDATA.=C2=A0 Over the weekend, I c= reated 8M tables with 16M indexes on those tables.=C2=A0 Table creation ini= tially took 0.018031 secs, average 0.027467 and after tossing out outliers = (qty 5) the maximum creation time found was 0.66139 seconds.=C2=A0 Total ti= me 30 hours, 31 minutes and 8.435049 seconds.=C2=A0 Tables were created by = a single process.=C2=A0 Do note that table creation is done via plpgsql fun= ction as there are other housekeeping tasks necessary though minimal.
=

No system tuning but here is a list of PostgreSQL knobs= and switches:
shared_buffers =3D 2GB
work_mem =3D 48 M= B
max_stack_depth =3D 4 MB
synchronous_commit =3D off
effective_cache_size =3D 200 GB
pg_xlog is on it's o= wn file system

There are some still obvious proble= ms.=C2=A0 General DBA functions such as VACUUM and ANALYZE should not be do= ne.=C2=A0 Each will run forever and cause much grief.=C2=A0 Backups are pro= blematic in the traditional pg_dump and PITR space.=C2=A0 Large JOIN's = by VIEW, SELECT or via table inheritance (I am abusing it in my test case) = are no-no's.=C2=A0 A system or database crash could take potentially ho= urs to days to recover.=C2=A0 There are likely other issues ahead.

You may wonder, "why is Greg attempting such a thing?= " =C2=A0I looked at DynamoDB, BigTable, and Cassandra.=C2=A0 I like Gr= eenplum but, let's face it, it's antiquated and don't get me st= arted on "Hadoop".=C2=A0 I looked at many others and ultimately t= he recommended use of each vendor was to have one table for all data.=C2=A0= That overcomes the millions of tables problem, right?

=
Problem with the "one big table" solution is I anticipate 1,= 200 trillion records.=C2=A0 Random access is expected and the customer expe= cts <30ms reads for a single record fetch.

No data is loaded... yet =C2=A0Table and index creation only.=C2=A0 I am i= nterested in the opinions of all including tests I may perform.=C2=A0 If yo= u had this setup, what would you capture / analyze?=C2=A0 I have a job runn= ing preparing data.=C2=A0 I did this on a much smaller scale (50k tables) a= nd data load via function allowed close to 6,000 records/second.=C2=A0 The = schema has been simplified since and last test reach just over 20,000 recor= ds/second with 300k tables.

I'm not look= ing for alternatives yet but input to my test.=C2=A0 Takers?
=
I can't promise immediate feedback but will do my best t= o respond with results.

TIA,
-Greg
=

I have = not seen any mention of transaction ID wraparound mentioned in this thread = yet. With the numbers that you are looking at, I could see this as a major = issue.

T
Thank you Terry.=C2= =A0 You get the gold star. =C2=A0:) =C2=A0 I was waiting for that to come u= p.

Suc= cess means handling this condition.=C2=A0 A whole database vacuum and dump-= restore is out of the question.=C2=A0 Can a properly tuned autovacuum preve= nt the situation?

-Greg

--001a1143d5ca6d3f5c053d7fb911--