MG2>"Queued Threads lining up problem" below
________________________________
From: Phil Steitz <[email protected]>
Sent: Sunday, May 6, 2018 5:10 PM
To: [email protected]
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: [email protected]
> For additional commands, e-mail: [email protected]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
