Re: Transactions per second - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: Transactions per second
Date
Msg-id 1146903452.3873.11.camel@localhost.localdomain
Whole thread Raw
In response to Re: Transactions per second  ("Jim C. Nasby" <jnasby@pervasive.com>)
List pgsql-hackers
Ü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




pgsql-hackers by date:

Previous
From: Rod Taylor
Date:
Subject: Re: 8.1.3 and unused files
Next
From: Martijn van Oosterhout
Date:
Subject: Re: InsertXLogFile in pg_resetxlog