From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Hubert Rutkowski <hubert.rutkowski@deepsense.ai>
To: pgsql-performance@lists.postgresql.org
Subject: Re: Milions of views - performance, stability
Date: Sat, 17 Sep 2022 07:33:03 +0200
Message-ID: <bf81586fddf9affc2552ca2e6d925b17d78731bc.camel@cybertec.at> (raw)
In-Reply-To: <CAG4sUhg4Jw=HAeiPLh6PZRGS8v-NVn-nJgt+6_zxEwXVmAhW-g@mail.gmail.com>
References: <CAG4sUhg4Jw=HAeiPLh6PZRGS8v-NVn-nJgt+6_zxEwXVmAhW-g@mail.gmail.com>
On Sat, 2022-09-17 at 01:05 +0200, Hubert Rutkowski wrote:
> Hello! I have written python program to benchmark view efficiency, because in our platform
> they have a role to play and we noticed the performance is less than expected.
If your platform plans to use millions of views, you should revise your design. As you
see, that is not going to fly. And no, I don't consider that a bug.
> Basically, benchmark creates table:
>
> CREATE TABLE IF NOT EXISTS foobar ( id int, text varchar(40) );
>
> for i in range(1200300):
> INSERT INTO foobar (id, text) VALUES ({i}, 'some string');
> CREATE VIEW foobar_{i} as select * from foobar where id={i};
>
> Couldn't be any simpler.
> [general slowness]
>
> What's even stranger is dropping performance: DROP TABLE foobar CASCADE;. First of all, had to
> increase locks to allow it to finish, otherwise it was quickly bailing because of "too little shared memory".
> alter system set max_locks_per_transaction=40000;
>
> But even after that, it took almost 7 hours and crashed:
>
> 2022-09-13 23:16:31.113 UTC [1] LOG: server process (PID 404) was terminated by signal 9: Killed
>
> After updating Postgres to 14.5, it crashed in a bit different way:
>
> 2022-09-15 19:20:26.000 UTC [67] LOG: checkpoints are occurring too frequently (23 seconds apart)
> 2022-09-15 19:20:26.000 UTC [67] HINT: Consider increasing the configuration parameter "max_wal_size".
> 2022-09-15 19:20:39.058 UTC [1] LOG: server process (PID 223) was terminated by signal 9: Killed
> 2022-09-15 19:20:39.058 UTC [1] DETAIL: Failed process was running: drop table foobar cascade;
>
> Wihout the views, table can be dropped in 20ms.
You misconfigured your operating system and didn't disable memory overcommit, so you got killed
by the OOM killer. Basically, the operation ran out of memory.
Yours,
Laurenz Albe
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, hubert.rutkowski@deepsense.ai, pgsql-performance@lists.postgresql.org
Subject: Re: Milions of views - performance, stability
In-Reply-To: <bf81586fddf9affc2552ca2e6d925b17d78731bc.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox