stats for network traffic WIP
Hi, I've been using postgres for many years but never took the time to play
with the code until now. As a learning experience i came up with this WIP
patch to keep track of the # of bytes sent and received by the server over
it's communication sockets. Counters are kept per database, per connection
and globally/shared.
The counters are incremented for tcp (remote and localhost) and for unix
sockets. The major WIP issue so far is that connections using SSL aren't
counted properly. If there's any interest, i'll keep working on it.
a few functions are added:
- pg_stat_get_bytes_sent() returns the total count of outgoing bytes for
the whole cluster (all dbs and all connections including replication)
- pg_stat_get_bytes_received() same but for incoming data
- pg_stat_get_db_bytes_sent(oid) returns count of outgoing bytes for a
specific database
- pg_stat_get_db_bytes_received(oid) same but for incoming data
"bytes_sent" and "bytes_received" columns are added to:
- pg_stat_get_activity function
- pg_stat_activity view
- pg_stat_database view
- pg_stat_replication view
The counters are reset with the existing reset functions, but a new
parameter value is added for the shared stats call (i named it "socket" for
lack of imagination), eg. pg_stat_reset_shared('socket').
some benefits of the patch:
- can be used to track bandwidth usage of postgres, useful if the host
isn't a dedicated db server, where host level statistics would include
other traffic.
- can track bandwidth usage of streaming replication.
- can be used to find misbehaving connections.
- can be used in multi-user/multi-database clusters for resource usage
tracking.
- competing databases have such metrics.
- could also be added to pg_stat_statements for extra debugging.
- etc.?
some negatives:
- extra code is called for each send() and recv(), I haven't measured the
performance impact yet. (but can be turned off using track_counts=off)
- stats collector has more work to do.
- some stats structs are changed which will cause an error while trying to
load them from disk the first time and the old stats will be lost.
- PL functions that create their own sockets aren't tracked.
- sockets from FDWs calls aren't tracked.
To debug the counters, i'm using clients connected through haproxy to
generate traffic and then compare haproxy's stats with what pg stores in
pg_stat/global.stat on shutdown. Attached is a very basic python script
that can read the global.stat file (it takes the DATADIR as a parameter).
Any feedback is appreciated,
-nigel.
Nigel,
* Nigel Heron (nheron@querymetrics.com) wrote:
Hi, I've been using postgres for many years but never took the time to play
with the code until now. As a learning experience i came up with this WIP
patch to keep track of the # of bytes sent and received by the server over
it's communication sockets. Counters are kept per database, per connection
and globally/shared.
Very neat idea. Please add it to the current commitfest
(http://commitfest.postgresql.org) and, ideally, someone will get in and
review it during the next CM.
Thanks!
Stephen
I added this to the current CF, and am starting to review it as I have time.
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Mon, Oct 21, 2013 at 11:32 AM, Stephen Frost <sfrost@snowman.net> wrote:
Show quoted text
Nigel,
* Nigel Heron (nheron@querymetrics.com) wrote:
Hi, I've been using postgres for many years but never took the time to
play
with the code until now. As a learning experience i came up with this WIP
patch to keep track of the # of bytes sent and received by the serverover
it's communication sockets. Counters are kept per database, per
connection
and globally/shared.
Very neat idea. Please add it to the current commitfest
(http://commitfest.postgresql.org) and, ideally, someone will get in and
review it during the next CM.Thanks!
Stephen
Hi, thanks, I'm still actively working on this patch. I've gotten the
traffic counters working when using SSL enabled clients (includes the
ssl overhead now) but I still have the walsender transfers under SSL
to work on.
I'll post an updated patch when i have it figured out.
Since the patch changes some views in pg_catalog, a regression test
fails .. i'm not sure what to do next. Change the regression test in
the patch, or wait until the review phase?
I was also thinking of adding global counters for the stats collector
(pg_stat* file read/write bytes + packets lost) and also log file io
(bytes written for txt and csv formats) .. any interest?
-nigel.
On Wed, Oct 23, 2013 at 12:50 PM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
I added this to the current CF, and am starting to review it as I have time.
__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management
| RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.comOn Mon, Oct 21, 2013 at 11:32 AM, Stephen Frost <sfrost@snowman.net> wrote:
Nigel,
* Nigel Heron (nheron@querymetrics.com) wrote:
Hi, I've been using postgres for many years but never took the time to
play
with the code until now. As a learning experience i came up with this
WIP
patch to keep track of the # of bytes sent and received by the server
over
it's communication sockets. Counters are kept per database, per
connection
and globally/shared.Very neat idea. Please add it to the current commitfest
(http://commitfest.postgresql.org) and, ideally, someone will get in and
review it during the next CM.Thanks!
Stephen
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Sounds good. I personally don't have any interest in log file i/o
counters, but that's just me. I wonder if stats collector counters might
be useful... I seem to recall an effort to improve that area. Maybe not
enough use to take the performance hit on a regular basis, though.
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Wed, Oct 23, 2013 at 1:44 PM, Nigel Heron <nheron@querymetrics.com>wrote:
Show quoted text
Hi, thanks, I'm still actively working on this patch. I've gotten the
traffic counters working when using SSL enabled clients (includes the
ssl overhead now) but I still have the walsender transfers under SSL
to work on.
I'll post an updated patch when i have it figured out.
Since the patch changes some views in pg_catalog, a regression test
fails .. i'm not sure what to do next. Change the regression test in
the patch, or wait until the review phase?I was also thinking of adding global counters for the stats collector
(pg_stat* file read/write bytes + packets lost) and also log file io
(bytes written for txt and csv formats) .. any interest?-nigel.
On Wed, Oct 23, 2013 at 12:50 PM, Mike Blackwell <mike.blackwell@rrd.com>
wrote:I added this to the current CF, and am starting to review it as I have
time.
__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management
| RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.comOn Mon, Oct 21, 2013 at 11:32 AM, Stephen Frost <sfrost@snowman.net>
wrote:
Nigel,
* Nigel Heron (nheron@querymetrics.com) wrote:
Hi, I've been using postgres for many years but never took the time to
play
with the code until now. As a learning experience i came up with this
WIP
patch to keep track of the # of bytes sent and received by the server
over
it's communication sockets. Counters are kept per database, per
connection
and globally/shared.Very neat idea. Please add it to the current commitfest
(http://commitfest.postgresql.org) and, ideally, someone will get inand
review it during the next CM.
Thanks!
Stephen
On Thu, Oct 24, 2013 at 12:23 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
Sounds good. I personally don't have any interest in log file i/o counters,
but that's just me. I wonder if stats collector counters might be useful...
I seem to recall an effort to improve that area. Maybe not enough use to
take the performance hit on a regular basis, though.
+1.
I tend to be a bit touchy about any changes to code that runs
frequently. We need to seriously test if the overhead added by this
patch is worth it.
IMO, the idea is pretty good. Its just that we need to do some wide
spectrum performance testing. Thats only my thought though.
Regards,
Atri
--
Regards,
Atri
l'apprenant
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Oct 23, 2013 at 1:58 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
IMO, the idea is pretty good. Its just that we need to do some wide
spectrum performance testing. Thats only my thought though.
I'm looking at trying to do some performance testing on this. Any
suggestions on test scenarios, etc?
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Wed, Oct 23, 2013 at 2:58 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
On Thu, Oct 24, 2013 at 12:23 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
Sounds good. I personally don't have any interest in log file i/o counters,
but that's just me. I wonder if stats collector counters might be useful...
I seem to recall an effort to improve that area. Maybe not enough use to
take the performance hit on a regular basis, though.+1.
I tend to be a bit touchy about any changes to code that runs
frequently. We need to seriously test if the overhead added by this
patch is worth it.IMO, the idea is pretty good. Its just that we need to do some wide
spectrum performance testing. Thats only my thought though.
I didn't implement the code yet, but my impression is that since it
will be the stats collector gathering counters about itself there will
be very little overhead (no message passing, etc.) .. just a few int
calculations and storing a few more bytes in the global stats file.
The log file io tracking would generate some overhead though, similar
to network stats tracking.
I think the stats collector concerns voiced previously on the list
were more about per relation stats which creates alot of io on servers
with many tables. Adding global stats doesn't seem as bad to me.
-nigel.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Oct 24, 2013 at 12:30 AM, Mike Blackwell <mike.blackwell@rrd.com> wrote:
On Wed, Oct 23, 2013 at 1:58 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
IMO, the idea is pretty good. Its just that we need to do some wide
spectrum performance testing. Thats only my thought though.I'm looking at trying to do some performance testing on this. Any
suggestions on test scenarios, etc?
Umm...Lots of clients together would be the first obvious testing that
comes to my mind.
One thing to look at would be erratic clients. If some clients connect
and disconnect within a short span of time, we should look if the
collector works fine there.
Also, we should verify the accuracy of the statistics collected. A
small deviation is fine, but we should do a formal test, just to be
sure.
Does anyone think that the new untracked ports introduced by the patch
could pose a problem? I am not sure there.
I havent taken a deep look at the patch yet, but I will try to do so.
However, since I will be in Dublin next week, it may happen that my
inputs may be delayed a bit. The plus side is that I will discuss this
with lots of people there.
Adding myself as the co reviewer specifically for the testing
purposes, if its ok with you.
Regards,
Atri
--
Regards,
Atri
l'apprenant
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Oct 23, 2013 at 2:10 PM, Atri Sharma <atri.jiit@gmail.com> wrote:
Adding myself as the co reviewer specifically for the testing
purposes, if its ok with you.
It's perfectly fine with me. Please do!
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Wed, Oct 23, 2013 at 2:44 PM, Nigel Heron <nheron@querymetrics.com> wrote:
Hi, thanks, I'm still actively working on this patch. I've gotten the
traffic counters working when using SSL enabled clients (includes the
ssl overhead now) but I still have the walsender transfers under SSL
to work on.
I'll post an updated patch when i have it figured out.
Since the patch changes some views in pg_catalog, a regression test
fails .. i'm not sure what to do next. Change the regression test in
the patch, or wait until the review phase?
here's v2 of the patch including the regression test update.
I omitted socket counters for walreceivers, i couldn't get them
working under SSL. Since they are using the front end libpq libs i
would have to duplicate alot of the code in the backend to be able to
instrument them under SSL (add openssl BIO custom send/recv like the
backend has), not sure it's worth it.. We can get the data from the
master's pg_stat_replication view anyways. I'm open to suggestions.
So, for now, the counters only track sockets created from an inbound
(client to server) connection.
-nigel.
Attachments:
So, for now, the counters only track sockets created from an inbound
(client to server) connection.
here's v3 of the patch (rebase and cleanup).
-nigel.
Attachments:
netstats-v3.patchtext/x-patch; charset=US-ASCII; name=netstats-v3.patchDownload+324-66
On Mon, Oct 21, 2013 at 5:14 AM, Nigel Heron <nheron@querymetrics.com>wrote:
- can be used to find misbehaving connections.
- can be used in multi-user/multi-database clusters for resource usage
tracking.
- competing databases have such metrics.
The most interesting thing that I could see calculating from these stats
would require also knowing how much time was spent waiting on writes and
reads on the network. With the cumulative time spent as well as the count
of syscalls you can calculate the average latency over any time period
between two snapshots. However that would involve adding two gettimeofday
calls which would be quite likely to cause a noticeable impact on some
architectures. Unless there's already a pair of gettimeofday calls you can
piggy back onto?
--
greg
On Tue, Oct 29, 2013 at 11:26 AM, Nigel Heron <nheron@querymetrics.com> wrote:
So, for now, the counters only track sockets created from an inbound
(client to server) connection.here's v3 of the patch (rebase and cleanup).
Hi,
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)
thanks,
-nigel.
Attachments:
netstats-v4.patchtext/x-patch; charset=US-ASCII; name=netstats-v4.patchDownload+416-66
Patch applies and builds against git HEAD (as of 6790e738031089d5). "make
check" runs cleanly as well.
The new features appear to work as advertised as far as I've been able to
check.
The code looks good as far as I can see. Documentation patches are
included for the new features.
Still to be tested:
the counts for streaming replication (no replication setup here to test
against yet).
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Fri, Nov 8, 2013 at 9:01 AM, Nigel Heron <nheron@querymetrics.com> wrote:
Show quoted text
On Tue, Oct 29, 2013 at 11:26 AM, Nigel Heron <nheron@querymetrics.com>
wrote:So, for now, the counters only track sockets created from an inbound
(client to server) connection.here's v3 of the patch (rebase and cleanup).
Hi,
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)thanks,
-nigel.
Also still to be tested: performance impact.
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Fri, Nov 8, 2013 at 9:33 AM, Mike Blackwell <mike.blackwell@rrd.com>wrote:
Show quoted text
Patch applies and builds against git HEAD (as of 6790e738031089d5). "make
check" runs cleanly as well.The new features appear to work as advertised as far as I've been able to
check.The code looks good as far as I can see. Documentation patches are
included for the new features.Still to be tested:
the counts for streaming replication (no replication setup here to test
against yet).__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*On Fri, Nov 8, 2013 at 9:01 AM, Nigel Heron <nheron@querymetrics.com>wrote:
On Tue, Oct 29, 2013 at 11:26 AM, Nigel Heron <nheron@querymetrics.com>
wrote:So, for now, the counters only track sockets created from an inbound
(client to server) connection.here's v3 of the patch (rebase and cleanup).
Hi,
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)thanks,
-nigel.
On Thu, Nov 7, 2013 at 8:21 PM, Greg Stark <stark@mit.edu> wrote:
The most interesting thing that I could see calculating from these stats
would require also knowing how much time was spent waiting on writes and
reads on the network. With the cumulative time spent as well as the count of
syscalls you can calculate the average latency over any time period between
two snapshots. However that would involve adding two gettimeofday calls
which would be quite likely to cause a noticeable impact on some
architectures. Unless there's already a pair of gettimeofday calls you can
piggy back onto?
Adding timing instrumentation to each send() and recv() would require
over 50 calls to gettimeofday for a simple psql -c "SELECT 1", while
the client was waiting. That would add ~40usec extra time (estimated
using pg_test_timing on my laptop without TSC). It might be more
overhead than it's worth.
-nigel.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, 2013-11-08 at 10:01 -0500, Nigel Heron wrote:
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)
Your patch needs to be rebased:
CONFLICT (content): Merge conflict in src/test/regress/expected/rules.out
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Nov 13, 2013 at 11:27 PM, Peter Eisentraut <peter_e@gmx.net> wrote:
On Fri, 2013-11-08 at 10:01 -0500, Nigel Heron wrote:
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)Your patch needs to be rebased:
CONFLICT (content): Merge conflict in src/test/regress/expected/rules.out
Hi,
here's a rebased patch with some additions.
an overview of it's current state...
a new pg_stat_socket global view:
- total bytes sent and received
- bytes sent and received for user backends
- bytes sent and received for wal senders
- total connection attempts
- successful connections to user backends
- successful connections to wal senders
- stats reset time
pg_stat_reset_shared('socket') resets the counters
added to pg_stat_database view:
- bytes sent and received per db
- successful connections per db
pg_stat_reset() resets the counters
added to pg_stat_activity view:
- bytes sent and received per backend
added to pg_stat_replication view:
- bytes sent and received per wal sender
using the existing track_counts guc to enable/disable these stats.
-nigel.
Attachments:
netstats-v5.patchtext/x-patch; charset=US-ASCII; name=netstats-v5.patchDownload+713-88
This patch looks good to me. It applies, builds, and runs the regression
tests. Documentation is included and it seems to do what it says. I don't
consider myself a code expert, but as far as I can see it looks fine. This
is a pretty straightforward enhancement to the existing pg_stat_* code.
If no one has any objections, I'll mark it ready for committer.
Mike
__________________________________________________________________________________
*Mike Blackwell | Technical Analyst, Distribution Services/Rollout
Management | RR Donnelley*
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com
<http://www.rrdonnelley.com/>
* <Mike.Blackwell@rrd.com>*
On Thu, Nov 14, 2013 at 11:29 PM, Nigel Heron <nheron@querymetrics.com>wrote:
Show quoted text
On Wed, Nov 13, 2013 at 11:27 PM, Peter Eisentraut <peter_e@gmx.net>
wrote:On Fri, 2013-11-08 at 10:01 -0500, Nigel Heron wrote:
here's v4 of the patch. I added documentation and a new global view
called "pg_stat_socket" (includes bytes_sent, bytes_received and
stats_reset time)Your patch needs to be rebased:
CONFLICT (content): Merge conflict in src/test/regress/expected/rules.out
Hi,
here's a rebased patch with some additions.an overview of it's current state...
a new pg_stat_socket global view:
- total bytes sent and received
- bytes sent and received for user backends
- bytes sent and received for wal senders
- total connection attempts
- successful connections to user backends
- successful connections to wal senders
- stats reset time
pg_stat_reset_shared('socket') resets the countersadded to pg_stat_database view:
- bytes sent and received per db
- successful connections per db
pg_stat_reset() resets the countersadded to pg_stat_activity view:
- bytes sent and received per backendadded to pg_stat_replication view:
- bytes sent and received per wal senderusing the existing track_counts guc to enable/disable these stats.
-nigel.
