On 7/29/11, lars hofhansl lhofha...@yahoo.com 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
On Wed, Jul 13, 2011 at 10:52 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Tue, Jul 12, 2011 at 6:15 PM, lars lhofha...@yahoo.com wrote:
Back to the first case, here's an strace from the backend doing the select
right after the updates.
write(13,
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
On Fri, Jul 15, 2011 at 5:21 PM, lars lhofha...@yahoo.com wrote:
On 07/14/2011 04:03 PM, Jeff Janes wrote:
On Wed, Jul 13, 2011 at 3:41 PM, larslhofha...@yahoo.com wrote:
On 07/13/2011 11:42 AM, Kevin Grittner wrote:
So transactions without an XID *are* sensitive to
synchronous_commit.
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 07/14/2011 04:03 PM, Jeff Janes wrote:
On Wed, Jul 13, 2011 at 3:41 PM, larslhofha...@yahoo.com 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
On Thu, Jul 14, 2011 at 4:05 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Tom Lane t...@sss.pgh.pa.us 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
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us 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
Tom Lane t...@sss.pgh.pa.us 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
On Wed, Jul 13, 2011 at 11:10 AM, lars lhofha...@yahoo.com 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
On Wed, Jul 13, 2011 at 3:41 PM, lars lhofha...@yahoo.com 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
[combining responses to two posts on this thread by lars]
lars lhofha...@yahoo.com 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
On Tue, Jul 12, 2011 at 6:15 PM, lars lhofha...@yahoo.com 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 Grittner kevin.gritt...@wicourts.gov 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
On 07/13/2011 08:17 AM, Tom Lane wrote:
Kevin Grittnerkevin.gritt...@wicourts.gov 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
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,
lars lhofha...@yahoo.com 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
On Wed, Jul 13, 2011 at 1:10 PM, lars lhofha...@yahoo.com wrote:
On 07/13/2011 08:17 AM, Tom Lane wrote:
Kevin Grittnerkevin.gritt...@wicourts.gov 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
Tom Lane t...@sss.pgh.pa.us 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
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us 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
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
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 Tue, Jul 12, 2011 at 8:22 AM, Ivan Voras ivo...@freebsd.org 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,
On 12/07/2011 16:18, Merlin Moncure wrote:
On Tue, Jul 12, 2011 at 8:22 AM, Ivan Vorasivo...@freebsd.org 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
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 Tue, Jul 12, 2011 at 9:36 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
lars lhofha...@yahoo.com 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
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
lars lhofha...@yahoo.com 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
lars lhofha...@yahoo.com 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
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
larslhofha...@yahoo.com 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
lars lhofha...@yahoo.com 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
On 07/12/2011 01:04 PM, lars wrote:
On 07/12/2011 12:08 PM, Kevin Grittner wrote:
larslhofha...@yahoo.com 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
On Tue, Jul 12, 2011 at 2:08 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
lars lhofha...@yahoo.com 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
lars lhofha...@yahoo.com 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
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=
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
Jeff Janes jeff.ja...@gmail.com writes:
On 7/12/11, lars lhofha...@yahoo.com 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
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 t...@sss.pgh.pa.us schrieb:
Jeff Janes jeff.ja...@gmail.com writes:
On 7/12/11, lars lhofha...@yahoo.com wrote:
The
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
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 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
lars hofhansl lhofha...@yahoo.com 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
On 07/11/2011 10:33 AM, Kevin Grittner wrote:
lars hofhansllhofha...@yahoo.com 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
lars lhofha...@yahoo.com 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
On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
lars lhofha...@yahoo.com 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
Merlin Moncure mmonc...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
lars lhofha...@yahoo.com 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
On Mon, Jul 11, 2011 at 4:55 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Merlin Moncure mmonc...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
lars lhofha...@yahoo.com wrote:
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the
On 07/11/2011 02:43 PM, Merlin Moncure wrote:
On Mon, Jul 11, 2011 at 2:16 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
larslhofha...@yahoo.com wrote:
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the SELECTs indeed shows a similar slowdown.
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
kevin.gritt...@wicourts.gov wrote:
larslhofha...@yahoo.com wrote:
Stopping the UPDATEs, waiting for any CHECKPOINTs to finish,
and then running the SELECTs
On 07/11/2011 08:26 AM, Robert Klemme wrote:
On Mon, Jul 11, 2011 at 3:13 PM, k...@rice.eduk...@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
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
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.
Sent: Sun, July 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
52 matches
Mail list logo
