Log filter for slow queries?

Ignition
1

On the gateway status page, if you click on database → Details, you get to see the most recent slow queries that executed.

I wanted to go over the past 5 or so days and see the slow queries from that time period. I assume this same data gets logged to the server logs as well, but I can’t seem to figure out what filter would help parse those out under status → logs
image

How can I view a history of the slow queries on the database?

2

There’s no special associated filtering key. It should be a WARN event from gateway.Database.Updates or gateway.Database.Selects, according to the code.

3

Hmm I’m still having trouble then.

I know I had at least 1 slow query within the last half an hour -

image
image1214×492 44.2 KB

but setting my logs to show me WARN or higher only and I don’t see anything -

image
image1209×281 20.6 KB

Would I be better off trying to GREP the log file directly?

4

My bad, it looks like that table is populated by a different mechanism.

If you’ve already got a GatewayContext object, it’s pretty easy:
context.getDatasourceManager().getDatasource(name).getMetrics().getExpensiveQueries()
That’ll be a list of QueryMetric objects.

5

Is there a way to get the GatewayContext object via jython scripting? Ultimately if I could put this into a function I run once a week, that would be ideal.

Also not sure what name is in this context.getDatasourceManager().getDatasource(name).getMetrics().getExpensiveQueries()

6

The name of the data source connection that the query is being run against.

1 Like
7

The easy way to get a GatewayContext is to use IgnitionGateway - searching on the forum should get you the exact import path to use. IgnitionGateway.get() will work in all 8.X versions and return the singleton mega-class that basically is the Ignition gateway. The only things you should use on it are those listed in GatewayContext.

1 Like
8

Ok great I have it working well enough now so that I can revisit the queries and see some specifics.

For anyone wondering how to do the same thing, this is a function I call from a gateway message handler:

"""
These scripts can ONLY be run on the gateway context.
"""
from com.inductiveautomation.ignition.gateway import IgnitionGateway
import system.util

logger = system.util.getLogger("gateway.query")
LIMIT = 100

def getLongQueries():
	"""
	Note: 
	query.duration is a long, measuring milliseconds
	query.startTime is the unix-time
	"""
	context = IgnitionGateway.get()
	logger.info("Got context")
	name = 'myDatasource'
	queries = context.getDatasourceManager().getDatasource(name).getMetrics().getExpensiveQueries()
	for query in queries:
		if query.duration > LIMIT:
			logger.info("Query: " + str(query.getQuery()))
			logger.info("Length of query: " + str(query.duration))
			logger.info("Start time: " + str(query.startTime))
2 Likes