Re: WALWriteLocks

[Don Seiler <don@xxxxxxxxx>]

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.github@xxxxxxxxx> 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.github@xxxxxxxxx> 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)

Thanks,

Vijay

On Fri, 30 Apr 2021 at 00:22, Laurenz Albe <laurenz.albe@xxxxxxxxxxx> 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