Slow temporary tables when using sync rep

From: Thom Brown <thom(at)linux(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Slow temporary tables when using sync rep
Date: 2012-04-16 23:27:33
Message-ID: CAA-aLv4KVxkJozSCazdmJj41cMjAD=xD_5K6n2YvxG1hLAE-qA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,

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

thom(at)test=# create temporary table temp_test (a text, b text);
CREATE TABLE
Time: 16.812 ms
thom(at)test=# SET synchronous_commit = 'local';
SET
Time: 2.739 ms
thom(at)test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 3.911 ms
thom(at)test=# SET synchronous_commit = 'remote_write';
SET
Time: 2.826 ms
thom(at)test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 831.384 ms
thom(at)test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 1700.154 ms
thom(at)test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 4976.853 ms
thom(at)test=# insert into temp_test (a, b) values ('one', 'two');
INSERT 0 1
Time: 5294.213 ms
thom(at)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(at)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(at)test=# begin;
BEGIN
Time: 0.469 ms
thom(at)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(at)test=*# end;
COMMIT
Time: 5649.679 ms

This doesn't apply to regular tables:

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

Is this a known problem?
--
Thom

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Nolan 2012-04-16 23:54:30 Re: Slow temporary tables when using sync rep
Previous Message Michael Banck 2012-04-16 23:21:09 Re: Bug tracker tool we need (was: Last gasp)