Count actual transaction per minute?

On Wed, May 12, 2010 at 09:12:43AM -0700, Melvin Davidson wrote:

Can anyone tell me how to measure _actual_ transactions per minute on a PostgreSQL server. I am not talking about using pgbench, as I am not interested in determining what is possible, but rather the actual count of queries / transactions being sent to the server.

sure. run:
select sum(xact_commit + xact_rollback) from pg_stat_database
every minute, substract previous result from current, and you'll know
your current tpm.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

On May 12, 2010, at 9:12 AM, Melvin Davidson wrote:

Can anyone tell me how to measure _actual_ transactions per minute on a PostgreSQL server. I am not talking about using pgbench, as I am not interested in determining what is possible, but rather the actual count of queries / transactions being sent to the server.

We log query durations, and then use net-snmp to count the amount of query durations we see per second. Now, granted, that's queries, not transactions, and it's not particularly elegant..... but it's pretty effective and is surprisingly more efficient than I assumed it would be before we tried it.

Thanks very much!  I put that query into a script that puts the initial result into a variable, sleeps for a minute, queries again into a second variable and subtracts the difference.
Works fine.

Melvin Davidson

Folk Alley - All Folk - 24 Hours a day
www.folkalley.com

--- On Wed, 5/12/10, hubert depesz lubaczewski <depesz@depesz.com> wrote:

From: hubert depesz lubaczewski <depesz@depesz.com>
Subject: Re: [GENERAL] Count actual transaction per minute?
To: "Melvin Davidson" <melvin6925@yahoo.com>
Cc: pgsql-general@postgresql.org
Date: Wednesday, May 12, 2010, 11:20 AM

sure. run:
select sum(xact_commit + xact_rollback) from pg_stat_database
every minute, substract previous result from current, and you'll know
your current tpm.

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007