Hallo zusammen,
mir ist in einem Shop mit recht vielen Besuchern aufgefallen, dass sich hin und wieder Queries aufstauen:
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)
MIT Index
Beste Grüße
mir ist in einem Shop mit recht vielen Besuchern aufgefallen, dass sich hin und wieder Queries aufstauen:
SQL:
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)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)
SQL:
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
SQL:
# 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