Re: Fastest way to clone schema ~1000x

We've experimented with shared buffers to no effect.

The 2048 we actually need for our test setup. The way this works is that we
have a single preforked backend for all tests where each backend worker
maintains persistent connections *per test* (in database-per-test), so with
say 50 backend processes, they might each meantain ~10-50 connections to
postgres. That's the reason for looking into schema-per-test that would
require only 1 persistent connection per worker and possibly only changing
the search_path to handle a request (and hencefort cheap initialization of
schema.sql being a new impediment).

But based on your answer, we will do some more research into pooling these
requests over pgbouncer with pool_mode=transaction. That should multiplex
all these backend->bouncer connections over a lot less connections to
postgres itself.

Thanks!

On Mon, Feb 26, 2024 at 2:14 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> po 26. 2. 2024 v 8:08 odesílatel Emiel Mols <emiel(at)crisp(dot)nl> napsal:
>
>> Thanks, as indicated we're using that right now. The 30% spinlock
>> overhead unfortunately persists.
>>
>
> try to increase shared_buffer
>
> 128MB can be too low
>
> max_connection = 2048 - it unrealistic high
>
>
>> - Fsync was already disabled, too. Complete postgresql.conf used in
>> testing:
>> listen_addresses = ''
>> max_connections = 2048
>> unix_socket_directories = '..'
>> shared_buffers = 128MB
>> log_line_prefix = ''
>> synchronous_commit = 'off'
>> wal_level = 'minimal'
>>
>> - linux perf report comparing schema-per-test vs database-per-test:
>> https://ibb.co/CW5w2MW
>>
>> - Emiel
>>
>>
>> On Mon, Feb 26, 2024 at 1:36 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>>
>>> Hi
>>>
>>> po 26. 2. 2024 v 7:28 odesílatel Emiel Mols <emiel(at)crisp(dot)nl> napsal:
>>>
>>>> Hello,
>>>>
>>>> To improve our unit and end-to-end testing performance, we are looking
>>>> to optimize initialization of around 500-1000 database *schemas* from a
>>>> schema.sql file.
>>>>
>>>> Background: in postgres, you cannot change databases on
>>>> existing connections, and using schemas would allow us to minimize the
>>>> amount of active connections needed from our test runners, as we can reuse
>>>> connections by updating search_path. In our current database-per-test setup
>>>> we see that there is around a ~30% (!!) total CPU overhead in
>>>> native_queued_spin_lock_slowpath (as profiled with linux perf), presumably
>>>> because of the high connection count. We run ~200 tests in parallel to
>>>> saturate a 128 core machine.
>>>>
>>>> In the schema-per-test setup, however, it becomes harder to cheaply
>>>> create the schema. Before we could `create database test001 with template
>>>> testbase` to set up the database for a test, and this was reasonably fast.
>>>> Re-inserting a schema with ~500 table/index definitions across 500 test
>>>> schema's is prohibitively expensive (around 1000ms per schema insertion
>>>> means we're wasting 500 cpu-seconds, and there seems to be quite some lock
>>>> contention too). Linux perf shows that in this setup we're reducing the
>>>> native_queued_spin_lock_slowpath overhead to around 10%, but total test
>>>> time is still slower due to all schema initialization being done. Also it
>>>> feels quite elegant functions and types can be reused between tests.
>>>>
>>>> Does anyone have some insights or great ideas :)? Also pretty curious
>>>> to the fundamental reason why having high/changing connection counts to
>>>> postgres results in this much (spin)lock contention (or perhaps we're doing
>>>> something wrong in either our configuration or measurements?).
>>>>
>>>> An alternative we haven't explored yet is to see if we can use
>>>> pgbouncer or other connection pooler to mitigate the 30% issue (set limits
>>>> so there are only ever X connections to postgres in total, and perhaps max
>>>> Y per test/database). This does add another piece of
>>>> infrastructure/complexity, so not really prefered.
>>>>
>>>
>>> For testing
>>>
>>> a) use templates - CREATE DATABASE test TEMPLATE some;
>>>
>>> b) disable fsync (only for testing!!!)
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>> Best,
>>>>
>>>> Emiel
>>>>
>>>