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?

Comments:

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 03:17 PM EDT #

Post a Comment:
Comments are closed for this entry.
About

Jignesh Shah is Principal Software Engineer in Application Integration Engineering, Oracle Corporation. AIE enables integration of ISV products including Oracle with Unified Storage Systems. You can also follow me on my blog http://jkshah.blogspot.com

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today