Thread: Trace JDBC connection to Java Thread / local socket port

Trace JDBC connection to Java Thread / local socket port

From
Ludwig Adam
Date:
* Remark*
Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to
seethe message. 

Dear JDBC list,

we are currently trying to pinpoint some performance issues on our database.
We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end
weonly know the output of ps which gives us the client socket port of the query in question, like: 

<procpid> postgres: <user> <database> <client ip (client socket port)> CMD

With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however in
orderto further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently running
thequery over this socket connection (in our case the java process is a JBoss AS and we would like to get the specific
worker(s)issueing the query in question). 

Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a
LocalPortand a SQLConnection instance? 

Basically what we want to do is output something like:

SQLConnection con= pool.getConnection(); // Get connection from pool, includes con.open()
Logger.debug(Thread.currentThread().getName() " has acquired a connection on "+
Mapping.getLocalPortForConnection(con));

As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a
specificport, thus enabling us to trace the connection to specific thread or at least give us pointers. 

Looking at the source code it seems that the actual Socket connection is done within  PGStream.
This socket is declared private and I don't see any debug statements which could give us hints on the local port used.

Current idea would be now to patch PGStream to add this Mapping.
Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific
Javathread. I am certain we are not the only ones with this kind of problem. 

Best regards,
Ludwig



petaFuel GmbH
Ludwig Adam
Geschäftsführer

Münchner Strasse 4
85354 Freising

Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
Fax: +49 (0) 8161 4060 - 401               eMail: ludwig.adam@petafuel.de

Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht München, HRB 133773 www.petaFuel.de



Re: Trace JDBC connection to Java Thread / local socket port

From
Florent Guillaume
Date:
Hi,

If you control the location where the JDBC connection is acquired, you
could try playing with Connection.setClientInfo to pass additional
info for "ApplicationName" to make it visible from pg_stat_activity.

For instance con.setClientInfo("ApplicationName",
Thread.currentThread().getName()).

Florent

On Thu, Jul 17, 2014 at 7:43 PM, Ludwig Adam <ludwig.adam@petafuel.de> wrote:
> * Remark*
> Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to
seethe message. 
>
> Dear JDBC list,
>
> we are currently trying to pinpoint some performance issues on our database.
> We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end
weonly know the output of ps which gives us the client socket port of the query in question, like: 
>
> <procpid> postgres: <user> <database> <client ip (client socket port)> CMD
>
> With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however
inorder to further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently
runningthe query over this socket connection (in our case the java process is a JBoss AS and we would like to get the
specificworker(s) issueing the query in question). 
>
> Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a
LocalPortand a SQLConnection instance? 
>
> Basically what we want to do is output something like:
>
> SQLConnection con= pool.getConnection(); // Get connection from pool, includes con.open()
> Logger.debug(Thread.currentThread().getName() " has acquired a connection on "+
Mapping.getLocalPortForConnection(con));
>
> As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a
specificport, thus enabling us to trace the connection to specific thread or at least give us pointers. 
>
> Looking at the source code it seems that the actual Socket connection is done within  PGStream.
> This socket is declared private and I don't see any debug statements which could give us hints on the local port
used.
>
> Current idea would be now to patch PGStream to add this Mapping.
> Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific
Javathread. I am certain we are not the only ones with this kind of problem. 
>
> Best regards,
> Ludwig
>
>
>
> petaFuel GmbH
> Ludwig Adam
> Geschäftsführer
>
> Münchner Strasse 4
> 85354 Freising
>
> Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
> Fax: +49 (0) 8161 4060 - 401               eMail: ludwig.adam@petafuel.de
>
> Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht München, HRB 133773 www.petaFuel.de
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc



--
Florent Guillaume, Director of R&D, Nuxeo
Open Source Content Management Platform for Business Apps
http://www.nuxeo.com   http://community.nuxeo.com


Re: Trace JDBC connection to Java Thread / local socket port

From
Ludwig Adam
Date:
Dear Florent,

that is a very nice approach and absolutely feasible. Thanks for your advice.

Ludwig

petaFuel GmbH
Ludwig Adam
Geschäftsführer

Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz
Amtsgericht München, HRB 133773

Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02

-----Ursprüngliche Nachricht-----
Von: Florent Guillaume [mailto:fg@nuxeo.com] 
Gesendet: Freitag, 18. Juli 2014 14:54
An: Ludwig Adam
Cc: pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC] Trace JDBC connection to Java Thread / local socket port

Hi,

If you control the location where the JDBC connection is acquired, you could try playing with Connection.setClientInfo
topass additional info for "ApplicationName" to make it visible from pg_stat_activity.
 

For instance con.setClientInfo("ApplicationName",
Thread.currentThread().getName()).

Florent

On Thu, Jul 17, 2014 at 7:43 PM, Ludwig Adam <ludwig.adam@petafuel.de> wrote:
> * Remark*
> Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to
seethe message.
 
>
> Dear JDBC list,
>
> we are currently trying to pinpoint some performance issues on our database.
> We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end
weonly know the output of ps which gives us the client socket port of the query in question, like:
 
>
> <procpid> postgres: <user> <database> <client ip (client socket port)> 
> CMD
>
> With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however
inorder to further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently
runningthe query over this socket connection (in our case the java process is a JBoss AS and we would like to get the
specificworker(s) issueing the query in question).
 
>
> Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a
LocalPortand a SQLConnection instance?
 
>
> Basically what we want to do is output something like:
>
> SQLConnection con= pool.getConnection(); // Get connection from pool, 
> includes con.open()
> Logger.debug(Thread.currentThread().getName() " has acquired a 
> connection on "+ Mapping.getLocalPortForConnection(con));
>
> As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a
specificport, thus enabling us to trace the connection to specific thread or at least give us pointers.
 
>
> Looking at the source code it seems that the actual Socket connection is done within  PGStream.
> This socket is declared private and I don't see any debug statements which could give us hints on the local port
used.
>
> Current idea would be now to patch PGStream to add this Mapping.
> Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific
Javathread. I am certain we are not the only ones with this kind of problem.
 
>
> Best regards,
> Ludwig
>
>
>
> petaFuel GmbH
> Ludwig Adam
> Geschäftsführer
>
> Münchner Strasse 4
> 85354 Freising
>
> Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
> Fax: +49 (0) 8161 4060 - 401               eMail: ludwig.adam@petafuel.de
>
> Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht 
> München, HRB 133773 www.petaFuel.de
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make 
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc



--
Florent Guillaume, Director of R&D, Nuxeo Open Source Content Management Platform for Business Apps
http://www.nuxeo.com   http://community.nuxeo.com

Re: Trace JDBC connection to Java Thread / local socket port

From
Jeremy Whiting
Date:
Hi Ludwig,
  I suggest you try using Byteman [1]. It is an invaluable tool for
tracing causes of issues. When system state or concurrency needs to be
detected and traced this tool was written for the purpose.

  The performance team I work in and other internal teams swear by it.

Regards,
Jeremy

[1] http://www.jboss.org/byteman

--
Jeremy Whiting
Senior Software Engineer, JBoss Performance Team
Red Hat

------------------------------------------------------------
Registered Address: RED HAT UK LIMITED, 64 Baker Street, 4th Floor, Paddington. London. United Kingdom W1U 7DF
Registered in UK and Wales under Company Registration No. 3798903  Directors: Michael Cunningham (US), Charles Peters
(US),Matt Parson (US) and Michael O'Neill(Ireland) 


On 07/17/2014 06:43 PM, Ludwig Adam wrote:
> * Remark*
> Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to
seethe message. 
>
> Dear JDBC list,
>
> we are currently trying to pinpoint some performance issues on our database.
> We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end
weonly know the output of ps which gives us the client socket port of the query in question, like: 
>
> <procpid> postgres: <user> <database> <client ip (client socket port)> CMD
>
> With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however
inorder to further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently
runningthe query over this socket connection (in our case the java process is a JBoss AS and we would like to get the
specificworker(s) issueing the query in question). 
>
> Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a
LocalPortand a SQLConnection instance? 
>
> Basically what we want to do is output something like:
>
> SQLConnection con= pool.getConnection(); // Get connection from pool, includes con.open()
> Logger.debug(Thread.currentThread().getName() " has acquired a connection on "+
Mapping.getLocalPortForConnection(con));
>
> As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a
specificport, thus enabling us to trace the connection to specific thread or at least give us pointers. 
>
> Looking at the source code it seems that the actual Socket connection is done within  PGStream.
> This socket is declared private and I don't see any debug statements which could give us hints on the local port
used.
>
> Current idea would be now to patch PGStream to add this Mapping.
> Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific
Javathread. I am certain we are not the only ones with this kind of problem. 
>
> Best regards,
> Ludwig
>
>
>
> petaFuel GmbH
> Ludwig Adam
> Geschäftsführer
>
> Münchner Strasse 4
> 85354 Freising
>
> Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
> Fax: +49 (0) 8161 4060 - 401               eMail: ludwig.adam@petafuel.de
>
> Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht München, HRB 133773 www.petaFuel.de
>
>
>




Re: Trace JDBC connection to Java Thread / local socket port

From
Ludwig Adam
Date:
Thanks, Jeremy, for your suggestion.
For the start we have now added the Client Info as previously suggested in the mailing list.
We have tried Byteman in the past but attaching it to the JBoss was not very performant so we didn't try further.
Perhaps it is worth a new try now if we get stuck with the Client Info solution.

Ludwig
________________________________________
Von: Jeremy Whiting [jwhiting@redhat.com]
Gesendet: Dienstag, 5. August 2014 10:43
An: Ludwig Adam; pgsql-jdbc@postgresql.org
Betreff: Re: [JDBC] Trace JDBC connection to Java Thread / local socket port

Hi Ludwig,
  I suggest you try using Byteman [1]. It is an invaluable tool for
tracing causes of issues. When system state or concurrency needs to be
detected and traced this tool was written for the purpose.

  The performance team I work in and other internal teams swear by it.

Regards,
Jeremy

[1] http://www.jboss.org/byteman

--
Jeremy Whiting
Senior Software Engineer, JBoss Performance Team
Red Hat

------------------------------------------------------------
Registered Address: RED HAT UK LIMITED, 64 Baker Street, 4th Floor, Paddington. London. United Kingdom W1U 7DF
Registered in UK and Wales under Company Registration No. 3798903  Directors: Michael Cunningham (US), Charles Peters
(US),Matt Parson (US) and Michael O'Neill(Ireland) 


On 07/17/2014 06:43 PM, Ludwig Adam wrote:
> * Remark*
> Resent this message from June 23rd as it did not arrive at the list somehow- please excuse if I was just too dumb to
seethe message. 
>
> Dear JDBC list,
>
> we are currently trying to pinpoint some performance issues on our database.
> We can identify queries that cause high load on our servers but cannot pinpoint the origin of this query - at the end
weonly know the output of ps which gives us the client socket port of the query in question, like: 
>
> <procpid> postgres: <user> <database> <client ip (client socket port)> CMD
>
> With a netstat -p | grep <client socket port> we are able to identify the PID of the JVM opening the socket, however
inorder to further pinpoint this issue we would need to get inside the JVM and determine the Java Thread currently
runningthe query over this socket connection (in our case the java process is a JBoss AS and we would like to get the
specificworker(s) issueing the query in question). 
>
> Is there any way to get to the Socket.getLocalPort() of a connection opened, i.e. establish a mapping between a
LocalPortand a SQLConnection instance? 
>
> Basically what we want to do is output something like:
>
> SQLConnection con= pool.getConnection(); // Get connection from pool, includes con.open()
> Logger.debug(Thread.currentThread().getName() " has acquired a connection on "+
Mapping.getLocalPortForConnection(con));
>
> As we have a central DB management layer we would be able to debug which thread currently occupies a connection on a
specificport, thus enabling us to trace the connection to specific thread or at least give us pointers. 
>
> Looking at the source code it seems that the actual Socket connection is done within  PGStream.
> This socket is declared private and I don't see any debug statements which could give us hints on the local port
used.
>
> Current idea would be now to patch PGStream to add this Mapping.
> Perhaps you have a different idea on how to identify the local port or -even better- trace a connection to a specific
Javathread. I am certain we are not the only ones with this kind of problem. 
>
> Best regards,
> Ludwig
>
>
>
> petaFuel GmbH
> Ludwig Adam
> Geschäftsführer
>
> Münchner Strasse 4
> 85354 Freising
>
> Tel.: +49 (0) 8161 4060 - 202               Mobil: +49 (0) 179-22 911 02
> Fax: +49 (0) 8161 4060 - 401               eMail: ludwig.adam@petafuel.de
>
> Geschäftsführer: Ludwig Adam, Dr. Peter Schönweitz Amtsgericht München, HRB 133773 www.petaFuel.de
>
>
>