On 7/29/11, lars hofhansl wrote:
> Thanks Pavan!
>
>
> I think the most important points are still that:
> 1. The WAL write should be happening asynchronously (if that is possible)
I think it is agreed that this is a "todo"; but since you reported
that turning off synchronous commit did not impro
cache these
optimizations are less important.)
-- Lars
- Original Message -
From: Pavan Deolasee
To: Merlin Moncure
Cc: lars ; Kevin Grittner ;
Ivan Voras ; pgsql-performance@postgresql.org
Sent: Wednesday, July 27, 2011 7:15 AM
Subject: Re: [PERFORM] UPDATEDs slowing SELECTs in a
On Wed, Jul 13, 2011 at 10:52 AM, Merlin Moncure wrote:
> On Tue, Jul 12, 2011 at 6:15 PM, lars wrote:
>> Back to the first case, here's an strace from the backend doing the select
>> right after the updates.
>> write(13,
>> "f\320\1\0\1\0\0\0\273\0\0\0\0\340\27\22`\32\0\0002833!000"..., 2400
On 07/16/2011 06:33 PM, Jeff Janes wrote:
2.6 ms for an fsync seems awfully quick. I wonder if EBS uses
nonvolatile/battery-backed write cache, or if it just lies about fsync
actually hitting disk.
They have the right type of cache in there to make fsync quick, when you
happen to be the l
On 07/14/2011 08:47 AM, Tom Lane wrote:
The implementation I was imagining was to define another bit in the info
parameter for XLogInsert, say XLOG_NON_TRANSACTIONAL. This could be a
high-order bit that would not go to disk. Anytime it was *not* set,
XLogInsert would set a global boolean that
On Fri, Jul 15, 2011 at 5:21 PM, lars wrote:
> On 07/14/2011 04:03 PM, Jeff Janes wrote:
>>
>> On Wed, Jul 13, 2011 at 3:41 PM, lars wrote:
>>>
>>> On 07/13/2011 11:42 AM, Kevin Grittner wrote:
So transactions without an XID *are* sensitive to
synchronous_commit. That's likely a u
On 07/14/2011 04:03 PM, Jeff Janes wrote:
On Wed, Jul 13, 2011 at 3:41 PM, lars wrote:
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 follow
On Wed, Jul 13, 2011 at 3:41 PM, lars wrote:
> 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 s
On Wed, Jul 13, 2011 at 11:10 AM, lars wrote:
...
> => update test set created_by = '001' where tenant =
> '001';
> UPDATE 3712
...
>
> There seems to be definitely something funky going on. Since created_by is
> indexed it shouldn't do any HOT logic.
Once the update has
Tom Lane wrote:
> It seems like we ought to distinguish heap cleanup activities from
> user-visible semantics (IOW, users shouldn't care if a HOT cleanup
> has to be done over after restart, so if the transaction only
> wrote such records there's no need to flush). This'd require more
> process
"Kevin Grittner" writes:
> Tom Lane wrote:
>> It seems like we ought to distinguish heap cleanup activities from
>> user-visible semantics (IOW, users shouldn't care if a HOT cleanup
>> has to be done over after restart, so if the transaction only
>> wrote such records there's no need to flush).
On Thu, Jul 14, 2011 at 4:05 PM, Kevin Grittner
wrote:
> Tom Lane wrote:
>
>> It seems like we ought to distinguish heap cleanup activities from
>> user-visible semantics (IOW, users shouldn't care if a HOT cleanup
>> has to be done over after restart, so if the transaction only
>> wrote such rec
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
"Kevin Grittner" writes:
> Tom Lane wrote:
>> Huh? If there was never an XID, there's no commit WAL record,
>> hence nothing to make asynchronous.
> If you look at the RecordTransactionCommit() function in xact.c
> you'll see that's not correct.
Oh, hmmm ... that code was written with the ide
Tom Lane wrote:
> Huh? If there was never an XID, there's no commit WAL record,
> hence nothing to make asynchronous.
If you look at the RecordTransactionCommit() function in xact.c
you'll see that's not correct. Currently the commit record has
nothing to do with whether it synchronizes on W
On Wed, Jul 13, 2011 at 1:10 PM, lars wrote:
> On 07/13/2011 08:17 AM, Tom Lane wrote:
>>
>> "Kevin Grittner" writes:
>>>
>>> ... Jeff does raise a good point, though -- it seems odd
>>> that WAL-logging of this pruning would need to be synchronous.
>>
>> Yeah, we need to get to the bottom of th
lars wrote:
> On 07/13/2011 07:46 AM, Kevin Grittner wrote:
>>
>> I've mentioned this in a hand-wavy general sense, but I should
>> have mentioned specifics ages ago: for a database where the
>> active portion of the database is fully cached, it is best to set
>> seq_page_cost and random_page_cost
On 07/13/2011 07:46 AM, Kevin Grittner wrote:
I've mentioned this in a hand-wavy general sense, but I should have
mentioned specifics ages ago: for a database where the active
portion of the database is fully cached, it is best to set
seq_page_cost and random_page_cost to the same value, somewhe
On 07/13/2011 08:17 AM, Tom Lane wrote:
"Kevin Grittner" writes:
... Jeff does raise a good point, though -- it seems odd
that WAL-logging of this pruning would need to be synchronous.
Yeah, we need to get to the bottom of that. If there's enough
shared_buffer space then it shouldn't be.
Thi
"Kevin Grittner" writes:
> ... Jeff does raise a good point, though -- it seems odd
> that WAL-logging of this pruning would need to be synchronous.
Yeah, we need to get to the bottom of that. If there's enough
shared_buffer space then it shouldn't be.
> We
> support asynchronous commits -- wh
On Tue, Jul 12, 2011 at 6:15 PM, lars wrote:
> Back to the first case, here's an strace from the backend doing the select
> right after the updates.
> write(13,
> "f\320\1\0\1\0\0\0\273\0\0\0\0\340\27\22`\32\0\0002833!000"..., 2400256)
> = 2400256
On Wed, Jul 13, 2011 at 9:46 AM, Kevin Grittn
[combining responses to two posts on this thread by lars]
lars wrote:
> On the face of it, though, this looks like Postgres would not be
> that useful as database that resides (mostly) in the cache.
I've mentioned this in a hand-wavy general sense, but I should have
mentioned specifics ages
shared_buffers is big enough to hold the entire database, and there is plenty
of extra space. (verified with PG_buffercache)
So i don't think that is the reason.
Tom Lane schrieb:
>Jeff Janes writes:
>> On 7/12/11, lars wrote:
>>> The fact that a select (maybe a big analytical query we'll
Jeff Janes writes:
> On 7/12/11, lars wrote:
>> The fact that a select (maybe a big analytical query we'll run) touching
>> many rows will update the WAL and wait
>> (apparently) for that IO to complete is making a fully cached database
>> far less useful.
>> I just artificially created this scen
On 7/12/11, lars wrote:
>
>
> The fact that a select (maybe a big analytical query we'll run) touching
> many rows will update the WAL and wait
> (apparently) for that IO to complete is making a fully cached database
> far less useful.
> I just artificially created this scenario.
I can't think of
On 07/12/2011 02:38 PM, Merlin Moncure wrote:
Something is not adding up here. Perhaps there is an alternate route
to WAL logged activity from selects I'm not thinking of. Right now
I'm thinking to run the selects on table 'a' and the inserts
concurrently on table 'b' and seeing how that behav
On 07/12/2011 02:51 PM, Kevin Grittner wrote:
I ran x a bunch of times to get a baseline, then y once, then x a
bunch more times. The results were a bit surprising:
cir=> \timing
Timing is on.
cir=> execute x('001','2011-6-30');
count
---
3456
(1 row)
Time: 9.823 ms
cir=
lars wrote:
> vacuum analyze;
I tried this out on a 16 core, 64 GB machine. It was a replication
target for a few dozen source databases into a couple 2 TB reporting
databases, and had some light testing going on, but it was only at
about 50% capacity, so that shouldn't throw this off by *too*
On Tue, Jul 12, 2011 at 2:08 PM, Kevin Grittner
wrote:
> lars wrote:
>
>> select count(*) from test where tenant = $1 and created_date = $2
>
> Ah, that might be a major clue -- prepared statements.
I'm really skeptical that this is the case -- the table is 100m and
there is no way you are bangi
On 07/12/2011 01:04 PM, lars wrote:
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
lars wrote:
select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it
lars wrote:
> So a read of a row *will* trigger dead tuple pruning, and that
> requires WAL logging, and this is known/expected?
Yes, because pruning dead line pointers will make subsequent reads
faster. It's intended to be an optimization.
> This is actually the only answer I am looking fo
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
lars wrote:
select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it is very likely *not* to be the same p
lars wrote:
> select count(*) from test where tenant = $1 and created_date = $2
Thinking about this some more, it would be interesting to know your
PostgreSQL configuration. I seem to remember you mentioning some
settings up-thread, but I'm not sure whether it was comprehensive.
Could you pa
lars wrote:
> select count(*) from test where tenant = $1 and created_date = $2
Ah, that might be a major clue -- prepared statements.
What sort of a plan do you get for that as a prepared statement?
(Note, it is very likely *not* to be the same plan as you get if you
run with literal value
On 07/12/2011 08:13 AM, Ivan Voras wrote:
On 12/07/2011 02:09, lars wrote:
Oh, and iowait hovers around 20% when SELECTs are slow:
avg-cpu: %user %nice %system %iowait %steal %idle
1.54 0.00 0.98 18.49 0.07 78.92
When SELECTs are fast it looks like this:
avg-cpu: %user %nice %system %iowait %
On Tue, Jul 12, 2011 at 9:36 AM, Kevin Grittner
wrote:
> lars wrote:
>
>> I am not trying to optimize this particular use case, but rather
>> to understand what Postgres is doing, and why SELECT queries are
>> affected negatively (sometimes severely) by concurrent (or even
>> preceding) UPDATEs a
On 12/07/2011 02:09, lars wrote:
Oh, and iowait hovers around 20% when SELECTs are slow:
avg-cpu: %user %nice %system %iowait %steal %idle
1.54 0.00 0.98 18.49 0.07 78.92
When SELECTs are fast it looks like this:
avg-cpu: %user %nice %system %iowait %steal %idle
8.72 0.00 0.26 0.00 0.00 91.01
On 12/07/2011 16:18, Merlin Moncure wrote:
On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras wrote:
On 08/07/2011 01:56, lars wrote:
Setup:
PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
instance (kernel 2.6.35) with the database and
WAL residing on the same EBS volume with EXT4 (
lars wrote:
> I am not trying to optimize this particular use case, but rather
> to understand what Postgres is doing, and why SELECT queries are
> affected negatively (sometimes severely) by concurrent (or even
> preceding) UPDATEs at all when the database resides in the cache
> completely.
I
On Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras wrote:
> On 08/07/2011 01:56, lars wrote:
>
>> Setup:
>> PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
>> instance (kernel 2.6.35) with the database and
>> WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1)
>> - y
On 08/07/2011 01:56, lars wrote:
Setup:
PostgreSQL 9.1beta2 on a high memory (~68gb, 12 cores) EC2 Linux
instance (kernel 2.6.35) with the database and
WAL residing on the same EBS volume with EXT4 (data=ordered, barriers=1)
- yes that is not an ideal setup
(WAL should be on separate drive, EBS
On 07/11/2011 08:26 AM, Robert Klemme wrote:
On Mon, Jul 11, 2011 at 3:13 PM, k...@rice.edu wrote:
I do not know if this makes sense in PostgreSQL and that readers
do not block writers and writes do not block readers. Are your
UPDATEs to individual rows, each in a separate transaction, or
do yo
On 07/11/2011 04:02 PM, lars wrote:
On 07/11/2011 02:43 PM, Merlin Moncure wrote:
On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
wrote:
lars wrote:
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the SELECTs indeed shows a similar slowdown.
Interestingly I s
On 07/11/2011 02:43 PM, Merlin Moncure wrote:
On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
wrote:
lars wrote:
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the SELECTs indeed shows a similar slowdown.
Interestingly I see very heavy WAL traffic while execu
On Mon, Jul 11, 2011 at 4:55 PM, Kevin Grittner
wrote:
> Merlin Moncure wrote:
>> Kevin Grittner wrote:
>>> lars wrote:
>>>
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the SELECTs indeed shows a similar slowdown.
Interestingly I see very hea
Merlin Moncure wrote:
> Kevin Grittner wrote:
>> lars wrote:
>>
>>> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
>>> and then running the SELECTs indeed shows a similar slowdown.
>>>
>>> Interestingly I see very heavy WAL traffic while executing the
>>> SELECTs. (So I was confuse
On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
wrote:
> lars wrote:
>
>> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
>> and then running the SELECTs indeed shows a similar slowdown.
>>
>> Interestingly I see very heavy WAL traffic while executing the
>> SELECTs. (So I was confus
lars wrote:
> Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
> and then running the SELECTs indeed shows a similar slowdown.
>
> Interestingly I see very heavy WAL traffic while executing the
> SELECTs. (So I was confused as to what caused the WAL traffic).
Hint bit changes aren
On 07/11/2011 10:33 AM, Kevin Grittner wrote:
lars hofhansl wrote:
Yep, I am not seeing the SELECTs slow down (measurably) during
checkpoints (i.e. when dirty pages are flushed to disk), but only
during writing of the WAL files.
How about if you do a whole slew of the UPDATEs and then stop t
lars hofhansl wrote:
> Yep, I am not seeing the SELECTs slow down (measurably) during
> checkpoints (i.e. when dirty pages are flushed to disk), but only
> during writing of the WAL files.
How about if you do a whole slew of the UPDATEs and then stop those
and run a bunch of SELECTs? (I don'
On Mon, Jul 11, 2011 at 05:26:49PM +0200, Robert Klemme wrote:
> On Mon, Jul 11, 2011 at 3:13 PM, k...@rice.edu wrote:
> > I do not know if this makes sense in PostgreSQL and that readers
> > do not block writers and writes do not block readers. Are your
> > UPDATEs to individual rows, each in a s
On Mon, Jul 11, 2011 at 3:13 PM, k...@rice.edu wrote:
> I do not know if this makes sense in PostgreSQL and that readers
> do not block writers and writes do not block readers. Are your
> UPDATEs to individual rows, each in a separate transaction, or
> do you UPDATE multiple rows in the same trans
Hi Lars,
I do not know if this makes sense in PostgreSQL and that readers
do not block writers and writes do not block readers. Are your
UPDATEs to individual rows, each in a separate transaction, or
do you UPDATE multiple rows in the same transaction? If you
perform multiple updates in a single t
10, 2011 4:11:39 PM
Subject: Re: [PERFORM] UPDATEDs slowing SELECTs in a fully cached database
On 11/07/2011 4:34 AM, lars wrote:
> I have since moved the WAL to its own EBS volume (ext4, data=writeback)
> to make it easier to monitor IO.
> The times where the SELECTs slow down coinci
On 11/07/2011 4:34 AM, lars wrote:
I have since moved the WAL to its own EBS volume (ext4, data=writeback)
to make it easier to monitor IO.
The times where the SELECTs slow down coincide with heavy write traffic
to the WAL volume.
In theory, UPDATEs shouldn't be blocking or slowing SELECTs. Whe
I have since moved the WAL to its own EBS volume (ext4, data=writeback)
to make it easier to monitor IO.
The times where the SELECTs slow down coincide with heavy write traffic
to the WAL volume.
Maybe this has to do with WALInsertLock or WALWriteLock (or some other
lock).
Since the slowdown w
I am doing some research that will hopefully lead to replacing a big
Oracle installation with a set PostgreSQL servers.
The current Oracle installations consists of multiple of RAC clusters
with 8 RAC nodes each. Each RAC node has 256gb of
memory (to be doubled soon).
The nature of our service
57 matches
Mail list logo
