Application Database Monitoring: The Middle Way

Sometimes you just need to know what a database client is doing in the database. You might be:

• Debugging an application and trying to figure out if its database interactions are a problem.
• Analyzing the performance of a serverless function to determine if some queries take too long.
• Testing an application and making sure its database interactions are as expected.
• Auditing a system to make sure it does not do anything funny in the database.
• Reverse-engineering an application.
• You get the picture, the list is endless.

By database client, we mean anything that talks directly to a database: it might be a middle-tier running in an app server, a report writer, a spreadsheet in Excel, a web app written in PHP, a lambda function in the cloud, and so on.

Whatever the reason, getting visibility into a client's database interactions is hugely helpful to understanding that client's behavior.

Generally speaking, there are two common approaches: server-based and client-based.

There is also a third option, which is less commonly used: monitoring database connections. This is the middle way: it focuses neither on the database servers, nor on the database clients, but rather on the connection between them.

Let's examine the advantages and disadvantages of the more common solutions, and see what this third way brings to the table.

Server-Side Monitoring

Most enterprise databases have built-in monitoring capabilities.  If you have admin-level access to the database, this type of monitoring might be a good solution, but it may not always be easy to use if there is more than one client accessing the database.

Server-side monitoring is preferred by people who view the world mostly from the perspective of the database, since this type of monitoring gives you full access to everything happening in the database server: CPU and memory, network and disk usage, deadlocks, etc.

The main disadvantages of this type of solution are:

• It requires privileged access to the database.
• For cloud databases, you are often stuck with whatever is provided by the cloud vendor.
• You have visibility to all requests from all clients, which can be overwhelming and might be a security problem.

Client-Side Monitoring

The solutions focusing on the database clients require you to install an agent in the client(s). This can take the form of a library or module, or perhaps a replacement driver library that intercepts all database calls.

Client monitoring is of course preferred by people who care more about the database client's perspective, such as application developers and testers. It allows you to focus on one specific client and see exactly what that client is doing, what requests it sends, what responses it gets, and so on. Most solutions also do more than just monitor database interactions.

There are, however, downsides to this type of monitoring:

• It requires you to make some changes to the client(s), which can be difficult.
• It may not even be an option if you don't control the client (e.g. third-party app, no longer maintained, etc.).
• It's not even an option if the client's language or platform is not supported by the monitoring solution.
• It can be inconvenient if you need to monitor multiple clients since you need to install the agent in each client.

Proxy-Based Monitoring: The Middle Way

There is a third option: monitoring the connection between clients and servers using a database proxy.

Instead of connecting directly to the database, the clients connect to the proxy, which forwards all requests and responses between the clients and the servers, and reports this activity to the monitoring service.

The proxy works at the wire protocol level: it behaves exactly like the database server, so there is no way for the client to know that it is not in fact talking directly to the database server.

The main advantage of this approach is that it requires no changes to either the database clients or the database servers, and can therefore work in any environment, for any client in any language. The only requirement is that the client should connect to the proxy instead of the database, which can usually be accomplished by changing a configuration parameter, a data source definition, or sometimes a network setting.

This is especially useful when dealing with third-party applications that you do not control, or with older applications that are no longer maintained. In these situations, making any change to the applications is often a daunting prospect. In contrast, directing the client to connect to the proxy instead of the database is normally trivial.

Proxy-based monitoring can also be the only practical solution for clients written in a language that is not supported by the monitoring system.

For instance, New Relic has application performance monitoring libraries for C, Java, JavaScript, Go, Ruby, .NET, PHP, and Python. If your client is written in an incompatible language, you will not be able to use New Relic's agent.

Proxy-based monitoring can be put in place by application developers and testers, without needing the collaboration of the database owners. One proxy can be used for many clients, which is helpful when you need to monitor a suite of applications.

Advantages of The Proxy Approach

• Requires no changes to the database clients or to the database servers.
• Works with on-site and cloud databases.
• Supports all platforms and languages because it works at the wire protocol level.
• Can be applied to multiple clients at the same time.

Disadvantages of The Proxy Approach

• Requires proxy server.
• A small increase in response time due to traffic going through the proxy.

Which Approach Is Right for You?

If you care mostly about the database, for instance as a DBA, or as a systems manager, then server-based monitoring is most likely the right solution for you — no surprise there.

If you are an application developer or tester, or you just need to know what a database client is doing in the database, then you have a choice between client-based monitoring and proxy-based monitoring.

If it is reasonable for you to install an agent in your client, that's a fine approach. You will get good insights into your client's behavior, and there are many products to choose from (just Google "APM database").

If installing an agent in your client is an unattractive prospect, or if your client uses an unsupported platform or language, then a proxy-based approach is probably your best choice, perhaps even your only choice.