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: user-unsubscr...@commons.apache.org
For additional commands, e-mail: user-h...@commons.apache.org
