Re: [GENERAL] Better Connection Statistics
> From: Shaun Thomas >To: 'bricklen' >Cc: "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 connection does. It >was only like that for 10 seconds, and I ended up with about 400MB of log >output. I shudder to think of what 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 then run the logs through pg_badger. Agreed that a more granular pg_stat_database would be awesome. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Better Connection Statistics
On Sun, Feb 9, 2014 at 4:52 AM, Greg Sabino Mullane 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Better Connection Statistics
-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 g...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 connection does. It was only like that for 10 seconds, and I ended up with about 400MB of log output. I shudder to think of what would happen if I left it that way. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ 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
Re: [GENERAL] Better Connection Statistics
On Fri, Feb 7, 2014 at 2:24 PM, bricklen wrote: > > On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas 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.
Re: [GENERAL] Better Connection Statistics
On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas wrote: > > > 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 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. > > > __ > > 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 >
Re: [GENERAL] Better Connection Statistics
> 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 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. __ 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
Re: [GENERAL] Better Connection Statistics
O n Fri, 2014-02-07 at 15:00 +, 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 amount of CPU time consumed, etc. So > far as I know, there is no module, statistic, or view that provides any of > this. It occurred to me after one of our NOC guys asked us if a certain > machine was spamming us with queries, and I realized I didn't know, 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 > stho...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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 amount of CPU time consumed, etc. So far as I know, there is no module, statistic, or view that provides any of this. It occurred to me after one of our NOC guys asked us if a certain machine was spamming us with queries, and I realized I didn't know, 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 stho...@optionshouse.com __ 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
