Message-ID: <bf81586fddf9affc2552ca2e6d925b17d78731bc.camel@cybertec.at>
Subject: Re: Milions of views - performance, stability
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Hubert Rutkowski <hubert.rutkowski@deepsense.ai>,
	pgsql-performance@lists.postgresql.org
Date: Sat, 17 Sep 2022 07:33:03 +0200
In-Reply-To: 
 <CAG4sUhg4Jw=HAeiPLh6PZRGS8v-NVn-nJgt+6_zxEwXVmAhW-g@mail.gmail.com>
References: 
	<CAG4sUhg4Jw=HAeiPLh6PZRGS8v-NVn-nJgt+6_zxEwXVmAhW-g@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/bf81586fddf9affc2552ca2e6d925b17d78731bc.camel%40cybertec.at>
Precedence: bulk

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