Thread: 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@pervasive.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@pervasive.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