Neu Index auf tbesucher.dLetzteAktivitaet?

[Heisenberg]

Hallo zusammen,

mir ist in einem Shop mit recht vielen Besuchern aufgefallen, dass sich hin und wieder Queries aufstauen:

DELETE FROM tbesucher WHERE dLetzteAktivitaet <= DATE_SUB(NOW(), INTERVAL 3 HOUR)

INSERT IGNORE INTO tbesucherarchiv
            (kBesucher, cIP, kKunde, kBestellung, cReferer, cEinstiegsseite, cBrowser,
              cAusstiegsseite, nBesuchsdauer, kBesucherBot, dZeit)
            SELECT kBesucher, cIP, kKunde, kBestellung, cReferer, cEinstiegsseite, cBrowser, cAusstiegsseite,
            (UNIX_TIMESTAMP(dLetzteAktivitaet) - UNIX_TIMESTAMP(dZeit)) AS nBesuchsdauer, kBesucherBot, dZeit
              FROM tbesucher
              WHERE dLetzteAktivitaet <= DATE_SUB(NOW(), INTERVAL 3 HOUR)

Die Statusse waren jeweils updating, waiting for handler commit oder executing. Auf meinen Datenbankservern ist allerings "sync_binlog=1" gesetzt was grundsätzlich immer darauf wartet, dass das binlog geschrieben wurde.

Das DELETE erzeugt einen exklusiven Lock auf die Tabelle tbesucher was dazu führt, das das INSERT ins tbesucherarchiv nicht ausgeführt werden kann.
Andersherum, wenn das INSERT direkt vor dem DELETE ausgeführt wird, kann das DELETE nicht ausgeführt werden weil es keinen exklusiven Lock anfordern kann und es muss auf das INSERT warten.
Da kein Index gewählt werden kann, wird zwangsläufig ein Full Table Scan durchgeführt. Bei 70 MB fällt sicherlich nicht viel Zeit an, aber es fällt Zeit an.
Kommen nun viele Queries quasi zur selben Zeit, multipliziert sich der Vorgang sehr schnell.

Mit einem Index wird nur noch die Row gelocked, was die Queries parallel ausführen lässt.

OHNE Index (anderer Shop)

mysql> EXPLAIN DELETE FROM tbesucher WHERE dLetzteAktivitaet <= DATE_SUB(NOW(), INTERVAL 3 HOUR);
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | DELETE      | tbesucher | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9815 |   100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

MIT Index

# INDEX
ALTER TABLE tbesucher ADD INDEX idx_letzte_aktivitaet (dLetzteAktivitaet);

mysql> EXPLAIN DELETE FROM tbesucher WHERE dLetzteAktivitaet <= DATE_SUB(NOW(), INTERVAL 3 HOUR);
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | DELETE      | tbesucher | NULL       | range | idx_letzte_aktivitaet | idx_letzte_aktivitaet | 6       | const |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+-------+------+----------+-------------+

Beste Grüße

 

[MKis84]

Hallo Heisenberg

Dieser fehlende Index hat unseren Shop bei massiven Besuchen von Bots auch schon in die Knie gezwungen. Seit wir den Index gesetzt haben läuft das wunderbar.

Gruss
Marco

 

[Heisenberg]

Ich habe gerade gesehen, dass https://issues.jtl-software.de/issues/SHOP-8584 das Problem auch beschreibt.