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?
For those of us stuck without dtrace everywhere, you can get a rough idea how many commits happened by looking at the statistics data. Something like this will collect the raw info:
psql -At -c "select now(),sum(n_tup_ins) as n_tup_ins,sum(n_tup_upd) as n_tup_upd,sum(n_tup_del) as n_tup_del from pg_stat_user_tables;"
Wrap that in a script that computes the delta (those numbers are cumulative sums) and waits some period between each sample, and you've got the most valuable data to collect here. This doesn't account for aborted transactions, in many situations that's not really critical to track though.
Posted by Greg Smith on May 15, 2009 at 10:17 PM EDT #