Locks Postgres

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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:
[image: 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