[DBCP] Possible to get query strings from datasource?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[DBCP] Possible to get query strings from datasource?

Shawn Heisey
Using dbcp 2.2.0 with pool 2.5.0.

If the only object I have access to is BasicDataSource, is it possible
to obtain the SQL queries being executed by active connections in the
pool right now?  If so, how would I go about doing that?

At the moment I'm not using PreparedStatement, but I have plans to do so
in the future.  When that happens, I will be interested in finding the
actual query being executed, not the query with the question mark
placeholders.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Phil Steitz
On 5/4/18 12:49 AM, Shawn Heisey wrote:

> Using dbcp 2.2.0 with pool 2.5.0.
>
> If the only object I have access to is BasicDataSource, is it
> possible to obtain the SQL queries being executed by active
> connections in the pool right now?  If so, how would I go about
> doing that?
>
> At the moment I'm not using PreparedStatement, but I have plans to
> do so in the future.  When that happens, I will be interested in
> finding the actual query being executed, not the query with the
> question mark placeholders.
>

The only thing close to this that you can do now is if you have
prepared statement pooling enabled, then via the JMX instrumentation
of commons pool you can see the output of statement.toString().  
See listAllObjects in GenericObjectPoolMXBean.  Again, the pool you
need to walk to get this is the prepared statement pool associated
with an individual connection, not the connection pool.

Phil
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Shawn Heisey
On 5/4/2018 10:38 AM, Phil Steitz wrote:
> The only thing close to this that you can do now is if you have
> prepared statement pooling enabled, then via the JMX instrumentation
> of commons pool you can see the output of statement.toString().  
> See listAllObjects in GenericObjectPoolMXBean.  Again, the pool you
> need to walk to get this is the prepared statement pool associated
> with an individual connection, not the connection pool.

Is this an answer to both parts of my query?  Only the second part was
concerned with prepared statements.  The first part was about getting
currently executing SQL queries from BasicDataSource.

I am not using prepared statements at this time.  I will not be using
them anytime soon.  The plan that includes prepared statements is a
complete rewrite of the application from scratch, to eliminate some
design problems.

I was using PoolingDataSource in the past.  Because setting that up
involves explicitly configuring the object pool, I did have access to
listAllObjects, but since switching to BasicDataSource (which appears to
be the recommended choice), that no longer seems to be available.

The "setAccessToUnderlyingConnectionAllowed" method implies that it
should be possible to access the connections ... but I can't figure out
how to do that.  Even if I manage that, I haven't yet found a way to
descend from the connection to get the query.

I acknowledge the possibility of an XY problem here, so here's what I'm
trying to do:

When my application fires off an alarm email, I want that email to
include all SQL queries the application is executing at the moment of
the alarm.

Because all DB access is coordinated through a single class (which I
creatively called "Database"), I can probably invent my own way of
tracking this info, but if I can do it through dbcp, that's one less
thing I have to write/debug.  The alarm subroutines can only reach
things that have access to the datasource, not anything below that.

I think the way I would do it myself is using a Map<Connection, String>
object built with Collections.synchronizedMap.  When I close a
connection (return it to the pool), I can remove its entry from that
tracking map.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Phil Steitz
On 5/4/18 12:16 PM, Shawn Heisey wrote:

> On 5/4/2018 10:38 AM, Phil Steitz wrote:
>> The only thing close to this that you can do now is if you have
>> prepared statement pooling enabled, then via the JMX instrumentation
>> of commons pool you can see the output of statement.toString().  
>> See listAllObjects in GenericObjectPoolMXBean.  Again, the pool you
>> need to walk to get this is the prepared statement pool associated
>> with an individual connection, not the connection pool.
> Is this an answer to both parts of my query?  Only the second part was
> concerned with prepared statements.  The first part was about getting
> currently executing SQL queries from BasicDataSource.

The answer to that is no, you can't get it directly from any BDS method.

>
> I am not using prepared statements at this time.  I will not be using
> them anytime soon.  The plan that includes prepared statements is a
> complete rewrite of the application from scratch, to eliminate some
> design problems.
>
> I was using PoolingDataSource in the past.  Because setting that up
> involves explicitly configuring the object pool, I did have access to
> listAllObjects, but since switching to BasicDataSource (which appears to
> be the recommended choice), that no longer seems to be available.

You can access it via JMX, but what you will see is just the
connection meta-data, what is exposed in PoolableConnectionMXBean.
>
> The "setAccessToUnderlyingConnectionAllowed" method implies that it
> should be possible to access the connections ... but I can't figure out
> how to do that.  Even if I manage that, I haven't yet found a way to
> descend from the connection to get the query.

If you cast the connection returned by BDS as a DelegatingConnection
and have setAccessToUnderlyingConnectionAllowed set to true, you can
use the getInnermostDelegate method to get the underlying physical
connection.  In general, you are not going to be able to get to
queries that way, though, as all you will have is the
javax.sql.Connection methods. 

>
> I acknowledge the possibility of an XY problem here, so here's what I'm
> trying to do:
>
> When my application fires off an alarm email, I want that email to
> include all SQL queries the application is executing at the moment of
> the alarm.
>
> Because all DB access is coordinated through a single class (which I
> creatively called "Database"), I can probably invent my own way of
> tracking this info, but if I can do it through dbcp, that's one less
> thing I have to write/debug.
I don't think DBCP is going to be much help there.  Another thing
you might consider is setting up a database user ID for the app and
using database utilities to dump the queries in progress for that
user when your alarm triggers.  Depending on the db / jdbc driver,
you may be able to do this from within your Java app.  See, e.g. [1]

Phil

[1]
https://stackoverflow.com/questions/14036690/dynamic-output-for-show-processlist-in-mysql-using-java/14036759#14036759

>   The alarm subroutines can only reach
> things that have access to the datasource, not anything below that.
>
> I think the way I would do it myself is using a Map<Connection, String>
> object built with Collections.synchronizedMap.  When I close a
> connection (return it to the pool), I can remove its entry from that
> tracking map.
>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

mgainty
In reply to this post by Shawn Heisey

MG>pls see below

________________________________
From: Shawn Heisey <[hidden email]>
Sent: Friday, May 4, 2018 3:16 PM
To: [hidden email]
Subject: Re: [DBCP] Possible to get query strings from datasource?

On 5/4/2018 10:38 AM, Phil Steitz wrote:
> The only thing close to this that you can do now is if you have
> prepared statement pooling enabled, then via the JMX instrumentation
> of commons pool you can see the output of statement.toString().
> See listAllObjects in GenericObjectPoolMXBean.  Again, the pool you
> need to walk to get this is the prepared statement pool associated
> with an individual connection, not the connection pool.

Is this an answer to both parts of my query?  Only the second part was
concerned with prepared statements.  The first part was about getting
currently executing SQL queries from BasicDataSource.

I am not using prepared statements at this time.  I will not be using
them anytime soon.  The plan that includes prepared statements is a
complete rewrite of the application from scratch, to eliminate some
design problems.

I was using PoolingDataSource in the past.  Because setting that up
involves explicitly configuring the object pool, I did have access to
listAllObjects, but since switching to BasicDataSource (which appears to
be the recommended choice), that no longer seems to be available.

MG>have a chat with your DBA on BasicDataSource validationQuery
MG>if this was Oracle it would be something like select 1 from DUAL

MG>from an application perspective all you want is java.sql.Connection right?
MG>org.apache.commons.DelegatingStatement executes sql query parameter:
  public ResultSet executeQuery(String sql) throws SQLException {
        checkOpen();
        try {
            return DelegatingResultSet.wrapResultSet(this,_stmt.executeQuery(sql));
        }
        catch (SQLException e) {
            handleException(e);
            throw new AssertionError();
        }
    }
MG>but who calls this ?
org.apache.commons.dbcp.PoolableConnectionFactory:
    public void validateConnection(Connection conn) throws SQLException {
        String query = _validationQuery;
        if(conn.isClosed()) {
            throw new SQLException("validateConnection: connection closed");
        }
        if(null != query) {
            Statement stmt = null;
            ResultSet rset = null;
            try {
                stmt = conn.createStatement();
                if (_validationQueryTimeout > 0) {
                    stmt.setQueryTimeout(_validationQueryTimeout);
                }
                rset = stmt.executeQuery(query);
//where is validationQuery set?
// in the PoolableConnectionFactory constructor
   public PoolableConnectionFactory(ConnectionFactory connFactory, ObjectPool pool, KeyedObjectPoolFactory stmtPoolFactory, String validationQuery, boolean defaultReadOnly, boolean defaultAutoCommit) {

//validationQuery is set in org.apache.commons.dbcp.BasicDataSource
 public void setValidationQuery(String validationQuery) {

//so in the end whatever validationQuery YOU supply to BasicDataSource
//is the only sql query that you will see
MG>does this answer your question?


The "setAccessToUnderlyingConnectionAllowed" method implies that it
should be possible to access the connections ... but I can't figure out
how to do that.  Even if I manage that, I haven't yet found a way to
descend from the connection to get the query.

I acknowledge the possibility of an XY problem here, so here's what I'm
trying to do:

When my application fires off an alarm email, I want that email to
include all SQL queries the application is executing at the moment of
the alarm.

Because all DB access is coordinated through a single class (which I
creatively called "Database"), I can probably invent my own way of
tracking this info, but if I can do it through dbcp, that's one less
thing I have to write/debug.  The alarm subroutines can only reach
things that have access to the datasource, not anything below that.

I think the way I would do it myself is using a Map<Connection, String>
object built with Collections.synchronizedMap.  When I close a
connection (return it to the pool), I can remove its entry from that
tracking map.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Shawn Heisey
On 5/4/2018 4:49 PM, Martin Gainty wrote:
> MG>have a chat with your DBA on BasicDataSource validationQuery
> MG>if this was Oracle it would be something like select 1 from DUAL

This has me chuckling.   Let me put on my DBA hat (which doesn't fit all
that well)...

The server is MySQL.  I set the validation query in my program.  It is
"SELECT 1".  Here's everything I am setting on the BDS:

  dsMain = new BasicDataSource();
  // TODO: Put the driver name in the properties file.
  dsMain.setDriverClassName("com.mysql.jdbc.Driver");
  dsMain.setUrl(mainUrl);
  dsMain.setUsername(dbUser);
  dsMain.setPassword(dbPass);
  dsMain.setValidationQuery(validationQuery);
  dsMain.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
  dsMain.setMaxWaitMillis(Const.THIRTY_SECONDS);
  dsMain.setMaxIdle(numShards);
  dsMain.setMaxTotal(numShards * 5);
  dsMain.setNumTestsPerEvictionRun(numShards * 5);
  dsMain.setTimeBetweenEvictionRunsMillis(Const.THIRTY_SECONDS);
  dsMain.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
  dsMain.setTestOnCreate(true);
  dsMain.setTestOnBorrow(true);
  dsMain.setTestOnReturn(true);
  dsMain.setTestWhileIdle(true);

We don't have a DBA position in the company.  I'm the primary sysadmin,
so the database servers are under my care, and one of the developers is
designated as the gatekeeper for changes to the databases themselves, to
keep things sane and not broken.

Here's what I did.  Because all database usage is handled through one
class, it was easy to find all the places where a query is executed.  I
added this field to that class:

    /** A map for tracking current SQL statements. */
    private static final Map<Connection, String> sqlTracker = Collections
            .synchronizedMap(new HashMap<Connection, String>());

I really wanted to use the diamond operator here, as I have done when
using synchronizedMap in other programs.  But eclipse complains about
that unless I set the compiler to produce code for Java 8.  I have ONE
system building/running this under Java 7, so I have to keep it like this.

Then before every query execution (after the Connection and Statement
were successfully created), I added this:

  sqlTracker.put(conn, query);

And in the helper method that always gets used for closing JDBC
connections, I added this:

  sqlTracker.remove(conn);

This is the "getPoolStats()" method:

https://apaste.info/v0UO

The implementation looks clean to me, but I need something bad to happen
before I will know if it's actually working.

I could improve it by tracking queries separately for the main
datasource and the master datasource, but just having the SQL queries,
even without knowing which pool it comes from, is a nice addition.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Shawn Heisey
On 5/4/2018 6:15 PM, Shawn Heisey wrote:
> The implementation looks clean to me, but I need something bad to happen
> before I will know if it's actually working.

I added a background thread that periodically logs pool stats.  At
first, I never got it to line up with any actual database usage, and I
never saw any queries in the stats.  By fiddling with the exact timing
for the first logging loop, I was able to get the pool stats to show me
one of the the SQL queries that the program uses when it first starts up.

Before I confirmed that, I did put a sanity check in to log all entries
*removed* from the map, which showed me that queries were indeed being
added.  The second line of this log excerpt (and a whole bunch of other
lines) shows an SQL query being removed:

https://apaste.info/dJK0

(the QC abbreviation means "quietClose" ... the name of the method where
I added the sanity-check logging.)

Looks like I've got it doing what I want, and if any SQL queries are
underway when an alarm is fired, they should be in the email.  So I have
a solution, and there should be enough information in this thread to
help others do the same.

I do wonder if similar SQL query tracking would be in-scope for DBCP
datasource objects.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

mgainty
ConnectionPool responsibility is only to execute Validation Query and deliver connection from pre-assembled connection-pool

unfortunately since SQL queries come from implementor there is no way for lower level
libraries such as commons-pool to know a-priori all of the queries being executed.
Moreover look at all of the testcases you would need to test this
TestForAllQueries
TestForValidationQuery
TestForOracle
TestForPostgres
TestForMySql
TestforMongoDB?

the same scenario would hold true if you wanted to trap username/password authentication creds since the database is the only entity to know the username/password credentials


Threading is tricky business as you will need a ThreadMonitor to control and monitor
your thread...something like this may help
https://www.javatips.net/api/lightblue-migrator-master/migrator/src/main/java/com/redhat/lightblue/migrator/ThreadMonitor.java

have you implemented a ThreadMonitor?


Martin
______________________________________________



________________________________
From: Shawn Heisey <[hidden email]>
Sent: Friday, May 4, 2018 10:48 PM
To: [hidden email]
Subject: Re: [DBCP] Possible to get query strings from datasource?

On 5/4/2018 6:15 PM, Shawn Heisey wrote:
> The implementation looks clean to me, but I need something bad to happen
> before I will know if it's actually working.

I added a background thread that periodically logs pool stats.  At
first, I never got it to line up with any actual database usage, and I
never saw any queries in the stats.  By fiddling with the exact timing
for the first logging loop, I was able to get the pool stats to show me
one of the the SQL queries that the program uses when it first starts up.

Before I confirmed that, I did put a sanity check in to log all entries
*removed* from the map, which showed me that queries were indeed being
added.  The second line of this log excerpt (and a whole bunch of other
lines) shows an SQL query being removed:

https://apaste.info/dJK0

(the QC abbreviation means "quietClose" ... the name of the method where
I added the sanity-check logging.)

Looks like I've got it doing what I want, and if any SQL queries are
underway when an alarm is fired, they should be in the email.  So I have
a solution, and there should be enough information in this thread to
help others do the same.

I do wonder if similar SQL query tracking would be in-scope for DBCP
datasource objects.

Thanks,
Shawn


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Phil Steitz
In reply to this post by Shawn Heisey
On 5/4/18 5:15 PM, Shawn Heisey wrote:

> On 5/4/2018 4:49 PM, Martin Gainty wrote:
>> MG>have a chat with your DBA on BasicDataSource validationQuery
>> MG>if this was Oracle it would be something like select 1 from DUAL
> This has me chuckling.   Let me put on my DBA hat (which doesn't fit all
> that well)...
>
> The server is MySQL.  I set the validation query in my program.  It is
> "SELECT 1".  Here's everything I am setting on the BDS:
>
>   dsMain = new BasicDataSource();
>   // TODO: Put the driver name in the properties file.
>   dsMain.setDriverClassName("com.mysql.jdbc.Driver");
>   dsMain.setUrl(mainUrl);
>   dsMain.setUsername(dbUser);
>   dsMain.setPassword(dbPass);
>   dsMain.setValidationQuery(validationQuery);
>   dsMain.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
>   dsMain.setMaxWaitMillis(Const.THIRTY_SECONDS);
>   dsMain.setMaxIdle(numShards);
>   dsMain.setMaxTotal(numShards * 5);
>   dsMain.setNumTestsPerEvictionRun(numShards * 5);
>   dsMain.setTimeBetweenEvictionRunsMillis(Const.THIRTY_SECONDS);
>   dsMain.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
>   dsMain.setTestOnCreate(true);
>   dsMain.setTestOnBorrow(true);
>   dsMain.setTestOnReturn(true);
>   dsMain.setTestWhileIdle(true);
>
> We don't have a DBA position in the company.  I'm the primary sysadmin,
> so the database servers are under my care, and one of the developers is
> designated as the gatekeeper for changes to the databases themselves, to
> keep things sane and not broken.
>
> Here's what I did.  Because all database usage is handled through one
> class, it was easy to find all the places where a query is executed.  I
> added this field to that class:
>
>     /** A map for tracking current SQL statements. */
>     private static final Map<Connection, String> sqlTracker = Collections
>             .synchronizedMap(new HashMap<Connection, String>());
>
> I really wanted to use the diamond operator here, as I have done when
> using synchronizedMap in other programs.  But eclipse complains about
> that unless I set the compiler to produce code for Java 8.  I have ONE
> system building/running this under Java 7, so I have to keep it like this.
>
> Then before every query execution (after the Connection and Statement
> were successfully created), I added this:
>
>   sqlTracker.put(conn, query);
>
> And in the helper method that always gets used for closing JDBC
> connections, I added this:
>
>   sqlTracker.remove(conn);
>
> This is the "getPoolStats()" method:
>
> https://apaste.info/v0UO
>
> The implementation looks clean to me, but I need something bad to happen
> before I will know if it's actually working.
>
> I could improve it by tracking queries separately for the main
> datasource and the master datasource, but just having the SQL queries,
> even without knowing which pool it comes from, is a nice addition.

Above looks like it will work, but it introduces a synchronization
bottleneck in your app.  If you have high concurrency and lots of
queries being submitted, threads are going to line up waiting for
the lock on the map.

From a throughput / app impact perspective, I think you would be
better off using MySQL's show processlist, which you can get from
the driver (see SO link I posted).  The sync cost of maintaining
that is already being paid by the engine.  That will work as long as
you can identify the queries from you app by the user id. 

Phil

>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

mgainty
MG2>"Queued Threads lining up problem" below

________________________________
From: Phil Steitz <[hidden email]>
Sent: Sunday, May 6, 2018 5:10 PM
To: [hidden email]
Subject: Re: [DBCP] Possible to get query strings from datasource?

On 5/4/18 5:15 PM, Shawn Heisey wrote:

> On 5/4/2018 4:49 PM, Martin Gainty wrote:
>> MG>have a chat with your DBA on BasicDataSource validationQuery
>> MG>if this was Oracle it would be something like select 1 from DUAL
> This has me chuckling.   Let me put on my DBA hat (which doesn't fit all
> that well)...
>
> The server is MySQL.  I set the validation query in my program.  It is
> "SELECT 1".  Here's everything I am setting on the BDS:
>
>   dsMain = new BasicDataSource();
>   // TODO: Put the driver name in the properties file.
>   dsMain.setDriverClassName("com.mysql.jdbc.Driver");
>   dsMain.setUrl(mainUrl);
>   dsMain.setUsername(dbUser);
>   dsMain.setPassword(dbPass);
>   dsMain.setValidationQuery(validationQuery);
>   dsMain.setValidationQueryTimeout(Const.FIVE_SECONDS / 1000);
>   dsMain.setMaxWaitMillis(Const.THIRTY_SECONDS);
>   dsMain.setMaxIdle(numShards);
>   dsMain.setMaxTotal(numShards * 5);
>   dsMain.setNumTestsPerEvictionRun(numShards * 5);
>   dsMain.setTimeBetweenEvictionRunsMillis(Const.THIRTY_SECONDS);
>   dsMain.setMinEvictableIdleTimeMillis(Const.ONE_MINUTE * 5);
>   dsMain.setTestOnCreate(true);
>   dsMain.setTestOnBorrow(true);
>   dsMain.setTestOnReturn(true);
>   dsMain.setTestWhileIdle(true);
>
> We don't have a DBA position in the company.  I'm the primary sysadmin,
> so the database servers are under my care, and one of the developers is
> designated as the gatekeeper for changes to the databases themselves, to
> keep things sane and not broken.
>
> Here's what I did.  Because all database usage is handled through one
> class, it was easy to find all the places where a query is executed.  I
> added this field to that class:
>
>     /** A map for tracking current SQL statements. */
>     private static final Map<Connection, String> sqlTracker = Collections
>             .synchronizedMap(new HashMap<Connection, String>());
>
> I really wanted to use the diamond operator here, as I have done when
> using synchronizedMap in other programs.  But eclipse complains about
> that unless I set the compiler to produce code for Java 8.  I have ONE
> system building/running this under Java 7, so I have to keep it like this.
>
> Then before every query execution (after the Connection and Statement
> were successfully created), I added this:
>
>   sqlTracker.put(conn, query);
>
> And in the helper method that always gets used for closing JDBC
> connections, I added this:
>
>   sqlTracker.remove(conn);
>
> This is the "getPoolStats()" method:
>
> https://apaste.info/v0UO
>
> The implementation looks clean to me, but I need something bad to happen
> before I will know if it's actually working.
>
> I could improve it by tracking queries separately for the main
> datasource and the master datasource, but just having the SQL queries,
> even without knowing which pool it comes from, is a nice addition.

Above looks like it will work, but it introduces a synchronization
bottleneck in your app.  If you have high concurrency and lots of
queries being submitted, threads are going to line up waiting for
the lock on the map.

MG>if you decide to create thread/query implementing Threaded Model
https://stackoverflow.com/questions/1202444/how-is-javas-threadlocal-implemented-under-the-hood/15653015
MG>you could isolate map entries to (local) vaues contained in the thread eg
ThreadLocalMap map = getMap(Thread.currentThread);

If ThreadLocalEntry goes stale then entry.get(ThreadLocalObject) would be null
https://runtimeverification.com/monitor/propertydb/java/lang/ThreadLocal.ThreadLocalMap.Entry.html
which would allow gc() to recover WeakReference items
https://plumbr.io/blog/garbage-collection/weak-soft-and-phantom-references-impact-on-gc
Weak, Soft and Phantom references: Impact on GC – Plumbr<https://plumbr.io/blog/garbage-collection/weak-soft-and-phantom-references-impact-on-gc>
plumbr.io
There is an entire class of issues affecting GC caused by the use of non-strong references in the application. While using such references may help to avoid an unwanted OutOfMemoryError in many cases, heavy usage of the non-strong references may significantly impact the way garbage collection can affect the performance of your application.


MG>WDYT?

From a throughput / app impact perspective, I think you would be
better off using MySQL's show processlist, which you can get from
the driver (see SO link I posted).  The sync cost of maintaining
that is already being paid by the engine.  That will work as long as
you can identify the queries from you app by the user id.

Phil

>
> Thanks,
> Shawn
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: [DBCP] Possible to get query strings from datasource?

Shawn Heisey
In reply to this post by Phil Steitz
On 5/6/2018 3:10 PM, Phil Steitz wrote:
> Above looks like it will work, but it introduces a synchronization
> bottleneck in your app.  If you have high concurrency and lots of
> queries being submitted, threads are going to line up waiting for
> the lock on the map.

I believe that all the usages of the synchronized map should be fast
enough to keep that from being a problem.  The map will never hold more
than about a dozen entries unless something goes REALLY wrong with the
program operation.  Most of the time, there should be more like one or
two entries.  The concurrency on the database is not high.

I have added a check in my watchdog thread to log a warning if the map's
size gets bigger than about twice what's expected, and to send an alarm
if that state persists for too long.  If it all goes pear-shaped, I
should get a notification before things degrade horribly.

Thanks for your input!


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]