Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpZFG-0007d0-TK for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 11:11:19 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bpZFG-000342-7f for pgsql-performance@arkaria.postgresql.org; Thu, 29 Sep 2016 11:11:18 +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 1bpZFF-00033v-Hc for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 11:11:17 +0000 Received: from newmail.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bpZF5-0004t6-Iu for pgsql-performance@postgresql.org; Thu, 29 Sep 2016 11:11:17 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id D9B8621C3569; Thu, 29 Sep 2016 14:11:05 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru Received: from mail.postgrespro.ru ([127.0.0.1]) by localhost (mail.postgrespro.ru [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id xL3NrLe0Fb4J; Thu, 29 Sep 2016 14:11:05 +0300 (MSK) Received: from inferno (unknown [192.168.27.1]) by mail.postgrespro.ru (Postfix) with ESMTPSA id AD53B21C32F6; Thu, 29 Sep 2016 14:11:05 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1475147465; bh=2khfgZZYnveVGWmDCkUPX1C0zZRl1SoBqf+LAepNOZc=; h=From:To:Cc:References:In-Reply-To:Subject:Date; b=HozT/AQvxglTvVRi+wR/WnzzzAO+8mA+5MsGgOjZOcH8JT0PnRVAw2e0oGVazpiO5 p3dD3hr0PKIgSRYxAGPP3D/4d/zwe4yovtawGXVqdNwLVUMj55iO3EzsHa0b63nWC+ nQOZIqrSq28ea+f89eumMSalavbtg7XHb2oc809s= From: "Alex Ignatov \(postgrespro\)" To: "'Greg Spiegelberg'" , "'Terry Schmitt'" Cc: "'pgsql-performa.'" References: In-Reply-To: Subject: Re: Millions of tables Date: Thu, 29 Sep 2016 14:11:07 +0300 Message-ID: <001401d21a42$2d21a8b0$8764fa10$@postgrespro.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0015_01D21A5B.526FA400" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQIGoPHtU7IEjWZl2VDwhNKwCwLMKAM/jopwAiDyRE2f/DvzYA== Content-Language: ru X-Pg-Spam-Score: -5.0 (-----) 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_0015_01D21A5B.526FA400 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =20 =20 =20 From: pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Greg = Spiegelberg Sent: Tuesday, September 27, 2016 7:28 PM To: Terry Schmitt Cc: pgsql-performa. Subject: Re: [PERFORM] Millions of tables =20 On Tue, Sep 27, 2016 at 10:15 AM, Terry Schmitt = > wrote: =20 =20 On Sun, Sep 25, 2016 at 7:50 PM, Greg Spiegelberg = > wrote: Hey all, =20 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. =20 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. =20 No system tuning but here is a list of PostgreSQL knobs and switches: shared_buffers =3D 2GB work_mem =3D 48 MB max_stack_depth =3D 4 MB synchronous_commit =3D off effective_cache_size =3D 200 GB pg_xlog is on it's own file system =20 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. =20 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? =20 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. =20 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. =20 I'm not looking for alternatives yet but input to my test. Takers? =20 I can't promise immediate feedback but will do my best to respond with = results. =20 TIA, -Greg =20 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. =20 T =20 Thank you Terry. You get the gold star. :) I was waiting for that to = come up. =20 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? =20 -Greg =20 Hi! With millions of tables you have to set autovacuum_max_workers = sky-high =3D). We have some situation when at thousands of tables = autovacuum can=E2=80=99t vacuum all tables that need it. Simply it = vacuums some of most modified table and never reach others. Only manual = vacuum can help with this situation. With wraparound issue it can be a = nightmare=20 =20 -- Alex Ignatov=20 Postgres Professional: = http://www.postgrespro.com=20 The Russian Postgres Company =20 ------=_NextPart_000_0015_01D21A5B.526FA400 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

 

 

 

From:<= /b> = pgsql-performance-owner@postgresql.org = [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Greg = Spiegelberg
Sent: Tuesday, September 27, 2016 7:28 = PM
To: Terry Schmitt = <tschmitt@schmittworks.com>
Cc: pgsql-performa. = <pgsql-performance@postgresql.org>
Subject: Re: = [PERFORM] Millions of tables

 

On Tue, Sep 27, 2016 at 10:15 AM, Terry Schmitt <tschmitt@schmittworks.com> = wrote:

 

 

On Sun, = Sep 25, 2016 at 7:50 PM, Greg Spiegelberg <gspiegelberg@gmail.com> = 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 =3D 2GB

work_mem =3D 48 MB

max_stack_depth =3D 4 MB

synchronous_commit =3D off

effective_cache_size =3D 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

<= /div>

 

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

 

Hi!

With = millions of tables you have to set = =C2=A0=C2=A0=C2=A0autovacuum_max_workers =C2=A0sky-high =3D). We have = some situation when at thousands of tables autovacuum can=E2=80=99t = vacuum all tables that need it. Simply it vacuums some of most modified = table and never reach others. Only manual vacuum can help with this = situation. With wraparound issue it can be a nightmare =

 

--=

Alex Ignatov =
Postgres Professional: http://www.postgrespro.com
The = Russian Postgres Company

 

------=_NextPart_000_0015_01D21A5B.526FA400--