Wer kennt das nicht, eine Anforderung im Projekt, dass der aktuelle Stand (Anzahl an Beiträgen, Besuche, Klicks etc.) auf der Oberfläche angezeigt werden soll. Häufig realisiert man dies mit einer select count(*) from …. Anweisung. Oder Schreibt die Statistikdaten direkt in die Datenbank.
Erstmal sollte man festhalten, dass Statistikdaten eigentlich nicht direkt und in Echtzeit in die DB durch gereicht werden sollten. Gerade bei verteilten Systemen verursacht diese viele konkurierende Zugriffe auf die gleichen resourcen. Dafür reicht ein einfacher Service im Hintergrund, der diese Daten im RAM hält und regelmässig mit der DB abgleicht.
Was passiert denn eigentlich bei einem Select count(*) ?
Ein select count(*) verursacht im ungünstigsten Fall einen Full Tablescan. Abhängig vom DBMS wird ein Index erst gar nicht angesprochen.
Leider, und das ist das Fatale an der Sache, kann selbst mit einer Indizierung und where Bedingung dieses Verhalten nicht verbessert werden. Der count(*) zählt durch alle Objekte durch, und berücksichtigt / zählt nur jene, die der Bedingungen entsprechen. Ein full Tablescan per se ist nicht immer problematisch. In vielen Fällen wäre eine FT einem verschachtelten Join häufig vor zu ziehen. Aber darum geht hier nicht.
Was passiert weiter bei einem count()? Die Tabelle wird gelockt. Das ist ein nicht unwesentliches Problem, denn der Lock wirkt sich auch auf alle anderen count() auf die Tabelle aus. Das mag am Anfang und kleinen Datenmengen kaum problematisch sein, wachsen diese zunehmend an, dauert der einzelne count() auch länger. Abhängig von der Masse der count()’s, den hinzukommenden Daten und der bestehenden Datenmenge tritt früher oder später der Effekt auf, dass diese Abfragen sich gegenseitig so massiv behindern und die Last ins unermessliche steigt. Hinzu kommt, dass dann häufig von der Anwendung noch mehr Verbindungen zur DB geöffnet werden was entweder zu Fehlern führt, da man das Limit der max Connections erreicht hat, oder eben auf der DB noch mehr Last erzeugt und damit zusätzlich ausgebremst wird.
Daraus resultiert eine Kettenreaktion, die leider sehr fatal zuschlägt und zumeist mit einem nicht mehr nutzbaren Betriebszustand endet.
Wie komme Ich darauf, dass es sich um einen Full Tablescan handelt?
Bevor ich das beantworte, kurz ein paar Erkenntnisse in einem Kundenfall. Ein Kunde betrieb einen Marktplatz und wollte zu jedem Kundenshop sehen, wie viele Artikel dort vorhanden sind. Das sollte die Besucher zu den gut gepflegten Shops leiten. Dies wiederum wurde mit select count(*) realisiert.
Soweit so gut, das ganze ging auch lange genug gut. Bis zu dem Tag, als sich dort 2 Ramsch Händler fanden. Diese waren IT Technisch auf dem neusten Stand, und scannten Ihre Konkurswaren welche dann auch unmittelbar in den Shop gelangten.
Man sollte es kaum für Möglich halten, aber diese beiden hatten es tatsächlich geschafft die write Rate der Artikeltabelle von ca. 2-3 / Minute auf eine Raten von 1-2 / Sekunde zu erhöhen. Die Datenbank war damit aber noch lange nicht ausgelastet.
Der Kunde rief mich in seiner Verzweiflung an, als einer der beiden Kunden eine Marketing Kampagne geschaltet hatte. Entsprechend das Besucheraufkommen sehr hoch war. Nach dem 2. Tag offline wurde noch nicht einmal mehr über den Tagessatz verhandelt. 2 Profis hatten wohl schon das menschenmögliche versucht und kamen zum Ergebnis, da muss eine M5000 in Vollausbaustufe her.. Das Abschalten von Integritätschecks etc. half leider alles nichts…… Der Querycache wurde auf 1 GB getrimmt davon wurden gerade mal 200Mb genutzt, geholfen hat das auch nicht. Zu dieser „Profi“ Tat enthalte ich mich.
Ich warf einen Blick auf die aktuellen Prozesse und musste eine Horde an select count(*) entdecken, welche auf die gleiche Tabelle mit ca. 250 000 000 Einträgen herum werkelte. Die Abfragen dauerten teilweise bis zu 120 Sekunden. Viel zu lang bei einer Tabelle mit ca. 15 GB größe.
Wärend mir noch einer der selbsternannten Profis erzählen wollte, wie toll doch der Query Cache ist…. schaltete Ich diesen einfach ab. Danach war er damit beschäftigt dem Kunden zu erzählen was für ein unfähiger DBA ich doch sei … Ich hatte dafür meine Ruhe und konnte mich dem Problem ernsthaft widmen.
Eine Sache die mir sofort auf viel, war: In der Shop Übersicht diese Anzeigen, wie viele Artikel in einem Shop vorhanden sind…. Bei 100 000 Artikeln und mehr, da kommts doch auf 100 mehr oder weniger auch nicht an oder? Der Kunde war damit einverstanden, denn auf die Anzeige der Artikelanzahl wollte er auf keinen Fall verzichten.
Entsprechend änderte ich die Abfrage von:
Select count(*) from artikel where shopid=? auf:
Select count(*) from artikel where shopid=? and create_date < SUBTIME(concat(curdate(),‘ ‚,curtime()),’1 0:1:00‘);
Mit einem Abstand von 60 Sekunden zum aktuellsten Datensatz war die Laufzeit von mehreren Sekunden auf wenige Millisekunden beschleunigt. Es hatten jedoch schon mehrere Sekunden genügt. 5 -10 ca. Obwohl die, zur Laufzeit der Abfrage, eingehende Inserts nicht mehr zum Ergebnis gezählt wurden, wirkten sich dieses trotzdem massiv auf die Laufzeit des Selects aus.
Bis zum Ersatz der „Statistikdatenlösung“ durch eine bessere, reicht dieser Workaround erstmal aus. Dem „Profi“ wurde hinterher glaubhaft gemacht, dass der Query Cache die Ursache war. Rein vorsorglich, damit der nicht wieder auf die Idee kommt, mit dem QCache unnötig Ressourcen zu verschwenden für Abfragen bei denen dieser gar nichts bringt. Hingegen wurden noch weitere Optimierungen durchgeführt, was letztendlich weitere Abfragen erheblich beschleunigt.
Zurück zur Frage:
Diese massive Verzögerung trat bei allen shop ids auf, bei den einen mehr, bei den anderen etwas weniger aber in allen Fällen zu langsam. Lediglich durch die zeitliche Abkoppelung, der oben genannten where Bedingung wurden alle Abfragen binnen weniger Millisekunden beantwortet.
Das war eine äusserst bemerkenswerte Erkenntnis. Von der Insertrate der beiden Shops waren also auch jene stark betroffen, die sehr selten Änderungen haben…
Daher ziehe ich den Rückschluss, dass sich die Zählung vom ersten bis zum letzten Datensatz durchläuft. Das könnte nun nicht so dramatisch sein, schließlich wird ja die Tabelle gelockt und keine Änderungen dürften vorkommen. Soweit eigentlich auch richtig. Daher sollte die Abfrage immer schnell zu einem Ergebnis führen. Beim Select count(*) werden die Änderungen offensichtlich erkannt später nachgeladen und der count zuende geführt. Auch wenn es nicht mehr ins Ergebnis fließt, werden die nachfolgenden eingehende Daten berücksichtigt.
Die Abgrenzung durch das genannte Statement führt offensichtlich dazu, dass beim count(*) die Daten in einer anderen Reihenfolge durchlaufen werden und vor allem, seltener mit den noch nicht commiteten Daten abgeglichen werden.
