From: | lars <lhofhansl(at)yahoo(dot)com> |
---|---|
To: | Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> |
Cc: | Ivan Voras <ivoras(at)freebsd(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: UPDATEDs slowing SELECTs in a fully cached database |
Date: | 2011-07-13 22:41:16 |
Message-ID: | 4E1E1F0C.70005@yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On 07/13/2011 11:42 AM, Kevin Grittner wrote:
> So transactions without an XID *are* sensitive to
> synchronous_commit. That's likely a useful clue.
>
> How much did it help the run time of the SELECT which followed the
> UPDATE?
It has surprisingly little impact on the SELECT side:
=> set synchronous_commit = on;
=> update test set created_by = '000000000000001' where tenant =
'000000000000001';
UPDATE 3712
Time: 384.702 ms
lars=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
count
-------
3712
(1 row)
Time: 36.571 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
count
-------
3712
(1 row)
Time: 5.702 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
count
-------
3712
(1 row)
Time: 5.822 ms
=> set synchronous_commit = off;
SET
Time: 0.145 ms
=> update test set created_by = '000000000000001' where tenant =
'000000000000001';
UPDATE 3712
Time: 96.227 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
count
-------
3712
(1 row)
Time: 32.422 ms
=> select count(*) from test where tenant = '000000000000001' and
created_date = '2011-6-30';
count
-------
3712
(1 row)
Time: 6.080 ms
I tried it multiple times, and while the numbers change by 5-10ms the
relationship is the same.
The same results show when I use my JDBC code to run updates/selects as
fast as possible. When synchronous_commit is
off for the SELECTing process it seems to be slightly faster.
-- Lars
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Klemme | 2011-07-14 11:06:56 | Re: Trigger or Function |
Previous Message | Tom Lane | 2011-07-13 22:21:17 | Re: UPDATEDs slowing SELECTs in a fully cached database |