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