Re: CSStorm occurred again by postgreSQL8.2. (Re: poor performance with Context Switch Storm at TPC-W.)

"Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>" wrote:
> Katsuhiko Okano <okano(dot)katsuhiko(at)oss(dot)ntt(dot)co(dot)jp> writes:
> > It does not solve, even if it increases the number of NUM_SUBTRANS_BUFFERS.
> > The problem was only postponed.
>
> Can you provide a reproducible test case for this?

Seven machines are required in order to perform measurement.
(DB*1,AP*2,CLient*4)
Enough work load was not able to be given in two machines.
(DB*1,{AP+CL}*1)

It was not able to reappear to a multiplex run of pgbench
or a simple SELECT query.
TPC-W of a work load tool used this time is a full scratch.
Regrettably it cannot open to the public.
If there is a work load tool of a free license, I would like to try.

I will show if there is information required for others.

The patch which outputs the number of times of LWLock was used this time.
The following is old example output. FYI.

# SELECT * FROM pg_stat_lwlocks;
kind | pg_stat_get_lwlock_name | sh_call | sh_wait | ex_call | ex_wait | sleep

------+----------------------------+------------+-----------+-----------+-----------+-------

0 | BufMappingLock | 559375542 | 33542 | 320092 | 24025 | 0

1 | BufFreelistLock | 0 | 0 | 370709 | 47 | 0

2 | LockMgrLock | 0 | 0 | 41718885 | 734502 | 0

3 | OidGenLock | 33 | 0 | 0 | 0 | 0

4 | XidGenLock | 12572279 | 10095 | 11299469 | 20089 | 0

5 | ProcArrayLock | 8371330 | 72052 | 16965667 | 603294 | 0

6 | SInvalLock | 38822428 | 435 | 25917 | 128 | 0

7 | FreeSpaceLock | 0 | 0 | 16787 | 4 | 0

8 | WALInsertLock | 0 | 0 | 1239911 | 885 | 0

9 | WALWriteLock | 0 | 0 | 69907 | 5589 | 0

10 | ControlFileLock | 0 | 0 | 16686 | 1 | 0

11 | CheckpointLock | 0 | 0 | 34 | 0 | 0

12 | CheckpointStartLock | 69509 | 0 | 34 | 1 | 0

13 | CLogControlLock | 0 | 0 | 236763 | 183 | 0

14 | SubtransControlLock | 0 | 0 | 753773945 | 205273395 | 0

15 | MultiXactGenLock | 66 | 0 | 0 | 0 | 0

16 | MultiXactOffsetControlLock | 0 | 0 | 35 | 0 | 0

17 | MultiXactMemberControlLock | 0 | 0 | 34 | 0 | 0

18 | RelCacheInitLock | 0 | 0 | 0 | 0 | 0

19 | BgWriterCommLock | 0 | 0 | 61457 | 1 | 0

20 | TwoPhaseStateLock | 33 | 0 | 0 | 0 | 0

21 | TablespaceCreateLock | 0 | 0 | 0 | 0 | 0

22 | BufferIO | 0 | 0 | 695627 | 16 | 0

23 | BufferContent | 3568231805 | 1897 | 1361394 | 829 | 0

24 | CLog | 0 | 0 | 0 | 0 | 0

25 | SubTrans | 138571621 | 143208883 | 8122181 | 8132646 | 0

26 | MultiXactOffset | 0 | 0 | 0 | 0 | 0

27 | MultiXactMember | 0 | 0 | 0 | 0 | 0

(28 rows)

I am pleased if interested.

regards,
--------
Katsuhiko Okano
okano katsuhiko _at_ oss ntt co jp