#!/usr/sbin/dtrace -qs /* * USAGE : pglockwaits_84.d * * DESCRIPTION: * Show PostgreSQL Lock wait statistics for every 10 second * * AUTHOR : Jignesh Shah * * $Header$ */ dtrace:::BEGIN { lckmode[0] = "Exclusive"; lckmode[1] = "Shared"; lckid[0] = "BufFreelistLock"; lckid[1] = "ShmemIndexLock"; lckid[2] = "OidGenLock"; lckid[3] = "XidGenLock"; lckid[4] = "ProcArrayLock"; lckid[5] = "SInvalReadLock"; lckid[6] = "SInvalWriteLock"; lckid[7] = "WALInsertLock"; lckid[8] = "WALWriteLock"; lckid[9] = "ControlFileLock"; lckid[10] = "CheckpointLock"; lckid[11] = "CLogControlLock"; lckid[12] = "SubtransControlLock"; lckid[13] = "MultiXactGenLock"; lckid[14] = "MultiXactOffsetControlLock"; lckid[15] = "MultiXactMemberControlLock"; lckid[16] = "RelCacheInitLock"; lckid[17] = "BgWriterCommLock"; lckid[18] = "TwoPhaseStateLock"; lckid[19] = "TablespaceCreateLock"; lckid[20] = "BtreeVacuumLock"; lckid[21] = "AddinShemInitLock"; lckid[22] = "AutovacuumLock"; lckid[23] = "AutovacuumScheduleLock"; lckid[24] = "SyncScanLock"; lckid[25] = "BufMappingLocks"; lckid[41] = "LockMgrLocks"; lckid[57] = "DynamicLocks"; endtime = timestamp + (5 * 1000000000ull); } postgresql$1:::lwlock-wait-start /arg0 < 25/ { self->ts[arg0]=timestamp; @count[lckid[arg0], lckmode[arg1]] = count(); } postgresql$1:::lwlock-wait-start /arg0 >= 25 && arg0 <41/ { self->ts[arg0]=timestamp; @count[lckid[25], lckmode[arg1]] = count(); /* @count1[ arg0, lckmode[arg1]] = count(); */ } postgresql$1:::lwlock-wait-start /arg0 >= 41 && arg0 <57/ { self->ts[arg0]=timestamp; @count[lckid[41], lckmode[arg1]] = count(); /* @count1[ arg0, lckmode[arg1]] = count(); */ } postgresql$1:::lwlock-wait-start /arg0 >= 57/ { self->ts[arg0]=timestamp; @count[lckid[57], lckmode[arg1]] = count(); /* @count1[ arg0, lckmode[arg1]] = count(); */ } postgresql$1:::lwlock-wait-done /(arg0 < 25) && self->ts[arg0]/ { @time[lckid[arg0],lckmode[arg1]] = sum (timestamp - self->ts[arg0]); self->ts[arg0]=0; } postgresql$1:::lwlock-wait-done /(arg0 >= 25) && (arg0 <41) && self->ts[arg0]/ { @time[lckid[25],lckmode[arg1]] = sum (timestamp - self->ts[arg0]); /* @time1[arg0,lckmode[arg1]] = sum (timestamp - self->ts[arg0]); */ self->ts[arg0]=0; } postgresql$1:::lwlock-wait-done /(arg0 >= 41) && (arg0 <57) && self->ts[arg0]/ { @time[lckid[41],lckmode[arg1]] = sum (timestamp - self->ts[arg0]); /* @time1[arg0,lckmode[arg1]] = sum (timestamp - self->ts[arg0]); */ self->ts[arg0]=0; } postgresql$1:::lwlock-wait-done /(arg0 >= 57) && self->ts[arg0]/ { @time[lckid[57],lckmode[arg1]] = sum (timestamp - self->ts[arg0]); /* @time1[arg0,lckmode[arg1]] = sum (timestamp - self->ts[arg0]); */ self->ts[arg0]=0; } /* * Run the script for ~10 secs and exit. Since there is processing time * before the probes are enabled and after they are disabled, endtime will be * be longer than the specified time, so adjust it accordingly. */ tick-10sec { normalize(@time, 1000000); printf("\n%20Y %15s %20s %15s\n", walltimestamp, "Lock-Id Mode", "Wait-Time(ms)", "Count"); printa("%20s %15s %@20d %@15d\n",@time, @count); clear(@time); clear(@count); /* normalize(@time1, 1000000); printf("\n%20s %15s %20s %15s\n", "Lock-Id", "Mode", "Wait-Time(ms)", "Count"); printa("%20d %15s %@20d %@15d\n",@time1, @count1); clear(@time1); clear(@count1); */ }