InnerWeb
Skip to main content

Guide: measuring the cost of telemetry queries

Heartbeat generates between half and more than 90% of API traffic, so its cost sets the scaling threshold of the system. This guide shows how a database administrator measures the cost of one heartbeat in rows, finds the query that does a full table scan and checks the effect of an index: in the case described, from 3,700 to 2,000 rows.

-46%
rows read per heartbeat after the index
Source: calculation from the card: 3,700 to 2,000
250 thousand
log lines per day before the level was lowered
Source: card

Why the heartbeat sets the scaling threshold

The heartbeat generates most of the API traffic of the system: between half and more than 90% of all HTTP requests, depending on the environment. For this reason, the cost of one heartbeat sets the scaling threshold of the whole system. If a single query reads thousands of rows, traffic grows linearly with the number of devices, and the database becomes the bottleneck long before the application servers run out of capacity.

The database administrator therefore has one overriding task: to know the cost of one heartbeat expressed in rows read, and to know which query creates that cost. This page describes the measurement method and three typical causes of excessive cost.

Measurement method: rows per request

The simplest method requires two readings of the counter of rows read by the database, taken at an interval, for example five minutes apart, plus the number of heartbeat requests in the same window. The difference between the counters is divided by the number of requests. The result is the average number of rows read per heartbeat.

In the measured case, the cost fell from about 3,700 to about 2,000 rows per request after an index was added on the pair of transmitter identifiers. This is a reduction of about 46%, calculated as the difference of 1,700 divided by 3,700. The absolute value depends on the environment, but the method is the same: two counter readings and a division.

The measurement should be repeated after each schema change and each time the number of devices grows. The trend of cost per request says more than a single value, because it shows the moment when a new feature started to load the heartbeat path.

Rows read per heartbeat

Rows read per heartbeatBefore and after chart: heartbeat cost fell from 3,700 to 2,000 rows.01000200030004000rowsBefore the index: 3700 rows3700 rowsBefore the indexAfter the index: 2000 rows2000 rowsAfter the index
Source: Cell card: about 3,700 and about 2,000 rows per request

Finding the query that limits scaling

After the measurement, the culprit must be identified. Execution plan logging should be enabled for the queries run on the heartbeat path, and full table scans should be looked for. In the described case, the search for a transmitter performed a full scan, because the existing unique index started with a different column from the one used in the search. A secondary index changed the scan into a lookup of a single row.

The second query concerned the location history of an employee. It read the entire history of their heartbeats before applying the date filter. A composite index on the employee and the time turned this into a range search, which reads only the dates needed. The general rule: the order of columns in an index must match the order of conditions in the query.

Writing the heartbeat to the system is protected by error handling, so a performance problem should not show up as application errors, but as growing response times. That is why the cost measured in rows is a more sensitive indicator than the number of errors.

Creating an index on a large table

The heartbeat table holds several dozen million records. The composite index was created without a lock and without any 5xx errors within a few minutes. This is possible with the index creation mode that does not block writes, but it requires spare disk space and a window with lower traffic. Before the change, free space must be checked and a rollback plan prepared.

A live schema change is best carried out according to a separate procedure, described in the implementation notes on live database schema changes. After completion, the cost per request should be measured again and compared with the value before the change.

The cost of the diagnostic log

The second, often overlooked cost is the diagnostic log. A warning-level entry written on every heartbeat produced about 250 thousand lines a day, which is on average about 2.9 lines per second. After the level was lowered to debug, the writing disappeared from normal operation. Excess logs burden the disk, manual analysis and storage costs.

It is worth reviewing which messages reach the log on the heartbeat path, and setting a level that matches their importance. A warning should mean a situation requiring a response, not the routine acceptance of a position.

Checklist for the administrator

The order of actions can be summarised in four steps. First, measure the cost of one heartbeat in rows. Next, search the execution plans for full scans on the heartbeat path. Then add an index that matches the order of conditions and measure again. Finally, review the log levels and lower those that flood the disk.

There remains the question of retention. Heartbeat telemetry has no default retention, and the cleanup mechanism is switched off until the client decides. A growing table worsens query cost, so the decision on how long data is kept should be taken early.

Checklist

Video

Frequently asked questions

How do I measure the cost of one heartbeat?

Read the database row counter twice at an interval, count the requests in that window and divide the difference by the number of requests.

Why was the unique index not enough to search for a transmitter?

It started with a different column from the one used in the search, so the database performed a full scan.

Does creating an index on a large table block the system?

In the described case, no: the index on several dozen million records was created without a lock and without 5xx errors.

Why was the log level lowered?

A warning written on every heartbeat produced about 250 thousand lines a day, while routine acceptance of a position does not need a warning.

Let us measure the heartbeat cost at your site

Together with your database administrator we will carry out the measurement and point to the queries to improve.

Let's talk