Doctor, what’s happening with my query in the MonetDB kernel?

All database users will recognize this situation. A user has started a query and it takes ages before the result set is available or, even worse, the database server seems unresponsive. In production systems, this is countered by logging all queries whose response time exceed a high-watermark or to tap into the system using a network monitoring system to observe its CPU and I/O behaviour. However, this only helps in post-mortem analysis. Monitoring all running queries alone is also not sufficient. It can only raise the red flag. In a more strict development environment, the query optimiser can be called for a prediction on the execution cost. But, that does not cope with unexpected behaviours either, which might have been caused by a simple typo in a front-end application form.

What is our solution?

For several years MonetDB had a better solution, called the stethoscope. It is a simple application that can be attached at any time to a running database system to listen to what is happening. Unlike most databases, it can give precise information on what is going on in terms of the relational operators being executed, their input/output sizes and the time spent, because the physical relational plan is executed using the materialise-all-intermediates principle. This is much easier to interpret than the aggregated CPU and I/O parameters for the whole plan in flight when using a traditional multi-threaded pipelined execution engine.

In our June 2020 release, we have cleaned up the interface by sending only bare events as JSON structures to a listener, called pystethoscope. This tool can take care of filtering events, rendering for better human consumption, obfuscate the output for privacy protection, or pass it onward to your favourite monitoring tools. The pystethoscope output can readily be integrated into existing system monitoring platforms using their plug-in capabilities.

The best way to experience how it works is to download the program and attach it to a running MonetDB instance. Practice as a doctor and look for symptoms like excessive intermediate sizes, overly expensive operators, unexpected operator arguments, or a never-ending blast of simple relational operators. If you opt for the condensed representation, you will see a stream of events, e.g.

$ pystethoscope -i pc,usec,state,stmt -t statement -e version sf10

This example shows part of the ongoing execution of query TPCH SF10 Q6.

What’s next?

Pystethoscope is a precise tool in the hands of a database user. In the process of renovating our the MonetDB monitoring tools, we have ditched the old tools tachograph, a progress monitor, and the tomograph, a query execution visual. They soon will be replaced by alternative solutions. The emphasis for the near future is to add more filtering capabilities to pystethoscope.