Re: WALWriteLocks

I appreciate everyone's feedback and help but we seem to be dancing around
the actual question.

What I want to know is if there are other events that would contribute to a
WALWriteLock (eg writing to a data file or the postgresql.log file) or is
it 100% writing to the WAL file. So when we see a sustained spike in
WALWriteLock wait events, we know where to focus our investigation efforts.
The effort itself into the WAL writes is a separate issue. First I want to
know if there is any other process/place that we also need to account for.

Don.

On Thu, Apr 29, 2021 at 3:04 PM Vijaykumar Jain <
vijaykumarjain(dot)github(at)gmail(dot)com> wrote:

> ok my bad,
> it seems it was renamed,
>
> WALWriteLock in 9,6 to WALWrite :)
>
> so Yes, I do see WALWrite LWLocks.
>
> Thanks,
> Vijay
>
> On Fri, 30 Apr 2021 at 01:30, Vijaykumar Jain <
> vijaykumarjain(dot)github(at)gmail(dot)com> wrote:
>
>> ok i have a small laptop with an even smaller vm :)
>>
>> pgbench -i s 3
>> pgbench -c 20 -j 5 -T 900 -M prepared
>>
>> select wait_event_type, wait_event from pg_stat_activity;
>>
>> wait_event_type | wait_event
>> -----------------+---------------------
>> Activity | LogicalLauncherMain
>> Activity | AutoVacuumMain
>> LWLock | WALWrite
>> Lock | transactionid
>> Lock | tuple
>> LWLock | WALWrite
>> Lock | transactionid
>> Lock | transactionid
>> Lock | tuple
>> Lock | transactionid
>> Lock | transactionid
>> Lock | transactionid
>> Lock | tuple
>> IO | WALSync
>> Lock | transactionid
>> |
>> Activity | BgWriterHibernate
>> Activity | CheckpointerMain
>> LWLock | WALWrite
>>
>>
>> i then destroyed the slow WAL db
>> rm -rf data
>>
>>
>> and built a new one without additional latency to WALdir.
>>
>> initdb -D data
>> pg_ctl -D data start
>>
>> pgbench -i -s 3
>> pgbench -c 20 -j 5 -T 900 -M prepared
>>
>> i see similar output as above
>> but no
>> WALWriteLock
>>
>> old thread, just for reference. do not read into anything :)
>>
>> PostgreSQL - hackers - An attempt to reduce WALWriteLock contention
>> (postgresql-archive.org)
>> <https://www.postgresql-archive.org/An-attempt-to-reduce-WALWriteLock-contention-td5935907.html>
>>
>>
>> Thanks,
>> Vijay
>>
>>
>>
>>
>>
>> On Fri, 30 Apr 2021 at 00:22, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
>> wrote:
>>
>>> On Fri, 2021-04-30 at 00:13 +0530, Vijaykumar Jain wrote:
>>> > I tried the below, but i could not simulate WALWriteLock waits.
>>> >
>>> > create table foo(id int)
>>> >
>>> > for i in {1..10}; do psql -c "begin transaction; insert into foo
>>> select 1 from generate_series(1, 1000); commit;" & done
>>>
>>> Perhaps you should do simple inserts of one row and run that script
>>> with a pgbench and lots of clients.
>>>
>>> Yours,
>>> Laurenz Albe
>>> --
>>> Cybertec | https://www.cybertec-postgresql.com
>>>
>>>

--
Don Seiler
www.seiler.us