InnerWeb
Skip to main content

Case: heartbeat scaling and database indexes in a location system

A location system slows down after years of operation for a simple reason: the heartbeat generates most of the traffic, and queries that were invisible on a small database read thousands of rows on a large one. We describe a real system where the IT team measured the cost of a single heartbeat and reduced it with two indexes, without downtime.

about 3,700
rows read per heartbeat before the change
Source: karta
about 2,000
rows per heartbeat after adding the index
Source: karta
about 250 thousand
log lines per day from one warning raised on each heartbeat
Source: karta

The situation: the system slows down although nothing changed

The first symptom was trivial: the location history view took longer and longer to load, and the database server load kept rising with a constant number of employees. No new feature had been deployed that could explain it. The cause was the scale of the data itself: the heartbeat table grew with every heartbeat and after two years held tens of millions of records.

This is a classic trap of telemetry systems. Queries that run in milliseconds on an empty database lose performance linearly with the number of rows if the database lacks the right index. The IT team did not start by guessing, but by measuring.

Why the heartbeat sets the scaling threshold

The heartbeat generates most of the API traffic of the system: from half to over 90% of all HTTP requests, depending on the environment. This means that every inefficiency in the heartbeat path is multiplied by an enormous number of calls. One expensive query on a rarely used path is a minor issue, while the same query on the heartbeat path is the main cost of the system.

For this reason, scaling should start with the heartbeat, not with reporting views. Improving the cost of a single heartbeat by a fraction produces a bigger effect than optimizing several rarely opened screens.

Cost of a single heartbeat in rows read

Cost of a single heartbeat in rows readBefore and after chart: from about 3,700 to about 2,000 rows read per heartbeat.01000200030004000rowsBefore the index: Rows per heartbeat: 3700 rows3700 rowsBefore the indexAfter the index: Rows per heartbeat: 2000 rows2000 rowsAfter the index
Source: Card: measurement of rows read divided by the number of requests

How the cost of a single heartbeat was measured

The team used a simple measure: the difference in the number of database rows read before and after a series of requests, divided by the number of requests. The measure does not depend on momentary CPU load or on caching, so it is well suited to comparing the state before and after a change.

The result of the first measurement: about 3,700 rows read per heartbeat. This is an unacceptable number for a query whose job is to find one transmitter and store a reading.

The first problem found: a full scan when looking up a transmitter

Looking up a transmitter performed a full table scan, because the existing unique index started with a different column. The database could not use it, since an index works from the left side of its key. A supporting index on the pair of transmitter identifiers was added, which turned the scan into a lookup of one row. After this change, the cost fell from about 3,700 to about 2,000 rows read per heartbeat.

The second problem: an employee's location history

The history view read the entire pulse history of an employee and only then filtered by date. A composite index on employee and time turned this into a range search: the database passes only through rows from the requested interval.

Creating an index on a live table

The administrator's greatest concern was locking: an index on a table with tens of millions of records, created during operation, could stop the acceptance of heartbeats. In the described case, the composite index was created on a table of several dozen million records without a lock and without 5xx errors, within a few minutes.

The conditions that made this possible are discussed on a separate page about changing the database schema on a live system. The most important of them were choosing a window with lower traffic, an index creation mode that does not require locking writes, and constant monitoring of the error count during the operation.

A second side effect: noise in the log

Something else came out of the measurements. A diagnostic log at warning level, written on every heartbeat, produced about 250 thousand lines per day. A warning that appears hundreds of thousands of times stops being a warning: it hides real problems and takes up disk space. The level was lowered to debug.

The lesson is broader than one line: the frequency of an event should decide the logging level. Something that happens on every request is not a warning.

Conclusions for the IT team

First lesson: measure the cost of queries in rows, not in a subjective sense of speed. Second: start with the path that generates the most traffic. Third: an existing index does not guarantee that a query will use it, because column order matters. Fourth: an index on a live, large table is feasible if you plan the window, the mode and error monitoring.

The example calculation shows the scale of the effect: assuming 1,000,000 heartbeats per day, the drop from 3,700 to 2,000 rows per request is a difference of 1.7 billion rows read per day (3.7 billion versus 2.0 billion), that is about 46% fewer.

Example calculation

Scale of the daily effect under the assumed traffic

Assumptions
ParameterValue
Heartbeats per day1,000,000 requests
Rows per request before3,700 rows
Rows per request after2,000 rows

Result: 3.7 billion versus 2.0 billion rows read per day, a difference of 1.7 billion (about 46%).

Note: Example calculation: the number of heartbeats per day is an illustrative assumption, not data from a specific plant.

Video

Frequently asked questions

How do you measure the cost of a query in a telemetry system?

Read the database row counter before and after a series of requests, then divide the difference by the number of requests. The measure stays comparable before and after a change, regardless of momentary load.

Why was the existing index not used by the query?

The unique index started with a different column, and the database uses an index from the left side of its key. A query without that column ends with a full scan.

Does an index on a large table have to stop the system?

It does not have to. In the described case, the index on a table of several dozen million records was created within a few minutes, without a lock and without 5xx errors.

Why was the log level lowered to debug?

A warning written on every heartbeat produced about 250 thousand lines per day and hid real problems.

Is your telemetry database growing?

Let's discuss measuring the cost of the heartbeat and an index plan for your scale. We will show the measurement method in a demonstration.

Let's talk