On Sat, Feb 5, 2011 at 3:34 PM, Cédric Villemain
wrote:
> Anyway, without GUC is fine too as it won't fill the /var/log itself !
> I am just not opposed to have new GUC in those areas (log && debug).
OK. Committed without a new GUC, at least for now.
--
Robert Haas
EnterpriseDB: http://www.ent
2011/2/5 Robert Haas :
> On Sat, Feb 5, 2011 at 3:20 PM, Cédric Villemain
> wrote:
>>> In the case where a table is skipped for this reason, we log a message
>>> at log level LOG. The version of the patch I posted does that
>>> unconditionally, but my intention was to change it before commit so
>
On Sat, Feb 5, 2011 at 3:20 PM, Cédric Villemain
wrote:
>> In the case where a table is skipped for this reason, we log a message
>> at log level LOG. The version of the patch I posted does that
>> unconditionally, but my intention was to change it before commit so
>> that it only logs the messag
2011/2/5 Robert Haas :
> On Sat, Feb 5, 2011 at 12:54 PM, Cédric Villemain
> wrote:
>> what do you implement exactly ?
>> * The original request from Josh to get LOG when autovac can not run
>> because of locks
>> * VACOPT_NOWAIT, what is it ?
>
> What the patch implements is:
>
> If autovacuum ca
On Sat, Feb 5, 2011 at 12:54 PM, Cédric Villemain
wrote:
> what do you implement exactly ?
> * The original request from Josh to get LOG when autovac can not run
> because of locks
> * VACOPT_NOWAIT, what is it ?
What the patch implements is:
If autovacuum can't get the table lock immediately, i
2011/2/4 Robert Haas :
> On Sun, Jan 30, 2011 at 10:26 PM, Robert Haas wrote:
>> On Sun, Jan 30, 2011 at 10:03 PM, Alvaro Herrera
>> wrote:
>>> Excerpts from Robert Haas's message of dom ene 30 23:37:51 -0300 2011:
>>>
Unless I'm missing something, making autovacuum.c call
ConditionalLo
On Fri, Feb 4, 2011 at 12:59 AM, Josh Berkus wrote:
> Robert,
>
>> Seeing as how there seem to be neither objections nor endorsements,
>> I'm inclined to commit what I proposed more or less as-is. There
>> remains the issue of what do about the log spam. Josh Berkus
>> suggested logging it when
On Sun, Jan 30, 2011 at 10:26 PM, Robert Haas wrote:
> On Sun, Jan 30, 2011 at 10:03 PM, Alvaro Herrera
> wrote:
>> Excerpts from Robert Haas's message of dom ene 30 23:37:51 -0300 2011:
>>
>>> Unless I'm missing something, making autovacuum.c call
>>> ConditionalLockRelationOid() is not going to
On Sun, Jan 30, 2011 at 10:03 PM, Alvaro Herrera
wrote:
> Excerpts from Robert Haas's message of dom ene 30 23:37:51 -0300 2011:
>
>> Unless I'm missing something, making autovacuum.c call
>> ConditionalLockRelationOid() is not going to work, because the vacuum
>> transaction isn't started until w
Excerpts from Robert Haas's message of dom ene 30 23:37:51 -0300 2011:
> Unless I'm missing something, making autovacuum.c call
> ConditionalLockRelationOid() is not going to work, because the vacuum
> transaction isn't started until we get all the way down to
> vacuum_rel().
Maybe we need Condit
On Mon, Jan 17, 2011 at 4:08 PM, Tom Lane wrote:
> Simon Riggs writes:
>> On Mon, 2011-01-17 at 14:46 -0500, Tom Lane wrote:
>>> Do we actually need a lock timeout either? The patch that was being
>>> discussed just involved failing if you couldn't get it immediately.
>>> I suspect that's suffic
On mån, 2011-01-17 at 17:26 -0800, Josh Berkus wrote:
> However, it's hard for me to imagine a real-world situation where a
> table would be under repeated full-table-locks from multiple
> connections. Can anyone else?
If you want to do assertion-type checks at the end of transactions in
the curr
Josh Berkus writes:
> On 1/17/11 11:46 AM, Tom Lane wrote:
>> Do we actually need a lock timeout either? The patch that was being
>> discussed just involved failing if you couldn't get it immediately.
>> I suspect that's sufficient for AV. At least, nobody's made a
>> compelling argument why we
On Mon, Jan 17, 2011 at 8:26 PM, Josh Berkus wrote:
> On 1/17/11 11:46 AM, Tom Lane wrote:
>> Do we actually need a lock timeout either? The patch that was being
>> discussed just involved failing if you couldn't get it immediately.
>> I suspect that's sufficient for AV. At least, nobody's made
On 1/17/11 11:46 AM, Tom Lane wrote:
> Do we actually need a lock timeout either? The patch that was being
> discussed just involved failing if you couldn't get it immediately.
> I suspect that's sufficient for AV. At least, nobody's made a
> compelling argument why we need to expend a very subst
Simon Riggs writes:
> On Mon, 2011-01-17 at 14:46 -0500, Tom Lane wrote:
>> Do we actually need a lock timeout either? The patch that was being
>> discussed just involved failing if you couldn't get it immediately.
>> I suspect that's sufficient for AV. At least, nobody's made a
>> compelling ar
On Mon, 2011-01-17 at 14:46 -0500, Tom Lane wrote:
> Josh Berkus writes:
> > However, we'd want a separate lock timeout for autovac, of course. I'm
> > not at all keen on a *statement* timeout on autovacuum; as long as
> > autovacuum is doing work, I don't want to cancel it. Also, WTF would we
>
Josh Berkus writes:
> However, we'd want a separate lock timeout for autovac, of course. I'm
> not at all keen on a *statement* timeout on autovacuum; as long as
> autovacuum is doing work, I don't want to cancel it. Also, WTF would we
> set it to?
Yeah --- in the presence of vacuum cost delay,
On Sun, Jan 16, 2011 at 8:36 PM, Simon Riggs wrote:
> I agree with you, but if we want it *this* release, on top of all the
> other features we have queued, then I suggest we compromise. If you hold
> out for more feature, you may get less.
>
> Statement timeout = 2 * (100ms + autovacuum_vacuum_co
On Sun, 2011-01-16 at 12:50 -0800, Josh Berkus wrote:
> On 1/16/11 11:19 AM, Simon Riggs wrote:
> > I would prefer it if we had a settable lock timeout, as suggested many
> > moons ago. When that was discussed before it was said there was no
> > difference between a statement timeout and a lock tim
On 1/16/11 11:19 AM, Simon Riggs wrote:
> I would prefer it if we had a settable lock timeout, as suggested many
> moons ago. When that was discussed before it was said there was no
> difference between a statement timeout and a lock timeout, but I think
> there clearly is, this case being just one
On Sun, 2011-01-16 at 13:08 -0500, Greg Smith wrote:
> Simon Riggs wrote:
> > I'm fairly confused by this thread.
> >
>
> That's becuase you think it has something to do with cancellation, which
> it doesn't. The original report here noted a real problem but got the
> theorized cause wrong.
Greg Smith writes:
> Tom Lane wrote:
>> No, I don't believe we should be messing with the semantics of
>> try_relation_open. It is what it is.
> With only four pretty simple callers to the thing, and two of them
> needing the alternate behavior, it seemed a reasonable place to modify
> to me.
Tom Lane wrote:
No, I don't believe we should be messing with the semantics of
try_relation_open. It is what it is.
With only four pretty simple callers to the thing, and two of them
needing the alternate behavior, it seemed a reasonable place to modify
to me. I thought the "nowait" bool
Greg Smith writes:
> Simon Riggs wrote:
>> I'm fairly confused by this thread.
> That's becuase you think it has something to do with cancellation, which
> it doesn't. The original report here noted a real problem but got the
> theorized cause wrong.
I think that cancellations are also a pote
Simon Riggs wrote:
I'm fairly confused by this thread.
That's becuase you think it has something to do with cancellation, which
it doesn't. The original report here noted a real problem but got the
theorized cause wrong. It turns out the code that acquires a lock when
autovacuum decides
Simon Riggs writes:
> I'm fairly confused by this thread.
> We *do* emit a message when we cancel an autovacuum task. We went to a
> lot of trouble to do that. The message is DEBUG2, and says
> "sending cancel to blocking autovacuum pid =".
That doesn't necessarily match one-to-one with actual c
On Sun, 2011-01-16 at 11:47 -0500, Tom Lane wrote:
> Greg Smith writes:
> > try_relation_open calls LockRelationOid, which blocks. There is also a
> > ConditionalLockRelationOid, which does the same basic thing except it
> > exits immediately, with a false return code, if it can't acquire the
Greg Smith writes:
> try_relation_open calls LockRelationOid, which blocks. There is also a
> ConditionalLockRelationOid, which does the same basic thing except it
> exits immediately, with a false return code, if it can't acquire the
> lock. I think we just need to nail down in what existing
Robert Haas wrote:
On Sat, Jan 15, 2011 at 11:14 AM, Tom Lane wrote:
Greg Smith writes:
Does try_relation_open need to have a lock acquisition timeout when AV
is calling it?
Hmm. I think when looking at the AV code, I've always subconsciously
assumed that try_relation_open wo
On Sat, Jan 15, 2011 at 11:14 AM, Tom Lane wrote:
> Greg Smith writes:
>> Does try_relation_open need to have a lock acquisition timeout when AV
>> is calling it?
>
> Hmm. I think when looking at the AV code, I've always subconsciously
> assumed that try_relation_open would fail immediately if i
Greg Smith writes:
> Does try_relation_open need to have a lock acquisition timeout when AV
> is calling it?
Hmm. I think when looking at the AV code, I've always subconsciously
assumed that try_relation_open would fail immediately if it couldn't get
the lock. That certainly seems like it woul
Josh Berkus wrote:
The lack of vacuum could be occurring for any of 4 reasons:
1) Locking
2) You have a lot of tables and not enough autovac_workers / too much
sleep time
3) You need to autovac this particular table more frequently, since it
gets dirtied really fast
4) The table has been set wit
David Fetter writes:
> On Fri, Jan 07, 2011 at 08:15:12PM -0500, Greg Smith wrote:
>> [1] Silly aside: I was thinking today that I should draw a chart of
>> all the common objections to code that show up here, looking like
>> those maps you see when walking into a mall. Then we can give a
>> cop
On Fri, Jan 07, 2011 at 08:15:12PM -0500, Greg Smith wrote:
> [1] Silly aside: I was thinking today that I should draw a chart of
> all the common objections to code that show up here, looking like
> those maps you see when walking into a mall. Then we can give a
> copy to new submitters with a b
Josh Berkus wrote:
It occurs to me that another way of diagnosis would simply be a way to
cause the autovac daemon to spit out output we could camp on, *without*
requiring the huge volumes of output also required for DEBUG3. This
brings us back to the logging idea again.
Right. I don't kno
Greg,
> It's already possible to detect the main symptom--dead row percentage is
> much higher than the autovacuum threshold, but there's been no recent
> autovacuum. That makes me less enthusiastic that there's such a genuine
> need to justify the overhead of storing more table stats just to det
Josh Berkus wrote:
Or should it perhaps be a per-table counter in pg_stat_user_tables,
given your statement above?
Or even a timestamp: last_autovacuum_attempt, which would record the
last time autovacuum was tried. If that's fairly recent and you have a
large number of dead rows, you kno
Robert Treat wrote:
This is a great use case for user level tracing support. Add a probe
around these bits, and you can capture the information when you need
it.
Sure. I would also like a pony.
--
Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD
PostgreSQL Training, Serv
> This is a great use case for user level tracing support. Add a probe
> around these bits, and you can capture the information when you need
> it.
Yeah, would be lovely if user-level tracing existed on all platforms.
--
-- Josh Berkus
On Wed, Jan 5, 2011 at 2:27 PM, Josh Berkus wrote:
>
>> If you could gather more info on whether this logging catches the
>> problem cases you're seeing, that would really be the right test for the
>> patch's usefulness. I'd give you solid 50/50 odds that you've correctly
>> diagnosed the issue,
> If you could gather more info on whether this logging catches the
> problem cases you're seeing, that would really be the right test for the
> patch's usefulness. I'd give you solid 50/50 odds that you've correctly
> diagnosed the issue, and knowing for sure would make advocating for this
> log
On Wed, Jan 5, 2011 at 07:55, Greg Smith wrote:
> a bit of work in userland, I don't see this even being justified as an INFO
> or LOG level message. Anytime I can script a SQL-level monitor for
> something that's easy to tie into Nagios or something, I greatly prefer that
> to log file scrapi
Josh Berkus wrote:
I've been trying to diagnose in a production database why certain tables
never get autovacuumed despite having a substantial % of updates. The
obvious reason is locks blocking autovacuum from vacuuming the table ...
Missed this dicussion when it popped up but have plenty
Itagaki Takahiro writes:
> On Thu, Nov 18, 2010 at 08:35, Tom Lane wrote:
>> Well, the way to deal with that would be to add a GUC that enables
>> reporting of those messages at LOG level. Â But it's a bit hard to argue
>> that we need such a thing without more evidence. Â Maybe you could just
>>
On Thu, Nov 18, 2010 at 08:35, Tom Lane wrote:
>> Yeah, it would be really good to be able to log that without bumping the
>> log levels of the server in general to DEBUG3.
>
> Well, the way to deal with that would be to add a GUC that enables
> reporting of those messages at LOG level. But it's
Josh Berkus writes:
>> There *is* an elog(DEBUG3) in autovacuum.c
>> that reports whether autovac thinks a table needs vacuumed/analyzed ...
>> maybe that needs to be a tad more user-accessible.
> Yeah, it would be really good to be able to log that without bumping the
> log levels of the server
> It's hard to tell, because you're just handwaving about what it is you
> think isn't being logged; nor is it clear whether you have any evidence
> that locks are the problem. Offhand I'd think it at least as likely
> that autovacuum thinks it doesn't need to do anything, perhaps because
> of a
Josh Berkus writes:
> I've been trying to diagnose in a production database why certain tables
> never get autovacuumed despite having a substantial % of updates. The
> obvious reason is locks blocking autovacuum from vacuuming the table ...
> but the trick is we don't log such blocking behavior,
On Wed, 2010-11-17 at 13:46 -0800, Josh Berkus wrote:
> Hackers,
>
> I've been trying to diagnose in a production database why certain tables
> never get autovacuumed despite having a substantial % of updates. The
> obvious reason is locks blocking autovacuum from vacuuming the table ...
> but th
50 matches
Mail list logo
