Monitoring Dashboard

OmniDB has a cool feature called Monitoring Dashboard. We know a picture is worth a thousand words, so please take a look:

As you can see, this is a new kind of inner tab showing some charts and grids. This Monitoring inner tab is automatically opened once you expand the tree root node (the PostgreSQL node). You can keep it open or close it at any time. To open it again, right-click the root node and click on Dashboard.

The dashboard is composed of handy information rectangles called Monitoring Units. Here is an example of Monitoring Unit and its interface elements:

1: Title of the Monitoring Unit;
2: Refresh the Monitoring Unit. Depending on the type, clicking on this button will refresh the entire drawing area or just make the chart acquire a new set of values;
3: Pause the Monitoring Unit;
4: Interval in seconds for automatic refreshing;
5: Remove the Monitoring Unit of the Monitoring Dashboard;
6: Drawing area, that will be different depending on the type of the Monitoring Unit.

Types of Monitoring Units

There are 3 types of Monitoring Units:

Grid: The most simple kind, just executes a query from time to time and shows the results in a data grid.

Chart: Every time it refreshes, it renders a new complete chart. The old set of values is lost. This is most useful for pie charts, but other kind of charts can be used too.

Timeseries: Perhaps this is the most useful kind of Monitoring Unit. It is a chart that appends a new set of values every time it refreshes. Line or bar charts fit best for this type. The last 100 sets of values are kept by the component client-side to be viewed by the user.

Showing and hiding units in the dashboard

If you click in the button Refresh All, then all Monitoring Units will be refreshed at once. You can also remove undesired Monitoring Units by clicking in the Remove button. Let us go ahead and remove all units from the dashboard, making it empty:

The 17 units that come built into OmniDB (PostgreSQL and MySQL server metrics — transaction rate, backends, WAL production, database/table size, and so on) run as native code and need nothing extra installed in the database.

Now that our dashboard is empty, let us add some units. Click on the Manage Units button.

Click on the green action to add any of the built-in units — e.g. Backends and Database Size. Wait for some seconds and you will have a dashboard like this:

In a similar way, you can add and remove any unit you want to customize the dashboard the way you want.

Writing custom Monitoring Units

OmniDB lets you write your own units, or customize existing ones, by writing a single SQL query — the same connection and permissions your SQL editor already has, just running on a timer instead of on demand. There’s no separate scripting language or sandbox to learn.

Note: since the query re-runs unattended on a refresh interval, only SELECT (or WITH ... SELECT) queries are accepted — a destructive statement pasted in by mistake is rejected outright rather than silently firing on a timer.

To create a new Monitoring Unit, click on the Manage Units button in the dashboard, then click on the New Unit button. It will open a new kind of inner tab with a Name, a Type dropdown, a Refresh Interval, and a single SQL Query editor (plus a Chart Type dropdown, shown only for Chart units).

The query’s shape depends on the unit’s Type:

Grid: no particular shape — whatever columns and rows the query returns become the grid directly. For example:
```
SELECT * FROM pg_stat_activity
```
Chart: one row per category — the first column is the label, the remaining column(s) are one series each (the column name becomes the series name). Pick the Chart Type (bar, pie, doughnut, or line) from the dropdown next to the Type selector. For example, a pie chart of database sizes:
```
SELECT datname,
       round(pg_catalog.pg_database_size(datname) / 1048576.0, 2) AS size_mb
FROM pg_catalog.pg_database
WHERE datname NOT IN ('template0', 'template1')
```
Timeseries: exactly one row — each numeric column becomes one continuously-appended line series, refreshed on the configured interval. For example, tracking database size over time:
```
SELECT round(sum(pg_catalog.pg_database_size(datname)) / 1048576.0, 2) AS total_size_mb
FROM pg_catalog.pg_database
WHERE NOT datistemplate
```

Write (or paste) the query into the SQL Query editor, then click the Test button to see it rendered in the preview drawing area — you can test as many times as you like. Once you’re happy with it, give the unit a name, set a refresh interval, and hit Save. Close the edit tab and the new unit appears in the unit list, ready to add to the dashboard (green action) just like a built-in one.