Patterdale

Probes are defined in patterdale.yml and are specific to a database. If you want to run the same probe against multiple databases, simply duplication the definition (e.g SELECT 1 FROM DUAL).

An example set of probe definitions in patterdale.yml:

databases:
    ...
    - query: SELECT 1 FROM DUAL
      type: exists
      metricName: database_up
      metricLabels: database="myDB2",query="SELECT 1 FROM DUAL"
    - query: select elapsed_time, sql_text from   v$sql order by ELAPSED_TIME desc FETCH NEXT 2 ROWS ONLY;
      type: list
      metricName: slowest_queries
      metricLabels: database="myDB2",slowQuery="%s"

The first query, SELECT 1 FROM DUAL, has the type exists maps to ExistsOracleSQLProbe.java. The second query, SELECT * FROM slowest_queries TOP 5, maps to ListOracleSQLProbe.java.

Exists

The “exists” type expects a SQL query to be run which returns one row and one column.

If the value in that column is a ‘1’. The probe is treated as a success. SELECT 1 FROM DUAL is a perfect example of query for this type of probe and is recommended to be run on every database which is configured.

Any other integer value is treated as a failure.

Using the above configuration, the probe will result in a line on the applications ‘/metrics’ web page in the format:

database_up{database="myDB2",query="SELECT 1 FROM DUAL"} 1.0

if the query was successful

database_up{database="myDB2",query="SELECT 1 FROM DUAL"} 0.0

if the query was unsuccessful

If the query was not executed succesfully or timed out against the database, a descriptive log is produced and no metric is presented.

This conforms to the Prometheus standard.

List

The “list” type expects a SQL query to be run which returns a number of rows. The first column is expected to be a number, representing the metric value.

The rest of the columns will be treated as Strings and filtered into the metricLabels. For this the metricLabels is expected to have a key/value pair per extra column after the first, in the same order as the SQL: columnName="%s" where %s will be filtered in using java.lang.String#format.

Using the above configuration, the probe will result in a number of lines on the applications ‘/metrics’ web page in the format:

slowest_queries{database="myDB2",slowQuery="SELECT * FROM HUGE_TABLE"} 50.5
slowest_queries{database="myDB2",slowQuery="SELECT name FROM TINY_TABLE"} 0.4

This conforms to the Prometheus standard.

Examples

Some curated examples can be found here.

This site is open source. Improve this page.