Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-11-02 Thread Jeff Janes
On Mon, Nov 2, 2015 at 7:32 AM, Tom Dearman wrote: > Thanks for the prompt replies so far, I have done some more investigation to > be able to clearly answer some of the question. > > The original shared-buffers was 8G and I have done another run on Friday > using this old value instead of my more

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-11-02 Thread Jim Nasby
On 11/2/15 9:32 AM, Tom Dearman wrote: My system under load is using just over 500M of the shared_buffer at usage count >= 3. Our system is very write heavy, with all of the big tables written to but not read from (at least during the load test run). Although our db will grow (under load) to 1

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-11-02 Thread Tom Dearman
Thanks for the prompt replies so far, I have done some more investigation to be able to clearly answer some of the question. The original shared-buffers was 8G and I have done another run on Friday using this old value instead of my more recent 1G limit. There was no noticeable improvement. I

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-10-28 Thread Jeff Janes
On Wed, Oct 28, 2015 at 8:43 AM, Tom Dearman wrote: > We have a performance problem when our postgres is under high load. The CPU > usage is very low, we have 48 cores for our postgres and the idle time > averages at 90%. The problem is we get spikes in our transaction times > which don’t appear

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-10-28 Thread Igor Neyman
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Dearman Sent: Wednesday, October 28, 2015 11:44 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5 We have a performance problem when

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-10-28 Thread Jim Nasby
On 10/28/15 12:11 PM, Tom Dearman wrote: It is also interesting that a later attempt to get the exclusive lock by process 41911 says it is waiting for id 41907 even though according to the log other processes have already acquired the lock. Those would be different acquisitions of the same lock

Re: [GENERAL] Waiting on ExclusiveLock on extension 9.3, 9.4 and 9.5

2015-10-28 Thread Tom Dearman
The processes involved are always client (java) processes which finish (normally) quickly. The actual process can be any of the client processes, the point is that an extension to the relation is going on and it seems to acquire excessive locks. You typically see a whole series of process id c

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-20 Thread Andomar
Another update (let me know if I'm mailing too much). > 59.73% postmaster [kernel.kallsyms] [k] > compaction_alloc The compaction_alloc function seemed to point to issues with transparent huge pages (THP.) I tried to turn this off with: echo never > /sys/kernel/mm/transparen

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-20 Thread Andomar
Would you be able to get a stack trace of a backend that's holding an extension lock? Or maybe perf would provide some insight. The outage occurred again but briefer. There were no ExclusiveLock messages, presumably because the timeout for logging locks was not exceeded. But all available c

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-19 Thread Jim Nasby
On 4/19/15 3:08 PM, Jim Nasby wrote: > I do suspect your pgfree/s is very high though; putting 200k pages/s on > the free list seems like something's broken. > The system has constant and considerable load of small writes. The pg_activity tool shows 300 IOPs sustained (it claims max IPs above

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-19 Thread Jim Nasby
On 4/19/15 4:24 AM, Andomar wrote: To put the top question first: How can table extension locks explain a a massive spike in CPU usage? I can imagine 400 connections waiting on disk I/O, but then, wouldn't they all be sleeping? Not necessarily. Spinlocks don't put the process to sleep, but th

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-19 Thread Andomar
To put the top question first: How can table extension locks explain a a massive spike in CPU usage? I can imagine 400 connections waiting on disk I/O, but then, wouldn't they all be sleeping? > Ok, that's a MAJOR hint, because relation 1249 is a system catalog; > namely pg_attribute. So I th

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Jim Nasby
On 4/17/15 4:22 PM, Andomar wrote: Yes, but did you have the same workload when you upgraded to 9.3 as you do today? The workload is very similar. We upgraded from 9.1 to 9.3 only two months ago, and our usage statistics have not changed much. There were no "remaining connection slots are res

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Andomar
Yes, but did you have the same workload when you upgraded to 9.3 as you do today? The workload is very similar. We upgraded from 9.1 to 9.3 only two months ago, and our usage statistics have not changed much. There were no "remaining connection slots are reserved for non-replication superus

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Jim Nasby
On 4/16/15 4:39 PM, Andomar wrote: Thanks for your reply. This issue has been complained several times, and here is the most recent one: http://www.postgresql.org/message-id/0ddfb621-7282-4a2b-8879-a47f7cecb...@simply.name That post is about a server with huge shared_buffers, but ours is just

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Qingqing Zhou
On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund wrote: > Hm. I'm not aware of related changes in 9.4? 9.5 should be a bit better, > but I don't think 9.4 will make much of a difference. > You are right. I mis-read the check-in log. > I don't really agree that that's the most important bit. See > h

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Jeff Janes
On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund wrote: > On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > > On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > > > > b) How can you find the name of the relation being extended? based on > the > > > relation number. > > select ::regclass; > > Tha

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Andres Freund
On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > > After upgrading our database from 9.3.5 to 9.4.1 last night, the server > > suffers from high CPU spikes. During these spikes, there are a lot of these > > messages in the logs: > > > > pro

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-17 Thread Andomar
Are you able to take some 'perf top' during high CPU spike and see what's burning CPU there? Though the issue is related to blocking, but high CPU spikes may hint some spinning to acquire behavior. Will do, although hopefully the spikes were only growing pains after the upgrade. If your prev

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-16 Thread Qingqing Zhou
On Thu, Apr 16, 2015 at 2:39 PM, Andomar wrote: > That post is about a server with huge shared_buffers, but ours is just 8GB. > Total memory 48GB memory on a dedicated server. Checkpoints write around 2% > of the buffers. Are you able to take some 'perf top' during high CPU spike and see what's bu

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-16 Thread Andomar
Thanks for your reply. This issue has been complained several times, and here is the most recent one: http://www.postgresql.org/message-id/0ddfb621-7282-4a2b-8879-a47f7cecb...@simply.name That post is about a server with huge shared_buffers, but ours is just 8GB. Total memory 48GB memory on a de

Re: [GENERAL] Waiting on ExclusiveLock on extension

2015-04-16 Thread Qingqing Zhou
On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > After upgrading our database from 9.3.5 to 9.4.1 last night, the server > suffers from high CPU spikes. During these spikes, there are a lot of these > messages in the logs: > > process X still waiting for ExclusiveLock on extension of relation