Re: New statistics for WAL buffer dirty writes

Hi,

Jeff Janes has pointed out that my previous patch could hold
a number of the dirty writes only in single local backend, and
it could not hold all over the cluster, because the counter
was allocated in the local process memory.

That's true, and I have fixed it with moving the counter into
the shared memory, as a member of XLogCtlWrite, to keep total
dirty writes in the cluster.

Regards,

2012/07/07 21:00, Satoshi Nagayasu wrote:
> Hi all,
>
> I've created new patch to get/reset statistics of WAL buffer
> writes (flushes) caused by WAL buffer full.
>
> This patch provides two new functions, pg_stat_get_xlog_dirty_write()
> and pg_stat_reset_xlog_dirty_write(), which have been designed to
> determine an appropriate value for WAL buffer size.
>
> If this counter is increasing in the production environment,
> it would mean that the WAL buffer size is too small to hold
> xlog records generated the transactions. So, you can increase
> your WAL buffer size to keep xlog records and to reduce WAL writes.
>
> I think this patch would not affect to WAL write performance,
> but still paying attention to it.
>
> Any comments or suggestions?
>
> Regards,
>
> -----------------------------------------------------------
> [snaga(at)devvm03 src]$ psql -p 15432 postgres
> psql (9.3devel)
> Type "help" for help.
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# \q
> [snaga(at)devvm03 src]$ pgbench -p 15432 -s 10 -c 32 -t 1000 postgres
> Scale option ignored, using pgbench_branches table count = 10
> starting vacuum...end.
> transaction type: TPC-B (sort of)
> scaling factor: 10
> query mode: simple
> number of clients: 32
> number of threads: 1
> number of transactions per client: 1000
> number of transactions actually processed: 32000/32000
> tps = 141.937738 (including connections establishing)
> tps = 142.123457 (excluding connections establishing)
> [snaga(at)devvm03 src]$ psql -p 15432 postgres
> psql (9.3devel)
> Type "help" for help.
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# begin;
> BEGIN
> postgres=# DELETE FROM pgbench_accounts;
> DELETE 1000000
> postgres=# commit;
> COMMIT
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 19229
> (1 row)
>
> postgres=# SELECT pg_stat_reset_xlog_dirty_write();
> pg_stat_reset_xlog_dirty_write
> --------------------------------
>
> (1 row)
>
> postgres=# SELECT pg_stat_get_xlog_dirty_write();
> pg_stat_get_xlog_dirty_write
> ------------------------------
> 0
> (1 row)
>
> postgres=# \q
> [snaga(at)devvm03 src]$
> -----------------------------------------------------------
>
>

--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp