On Jun 3, 2013, at 6:45 PM, Craig Ringer cr...@2ndquadrant.com wrote:
On 06/04/2013 05:27 AM, Peter Geoghegan wrote:
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
I've seen cases on Stack Overflow and elsewhere in which disk merge
sorts perform vastly better than
On Tue, Jun 18, 2013 at 6:11 PM, Jim Nasby j...@nasby.net wrote:
IIRC there's some kind of compression or something used with on-disk sorts.
I think you're mistaken.
If that's correct then I think what's happening is that the on-disk sort
that fits into cache
is actually using less memory
On Mon, Jun 3, 2013 at 6:42 AM, Andres Freund and...@2ndquadrant.comwrote:
On 2013-06-02 11:44:04 -0700, Jeff Janes wrote:
Do we know why anti-wraparound uses so many resources in the first place?
The default settings seem to be quite conservative to me, even for a
system that has only a
On 2013-06-12 14:43:53 -0700, Jeff Janes wrote:
The default suggestion that frequently seems to be made is just to
disable autovac cost limitations because of that.
Is there general agreement that this suggestion is bad? Setting
autovacuum_vacuum_cost_delay to zero is basically saying I
Jeff Janes jeff.ja...@gmail.com wrote:
Are there other anecdotes about what settings work well in
practise, assuming people ever find ones that work well?
Putting WAL on its own RAID on its own battery-backed cached can
help a lot more than I would have thought -- even with read-only
I suspect vacuum, autovacuum, autovacuum tuning, table and index bloat,
etc is just too complicated for a lot of people running Pg installs to
really understand. I'd really, really love to see some feedback-based
auto-tuning of vacuum.
Heck, it's hard for *me* to understand, and I helped
On 06/07/2013 04:38 AM, Jeff Janes wrote:
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
My database is slow
-
This autovacuum thing is using up lots of I/O and CPU, I'll increase
this delay setting here
Do you think this was the correct diagnosis but with the
Craig Ringer cr...@2ndquadrant.com wrote:
On 06/07/2013 04:38 AM, Jeff Janes wrote:
Craig Ringer cr...@2ndquadrant.com
The problem is that vacuum running too slow tends to result in
table and index bloat. Which results in less efficient cache use,
slower scans, and generally worsening
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
On 06/02/2013 05:56 AM, Robert Haas wrote:
(b) users
making ridiculous settings changes to avoid the problems caused by
anti-wraparound vacuums kicking in at inconvenient times and eating up
too many resources.
On Mon, Jun 3, 2013 at 6:34 AM, Kevin Grittner kgri...@ymail.com wrote:
Where I hit a nightmare scenario with an anti-wraparound
autovacuum, personally, was after an upgrade using pg_dump piped to
psql. At a high OLTP transaction load time (obviously the most
likely time for it to kick in,
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
-
I'll whack in some manual VACUUM cron jobs during low load maintenance
hours and hope that keeps the worst of the problem away, that's what
random forum posts on the Internet say to do.
- oh my, why did my DB just do
On Thu, May 30, 2013 at 7:48 PM, Josh Berkus j...@agliodbs.com wrote:
The big, big picture is this:
90% of our users need to think about VACUUM/ANALYZE
at least 10% of the time
and 10% of our users need to think about it
almost 90% of the time.
That's considerably better than
On Mon, Jun 3, 2013 at 1:20 AM, Robert Haas robertmh...@gmail.com wrote:
On Sun, Jun 2, 2013 at 2:44 PM, Jeff Janes jeff.ja...@gmail.com wrote:
Do we know why anti-wraparound uses so many resources in the first place?
The default settings seem to be quite conservative to me, even for a system
On 06/02/2013 05:56 AM, Robert Haas wrote:
On Sat, Jun 1, 2013 at 1:49 PM, Simon Riggs si...@2ndquadrant.com wrote:
On 30 May 2013 19:48, Josh Berkus j...@agliodbs.com wrote:
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I
Craig Ringer cr...@2ndquadrant.com wrote:
On 06/02/2013 05:56 AM, Robert Haas wrote:
I agree with all that. I don't have any data either, but I agree that
AFAICT it seems to mostly be a problem for large (terabyte-scale)
databases, or ones that are dreadfully short of I/O bandwidth. AWS,
On 2013-06-02 11:44:04 -0700, Jeff Janes wrote:
Do we know why anti-wraparound uses so many resources in the first place?
The default settings seem to be quite conservative to me, even for a
system that has only a single 5400 rpm hdd (and even more so for any real
production system that would
I agree with all that. I don't have any data either, but I agree that
AFAICT it seems to mostly be a problem for large (terabyte-scale)
databases, or ones that are dreadfully short of I/O bandwidth. AWS,
I'm looking at you.
Well, at this point, numerically I'd bet that more than 50% of our
Jeff,
Do we know why anti-wraparound uses so many resources in the first place?
The default settings seem to be quite conservative to me, even for a
system that has only a single 5400 rpm hdd (and even more so for any real
production system that would be used for a many-GB database).
I
On Mon, Jun 3, 2013 at 1:28 PM, Josh Berkus j...@agliodbs.com wrote:
Does anti-wraparound vacuum (AWAV) write synchronously? If so, there's
a potential whole world of hurt there.
Not any moreso than anything else ... although it probably does a very
high percentage of FPIs, which might lead to
Also, locking while it does its work.
Eh?
Even if we're doing lazy vacuum, we have to lock a few pages at a time
of each table. This does result in response time delays on the current
workload, which can be quite bad if it's a highly contended table already.
--
Josh Berkus
PostgreSQL
On 2013-06-03 11:00:38 -0700, Josh Berkus wrote:
Also, locking while it does its work.
Eh?
Even if we're doing lazy vacuum, we have to lock a few pages at a time
of each table. This does result in response time delays on the current
workload, which can be quite bad if it's a highly
On 06/03/2013 11:12 AM, Andres Freund wrote:
On 2013-06-03 11:00:38 -0700, Josh Berkus wrote:
Also, locking while it does its work.
Eh?
Even if we're doing lazy vacuum, we have to lock a few pages at a time
of each table. This does result in response time delays on the current
workload,
On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote:
I can't rule that out. Personally, I've always attributed it to the
fact that it's (a) long and (b) I/O-intensive. But it's not
impossible there could also be bugs lurking.
It could be related to the OS. I have no evidence for
On Mon, Jun 3, 2013 at 2:56 PM, Josh Berkus j...@agliodbs.com wrote:
Really? I though vacuum held onto its locks until it reached
vacuum_cost. If it doesn't, then maybe we should adjust the default for
vacuum_cost_limit upwards.
That would be completely insane.
Or in other words, no, it
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
I've seen cases on Stack Overflow and elsewhere in which disk merge
sorts perform vastly better than in-memory quicksort, so the user
benefited from greatly *lowering* work_mem.
I've heard of that happening on Oracle,
On Mon, Jun 3, 2013 at 3:48 PM, Martijn van Oosterhout
klep...@svana.org wrote:
On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote:
I can't rule that out. Personally, I've always attributed it to the
fact that it's (a) long and (b) I/O-intensive. But it's not
impossible there
Robert Haas robertmh...@gmail.com wrote:
Martijn van Oosterhout klep...@svana.org wrote:
On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote:
It could be related to the OS. I have no evidence for or against, but
it's possible that OS write-out routines defeat the careful cost based
On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote:
But it seems like the kernel is disposed to cache large amounts
of dirty data for an unbounded period of time even if the I/O
system is completely idle,
It's not unbounded time. Last I heard, the default was 30 seconds.
On 06/04/2013 05:27 AM, Peter Geoghegan wrote:
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote:
I've seen cases on Stack Overflow and elsewhere in which disk merge
sorts perform vastly better than in-memory quicksort, so the user
benefited from greatly *lowering*
On 2013-06-03 16:41:32 -0700, Peter Geoghegan wrote:
On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote:
But it seems like the kernel is disposed to cache large amounts
of dirty data for an unbounded period of time even if the I/O
system is completely idle,
It's not
All that has pretty significantly changed - and imo improved! - in the
last year or so of kernel development. Unfortunately it will take a
while till we commonly see those kernels being used :(
... after being completely broken for 3.2 through 3.5.
We're actually using 3.9 in production on
On Saturday, June 1, 2013, Robert Haas wrote:
I agree with all that. I don't have any data either, but I agree that
AFAICT it seems to mostly be a problem for large (terabyte-scale)
databases, or ones that are dreadfully short of I/O bandwidth. AWS,
I'm looking at you.
It would be
On Sun, Jun 2, 2013 at 2:44 PM, Jeff Janes jeff.ja...@gmail.com wrote:
Do we know why anti-wraparound uses so many resources in the first place?
The default settings seem to be quite conservative to me, even for a system
that has only a single 5400 rpm hdd (and even more so for any real
On 30 May 2013 19:48, Josh Berkus j...@agliodbs.com wrote:
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I wanted to take a step back
and take a look at the big picture for VACUUM, FREEZE and ANALYZE.
That is a very
On Sat, Jun 1, 2013 at 1:49 PM, Simon Riggs si...@2ndquadrant.com wrote:
On 30 May 2013 19:48, Josh Berkus j...@agliodbs.com wrote:
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I wanted to take a step back
and take a look
Folks,
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I wanted to take a step back
and take a look at the big picture for VACUUM, FREEZE and ANALYZE.
Otherwise, we're liable to repeat the 8.4 problem of making one
operation
Hi,
On 2013-05-30 11:48:12 -0700, Josh Berkus wrote:
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I wanted to take a step back
and take a look at the big picture for VACUUM, FREEZE and ANALYZE.
Otherwise, we're liable to
On 30 May 2013 19:48, Josh Berkus j...@agliodbs.com wrote:
Folks,
There's currently some great ideas bouncing around about eliminating the
overhead associated with FREEZE. However, I wanted to take a step back
and take a look at the big picture for VACUUM, FREEZE and ANALYZE.
Otherwise,
Problem: As of 9.3, there's a significant benefit to vacuum freezing
tables early so that index-only scan is enabled, since freezing also
updates the visibility map. However, with default settings, such
freezing only happens for data which is very old. This means that
index-only scan is less
Inhowfar did 8.4 make freezing worse? I can't remember any new problems
there?
Before the Visibility Map, we always vacuumed all pages in a relation
when it was vacuumed at all. This means that we froze tuples at
vacuum_min_freeze_age, not at autovacuum_max_freeze_age, which is when
we do it
On 2013-05-30 12:11:23 -0700, Josh Berkus wrote:
Inhowfar did 8.4 make freezing worse? I can't remember any new problems
there?
Before the Visibility Map, we always vacuumed all pages in a relation
when it was vacuumed at all. This means that we froze tuples at
vacuum_min_freeze_age,
On 2013-05-30 20:01:01 +0100, Thom Brown wrote:
Problem: As of 9.3, there's a significant benefit to vacuum freezing
tables early so that index-only scan is enabled, since freezing also
updates the visibility map. However, with default settings, such
freezing only happens for data which is
If we have reason to vacuum the relation we do it at
vacuum_freeze_table_age, not only at autovacuum_max_freeze_age. The
difference is that the latter triggers a vacuum, while the former only
changes a partial vacuum into a full one.
Calling that behaviour unconditionally worse is, err,
On 2013-05-30 12:18:29 -0700, Josh Berkus wrote:
If we have reason to vacuum the relation we do it at
vacuum_freeze_table_age, not only at autovacuum_max_freeze_age. The
difference is that the latter triggers a vacuum, while the former only
changes a partial vacuum into a full one.
44 matches
Mail list logo
