Thread: Get bytes sent to client

Get bytes sent to client

From
Alexander Pyhalov
Date:
Hello.
I'd like to collect some statistics in my application (using JDBC). In
Oracle I could collect data, sent to client, using the following query:

SELECT S.value FROM V$MYSTAT S,SYS.V_$STATNAME NAMES
WHERE NAMES.STATISTIC#=S.STATISTIC#
AND NAMES.NAME='bytes sent via SQL*Net to client';

Can I do something like this in PostgreSQL? I'd like to estimate query
result size, to be more precise, amount of network traffic, which is
generated in response to a query.

--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of South Federal University

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
I had some thoughts about extending ProtocolConnectionImpl, PGStream
and so on (to account bytes on client side, when PGStream calls pg_input
  methods), but they have a lot of private fields and methods... It is
also is quite time-consuming... Are there any other ideas? The idea is
to account size of raw data sent to client...

Alexander Pyhalov wrote:
> Hello.
> I'd like to collect some statistics in my application (using JDBC). In
> Oracle I could collect data, sent to client, using the following query:
>
> SELECT S.value FROM V$MYSTAT S,SYS.V_$STATNAME NAMES
> WHERE NAMES.STATISTIC#=S.STATISTIC#
> AND NAMES.NAME='bytes sent via SQL*Net to client';
>
> Can I do something like this in PostgreSQL? I'd like to estimate query
> result size, to be more precise, amount of network traffic, which is
> generated in response to a query.
>


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.


Re: Get bytes sent to client

From
dmp
Date:
I have had similar interest in my applications raw byte traffic on the
network and
just ended up using on Linux iptraf monitor. You can reset it then
execute a query
in the client to see exactly the bytes being transmitted/received. I'm
sure there must
be something similar for other platforms.

danap.

> I had some thoughts about extending ProtocolConnectionImpl, PGStream
> and so on (to account bytes on client side, when PGStream calls
> pg_input  methods), but they have a lot of private fields and
> methods... It is also is quite time-consuming... Are there any other
> ideas? The idea is to account size of raw data sent to client...
>
> Alexander Pyhalov wrote:
>
>> Hello.
>> I'd like to collect some statistics in my application (using JDBC).
>> In Oracle I could collect data, sent to client, using the following
>> query:
>>
>> SELECT S.value FROM V$MYSTAT S,SYS.V_$STATNAME NAMES
>> WHERE NAMES.STATISTIC#=S.STATISTIC#
>> AND NAMES.NAME='bytes sent via SQL*Net to client';
>>
>> Can I do something like this in PostgreSQL? I'd like to estimate
>> query result size, to be more precise, amount of network traffic,
>> which is generated in response to a query.
>>

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
Hello.
Thanks for answer. I used dumpster in my tests. However, executing
system tools from lava is not a brilliant idea. And I have to collect
this statistics dynamically at runtime :(

damp wrote:
> I have had similar interest in my applications raw byte traffic on the
> network and
> just ended up using on Linux iptraf monitor. You can reset it then
> execute a query
> in the client to see exactly the bytes being transmitted/received. I'm
> sure there must
> be something similar for other platforms.



--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.


Re: Get bytes sent to client

From
Craig Ringer
Date:
On 17/12/2009 12:56 AM, Alexander Pyhalov wrote:
> Hello.
> Thanks for answer. I used dumpster in my tests. However, executing
> system tools from lava is not a brilliant idea. And I have to collect
> this statistics dynamically at runtime :(

It sounds like you'll need to modify the JDBC driver to add the hooks
you need, then.

Perhaps if you can raise it with the JDBC folks (wherever they hang out)
and talk it over first, you might be able to produce a patch that
doesn't affect people who don't use the feature, so they might accept
into the main JDBC driver.

--
Craig Ringer

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
I've made patch for Postgres JDBC driver. Now driver accounts bytes
sent/received from client ing PGStream. I hope I didn't miss anything. I
made several tests. tcpdump shows about 10% overhead, but results are
quite similar. I think that overhead is caused by auxiliary data
(packets' headers and so on).

Craig Ringer wrote:
> On 17/12/2009 12:56 AM, Alexander Pyhalov wrote:
>> Hello.
>> Thanks for answer. I used dumpster in my tests. However, executing
>> system tools from lava is not a brilliant idea. And I have to collect
>> this statistics dynamically at runtime :(
>
> It sounds like you'll need to modify the JDBC driver to add the hooks
> you need, then.
>
> Perhaps if you can raise it with the JDBC folks (wherever they hang out)
> and talk it over first, you might be able to produce a patch that
> doesn't affect people who don't use the feature, so they might accept
> into the main JDBC driver.
>
> --
> Craig Ringer


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/BaseConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java    2008-04-15 08:23:54.000000000 +0400
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/BaseConnection.java    2009-12-17 21:47:47.000000000 +0300
@@ -139,4 +139,8 @@

     // Get the bind-string-as-varchar config flag
     public boolean getStringVarcharFlag();
+
+    public int getRecvdBytes();
+
+    public int getSentBytes();
 }
diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/PGStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/PGStream.java    2009-12-17 21:31:00.000000000 +0300
@@ -46,6 +46,8 @@
     private Encoding encoding;
     private Writer encodingWriter;

+    protected int sentBytes;
+    protected int recvdBytes;
     /**
      * Constructor:  Connect to the PostgreSQL back end and return
      * a stream connection.
@@ -56,6 +58,7 @@
      */
     public PGStream(String host, int port) throws IOException
     {
+        sentBytes=recvdBytes=0;
         this.host = host;
         this.port = port;

@@ -78,6 +81,15 @@
         return connection;
     }

+    public int getSentBytes()
+    {
+        return sentBytes;
+    }
+
+    public int getRecvdBytes()
+    {
+        return recvdBytes;
+    }
     /**
      * Check for pending backend messages without blocking.
      * Might return false when there actually are messages
@@ -172,6 +184,7 @@
     public void SendChar(int val) throws IOException
     {
         pg_output.write(val);
+        sentBytes+=1;
     }

     /**
@@ -187,6 +200,7 @@
         _int4buf[2] = (byte)(val >>> 8);
         _int4buf[3] = (byte)(val);
         pg_output.write(_int4buf);
+        sentBytes+=4;
     }

     /**
@@ -203,6 +217,7 @@
         _int2buf[0] = (byte)(val >>> 8);
         _int2buf[1] = (byte)val;
         pg_output.write(_int2buf);
+        sentBytes+=2;
     }

     /**
@@ -214,6 +229,7 @@
     public void Send(byte buf[]) throws IOException
     {
         pg_output.write(buf);
+        sentBytes+=buf.length;
     }

     /**
@@ -246,6 +262,7 @@
         {
             pg_output.write(0);
         }
+        sentBytes+= siz;
     }

     /**
@@ -259,6 +276,7 @@
         int c = pg_input.read();
         if (c < 0)
             throw new EOFException();
+        recvdBytes++;
         return c;
     }

@@ -272,7 +290,7 @@
     {
         if (pg_input.read(_int4buf) != 4)
             throw new EOFException();
-
+        recvdBytes+=4;
         return (_int4buf[0] & 0xFF) << 24 | (_int4buf[1] & 0xFF) << 16 | (_int4buf[2] & 0xFF) << 8 | _int4buf[3] &
0xFF;
     }

@@ -286,7 +304,7 @@
     {
         if (pg_input.read(_int2buf) != 2)
             throw new EOFException();
-
+        recvdBytes+=2;
         return (_int2buf[0] & 0xFF) << 8 | _int2buf[1] & 0xFF;
     }

@@ -304,6 +322,7 @@
         String res = encoding.decode(pg_input.getBuffer(), pg_input.getIndex(),
                                      len);
         pg_input.skip(len);
+        recvdBytes+=len;
         return res;
     }

@@ -320,6 +339,7 @@
         String res = encoding.decode(pg_input.getBuffer(), pg_input.getIndex(),
                                      len - 1);
         pg_input.skip(len);
+        recvdBytes+=len;
         return res;
     }

@@ -447,6 +467,7 @@
                 throw new EOFException();
             s += w;
         }
+        recvdBytes+=siz;
     }

     public void Skip(int size) throws IOException {
@@ -454,6 +475,7 @@
         while (s < size) {
             s += pg_input.skip(size - s);
         }
+        recvdBytes+=size;
     }


diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/ProtocolConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/ProtocolConnection.java    2009-12-17 21:48:01.000000000
+0300
@@ -132,4 +132,8 @@
      * @return the version of the implementation
      */
     public int getProtocolVersion();
+
+    public int getRecvdBytes();
+
+    public int getSentBytes();
 }
diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/v2/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/v2/ProtocolConnectionImpl.java    2009-12-17
21:49:51.000000000+0300 
@@ -218,4 +218,18 @@
     private final String database;
     private final QueryExecutorImpl executor;
     private final Logger logger;
+
+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }
 }
diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/v3/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/core/v3/ProtocolConnectionImpl.java    2009-12-17
21:50:21.000000000+0300 
@@ -200,6 +200,20 @@
         return 3;
     }

+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }
+
     private String serverVersion;
     private int cancelPid;
     private int cancelKey;
diff -ur postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java
postgresql-jdbc-8.4-701.src_modified/org/postgresql/jdbc2/AbstractJdbc2Connection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-07-01 09:00:40.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_modified/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-12-17
21:52:26.000000000+0300 
@@ -1068,4 +1068,18 @@
             copyManager = new CopyManager(this);
         return copyManager;
     }
+
+     public int getRecvdBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getSentBytes();
+    }
 }

Re: Get bytes sent to client

From
Craig Ringer
Date:
On 18/12/2009 2:34 PM, Alexander Pyhalov wrote:
>
> I've made patch for Postgres JDBC driver. Now driver accounts bytes
> sent/received from client ing PGStream.

Can't you extend or wrap the pg_input / pg_output streams to do the
accounting in their read(...) / write(...) method instead? The way
you've done it is rather fragile and is very prone to bitrot as other
parts of the driver are changed.

--
Craig Ringer

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
I'll look at this in the weekend an report later...
Craig Ringer wrote:
> sent/received from client ing PGStream.
>
> Can't you extend or wrap the pg_input / pg_output streams to do the
> accounting in their read(...) / write(...) method instead? The way
> you've done it is rather fragile and is very prone to bitrot as other
> parts of the driver are changed.


--
Best regards,
Alexander Pyhalov,
system administrator of Computer Center of South Federal University

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
Good day..
I've just made new patch - extended BufferedOutputStream and
VisibleBufferedInputStream, as you suggested. It works, and results are
comparable with tcpdump data (for received traffic). For sent traffic
they are incorrect, because I can't account all ACK, but only PGSQL
traffic.
It's suprising, that received traffic is more then tcpdump shows ( 2%,
butt more). I thought it would be a bit smaller then actual data.
However, this data gives good approximation (2% for 100MB traffic is not
a lot).

Craig Ringer wrote:
> On 18/12/2009 2:34 PM, Alexander Pyhalov wrote:
>>
>> I've made patch for Postgres JDBC driver. Now driver accounts bytes
>> sent/received from client ing PGStream.
>
> Can't you extend or wrap the pg_input / pg_output streams to do the
> accounting in their read(...) / write(...) method instead? The way
> you've done it is rather fragile and is very prone to bitrot as other
> parts of the driver are changed.
>
> --
> Craig Ringer


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/BaseConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java 2008-04-15 08:23:54.000000000 +0400
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/BaseConnection.java    2009-12-19 12:22:19.707859272 +0300
@@ -139,4 +139,8 @@

     // Get the bind-string-as-varchar config flag
     public boolean getStringVarcharFlag();
+
+    public int getSentBytes();
+
+    public int getRecvdBytes();
 }
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingBOutputStream.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/CalculatingBOutputStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingBOutputStream.java    1970-01-01 03:00:00.000000000
+0300
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/CalculatingBOutputStream.java    2009-12-19 12:22:19.707859272
+0300
@@ -0,0 +1,58 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+
+package org.postgresql.core;
+
+import java.io.BufferedOutputStream;
+import java.io.OutputStream;
+import java.io.IOException;
+/**
+ *
+ * @author alp
+ */
+public class CalculatingBOutputStream extends BufferedOutputStream{
+
+    protected int sentBytes;
+
+    public CalculatingBOutputStream(OutputStream out){
+        super(out);
+        sentBytes=0;
+    }
+
+    public CalculatingBOutputStream(OutputStream out, int size){
+        super(out,size);
+        sentBytes=0;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public void write(int b) throws IOException{
+        super.write(b);
+        sentBytes++;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public void write(byte[] b) throws IOException{
+        super.write(b);
+        sentBytes+=b.length;
+    }
+
+    public void write(byte[] b, int off, int len) throws IOException{
+        super.write(b,off,len);
+        sentBytes+=len;
+    }
+
+    /**
+     * Get number of bytes sent to this stream
+     * @return - number of bytes sent
+     */
+    public int getSentBytes()
+    {
+        return sentBytes;
+    }
+}
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingVBInputStream.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/CalculatingVBInputStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingVBInputStream.java    1970-01-01 03:00:00.000000000
+0300
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/CalculatingVBInputStream.java    2009-12-19 12:22:19.688492698
+0300
@@ -0,0 +1,83 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+
+package org.postgresql.core;
+
+import java.io.IOException;
+import java.io.InputStream;
+
+/**
+ *
+ * @author alp
+ */
+public class CalculatingVBInputStream extends VisibleBufferedInputStream {
+
+    protected int rcvdBytes;
+
+    public CalculatingVBInputStream(InputStream in, int bufferSize) {
+        super(in,bufferSize);
+        rcvdBytes=0;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public long skip(long n) throws IOException
+    {
+        long skipped;
+
+        skipped=super.skip(n);
+        if(skipped>0)
+            rcvdBytes+=skipped;
+        return skipped;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public int read(byte[] b) throws IOException
+    {
+        int read;
+
+        read=super.read(b);
+        if(read>0)
+            rcvdBytes+=read;
+        return read;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public int read() throws IOException
+    {
+        int read;
+
+        read=super.read();
+        if(read>0)
+            rcvdBytes++;
+        return read;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public int read(byte to[], int off, int len) throws IOException {
+        int read;
+
+        read=super.read(to,off,len);
+        if(read>0)
+            rcvdBytes+=read;
+        return read;
+    }
+
+    /**
+     * Get number of bytes read from this stream
+     * @return - number of bytes read
+     */
+    public int getRecvdBytes()
+    {
+        return rcvdBytes;
+    }
+}
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/PGStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/PGStream.java    2009-12-19 12:22:19.688492698 +0300
@@ -108,8 +108,8 @@
         connection.setTcpNoDelay(true);

         // Buffer sizes submitted by Sverre H Huseby <sverrehu@online.no>
-        pg_input = new VisibleBufferedInputStream(connection.getInputStream(), 8192);
-        pg_output = new BufferedOutputStream(connection.getOutputStream(), 8192);
+        pg_input = new CalculatingVBInputStream(connection.getInputStream(), 8192);
+        pg_output = new CalculatingBOutputStream(connection.getOutputStream(), 8192);

         if (encoding != null)
             setEncoding(encoding);
@@ -534,4 +534,23 @@
         pg_input.close();
         connection.close();
     }
+
+    public int getRecvdBytes() {
+        if(pg_input!=null){
+            return ((CalculatingVBInputStream)pg_input).getRecvdBytes();
+        }
+        else {
+            return 0;
+        }
+    }
+
+    public int getSentBytes() {
+        if(pg_output!=null){
+            return ((CalculatingBOutputStream)pg_output).getSentBytes();
+        }
+        else {
+            return 0;
+        }
+    }
+
 }
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/ProtocolConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/ProtocolConnection.java    2009-12-19 12:22:19.689492804 +0300
@@ -132,4 +132,8 @@
      * @return the version of the implementation
      */
     public int getProtocolVersion();
+
+    public int getRecvdBytes();
+
+    public int getSentBytes();
 }
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/v2/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/v2/ProtocolConnectionImpl.java    2009-12-19
12:22:19.685493219+0300 
@@ -200,6 +200,20 @@
     {
         return 2;
     }
+
+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }

     private String serverVersion;
     private int cancelPid;
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/v3/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/v3/ProtocolConnectionImpl.java    2009-12-19
12:22:19.686493045+0300 
@@ -200,6 +200,20 @@
         return 3;
     }

+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }
+
     private String serverVersion;
     private int cancelPid;
     private int cancelKey;
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/jdbc2/AbstractJdbc2Connection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-07-01 09:00:40.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-12-19 12:22:19.715864030
+0300
@@ -1068,4 +1068,19 @@
             copyManager = new CopyManager(this);
         return copyManager;
     }
+
+    public int getRecvdBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getSentBytes();
+    }
+
 }

Re: Get bytes sent to client

From
Alexander Pyhalov
Date:
Hello.
Extending VisibleBufferedInputStream was not a good idea - work with
buffer (like ensureBytes() calls ) can't be accounted. So, I modified
it. Results are more precise now.

Alexander Pyhalov wrote:
> Good day..
> I've just made new patch - extended BufferedOutputStream and
> VisibleBufferedInputStream, as you suggested. It works, and results are
> comparable with tcpdump data (for received traffic). For sent traffic
> they are incorrect, because I can't account all ACK, but only PGSQL
> traffic.
> It's suprising, that received traffic is more then tcpdump shows ( 2%,
> butt more). I thought it would be a bit smaller then actual data.
> However, this data gives good approximation (2% for 100MB traffic is not
> a lot).
>
> Craig Ringer wrote:
>> On 18/12/2009 2:34 PM, Alexander Pyhalov wrote:
>>>
>>> I've made patch for Postgres JDBC driver. Now driver accounts bytes
>>> sent/received from client ing PGStream.
>>
>> Can't you extend or wrap the pg_input / pg_output streams to do the
>> accounting in their read(...) / write(...) method instead? The way
>> you've done it is rather fragile and is very prone to bitrot as other
>> parts of the driver are changed.
>>
>> --
>> Craig Ringer
>
>
>
> ------------------------------------------------------------------------
>
>


--
С уважением,
Александр Пыхалов,
системный администратор ЮГИНФО ЮФУ.
diff -urN postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java
postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/BaseConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/BaseConnection.java 2008-04-15 08:23:54.000000000 +0400
+++ postgresql-jdbc-8.4-701.src_mod2/org/postgresql/core/BaseConnection.java    2009-12-19 12:22:19.707859272 +0300
@@ -139,4 +139,8 @@

     // Get the bind-string-as-varchar config flag
     public boolean getStringVarcharFlag();
+
+    public int getSentBytes();
+
+    public int getRecvdBytes();
 }
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingBOutputStream.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/CalculatingBOutputStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/CalculatingBOutputStream.java    1970-01-01 03:00:00.000000000
+0300
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/CalculatingBOutputStream.java    2009-12-21 11:46:45.075261312
+0300
@@ -0,0 +1,58 @@
+/*
+ * To change this template, choose Tools | Templates
+ * and open the template in the editor.
+ */
+
+package org.postgresql.core;
+
+import java.io.BufferedOutputStream;
+import java.io.OutputStream;
+import java.io.IOException;
+/**
+ *
+ * @author alp
+ */
+public class CalculatingBOutputStream extends BufferedOutputStream{
+
+    protected int sentBytes;
+
+    public CalculatingBOutputStream(OutputStream out){
+        super(out);
+        sentBytes=0;
+    }
+
+    public CalculatingBOutputStream(OutputStream out, int size){
+        super(out,size);
+        sentBytes=0;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public void write(int b) throws IOException{
+        super.write(b);
+        sentBytes++;
+    }
+
+    /**
+     * {@inheritDoc}
+     */
+    public void write(byte[] b) throws IOException{
+        super.write(b);
+        sentBytes+=b.length;
+    }
+
+    public void write(byte[] b, int off, int len) throws IOException{
+        super.write(b,off,len);
+        sentBytes+=len;
+    }
+
+    /**
+     * Get number of bytes sent to this stream
+     * @return - number of bytes sent
+     */
+    public int getSentBytes()
+    {
+        return sentBytes;
+    }
+}
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/PGStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/PGStream.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/PGStream.java    2009-12-21 11:46:45.067081118 +0300
@@ -109,7 +109,7 @@

         // Buffer sizes submitted by Sverre H Huseby <sverrehu@online.no>
         pg_input = new VisibleBufferedInputStream(connection.getInputStream(), 8192);
-        pg_output = new BufferedOutputStream(connection.getOutputStream(), 8192);
+        pg_output = new CalculatingBOutputStream(connection.getOutputStream(), 8192);

         if (encoding != null)
             setEncoding(encoding);
@@ -534,4 +534,23 @@
         pg_input.close();
         connection.close();
     }
+
+    public int getRecvdBytes() {
+        if(pg_input!=null){
+            return pg_input.getRecvdBytes();
+        }
+        else {
+            return 0;
+        }
+    }
+
+    public int getSentBytes() {
+        if(pg_output!=null){
+            return ((CalculatingBOutputStream)pg_output).getSentBytes();
+        }
+        else {
+            return 0;
+        }
+    }
+
 }
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/ProtocolConnection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/ProtocolConnection.java    2008-01-08 09:56:27.000000000 +0300
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/ProtocolConnection.java    2009-12-21 11:46:45.075261312 +0300
@@ -132,4 +132,8 @@
      * @return the version of the implementation
      */
     public int getProtocolVersion();
+
+    public int getRecvdBytes();
+
+    public int getSentBytes();
 }
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/VisibleBufferedInputStream.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/VisibleBufferedInputStream.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/VisibleBufferedInputStream.java    2008-01-08 09:56:27.000000000
+0300
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/VisibleBufferedInputStream.java    2009-12-21
11:46:45.074235505+0300 
@@ -55,6 +55,11 @@
     private int endIndex;

     /**
+     * How many bytes was read from wrapped stream
+     */
+    protected int rcvdBytes;
+
+    /**
      * Creates a new buffer around the given stream.
      *
      * @param in The stream to buffer.
@@ -64,6 +69,7 @@
         wrapped = in;
         buffer = new byte[bufferSize < MINIMUM_READ ?
                           MINIMUM_READ : bufferSize];
+        rcvdBytes=0;
     }

     /**
@@ -135,7 +141,8 @@
         int read = wrapped.read(buffer, endIndex, canFit);
         if (read < 0) {
             return false;
-        }
+        }
+        rcvdBytes +=read;
         endIndex += read;
         return true;
     }
@@ -210,6 +217,7 @@
             if (r <= 0) {
                 return (read == 0) ? r : read;
             }
+            rcvdBytes+=r;
             read += r;
             off += r;
             len -= r;
@@ -222,6 +230,7 @@
      * {@inheritDoc}
      */
     public long skip(long n) throws IOException {
+        long skipped;
         int avail = endIndex - index;
         if (avail >= n) {
             index += n;
@@ -230,7 +239,9 @@
         n -= avail;
         index = 0;
         endIndex = 0;
-        return avail + wrapped.skip(n);
+        skipped=wrapped.skip(n);
+        rcvdBytes+=skipped;
+        return avail + skipped;
     }

     /**
@@ -290,4 +301,10 @@
             pos = index;
         }
     }
+
+    public int getRecvdBytes()
+    {
+        return rcvdBytes;
+    }
+
 }
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/v2/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v2/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/v2/ProtocolConnectionImpl.java    2009-12-21
11:46:45.050828561+0300 
@@ -200,6 +200,20 @@
     {
         return 2;
     }
+
+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }

     private String serverVersion;
     private int cancelPid;
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/v3/ProtocolConnectionImpl.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/core/v3/ProtocolConnectionImpl.java    2008-04-01 11:19:20.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/core/v3/ProtocolConnectionImpl.java    2009-12-21
11:46:45.051828667+0300 
@@ -200,6 +200,20 @@
         return 3;
     }

+    public int getRecvdBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(pgStream==null)
+            return 0;
+        else
+            return pgStream.getSentBytes();
+    }
+
     private String serverVersion;
     private int cancelPid;
     private int cancelKey;
diff -Nru postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java
postgresql-jdbc-8.4-701.src_mod3/org/postgresql/jdbc2/AbstractJdbc2Connection.java
--- postgresql-jdbc-8.4-701.src/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-07-01 09:00:40.000000000
+0400
+++ postgresql-jdbc-8.4-701.src_mod3/org/postgresql/jdbc2/AbstractJdbc2Connection.java    2009-12-21 11:46:45.091416093
+0300
@@ -1068,4 +1068,19 @@
             copyManager = new CopyManager(this);
         return copyManager;
     }
+
+    public int getRecvdBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getRecvdBytes();
+    }
+
+    public int getSentBytes() {
+        if(protoConnection==null)
+            return 0;
+        else
+            return protoConnection.getSentBytes();
+    }
+
 }