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
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
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