Re: Global temporary tables

út 20. 8. 2019 v 16:51 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> On 19.08.2019 18:53, Pavel Stehule wrote:
>
>
>
>
>> Certainly, default (small) temp buffer size plays roles.
>> But it this IPC host this difference is not so important.
>> Result with local temp tables and temp_buffers = 1GB: 859k TPS.
>>
>
> It is little bit unexpected result.I understand so it partially it is
> generic problem access to smaller dedicated caches versus access to bigger
> shared cache.
>
> But it is hard to imagine so access to local cache is 10% slower than
> access to shared cache. Maybe there is some bottle neck - maybe our
> implementation of local buffers are suboptimal.
>
>
> It may be caused by system memory allocator - in case of using shared
> buffers we do not need to ask OS to allocate more memory.
>

maybe, but shared buffers you have a overhead with searching free buffers
and some overhead with synchronization processes.

>
>
> Using local buffers for global temporary tables can be interesting from
> another reason - it uses temporary files, and temporary files can be
> forwarded on ephemeral IO on Amazon cloud (with much better performance
> than persistent IO).
>
>
>
> My assumption is that temporary tables almost always fit in memory. So in
> most cases there is on need to write data to file at all.
>
>
> As I wrote at the beginning of this thread, one of the problems with
> temporary table sis that it is not possible to use them at replica.
> Global temp tables allows to share metadata between master and replica.
>

I am not sure if I understand to last sentence. Global temp tables should
be replicated on replica servers. But the content should not be replicated.
This should be session specific.

> I perform small investigation: how difficult it will be to support inserts
> in temp tables at replica.
> First my impression was that it can be done in tricky but simple way.
>
> By making small changes changing just three places:
> 1. Prohibit non-select statements in read-only transactions
> 2. Xid assignment (return FrozenTransactionId)
> 3. Transaction commit/abort
>
> I managed to provide normal work with global temp tables at replica.
> But there is one problem with this approach: it is not possible to undo
> changes in temp tables so rollback doesn't work.
>
> I tried another solution, but assigning some dummy Xids to standby
> transactions.
> But this approach require much more changes:
> - Initialize page for such transaction in CLOG
> - Mark transaction as committed/aborted in XCLOG
> - Change snapshot check in visibility function
>
> And still I didn't find safe way to cleanup CLOG space.
> Alternative solution is to implement "local CLOG" for such transactions.
> The straightforward solution is to use hashtable. But it may cause memory
> overflow if we have long living backend which performs huge number of
> transactions.
> Also in this case we need to change visibility check functions.
>
> So I have implemented simplest solution with frozen xid and force backend
> termination in case of transaction rollback (so user will no see
> inconsistent behavior).
> Attached please find global_private_temp_replica.patch which implements
> this approach.
> It will be nice if somebody can suggest better solution for temporary
> tables at replica.
>

This is another hard issue. Probably backend temination should be
acceptable solution. I don't understand well to this area, but if replica
allows writing (to global temp tables), then replica have to have local
CLOG.

CLOG for global temp tables can be more simple then standard CLOG. Data are
not shared, and life of data (and number of transactions) can be low.

Another solution is wait on ZHeap storage and replica can to have own UNDO
log.

>
>
>
>
>
>
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>