Thread: Slow temporary tables when using sync rep

Hi,

I've noticed that when using synchronous replication (on 9.2devel at
least), temporary tables become really slow:

thom@test=# create temporary table temp_test (a text, b text);
CREATE TABLE
Time: 16.812 ms
thom@test=# SET synchronous_commit = 'local';
SET
Time: 2.739 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 3.911 ms
thom@test=# SET synchronous_commit = 'remote_write';
SET
Time: 2.826 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 831.384 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 1700.154 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 4976.853 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 5294.213 ms
thom@test=# insert into temp_test (a, b) values ('one', 'two');

It appears to be taking exactly 6 seconds between each transaction,
but as if it's only attempting to complete every 6 seconds like a
heartbeat:

thom@test=# insert into temp_test (a, b) values ('one', 'two');insert
into temp_test (a, b) values ('one', 'two');insert into temp_test (a,
b) values ('one', 'two');insert into temp_test (a, b) values ('one',
'two');
INSERT 0 1
Time: 141.586 ms
INSERT 0 1
Time: 6009.059 ms
INSERT 0 1
Time: 6009.305 ms
INSERT 0 1
Time: 6009.610 ms

thom@test=# begin;
BEGIN
Time: 0.469 ms
thom@test=*# insert into temp_test (a, b) values ('one',
'two');insert into temp_test (a, b) values ('one', 'two');insert into
temp_test (a, b) values ('one', 'two');insert into temp_test (a, b)
values ('one', 'two');
INSERT 0 1
Time: 0.841 ms
INSERT 0 1
Time: 0.395 ms
INSERT 0 1
Time: 0.354 ms
INSERT 0 1
Time: 0.419 ms
thom@test=*# end;
COMMIT
Time: 5649.679 ms


This doesn't apply to regular tables:

thom@test=# create table test (a text, b text);
CREATE TABLE
Time: 18.806 ms
thom@test=# SET synchronous_commit = 'local';
SET
Time: 2.751 ms
thom@test=# insert into test (a, b) values ('one', 'two');
INSERT 0 1
Time: 6.546 ms
thom@test=# SET synchronous_commit = 'remote_write';
SET
Time: 2.713 ms
thom@test=# insert into test (a, b) values ('one', 'two');
INSERT 0 1
Time: 7.257 ms
thom@test=# insert into test (a, b) values ('one', 'two');
INSERT 0 1
Time: 6.308 ms
thom@test=# insert into test (a, b) values ('one', 'two');
INSERT 0 1
Time: 8.871 ms

Is this a known problem?
-- 
Thom

On 17.04.2012 02:54, Michael Nolan wrote:
> On Mon, Apr 16, 2012 at 6:27 PM, Thom Brown<thom@linux.com>  wrote:
>
>> Hi,
>>
>> I've noticed that when using synchronous replication (on 9.2devel at
>> least), temporary tables become really slow:
>
> Since temporary tables are only present until the session ends (or
> possibly only until a commit), why are they replicated at all?

They're not replicated.

What happens is that we write the commit record if the transaction
accesses a temporary table, but we don't flush it. However, we still
wait until it's replicated to the standby. The obvious fix is to not
wait for that, see attached.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

On 17 April 2012 11:30, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> What happens is that we write the commit record if the transaction accesses
> a temporary table, but we don't flush it. However, we still wait until it's
> replicated to the standby. The obvious fix is to not wait for that, see
> attached.

Tested patch.  Yes, that fixes the problem.  Thanks.

-- 
Thom

On 17.04.2012 14:10, Thom Brown wrote:
> On 17 April 2012 11:30, Heikki Linnakangas
> <heikki.linnakangas@enterprisedb.com>  wrote:
>> What happens is that we write the commit record if the transaction accesses
>> a temporary table, but we don't flush it. However, we still wait until it's
>> replicated to the standby. The obvious fix is to not wait for that, see
>> attached.
>
> Tested patch.  Yes, that fixes the problem.  Thanks.

Ok, committed.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com

On 17 April 2012 14:35, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:
> On 17.04.2012 14:10, Thom Brown wrote:
>>
>> On 17 April 2012 11:30, Heikki Linnakangas
>> <heikki.linnakangas@enterprisedb.com>  wrote:
>>>
>>> What happens is that we write the commit record if the transaction
>>> accesses
>>> a temporary table, but we don't flush it. However, we still wait until
>>> it's
>>> replicated to the standby. The obvious fix is to not wait for that, see
>>> attached.
>>
>>
>> Tested patch.  Yes, that fixes the problem.  Thanks.
>
>
> Ok, committed.

Thanks Heikki.

--
Thom