Update: 27.11.2023
So, Schluss mit lustig. Auch die unten dargestellten (scheinbaren) Verbesserungen führten nicht zu einer akzeptablen Ausführungszeit der Löschroutine. Daher habe ich nun alles so eingestellt, dass alle Werte aus der Tabelle "condition" gelöscht werden, wenn sie älter (utc_read) als 31 Tage sind. Diese Routine wird einmal am Tag (19:35 Uhr) ausgeführt.
Ich habe die letzten Tage gesucht, warum hin und wieder (zuletzt alle 8-12 Minuten) Messwerte nicht korrekt in der Tabelle condition eingetragen wurden, obwohl diese pünktlich von den Raspis in der Puffer-Tabelle abgeliefert wurden. Die Ursache war eigentlich ganz einfach und wie immer wenn die Lösung einfach ist, findet man sie besonders schwer.
Also, "Schuld" war nicht die Prozedur, die die Datensätze aus dem Puffer auflöst und in die Tabelle condition schreibt, sondern eine Löschabfrage auf die Tabelle condition mit der die Datensätze gelöscht werden, der Aufbewahrungszeit (lifetime_day) abgelaufen ist. Diese Anfrage wurde alle 10 Minuten ausgeführt und brauchte so lange (ca. 3 Minuten), dass in der Zwischenzeit durch die Datensatzsperren keine neuen Daten in der Tabelle eingetragen werden konnten. Da die Aufbewahrungszeit in Tagen festgelegt ist, macht das Löschen alle 10 Minuten ja auch keinen Sinn; einmal am Tag reicht völlig aus.
Nächste Erkenntnis war dann, dass das Löschen insgesamt etwa um den Faktor 2 schneller geht, wenn man die zu löschenden Datensätze zunächst markiert und erst im zweiten Schritt löscht. Das hat wohl etwas mit der Art und Weise des Index (neu) aufbaus nach dem Löschen zu tun.
So wird es nun gemacht und täglich 19:35 Uhr ausgeführt:
# die Datensätze zunächst Markieren und dann die markierten Löschen geht schneller - etwa Faktor 2
UPDATE `condition` AS c
INNER JOIN `_device` AS d ON d.id = c.device_id
SET c.mark = 1
WHERE c.utc_read < DATE_SUB(NOW(), INTERVAL d.lifetime_day DAY);
DELETE FROM `condition`
WHERE mark = 1
order by id desc;
