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 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?
BTW, should we have an entry in the index for 'temporary tables?
--
Mike Nolan
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
Attachments:
temp-rel-syncrep-flush-1.patchtext/x-diff; name=temp-rel-syncrep-flush-1.patchDownload
diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c
index e22bdac..d8523f3 100644
--- a/src/backend/access/transam/xact.c
+++ b/src/backend/access/transam/xact.c
@@ -1168,7 +1168,8 @@ RecordTransactionCommit(void)
* Note that at this stage we have marked clog, but still show as running
* in the procarray and continue to hold locks.
*/
- SyncRepWaitForLSN(XactLastRecEnd);
+ if (wrote_xlog)
+ SyncRepWaitForLSN(XactLastRecEnd);
/* Reset XactLastRecEnd until the next transaction writes something */
XactLastRecEnd.xrecoff = 0;
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