Thread: Better Connection Statistics
Hi everyone, So, I haven't ever heard of this, but I could be missing something. Is there a module or extension for PostgreSQL keep connectionstatistics? I don't mean what's in pg_stat_activity, but cumulative. And not like what's in pg_stat_statements,but about the connections themselves. Nor even pg_stat_database, since that's only granular at the databaselevel. For instance, I want the number of transactions a specific connection has submitted. The number of queries. Total amountof CPU time consumed, etc. So far as I know, there is no module, statistic, or view that provides any of this. It occurredto me after one of our NOC guys asked us if a certain machine was spamming us with queries, and I realized I didn'tknow, and had no way of finding out. The best I can do is see if any are currently, this very second, executing something.If the connection happens to be between transactions when I poll pg_stat_statements, I get nothing. I know pg_pool and pg_bouncer provide info like this, but we don't use those. Is there anything internal to PG that can...eventually get it? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
O n Fri, 2014-02-07 at 15:00 +0000, Shaun Thomas wrote:S o, I haven't ever heard of this, but I could be missing something. Is there a module or extension for PostgreSQL keep connection statistics? I don't mean what's in pg_stat_activity, but cumulative. And not like what's in pg_stat_statements, but about the connections themselves. Nor even pg_stat_database, since that's only granular at the database level. > > For instance, I want the number of transactions a specific connection has submitted. The number of queries. Total amountof CPU time consumed, etc. So far as I know, there is no module, statistic, or view that provides any of this. It occurredto me after one of our NOC guys asked us if a certain machine was spamming us with queries, and I realized I didn'tknow, and had no way of finding out. The best I can do is see if any are currently, this very second, executing something.If the connection happens to be between transactions when I poll pg_stat_statements, I get nothing. > > I know pg_pool and pg_bouncer provide info like this, but we don't use those. Is there anything internal to PG that can...eventually get it? > > -- > Shaun Thomas > OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 > 312-676-8870 > sthomas@optionshouse.com > > ______________________________________________ > > See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email > > Perhaps this might be of use. http://www.postgresql.org/docs/current/static/pgstatstatements.html
> Perhaps this might be of use. > > http://www.postgresql.org/docs/current/static/pgstatstatements.html Nope. As I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want toknow, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know: * How many queries that connection has executed. * How much CPU time that connection has used since it connected. * How much data was sent to that connection. * How much data that connection sent to the database. And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activityto track cumulative totals, since it always has the status of all connections. ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:
Nope. As I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want to know, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know:
> Perhaps this might be of use.
>
> http://www.postgresql.org/docs/current/static/pgstatstatements.html
* How many queries that connection has executed.
* How much CPU time that connection has used since it connected.
* How much data was sent to that connection.
* How much data that connection sent to the database.
And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activity to track cumulative totals, since it always has the status of all connections.
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Feb 7, 2014 at 2:24 PM, bricklen <bricklen@gmail.com> wrote:
On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas <sthomas@optionshouse.com> wrote:s I said in the original message, pg_stat_statements only gives query stats for the whole database. What I want to know, is information about each client. Say there's a specific connection from 192.168.1.20. I want to know:
* How many queries that connection has executed.
* How much CPU time that connection has used since it connected.
* How much data was sent to that connection.
* How much data that connection sent to the database.
And so on. I don't believe that's currently possible. Effectively, it would just be adding a few more columns to pg_stat_activity to track cumulative totals, since it always has the status of all connections.
I don't know any tools off-hand, but you might be able to generate partial statistics from the log files with a descriptive log_line_prefix like "%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] ". Using the %p and vxi/txid might help to group the queries executed for easier consumption. I don't think that helps much with individual connections though.
> I don't know any tools off-hand, but you might be able to generate > partial statistics from the log files with a descriptive log_line_prefix > like "%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] " We get 60k queries per second all day long. No way am I turning on query logging to capture the stats I want. :) Last month, I needed to track something down and set log_min_duration_statement to 0, logging everything each connectiondoes. It was only like that for 10 seconds, and I ended up with about 400MB of log output. I shudder to think ofwhat would happen if I left it that way. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 sthomas@optionshouse.com ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 > For instance, I want the number of transactions a specific connection has submitted. > The number of queries. Total amount of CPU time consumed, etc. So far as I know, > there is no module, statistic, or view that provides any of this. For the basic connection information, you could parse the Postgres logs, assuming you are being verbose enough to capture everything. Certainly you could get commits/rollbacks/queries/avg_time per connection. For deeper and better introspection, check out integrating DTrace or SystemTap. - -- Greg Sabino Mullane greg@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201402081451 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -----BEGIN PGP SIGNATURE----- iEYEAREDAAYFAlL2itEACgkQvJuQZxSWSshlIgCgo+jkIYojwc23O4jwLGYxqZ8H tJgAn3/K7lK+S4c4003xO+nVcWzsc+TK =uFOM -----END PGP SIGNATURE-----
On Sun, Feb 9, 2014 at 4:52 AM, Greg Sabino Mullane <greg@turnstep.com> wrote: >> For instance, I want the number of transactions a specific connection has submitted. >> The number of queries. Total amount of CPU time consumed, etc. So far as I know, >> there is no module, statistic, or view that provides any of this. > > For the basic connection information, you could parse the Postgres logs, assuming > you are being verbose enough to capture everything. In this case, a log parser like pgbadger would help for sure. -- Michael
> From: Shaun Thomas <sthomas@optionshouse.com> >To: 'bricklen' <bricklen@gmail.com> >Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> >Sent: Friday, 7 February 2014, 22:36 >Subject: Re: [GENERAL] Better Connection Statistics > > >> I don't know any tools off-hand, but you might be able to generate >> partial statistics from the log files with a descriptive log_line_prefix >> like "%m [%p] (user=%u) (db=%d) (rhost=%h) [vxid:%v txid:%x] [%i] " > >We get 60k queries per second all day long. No way am I turning on query logging to capture the stats I want. :) > >Last month, I needed to track something down and set log_min_duration_statement to 0, logging everything each connectiondoes. It was only like that for 10 seconds, and I ended up with about 400MB of log output. I shudder to think ofwhat would happen if I left it that way. > We have a similar issue here, I tend to set log_min_diration statement = 0 just for the user I want to know about and thenrun the logs through pg_badger. Agreed that a more granular pg_stat_database would be awesome.