RE: Exclusive Locks on Insert into large Logging tables
> We have heard reports of excessive contention for relation extension locks before, and there's been work done to mitigate that by extending the relation more than one block at a time. Maybe you're hitting a case where that doesn't work well?
One of the larger tables holds a lot of binary data and for that table the relation that is exclusively locked is the TOAST table.
The other tables are log tables. All three tables have a column that is 'text'. Would that make a difference?
> Or maybe the problem is just that you've got under-specced mass storage? If the filesystem goes out to lunch when asked to do a lot of writing, it'd very possibly result in this symptom.
We are running on a 8000 IOPS drive. Partitioning is definitely on the road map. Log records from 10+ years ago really need to be moved onto a slower spec'd tablespace.
Want faster answers from InfoTech? Check out www.portal.jims.net
 | Peter Neave | DevOps Lead | Peter.Neave@jims.net
| ||||
This email and any attachment(s) are confidential. If you are not the intended recipient you must not copy, use, disclose, distribute or rely on the information contained in it. If you have received this email in error, please notify the sender immediately by reply email and delete the email from your system. Confidentiality and legal privilege attached to this communication are not waived or lost by reason of mistaken delivery to you. While Jim's Group employs Anti-Virus Software, we cannot guarantee that this email or the attachment(s) are unaffected by computer virus, corruption or other defects and we recommend that this email and any attachments be tested before opening.
Please consider the environment before printing this email.
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, 19 February 2019 12:58 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Peter Neave <Peter.Neave@jims.net>; pgsql-novice@lists.postgresql.org
Subject: Re: Exclusive Locks on Insert into large Logging tables
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Peter Neave wrote:
>> I’ve been trying to get down to the source of some locks. ...
>> LOG: process 116665 acquired ExclusiveLock on extension of relation 59049887 of database 59049867 after 5838.015 ms).
> Exclusive locks conflict with everything but SELECTs, see
> https://www.postgresql.org/docs/current/explicit-locking.html#TABLE-LO
> CK-COMPATIBILITY
True, but not very relevant, because:
> This particular exclusive lock is takes when the relation has to be
> extended with new blocks because there is no more room for the new row
> in the existing blocks of the table.
Yeah. This is an implementation-level lock that is independent of anything you can take at the SQL level. It's needed because only one backend can extend (physically add space to) a table at a time.
If you have lots of transactions all trying to insert into one table concurrently, some contention for that lock is to be expected.
Blocking for ~6 sec is pretty darn horrid though --- the expected lock hold time is only what's needed to write out a page full of zeroes.
We have heard reports of excessive contention for relation extension locks before, and there's been work done to mitigate that by extending the relation more than one block at a time. Maybe you're hitting a case where that doesn't work well?
Or maybe the problem is just that you've got under-specced mass storage? If the filesystem goes out to lunch when asked to do a lot of writing, it'd very possibly result in this symptom.
regards, tom lane
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
Click here to report this message as spam:
https://console.mailguard.com.au/ras/1V8T9ILUC1/6T3fIF2lAR4a3VRoxx6NZR/5
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, 19 February 2019 12:58 PM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Peter Neave <Peter.Neave@jims.net>; pgsql-novice@lists.postgresql.org
Subject: Re: Exclusive Locks on Insert into large Logging tables
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Peter Neave wrote:
>> I’ve been trying to get down to the source of some locks. ...
>> LOG: process 116665 acquired ExclusiveLock on extension of relation 59049887 of database 59049867 after 5838.015 ms).
> Exclusive locks conflict with everything but SELECTs, see
> https://www.postgresql.org/docs/current/explicit-locking.html#TABLE-LO
> CK-COMPATIBILITY
True, but not very relevant, because:
> This particular exclusive lock is takes when the relation has to be
> extended with new blocks because there is no more room for the new row
> in the existing blocks of the table.
Yeah. This is an implementation-level lock that is independent of anything you can take at the SQL level. It's needed because only one backend can extend (physically add space to) a table at a time.
If you have lots of transactions all trying to insert into one table concurrently, some contention for that lock is to be expected.
Blocking for ~6 sec is pretty darn horrid though --- the expected lock hold time is only what's needed to write out a page full of zeroes.
We have heard reports of excessive contention for relation extension locks before, and there's been work done to mitigate that by extending the relation more than one block at a time. Maybe you're hitting a case where that doesn't work well?
Or maybe the problem is just that you've got under-specced mass storage? If the filesystem goes out to lunch when asked to do a lot of writing, it'd very possibly result in this symptom.
regards, tom lane
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
Click here to report this message as spam:
https://console.mailguard.com.au/ras/1V8T9ILUC1/6T3fIF2lAR4a3VRoxx6NZR/5
Message protected by MailGuard: e-mail anti-virus, anti-spam and content filtering.
http://www.mailguard.com.au
Вложения
В списке pgsql-novice по дате отправления: