Better Connection Statistics

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 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
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Fri, Feb 7, 2014 at 10:08 AM, Shaun Thomas <sthomas@optionshouse.com>wrote:

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 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
sthomas@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

Using PostgreSQL 9.3.2 and PostGIS 2.1.0.

Can someone explain the best way to perform an operation such as finding the nearest
fire station to a fire?  Given that we have a fire at POINT(-87.638 41.8140) and:

        CREATE TABLE firestations
        (
                name            VARCHAR,
                location        VARCHAR,
                latlong         GEOMETRY
        );

        \copy firestations from stdin csv
        E119,"6030 N AVONDALE AVE CHICAGO,IL 60631",0101000000E6AE5E741FF355C01C284FDBDFFE4440
        E121,"1724 W 95TH ST CHICAGO,IL 60643",010100000044D79DDD9BEA55C0F9E9FED051DC4440
        E80,"12701 S DOTY AVE CHICAGO,IL 60633",01010000000E4F5159CCE555C02C548D14D0D44440
        E1,"419 S WELLS ST CHICAGO,IL 60607",01010000004519979788E855C03515A05722F04440
        E14,"1129 W CHICAGO AVE CHICAGO,IL 60642",010100000006DBC095FFE955C01B87FE5FAFF24440
        E49,"4401 S ASHLAND AVE CHICAGO,IL 60609",01010000002D94A0E68EEA55C022EDCA8832E84440
        E54,"7101 S PARNELL AVE CHICAGO,IL 60621",0101000000BDB211FFD9E855C0547A1607F4E14440
        E73,"8630 S EMERALD AVE CHICAGO,IL 60620",0101000000F9B5F3FC1DE955C0590854E953DE4440
        E34,"4034 W 47TH ST CHICAGO,IL 60632",0101000000067CA2F663EE55C093AF6C4669E74440
        E93,"330 W 104TH ST CHICAGO,IL 60628",01010000004D100BC979E855C05C68056F45DA4440
        E86,"3918 N HARLEM AVE CHICAGO,IL 60634",0101000000B4284F7BA9F355C0FCAFAE09BFF94440
        \.

I can easily find the least distance to the fire with:

        SELECT min(ST_Distance('POINT(-87.638 41.8140)',latlong)) FROM firestations;

but I can't include the name of the station in that select.  If I try grouping by station name,
I get all the stations:
        
        SELECT name,min(ST_Distance('POINT(-87.638 41.8140)',latlong))
                FROM firestations GROUP BY name;
 
This query gives the answer I want, but I'm wondering if there is a solution using
the 'min' aggregate function.

        SELECT name,location, ST_Distance('POINT(-87.638 41.8140)',latlong) dist
                FROM firestations ORDER BY dist LIMIT 1;

Thanks,
Paul Jones

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

On 08/02/14 12:33, Paul Jones wrote:

Using PostgreSQL 9.3.2 and PostGIS 2.1.0.

Can someone explain the best way to perform an operation such as finding the nearest
fire station to a fire? Given that we have a fire at POINT(-87.638 41.8140) and:

CREATE TABLE firestations
(
name VARCHAR,
location VARCHAR,
latlong GEOMETRY
);

\copy firestations from stdin csv
E119,"6030 N AVONDALE AVE CHICAGO,IL 60631",0101000000E6AE5E741FF355C01C284FDBDFFE4440
E121,"1724 W 95TH ST CHICAGO,IL 60643",010100000044D79DDD9BEA55C0F9E9FED051DC4440
E80,"12701 S DOTY AVE CHICAGO,IL 60633",01010000000E4F5159CCE555C02C548D14D0D44440
E1,"419 S WELLS ST CHICAGO,IL 60607",01010000004519979788E855C03515A05722F04440
E14,"1129 W CHICAGO AVE CHICAGO,IL 60642",010100000006DBC095FFE955C01B87FE5FAFF24440
E49,"4401 S ASHLAND AVE CHICAGO,IL 60609",01010000002D94A0E68EEA55C022EDCA8832E84440
E54,"7101 S PARNELL AVE CHICAGO,IL 60621",0101000000BDB211FFD9E855C0547A1607F4E14440
E73,"8630 S EMERALD AVE CHICAGO,IL 60620",0101000000F9B5F3FC1DE955C0590854E953DE4440
E34,"4034 W 47TH ST CHICAGO,IL 60632",0101000000067CA2F663EE55C093AF6C4669E74440
E93,"330 W 104TH ST CHICAGO,IL 60628",01010000004D100BC979E855C05C68056F45DA4440
E86,"3918 N HARLEM AVE CHICAGO,IL 60634",0101000000B4284F7BA9F355C0FCAFAE09BFF94440
\.

I can easily find the least distance to the fire with:

SELECT min(ST_Distance('POINT(-87.638 41.8140)',latlong)) FROM firestations;

but I can't include the name of the station in that select. If I try grouping by station name,
I get all the stations:

SELECT name,min(ST_Distance('POINT(-87.638 41.8140)',latlong))
FROM firestations GROUP BY name;

This query gives the answer I want, but I'm wondering if there is a solution using
the 'min' aggregate function.

SELECT name,location, ST_Distance('POINT(-87.638 41.8140)',latlong) dist
FROM firestations ORDER BY dist LIMIT 1;

Thanks,
Paul Jones

I thought Fire engines had to follow roads, therefore could not fly
through hills and buildings etc.!

Also that some routes may be faster than others due to traffic and/or
other considerations, so the shortest route may not necessarily be the
fastest...

Cheers,
Gavin

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

-----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-----

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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