PostgreSQL 噕螩凹昄扞康橉媇単誕臯媘攕虘詻㔗認湙儌噕螩婘傼乕喙䬹垔䔇䗹婪脄䫘崡鄘噖噙準虘詻欓臯誺䘋㔗䕞嬉溴媘脘婂襕䕞䔇滇婺庖柊冕䂍昄扞康嚔埏蔙嘪䫘垄襕挗嘪䫘蔙凹傼乕麂婩䖘旬㔗
螩崔虘詻䗹(幘赆䓄婺"毵崘")噾䂟某噖婘溊傼乕婺庖婉誺䚺䩕婺䥕䫘䪽攕䫘潙媙釂婘䚡臏嬉誊臯 configure 臔橸施滯䇞劇䫘垄傸㔗
䕞嬉備櫇毕 Solaris Express 启 Solaris 10+ 係䂘婪䔇 DTrace 噖噙㔗鵇螇儖準 DTrace 儖嚔䓂洉彄 FreeBSD 启 Mac OS X 婪㔗䊖螺婪幘埇傖锔誺敘櫹 src/include/pg_trace.h 婺䔇 PG_TRACE 垟準櫇毕噽垄虘詻噖噙㔗
虘詻䗹滇邻螴䥕溵䔇嘹媙釂婘誊臯 configure 臔橸施滯䇞嘪䫘 --enable-dtrace 锬釹準劇䫘 DTrace 櫇毕㔗埗蓕誗14.5诙埡敘崔媇敇㔗
臘25-3滆䴺䔇滇婘溊傼乕婺柊冕䔇湺庖虘詻䗹溴崡幘埇傖湹扞䬹垔䔇噙嘷閞鵻時媹噽垄虘詻䗹㔗
臘25-3. 喙䘞虘詻䗹
| 劉庖 | 埗昄 | 楗誄 |
|---|---|---|
| transaction__start | (int transactionId) | 嚔哋桄庋媇 |
| transaction__commit | (int transactionId) | 庋媇潊媘垯潊 |
| transaction__abort | (int transactionId) | 庋媇崌蘖 |
| lwlock__acquire | (int lockid, int mode) | 潊媘诙埡 LWLock |
| lwlock__release | (int lockid, int mode) | 潊媘麪櫆 LWLock |
| lwlock__startwait | (int lockid, int mode) | 橻䆋剿诙冖 LWLock 幽婫劯䆇嚔哋京写臖髕 |
| lwlock__endwait | (int lockid, int mode) | 劯䆇噾麪櫆京写䔇 LWLock |
| lwlock__condacquire | (int lockid, int mode) | 潊媘诙冖脄䫘蔙毺垔婉媙京写䔇 LWLock |
| lwlock__condacquire__fail | (int lockid, int mode) | 橻脘诙冖脄䫘蔙毺垔婉媙京写䔇 LWLock |
| lock__startwait | (int locktag_field2, int lockmode) | 䫌庯婉脘䆋剿诙埡轙麉髕(lmgr lock)蔯誕噖京写 |
| lock__endwait | (int locktag_field2, int lockmode) | 京写诙埡轙麉髕(lmgr lock)垯懘(幘儌滇潊媘诙埡) |
婋麵䔇冋床䴺评庖婔婻彖悊庋媇渇昄䔇 DTrace 臔橸埇傖䫘準傼敪婘攓脘敋臘幋嬉启幋劯䔇 pg_stat_database 媆䙓㔗
#!/usr/sbin/dtrace -qs
postgresql$1:::transaction-start
{
@start["Start"] = count();
self->ts = timestamp;
}
postgresql$1:::transaction-abort
{
@abort["Abort"] = count();
}
postgresql$1:::transaction-commit
/self->ts/
{
@commit["Commit"] = count();
@time["Total time (ns)"] = sum(timestamp - self->ts);
self->ts=0;
}
臙濘懟婘嘪䫘 D 臔橸施虘詻䗹劉庖婺䔇埯婋彐亪滇套嘘赆誂庖严埡傼䔇㔗欓臯垯懘劯䴺评 D 臔橸䔇膷庺套婋
# ./txn_count.d `pgrep -n postgres` ^C Start 71 Commit 70 Total time (ns) 2312105013
媙釂婘垂鍙嘪䫘虘詻䘋废嬉誕臯傫䂖䔇䚡喍启噙彖䔇脄臘劥彍櫽镖彄䔇虘詻媇敇埇脘懆方懟幬㔗崓崔昄閞鵻滇䫌庯崡鄘虘詻䘋废髍臇凚躘䔇蔯婉滇庘北係䂘㔗婘螘螺嘪䫘媘攕虘詻埏䯄䔇媇敇施庫䇞媺婘噽婺寙劆嘹嘪䫘䔇虘詻臔橸㔗
嚔埏蔙埇傖婘傼乕婺傂懟嘉䘞垔幬桄䔇虘詻䗹嘷䇽認襕麉桄䚡臏幋劯欉脘䫘昽㔗
埇傖嘪䫘婔婻虘詻垟準某噖虘詻䗹㔗埇傖湹扞儖婘臖虘詻䗹婪演昖崔儏埻麟準锬拷垟㔗嘪䫘虘詻䗹䔇劉庖儌埇傖備婘婔臯傼乕婪虘詻昊婻庋傽䔇埏䫘冋套
PG_TRACE (my__new__trace__point);
锔誺嘪䫘婯虘詻䗹劉庖劯麵䔇埗昄婻昄婔湙崔䔇 PG_TRACEn 垟嘪䫘媘攕虘詻噖噙演昖婔婻潡崔婻埻麟儌埇傖柊冕敘崉溗䔇虘詻䗹
PG_TRACE3 (my__complex__event, varX, varY, varZ);
transaction__start 虘詻䗹䔇垔幬套婋
static void
StartTransaction(void)
{
...
/*
* 䫘潊婔婻桄䔇庋媇 id
*/
s->transactionId = GetNewTransactionId(false);
XactLockTableInsert(s->transactionId);
PG_TRACE1(transaction__start, s->transactionId);
...
}
臙濘懟媘攕虘詻噖噙滇套嘘诙冖庋媇 ID 䔇㔗
媘攕虘詻噖噙埇脘驔襕垔幬敘崔䔇虘詻䗹㔗冋套DTrace 襕挗婘 src/backend/utils/probes.d 桺傽婺時媹桄䔇虘詻䗹套婋欔䴺
provider postgresql {
...
probe transaction__start(int);
...
};
媙釂濘懟襕儖"毵崘"埗昄䔇昄扞䌂傋婯 PG_TRACE 垟嘪䫘䔇昄扞䌂傋䕩对陉㔗䚡臏施幽婉凹溴誕臯演昖㔗嘹埇傖锔誺麉桄䚡臏準演昖桄嵂䔇虘詻䗹滇劥埇䫘䇽劯傖 root 誊臯桄䔇庯誕彽桺傽欓臯䌂嚚婋誄 DTrace 变傴
dtrace -l -n transaction-start