InnerWeb
Skip to main content

Heartbeat traffic and database indexes: implementation notes for the database administrator

The heartbeat endpoint generates most of the API traffic: 96% of requests on a test instance under the demo generator, and about half in production. Scaling the device fleet therefore means scaling this one query. We describe how an index on the major and minor pair reduced the load and how to plan the maintenance window.

96%
share of heartbeats in requests on a test instance under the demo generator
Source: karta
about 50%
share of heartbeats in requests in production
Source: karta
3,665 to 1,928 to 2,205
rows read per heartbeat after adding the index
Source: karta

Scale of traffic: heartbeat dominates the API

A heartbeat is a cyclical signal from a device, which the server receives, matches to a transmitter and records. Because every device reports in regularly, this endpoint generates most of the API traffic. On a test instance under the demo generator it accounted for 96% of requests, and in production for about half.

From the database administrator's point of view, this means the performance of the whole API depends largely on one query. Optimising this query has a bigger effect than tuning ten queries that are called less often.

Index on the major and minor pair

For each heartbeat the system looks up the transmitter by its major and minor pair. Without an index, this lookup scans the whole table. Adding an index on this pair reduced the number of rows read per heartbeat from 3,665 to between 1,928 and 2,205, because the lookup of a transmitter stopped browsing the entire table.

This is a reduction of about 40 to 47% (from 3,665 to a range of 1,928 to 2,205 rows) with the same results. At traffic measured in thousands of reports per hour, the effect accumulates into a noticeable difference in load.

How to verify the effect on your own system

Before adding the index, measure the average number of rows read per heartbeat and the response time of the endpoint. After adding it, measure the same on comparable traffic. A comparison on traffic of a different intensity makes no sense, so use the same generator or the same time window.

What the index costs

Every index takes up disk space and slows down writes. On a table that keeps growing, this cost is visible. The index on the major and minor pair speeds up finding a transmitter, but it does not solve the problem of a growing telemetry table. We describe that separate issue in the recommendations on retention.

Rows read per heartbeat: before and after the index

Rows read per heartbeat: before and after the indexBefore and after chart: 3,665 rows before the index, between 1,928 and 2,205 rows after the index.01000200030004000rowsBefore the index: 3665 rows3665 rowsBefore the indexAfter the index (lower value): 1928 rows1928 rowsAfter the index (lowervalue)After the index (upper value): 2205 rows2205 rowsAfter the index (uppervalue)
Source: Internal record: measurement of rows read per heartbeat before and after adding the index on the major and minor pair

Maintenance window

Adding an index to a large table loads the database and, depending on the engine, may block writes. Schedule it in the maintenance window with the lowest traffic, ideally after a shift change, when the device fleet is put on the chargers and reports less often.

Before the window, take a backup and set the criterion for withdrawing the change. After the window, compare the number of rows read per heartbeat with the value from before the change.

  • Measure the current number of rows read per heartbeat and the response time.
  • Take a backup of the database and verify that it can be restored.
  • Add the index on the major and minor pair in the maintenance window.
  • Measure the same indicators on comparable traffic.
  • Record the results in the installation documentation.

Scaling the device fleet

If you plan to increase the number of devices several times over, heartbeat traffic will grow proportionally, and the table will start to grow faster. On one installation the heartbeat table reached 73.9 million rows and 6.2 GB after two years. With a fleet an order of magnitude larger, the rate of growth will be clearly higher.

That is why, before scaling, you should plan three things in parallel: indexes for finding transmitters, a retention policy for the telemetry table, and monitoring of the number of rows read per heartbeat. The first lowers the cost of a single report, the second limits the size, and the third gives early warning.

Remember too that heartbeat is a query with a large share of the traffic, so it is worth repeating the measurement after every change to the database schema or to the number of devices.

Share of heartbeats in API requests on a test instance under the demo generator

Share of heartbeats in API requests on a test instance under the demo generatorPie chart: heartbeat is 96% of API requests, the other 4%.Heartbeat: 96 % of requests (96%)Other: 4 % of requests (4%)100% of requestsHeartbeat96 % of requests (96%)Other4 % of requests (4%)
Source: Internal record: test instance under the demo generator

Video

Frequently asked questions

Why does heartbeat generate so much traffic?

Each device reports in cyclically, so the number of reports grows with the number of devices. On a test instance under the demo generator it is 96% of requests, and in production about half.

By how much does the index reduce the load?

The number of rows read per heartbeat fell from 3,665 to between 1,928 and 2,205, because the transmitter lookup stopped scanning the whole table.

When should I add the index?

In the maintenance window with the lowest traffic, after a backup, with measurements before and after on comparable traffic.

Does the index solve the problem of table size?

No. It speeds up the lookup, but the table keeps growing. The size is limited by a retention policy, which the client has to decide on.

Scaling your fleet of location devices?

We will review the indexes and the maintenance window with your database administrator before traffic grows. Book a conversation with the InnerWeb team.

Let's talk