Re: Global temporary tables

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.

>
> 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 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.

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