Re: Transactions per second

Ü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