Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Locks Postgres

[GENERAL] Locks Postgres

From
Patrick B
Date:
Hi guys

I just wanna understand the locks in a DB server:
Imagem inline 1

Access share = Does that mean queries were waiting because an update/delete/insert was happening?

I'm asking because I got a very big spike with > 30 seconds web response time.
Running PG 9.3

Thanks!
Patrick
Attachment

Re: [GENERAL] Locks Postgres

From
Adrian Klaver
Date:
On 02/09/2017 09:00 PM, Patrick B wrote:
> Hi guys
>
> I just wanna understand the locks in a DB server:
> Imagem inline 1
>
> Access share = Does that mean queries were waiting because an
> update/delete/insert was happening?

https://www.postgresql.org/docs/9.3/static/explicit-locking.html

>
> I'm asking because I got a very big spike with > 30 seconds web response
> time.
> Running PG 9.3
>
> Thanks!
> Patrick


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] Locks Postgres

From
John R Pierce
Date:
On 2/9/2017 9:00 PM, Patrick B wrote:
>
> Access share = Does that mean queries were waiting because an
> update/delete/insert was happening?
>

access share is taken by a SELECT, and all it blocks is an ACCESS
EXCLUSIVE lock, which is taken by operations like ALTER TABLE, VACUUM
FULL, and such global table operations.   that spike in your graph
suggests you had 8000 concurrent SELECT operations going on, which is
likely way more than you have compute and IO resources to handle
efficiently.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Locks Postgres

From
John R Pierce
Date:
On 2/9/2017 9:16 PM, John R Pierce wrote:
> that spike in your graph suggests you had 8000 concurrent SELECT
> operations...

errr, 7000, still way too many.


--
john r pierce, recycling bits in santa cruz

Re: [GENERAL] Locks Postgres

From
Patrick B
Date:
2017-02-10 18:18 GMT+13:00 John R Pierce <pierce@hogranch.com>:
On 2/9/2017 9:16 PM, John R Pierce wrote:
that spike in your graph suggests you had 8000 concurrent SELECT operations...

errr, 7000, still way too many.

Thanks a lot John!! Got it

PAtrick

Re: [GENERAL] Locks Postgres

From
Jeff Janes
Date:
On Thu, Feb 9, 2017 at 9:00 PM, Patrick B <patrickbakerbr@gmail.com> wrote:
Hi guys

I just wanna understand the locks in a DB server:
Imagem inline 1

Access share = Does that mean queries were waiting because an update/delete/insert was happening?



It would seem more plausible that your chart is showing the locks that are *held*, not the locks that are *waiting to be granted*.  But without knowing where the chart came from, we can't know for sure.

If those are locks being held, it just means your server was kind of busy (which you already knew).  But we don't know how busy.  A single complex query can easily hold several dozens locks.

Cheers,

Jeff
Attachment