Application database monitoring tutorial

This tutorial will show you, step by step, how to monitor your application's database interactions using Gallium Data.

We'll be using SQL Server as the database. There is nothing special about SQL Server: we could also use MySQL, PostgreSQL or MongoDB, but we had to pick one. Adapting this tutorial to a different database would not be difficult.

For recording and displaying monitoring data, we'll be using New Relic. There is nothing special about New Relic: it's a perfectly fine monitoring platform, but other similar platforms, such as DataDog, DynaTrace, Graphite, AWS CloudWatch and many others, would work in a similar manner. We chose New Relic mostly because it offers a free tier -- you can create an account and use it without having to pay anything.

The big picture

In this tutorial, we're going to run a SQL Server instance, and monitor the database connections, queries, and result sets in real time, in a New Relic dashboard:

This is a fairly basic set of metrics. As we'll see later, we can go beyond this and monitor some metrics that would be difficult, or even impossible with an off-the-shelf solution. But let's not get ahead of ourselves...

Setup

⇨ Make sure Docker is up and running

⇨ Run the following from a command line:

docker network create gallium-monitoring

docker run -d --rm --name gallium-mssql --network gallium-monitoring galliumdata/gallium-data-demo-mssql:4

docker run -d --rm --name gallium-mon --network gallium-monitoring -p 8093:8080 -p 1439:1433 -e repository_location=/galliumdata/repo_mssql galliumdata/gallium-data-engine:1.3.0-1217

docker run -it --rm --name gallium-mon-cli --network gallium-monitoring galliumdata/gallium-mssql-cli:1 -S gallium-mon -U sa -P Password1 -d GalliumDemoDB

Hint: try the same command again if it fails the first time -- it usually means that SQL Server was not yet up.

If everything went well, you should see a command prompt for SQL Server, e.g.:

GalliumDemoDB>

Everything is now in place, let's get started.

Connecting to New Relic

If you don't already have an account with New Relic, now would be the perfect time to create one (it's free).

Once you have an account,

⇨ in New Relic, create an Insights Insert API key (top-right menu -> API Keys -> Insights Insert key)

The API key should look something like NRII-xxXxxXXXxxXXxxXxxxXXXxXXxxxXxXxx

To talk to New Relic, we'll also need to install their Java library:

⇨ In Gallium Data (http://127.0.0.1:8093), go to Libraries and (using the Find tab) look up organization com.newrelic.telemetry with artifact telemetry-http-java11 and add the latest version to the repository.

Don't forget to publish.

Monitoring connections

The first filter in Gallium Data will be responsible for setting up a connection to New Relic, and for reporting that a new database connection has been opened.

⇨ in Gallium Data, create a JavaScript connection filter and call it "Monitor connections", with the following code:

// Set up the connection to New Relic if necessary

if ( ! context.projectContext.reportEvent) {

const Attributes = Java.type("com.newrelic.telemetry.Attributes");

const Java11HttpPoster = Java.type("com.newrelic.telemetry.Java11HttpPoster");

const TelemetryClient = Java.type("com.newrelic.telemetry.TelemetryClient");

const Event = Java.type("com.newrelic.telemetry.events.Event");

const EventBatch = Java.type("com.newrelic.telemetry.events.EventBatch");

const Collections = Java.type("java.util.Collections");

const Duration = Java.type("java.time.Duration");

let commonAttributes = new Attributes()

.put("DatabaseType", "MSSQL")

.put("Environment", "dev");

let telemetryClient = TelemetryClient.create(

() => new Java11HttpPoster(Duration.ofSeconds(10)),

"NRII-xxXxxXXXxxXXxxXxxxXXXxXXxxxXxXxx");

telemetryClient.withNotificationHandler({

noticeInfo: function(s, e, telemetryBatch) {

log.debug("Notice from telemetry: " + s);

},

noticeError: function(s, throwable, telemetryBatch) {

log.debug("Error from telemetry: " + s);

}

});

context.projectContext.reportEvent = function(evtType, ...attrs) {

let nr = context.projectContext.newRelic;

let attributes = new Attributes();

for (let i = 0; i < attrs.length; i += 2) {

attributes.put(attrs[i], attrs[i + 1]);

}

let evt = new Event(evtType, attributes);

telemetryClient.sendBatch(new EventBatch(Collections.singleton(evt), commonAttributes));

log.debug("Event sent to New Relic: " + evtType);

};

}

// Report a new connection

context.projectContext.reportEvent("ConnectionOpened",

"Client", context.socket.getRemoteSocketAddress().getAddress().toString());

⇨ Replace NRII-xxXxxXXXxxXXxxXxxxXXXxXXxxxXxXxx with your own New Relic API key

This code does two things:

1. it checks whether the necessary environment has already been set, and if not, it sets it up. This means looking up a few Java classes, creating a telemetry client using New Relic's API, then creating a function that we will use to report events

2. it reports a new connection event to New Relic

⇨ Publish to Gallium Data

⇨ In mssql-cli, run the command: quit

⇨ Restart mssql-cli (hint: up arrow usually works)

This will open a new connection to the database, and therefore send a data point to New Relic.

Check the log (in Gallium Data's Logs page) and verify that you see the message Event sent to New Relic: ConnectionOpened (you may see more than one). If you don't see it, something is not working as expected.

Creating a dashboard in New Relic

Now that we have at least one event in New Relic, let's display it in a dashboard.

⇨ Connect to New Relic and create a new dashboard (Dashboards -> Create a dashboard -> Create a new dashboard) -- call it "Database monitoring"

⇨ In that dashboard, click Add a new chart, then Add a chart

⇨ In the input labeled View a chart with, click and select ConnectionOpened, then *, then count

If you don't see ConnectionOpened, you may need to wait a few moments -- sometimes New Relic takes a little while to sync everything up.

⇨ Click Save, and you have your new dashboard

You should see a graph appear, with at least one data point. It should look something like:

Monitoring queries

Now let's get some visibility into how many queries are being run.

⇨ In Gallium Data, create a new request filter of type Query filter - MSSQL and give it the code:

This will send one data point to New Relic. You can check the log in Gallium Data to verify.

⇨ In New Relic, click the + button (top right) to add a new widget to the dashboard, and select Add a chart

⇨ In the input labeled View a chart with, click and select Query, then *, then count

⇨ Save your new widget

You should now have two widgets on your dashboard:

A few New Relic pointers

• If the dashboard does not refresh as expected, you can change the time period (top right menu) to force a refresh.

• You can reposition widgets by clicking the pencil icon (top right) and moving and resizing widgets. Click Done editing when you're done.

• You can edit the title of each widget by clicking the ... button in the widget and entering a new name

• You can combine multiple graphs into one widget by editing the widget and selecting Add another query

• You can give each graph a better label by changing the query in the widget to add an alias, e.g.:

SELECT count(*) as 'Open' FROM ConnectionOpened TIMESERIES SINCE 1800 seconds ago EXTRAPOLATE

Viewing queries

Now let's have a sample of the latest SQL queries displayed on the dashboard. The filter we just created in Gallium Data is already sending the text of SQL queries, so we don't need to create another filter.

⇨ In New Relic, click the + button (top right) to add a new widget to the dashboard, and select Add a chart

⇨ In the input labeled View a chart with, click and select Query, then SQL, then uniques

⇨ Save your new widget

⇨ In mssql-cli, execute:

Over time, you may see some queries that you did not execute yourself. That's because mssql-cli often executes queries in the background for its own use. You may not wish to display these queries: in that case, you can put a condition on the request filter in Gallium Data.

For instance, if you set the Query patterns parameter of the query filter in Gallium Data to:

regex:.*gallium_demo.*

and publish, only the queries that contain gallium_demo will be displayed in the two widgets.

Monitoring result sets

Let's add one last metric to our dashboard, showing how many rows of data are being returned by the database.

⇨ In Gallium Data, create a new response filter of type JavaScript response filter - MSSQL and give it the code:

At the end of each result set, SQL Server sends a Done packet with the doneCount attribute set to true, and the number of rows in the rowCount attribute.

⇨ Publish to Gallium Data

⇨ In mssql-cli, run the command:

This will send one data point to New Relic. You can check the log in Gallium Data to verify.

⇨ In New Relic, click the + button (top right) to add a new widget to the dashboard, and select Add a chart

⇨ In the input labeled View a chart with, click and select ResultSetRow, then rowCount, then sum

⇨ Save your new widget

The new widget will now show how many rows have been returned by SQL Server:

You can run a few more queries in mssql-cli and see them reflected on the dashboard.

What else could we do?

We've only seen the most basic type of monitoring. One of the great powers of Gallium Data is that it can zero in on very specific aspects of the requests and responses.

We could, for instance, make the monitoring of queries dependent on the query, e.g. monitor only queries that contain the words "customers" and "accounts", or that originate from a specific user, or from a given range of IP addresses.

We could also monitor the values of parameters for prepared statements and stored procedures -- something that is difficult or impossible in most monitoring products.

We could monitor result sets at a very granular level and (for instance) have an alert whenever someone retrieves some specific data items (.e. "alert me if user jsmith looks up a customer with a balance greater than $10,000").

We could keep track of how long queries take to return the first row, or the entire result set.

Gallium Data gives you great flexibility in how you monitor database connections, all without having to touch either your database servers or your applications. Think of the possibilities.

Cleanup

Once you're done with this tutorial, and you want to remove everything that was installed,

⇨ Exit mssql-cli by running: quit

⇨ Execute the following commands: