On Fri, Nov 4, 2011 at 4:07 PM, Claudio Freire wrote:
>> Here again, you've set it to ten times the default value. That
>> doesn't seem like a good idea. I would start with the default and
>> tune down.
>
> Already did that. Waiting to see how it turns out.
Nope, still happening with those chan
On Fri, Nov 4, 2011 at 3:54 PM, Robert Haas wrote:
> On Fri, Nov 4, 2011 at 2:45 PM, Claudio Freire wrote:
>> I don't think 1 second can be such a big difference for the bgwriter,
>> but I might be wrong.
>
> Well, the default value is 200 ms. And I've never before heard of
> anyone tuning it u
On 11/04/2011 01:45 PM, Claudio Freire wrote:
I think you're misinterpreting the value.
It's in microseconds, that's 10 *milli*seconds
Wow. My brain totally skimmed over that section. Everything else is in
milliseconds, so I never even considered it. Sorry about that!
I stand by everything
On Fri, Nov 4, 2011 at 2:45 PM, Claudio Freire wrote:
> I don't think 1 second can be such a big difference for the bgwriter,
> but I might be wrong.
Well, the default value is 200 ms. And I've never before heard of
anyone tuning it up, except maybe to save on power consumption on a
system with
On Fri, Nov 4, 2011 at 3:26 PM, Shaun Thomas wrote:
> On 11/04/2011 12:22 PM, Claudio Freire wrote:
>
>> bgwriter_delay = 1000ms
>> wal_writer_delay=2000ms
>> commit_delay=1
>
> !?
>snip
> "Setting commit_delay can only help when there are many concurrently
> committing transactions, and it is
On 11/04/2011 12:22 PM, Claudio Freire wrote:
bgwriter_delay = 1000ms
wal_writer_delay=2000ms
commit_delay=1
!?
Maybe someone can back me up on this, but my interpretation of these
settings suggests they're *way* too high. That commit_delay especially
makes me want to cry. From the manu
On Fri, Nov 4, 2011 at 2:07 PM, Kevin Grittner
wrote:
> Before anything else, you might want to make sure you've spread your
> checkpoint activity as much as possible by setting
> checkpoint_completion_target = 0.9.
We have
shared_buffers = 2G
bgwriter_delay = 1000ms
effective_io_concurrency=8
s
Claudio Freire wrote:
> On Fri, Nov 4, 2011 at 1:26 PM, Kevin Grittner
> wrote:
>> As already pointed out, SELECT FOR UPDATE will require a disk
>> write of the tuple(s) read. If these are glutting, increasing
>> shared_buffers would tend to make things worse.
>
> I thought shared_buffers impro
On Fri, Nov 4, 2011 at 1:26 PM, Kevin Grittner
wrote:
> As already pointed out, SELECT FOR UPDATE will require a disk write
> of the tuple(s) read. If these are glutting, increasing
> shared_buffers would tend to make things worse.
I thought shared_buffers improved write caching.
We do tend to w
Claudio Freire wrote:
> Now, I'm thinking those writes are catching the DB at a bad moment
-
> we do have regular very write-intensive peaks.
>
> Maybe I should look into increasing shared buffers?
As already pointed out, SELECT FOR UPDATE will require a disk write
of the tuple(s) read. If t
On Fri, Nov 4, 2011 at 12:07 PM, Claudio Freire wrote:
> What are those writes about? HOT vacuuming perhaps?
Every tuple lock requires dirtying the page. Those writes are all
those dirty pages getting flushed out to disk. It's possible that the
OS is allowing the writes to happen asynchronously
On Thu, Nov 3, 2011 at 8:45 PM, Tom Lane wrote:
> But before pursuing that idea, probably first you should
> back up and confirm whether the process is actually waiting, or running,
> or just really slow due to CPU contention. It might be useful to see
> what strace has to say about it.
Thanks
Claudio Freire writes:
> On Thu, Nov 3, 2011 at 4:29 PM, Claudio Freire wrote:
>> Next time I find it blocking, I will check pg_locks directly and post
>> the output.
> Here it is, two instances of the query, while blocked:
Hmm ... definitely seems that you're not blocked on a FOR UPDATE tuple
On Thu, Nov 3, 2011 at 4:29 PM, Claudio Freire wrote:
> Next time I find it blocking, I will check pg_locks directly and post
> the output.
Here it is, two instances of the query, while blocked:
select * from pg_locks where pid = 22636;
locktype| database | relation | page | tuple | virt
On Thu, Nov 3, 2011 at 3:45 PM, Tom Lane wrote:
> Claudio Freire writes:
>> But I cannot figure out which transaction it would be. There *are*, in
>> fact, connections in state, which makes me think
>> those would be the culprit. But for the life of me, I cannot make
>> sense of the pg_locks vie
Claudio Freire writes:
> The same query, without FOR UPDATE, takes just 68 milliseconds.
> With the FOR UPDATE, it takes like half a minute or more to finish.
> Now, I understand the for update part may be blocking on some other
> transaction, and it's probably the case.
Yeah, that's what I'd gu
On Thu, Nov 3, 2011 at 2:51 PM, Claudio Freire wrote:
> What other information should I provide?
Forgot all the usual details:
Server is postgresql 9.0.3, running in linux, quite loaded (load
average ~7), WAL on raid 1 2 spindles, data on raid 10 4 spindles, 16G
RAM.
Could it be high contention
Hi list,
I've been experiencing a weird performance issue lately.
I have a very simple (and usually very fast) query:
SELECT track_logs.id
FROM track_logs
WHERE track_logs.track_id = AND track_logs.track_status_id =
1 AND track_logs.date >= now() - interval '1 hours'
FOR UPDATE
Whose pla
18 matches
Mail list logo
