Monitoring Datasources in wildfly

Using the command line tool

cd quickstart/wildfly-10.1.0.Final/

./bin/jboss-cli.sh

-->You are disconnected at the moment. Type 'connect' to connect to the server or 'help' for the list of supported commands.

connect

To see the current state of the datasource you need to enable statistics (you may be able to reenter from the history)

/subsystem=datasources/data-source=CalendarDS/statistics=pool:write-attribute(name=statistics-enabled,value=true

To see the current state:

 /subsystem=datasources/data-source=CalendarDS/statistics=pool/:read-resource(recursive=false,proxies=false,include-runtime=true,include-defaults=true

The most important fields are AvailableCount and MaxUsedCount. We hope to see a large proportion of the pool available. Some of the otehr statistics may indicate we are runnign out of pool entries.

Checking a MySQL database

Run the mysql client on your db system and enter

show processlist;

The Time column should contain small values only. There may be some hung connections with large time values. The AutoKill process is supposed to clean these up. If you are seeing  lot of these with high values try setting the autoKill time in bwengine/system.xml to a lower value (or higher if it's set to 0). A 3 minute interval will mean a connection may hang for 3 minutes up to just under 6 minutes. Typically bedework does not run long queries. A minute may be sufficient.

Killing the query on the db side may help resolve issues. Try this

SELECT GROUP_CONCAT(CONCAT('KILL QUERY ',id,';') SEPARATOR ' ') KillQuery FROM information_schema.processlist WHERE user<>'system user' AND time >= 60\G

to get a bunch of kill commands. See this question and answers for the above approach and how to automate it.

Clearly this approach only attacks the symptoms but sometimes that's all we want.