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