Friday, May 15, 2009

PostgreSQL Transactions Per Second Using Dtrace

 I modified one of Robert's dtrace scripts so that it is  useful for my purpose to measure often asked transactions per second  for random workload running on PostgreSQL.




The script is as follows:


#!/usr/sbin/dtrace -qs
postgresql*:::transaction-start
{
@startpersec["New"] = count();
}
postgresql*:::transaction-commit
{
@commitpersec[ "Commit"] = count();
}
postgresql*:::transaction-abort
{
@abort["Abort"] = count();
}
profile:::tick-1s
{
printf("******** Transactions Per Second *********\n");
printf("%20s %15s\n", "Txn Type", "Count");
printf("==========================================\n");
printa("%20s %@15d\n", @startpersec);
printa("%20s %@15d\n", @commitpersec);
printa("%20s %@15d\n", @abort);
printf("\n");
clear(@startpersec);
clear(@commitpersec);
clear(@abort);
}



UPDATE: You can also download it pgtps.d



When you execute it you see outputs every second as follows:












# ./tps.d
******** Transactions Per Second *********
Txn Type Count
==========================================
New 192
Commit 192
Abort 1

******** Transactions Per Second *********
Txn Type Count
==========================================
New 175
Commit 172
Abort 0

******** Transactions Per Second *********
Txn Type Count
==========================================
New 195
Commit 198
Abort 0

******** Transactions Per Second *********
Txn Type Count
==========================================
New 183
Commit 178
Abort 2





How to interpret the output?



  • New mentions how many transactions started per second

  • Commit talks about how many transactions commited per second.

  • Aborts talks about transactions aborted in that second


Useful specially when some one  asks a questions that they are generally reading from a questionaire like how many transactions per second are we doing?


Where is your TPS report?


No comments: