Re: Transactions per second

I often find myself wanting to know how many transactions per second
a database is committing to disk, as well as how many queries per
second it's processing. While Larry's busy making stats changes, I'd
like to propose a few more counters:

Number of commits: Ideally, this would only count transactions that
actually modify data
Number of statements: Simply, how many statements have been executed
Number of DML statements: how many insert/update/delete statements
executed.

For the last two, it would be ideal if they counted statements that
were executed inside of a function.

Having these counters would make it easier to determine what kind of
workload a server is actually handling. It's currently possible to
determine transactions over a time period, but the process is rather
convoluted and doesn't differentiate between transactions that modify
data and those that don't.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Ühel kenal päeval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby:
> I often find myself wanting to know how many transactions per second
> a database is committing to disk, as well as how many queries per
> second it's processing. While Larry's busy making stats changes, I'd
> like to propose a few more counters:
>
> Number of commits: Ideally, this would only count transactions that
> actually modify data

I' prefer one counter for total and one for data modifying statements.

> Number of statements: Simply, how many statements have been executed
> Number of DML statements: how many insert/update/delete statements
> executed.

I'd like to add a request for function call counters, presented to user
as view pg_stat_user_functions, similar in content to current
pg_stat_user_tables.

actually I'd like to have the following data gathered for each function:

call count
total call time
min running time
max running time

I'd also like a possibility to gather information about usage of locks
for both function calls and simple DML statements.

----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com

NOTICE: This communication contains privileged or other confidential
information. If you have received it in error, please advise the sender
by reply email and immediately delete the message and any attachments
without copying or disclosing the contents.

On Sat, May 06, 2006 at 12:09:45AM +0300, Hannu Krosing wrote:
> ??hel kenal p??eval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby:
> > I often find myself wanting to know how many transactions per second
> > a database is committing to disk, as well as how many queries per
> > second it's processing. While Larry's busy making stats changes, I'd
> > like to propose a few more counters:
> >
> > Number of commits: Ideally, this would only count transactions that
> > actually modify data
>
> I' prefer one counter for total and one for data modifying statements.

The reason I added in a transaction counter is because that's the only
thing that tells you about the fsync rate on the WAL.

> > Number of statements: Simply, how many statements have been executed
> > Number of DML statements: how many insert/update/delete statements
> > executed.
>
> I'd like to add a request for function call counters, presented to user
> as view pg_stat_user_functions, similar in content to current
> pg_stat_user_tables.
>
> actually I'd like to have the following data gathered for each function:
>
> call count
> total call time
> min running time
> max running time

Wouldn't capturing timing statistics for short-running functions be too
prohibitive? I'm thinking this is similar to the overheads we see with
EXPLAIN ANALYZE...

> I'd also like a possibility to gather information about usage of locks
> for both function calls and simple DML statements.

What do you mean by 'usage of locks'?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Ühel kenal päeval, R, 2006-05-05 kell 17:51, kirjutas Jim C. Nasby:
> On Sat, May 06, 2006 at 12:09:45AM +0300, Hannu Krosing wrote:
> > ??hel kenal p??eval, N, 2006-05-04 kell 17:23, kirjutas Jim Nasby:
> > > I often find myself wanting to know how many transactions per second
> > > a database is committing to disk, as well as how many queries per
> > > second it's processing. While Larry's busy making stats changes, I'd
> > > like to propose a few more counters:
> > >
> > > Number of commits: Ideally, this would only count transactions that
> > > actually modify data
> >
> > I' prefer one counter for total and one for data modifying statements.
>
> The reason I added in a transaction counter is because that's the only
> thing that tells you about the fsync rate on the WAL.
>
> > > Number of statements: Simply, how many statements have been executed
> > > Number of DML statements: how many insert/update/delete statements
> > > executed.
> >
> > I'd like to add a request for function call counters, presented to user
> > as view pg_stat_user_functions, similar in content to current
> > pg_stat_user_tables.
> >
> > actually I'd like to have the following data gathered for each function:
> >
> > call count
> > total call time
> > min running time
> > max running time
>
> Wouldn't capturing timing statistics for short-running functions be too
> prohibitive? I'm thinking this is similar to the overheads we see with
> EXPLAIN ANALYZE...

I hope they are still several orders of manitude cheper than whole
statements, even simple ones like 'BEGIN;'.

Currently I have them recorded in pg_log anyway, and I suspect that they
are computed internally even if not requested by something like
log_min_duration_statement.

Having these timings in stats views would save me a lot of log-parsing,
which is often not trivial, especially when having to distinguish
functions with same name but different argument sets.

> > I'd also like a possibility to gather information about usage of locks
> > for both function calls and simple DML statements.
>
> What do you mean by 'usage of locks'?

Mostly I would like to have statistics about the locks that were not
granted immediately, that is if there has been a need to wait on locks.

It would be nice to have this info in both pg_stat_*_tables/indexes and
independently in pg_stat_locks view for locs not associated with any
relation.

----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com