0

I'm working on a systems monitoring product that currently focuses on performance at the system level. We're expanding out to monitoring database systems. Right now we can fetch simple performance information from a selection of DBMS, like connection count, disk IO rates, lock wait times, etc.

However, we'd really like a way to measure the execution time of every query going into a DBMS, without requiring the client to implement monitoring in their application code.

Some potential solutions might be:

  • Some sort of proxy that sits between client and server. SSL might be an issue here, plus it requires us to reverse engineer and implement the network protocol for each DBMS.
  • Plugin for each DBMS system that automatically records performance information when a query comes in.

Other problems include "anonymising" the SQL, i.e. taking something like SELECT * FROM products WHERE price > 20 AND name LIKE "%disk%" and producing SELECT * FROM products WHERE price > ? AND name LIKE "%?%", though this shouldn't be too difficult with some clever parsing and regex.

We're mainly focusing on:

  • MySQL
  • MSSQL
  • Oracle
  • Redis
  • mongodb
  • memcached

Are there any plugin-style mechanisms we can utilise for any of these? Or is there a simpler solution?

1 Answer 1

1

There's no generic solution applicable to all your databases. And trying to measure query response time on Oracle is very difficult. I've not worked with performance analysis on memcache/nosql dbs.

For mysql there's mysqlproxy - which can easily be scripted. However you'll probably get everything you need from the slow query log (with a threshold of zero). I use a modified version of this script to "anonymize" my queries.

There are (very expensive) tools like Compuware's client vantage which can sniff network traffic and deduce performance metrics at various tiers (including database). The only comparable free software tool I'm aware of is PastMon which (AFAIK) doesn't provide the deep inspection needed for measuring DB response times on a persistent TCP connection.

While it would be possible to capture all the metrics by funnelling all access via webservices, this introduces latency in the transaction. This is true for any proxy type operation - but will be particularly painful for non-persistent connections over SSL.

Have you considered how your going to collect / maintain / analyse all this data? You might want to have a look at graphite.

What types of clients are you using? Before you start looking at the databases, you should be measuring response ties at the client - you only need to start worrying about the database performance if you've got a performance problem there. And iof the client is a web browser then that's a much simpler task. Just set your webserver to log response times and start mashing up the data. You can even add simlpe bolt-ons for measuring page response - which is a far better indicator of perceived performance - e.g. Yahoo's Boomerang

1
  • I wasn't expecting there to be a generic solution. I hadn't seen mysqlproxy before, and that script looks great. With regards to the collection / analysis, that's what our product already does with system data. We have a few servers that take the data in from agents and produce all the analytics and graphs. We're trying to expand to query analysis for our push towards deeper monitoring.
    – Polynomial
    Nov 24, 2011 at 10:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .