On Tue, Jun 18, 2013 at 6:11 PM, Jim Nasby 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 than quick
On Jun 3, 2013, at 6:45 PM, Craig Ringer wrote:
> On 06/04/2013 05:27 AM, Peter Geoghegan wrote:
>> On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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
>>>
Jeff Janes 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
transactions.
http://www.po
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
On Mon, Jun 3, 2013 at 6:42 AM, Andres Freund wrote:
> 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
> 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
Craig Ringer wrote:
> On 06/07/2013 04:38 AM, Jeff Janes wrote:
>> Craig Ringer
> 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 performance.
>
> I've repeatedly seen the u
On 06/07/2013 04:38 AM, Jeff Janes wrote:
> On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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 wrong act
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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 an emergenc
On Mon, Jun 3, 2013 at 6:34 AM, Kevin Grittner 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, because it
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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.
>
> Some rec
On Thu, May 30, 2013 at 7:48 PM, Josh Berkus 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 was the cas
> 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 2013-06-03 16:41:32 -0700, Peter Geoghegan wrote:
> On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner 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 unbo
On 06/04/2013 05:27 AM, Peter Geoghegan wrote:
> On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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 hea
On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner 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.
I'm prett
Robert Haas wrote:
> Martijn van Oosterhout 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
>>> throttling that PostgreSQL do
On Mon, Jun 3, 2013 at 3:48 PM, Martijn van Oosterhout
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 could also b
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer 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, when the external sor
On Mon, Jun 3, 2013 at 2:56 PM, Josh Berkus 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 doesn't do anythin
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 eviden
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 c
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'
>> 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 E
On Mon, Jun 3, 2013 at 1:28 PM, Josh Berkus 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 lots of checkpo
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 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
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 wo
Craig Ringer 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,
>> I'm looking at
On 06/02/2013 05:56 AM, Robert Haas wrote:
> On Sat, Jun 1, 2013 at 1:49 PM, Simon Riggs wrote:
>> On 30 May 2013 19:48, 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
>
On Mon, Jun 3, 2013 at 1:20 AM, Robert Haas wrote:
> On Sun, Jun 2, 2013 at 2:44 PM, 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
On Sun, Jun 2, 2013 at 2:44 PM, 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
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 Sat, Jun 1, 2013 at 1:49 PM, Simon Riggs wrote:
> On 30 May 2013 19:48, 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,
On 30 May 2013 19:48, 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.
That is a very commendable approach.
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 on
> 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, er
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
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_freez
> 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
> "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
On 30 May 2013 19:48, Josh Berkus 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, we're liable to
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 t
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 bette
44 matches
Mail list logo
