MySQL Tabelle flotter machen

[Medium]

Das trifft es besser, stimmt. Ich hab das vorhin entsprechend umgebaut, und das ist in dieser Form nun wieder brauchbar zu bedienen. Besten Dank an alle!

[alcaeus]

Ich versuch mal mit den Grundlagen anzufangen:

1. Du hast eine DB mit 150k Datensaetzen. Das sind Peanuts - wenn du hier schon Performance-Probleme hast ist nie die Datenbank schuld. Wenn dann ist es die Hardware, vielleicht noch die Einstellungen, aber am wahrscheinlichsten ist es deine Software (=SQL-Abfragen).
2. Mach dich mit MySQL vertraut. Schau dir an, welche Moeglichkeiten es gibt, Queries zu analysieren (EXPLAIN, EXPLAIN EXTENDED, Profiling) sowie relevante Log-Informationen zu finden (Slow Query Log).
3. In der MySQL-Dokumentation gibt es ein ganzes Kapitel, welches sich der Optimierung widmet. Lies es und versteh es.
4. Mach dich mit den Einstellungen von MySQL vertraut. Oft kann man mit ein bisschen Tuning an den Werten viel veraendern. Dafuer gibt es auch Software (Stichwort: MySQL Tuning Primer)

Soviel zu den Grundlagen. Wenn du ein paar Grundregeln beachtest, kannst du auch mit ganz anderen Datenmengen umgehen. Wir setzen MySQL in mehreren Instanzen ein, wobei ein aktuelles Projekt momentan ca. 40 Millionen Datensaetze einer Queue-Instanz vorhaelt und imstande ist, 5000 Datensaetze pro Sekunde zu schreiben und gleichzeitig ca. das gleiche an Datensaetzen wieder aus der Datenbank auszulesen und woanders zu verarbeiten.

So, nachdem wir das grundsaetzliche geklaert haben, hier noch ein paar Praxistipps:

• Fang am Besten damit an, das Slow Query Log zu aktivieren. Hier werden Statements geloggt, welche laenger als x Sekunden (kann konfiguriert werden) dauern. Mit Hilfe von Tools kannst du dir dann das teuerste Query rausnehmen, es analysieren und optimieren.
• Wenn du Queries schreibst, achte auf deine Tabelle. Wenn du WHERE-Statements benutzt, sorge dafuer dass immer ein Index drauf ist. Wenn du sortierst, sorge dafuer dass immer ein Index aufs Feld ist. Natuerlich willst du nicht pauschal jedes Feld mit einem Index versehen, das bringt dann auch nichts.
• Du kannst direkt schon mit Indexen anfangen: Index auf printerdate, packagenr, chargenr, ggf. noch auf prodname. Ersterer ist der wichtigste, anschliessend musst du damit spielen und einfach testen. Bei der Datenmenge geht ein ALTER TABLE noch schnell, da kann man sowas auch mal machen. Spaetestens wenn deine CREATE INDEX-Statements 8 Stunden unterwegs sind weil 70 GB Daten umgeschrieben werden muessen, solltest du solche Erfahrungen hinter dir haben

Dann gehen wir mal kurz auf den Server selbst ein:

Zitat:

Speicher:
Der PC hat 2GB
MySQL
Key Buffer 2MB
Sort buffer 212kB
InnoDB
Buffer pool size 11MB
Additional mem Pool size 2MB

• RAM. Machst du Witze? Meine Entwickler-Maschine hat 8 GB RAM, 2 GB fuer nen DB-Server ist ehrlich gesagt eine Frechheit. Reg ein RAM-Update ein, Mindestens 12 GB sollten es sein. Warum siehst du gleich.
• Key Buffer ist dir komplett egal, der ist nur fuer MyISAM wichtig. Lass den auf 2M.
• Sort buffer solltest du vergroessern. Stell mal 2 MB ein, maximal aber 4 MB. Anschliessend wird es potentiell wieder langsamer. Der Sort buffer wird verwendet, um Daten im Speicher zu sortieren, anstatt ueber tmp-Files.
• Buffer Pool: viel zu klein. Und zwar wirklich. Ich zitiere mal die MySQL-Dokumentation zu buffer_pool_size:

  Zitat:

  On a dedicated database server, you may set this to up to 80% of the machine physical memory size.

  InnoDB verwendet diesen Buffer um Indexes und Daten im Speicher vorzuhalten, damit sie nicht von der Platte gelesen werden muessen. Du willst also dass der Buffer Pool mindestens so gross ist wie die Indexex derer Tabellen, auf die du regelmaessig zugreifst. Die Index-Groesse kannst du mit SHOW TABLE STATUS rausfinden, sie wird dort in Bytes angegeben. In deinem Fall wuerde ich den Buffer Pool auf 1.5 GB setzen, vorausgesetzt da laeuft nicht sonst noch Schrott drauf. Deshalb auch der Hinweis mit mehr RAM. Solange Platz ist, schreibt InnoDB die Daten zwar auf die Platte, haelt sie aber auch im RAM vor. Das bedeutet dass viele Abfragen keine Disk-I/O mehr erzeugen, was die Queries nochmal viel schneller macht.
• mem_pool_size ist eigentlich egal.
• Aktiviere den Query-Cache. Dieser haelt die Ergebnisse von Abfragen ein einem Cache vor und sorgt dafuer, dass nachfolgende gleiche Statements viel schneller ein Ergebnis liefern (kaeme z.B. vor wenn 2 Leute kurz hintereinander die Liste von Auftraegen gleich anzeigen).
• Beachte beim Einstellen aller Buffer, dass viele Buffer (z.B. auch sort_buffer_size) pro Connection angelegt werden koennen. Die oben erwaehnten Tuning-Tools rechnen dir dann gerne vor, wieviel RAM MySQL im schlimmsten Fall belegt. Das solltest du auf alle Faelle mit beachten.

So, jetzt erstmal lesen, bisserl spielen und du wirst feststellen dass das, was du mit MySQL bisher machst eigentlich keine Herausforderung fuer einen DB-Server ist. Wie gesagt, wir haben MySQL-Instanzen im Einsatz (nicht geclustered) die pro Stunde mehr Daten schreiben als du insgesamt hast - von Performance-Problemen keine Spur.

Greetz
alcaeus

[Medium]

Hey alcaeus, erstmal dicken Dank, dass du die die Zeit genommen hast für so viel Posting!

Den Optimierungsteil habe ich die Tage mal überflogen, wobei an den Statements und meiner (einen, kleinen schnuckeligen) Tabelle da bis auf die Indizes wohl wirklich nicht so arg viel zu machen ist, da das ja alles so simpel in der Struktur ist, wie es eigentlich geht. Da ist ja nichtmals ein Join drin, keine SPs, 0 Trigger, nix. Von daher werde ich definitiv an den Speichereinstellungen doktorn, das klingt doch sehr vielversprechend! Ich nehme stark an, dass damals dort einfach eine Standardinstallation gemacht wurde, und gut is.
Der PC mit dem DBMS ist schon ein älteres Schätzchen, und für ein eigentlich ja völlig "blödes" Log wird der Kunde da kaum in ein Geschoss investieren wollen. Die 2GB sollten aber locker langen, da das Log praktisch nur von einem anderen PC ausgewertet wird, alle paar Wochen mal ein zweiter. Die Datenmenge ist mit bisher ~30MB auch eher winzig, der Index ist, mit Index auf allen potenziell an WHERE und ORDER BY beteiligten Spalten (was letztlich eigentlich fast alle sind) in etwa genau so groß. Da auf der Kiste sonst nur ein Programm zur Kommunikation mit einer SPS läuft, welches praktisch nichts an CPU und RAM verbrät, sollte ich MySQL problemlos >1GB spendieren können, was dann sogar noch genug Luft nach oben wäre. (Mittelfristig wird dort eh die ganze Anlage mal überholt, so Ende des Jahres eventuell, da könnte man dann ja etwas mehr klotzen )

Dankschö!

[alcaeus]

Zitat von Medium:

Der PC mit dem DBMS ist schon ein älteres Schätzchen, und für ein eigentlich ja völlig "blödes" Log wird der Kunde da kaum in ein Geschoss investieren wollen. Die 2GB sollten aber locker langen, da das Log praktisch nur von einem anderen PC ausgewertet wird, alle paar Wochen mal ein zweiter. Die Datenmenge ist mit bisher ~30MB auch eher winzig, der Index ist, mit Index auf allen potenziell an WHERE und ORDER BY beteiligten Spalten (was letztlich eigentlich fast alle sind) in etwa genau so groß. Da auf der Kiste sonst nur ein Programm zur Kommunikation mit einer SPS läuft, welches praktisch nichts an CPU und RAM verbrät, sollte ich MySQL problemlos >1GB spendieren können, was dann sogar noch genug Luft nach oben wäre. (Mittelfristig wird dort eh die ganze Anlage mal überholt, so Ende des Jahres eventuell, da könnte man dann ja etwas mehr klotzen )

Das muss kein Geschoss sein - Datenbanken haben laut meiner Erfahrung aber die Eigenschaft, schnell ueber Limits zu wachsen, die man nicht bedacht hat. Wenn du momentan nur 30 MB Daten hast dann sollte es langen, den innodb_buffer_pool_size auf ca. 1 GB hochschrauben, den sort_buffer_size auf ca. 4 MB und die Indexes entsprechend zu setzen. Wenn du dann das Slow Query Log aktivierst kannst du in Zukunft auch relativ einfach feststellen, welche Queries Probleme machen und dort entsprechend handeln.

Viel Erfolg dann!

Greetz
alcaeus

PS: gib mir bitte Feedback ob das Buffer-Tuning das Problem behoben hat - das wuerde mich interessieren.

[Furtbichler]

Wir haben Ewigkeiten mit einer alten Mühle als DB-Server sehr gute Ergebnisse Ergebnisse erzielt. Die CPU-Geschwindigkeit ist nicht primär maßgebend, sondern -wie schon erwähnt- RAM. RAM und nochmals RAM. Da Speicher billig ist, würde ich das OS ausreizen.

[Medium]

Södale, Indizes alle da, Sortbuffer und Inno Pool aufgebohrt, und der Unterschied ist schon üppig! Danke!
Man merkt zwar noch, dass da ein Haufen Sätze beteiligt ist, aber bei weitem nicht mehr so behindernd wie zuvor. Ich konnte den Bufferpool allerdings nicht auf 1GB setzen, da hat dann Windows geweint, dass zu wenig Speicher wäre und hat dem MySQL Service gleich mal ganz den Start verweigert . Aber ich mutmaße mal ganz stark, dass es mit diesen Einstellungen bis zu Generalüberholung der zugehörigen Produktionsanlage (lies: u.a. Neuer Server) zufriedenstellend ist.

Ich musste übrigens herzhaft lachen, als ich nochmal genauer auf die Innereien geschaut habe. Die 2GB RAM sind eigentlich die 2GHz der Celeron CPU gewesen, hab ich mich verguckt. Gekrönt von eigentlich 248MB RAM . Das ist so alt, da wird's glatt schwierig noch Riegel für aufzutreiben - zumindest für einen vertretbaren Kurs. Aber bisher langt der Mops, und ich hab ja auch auf meinem Arbeitsrechner ohne obige Änderungen sehr Lahme Abfragen gehabt. Hach Industrie. Ich glaub wir haben irgendwo sogar noch einen 486er kleinere Dinge erledigen bei einem Kunden. Aber er tut!

Nochmals besten Dank! Läuft viel besser, und dank nochmals hin gucken weiss ich ja nun wo der Hund begraben liegt.

[alcaeus]

Freut mich dass es tut.

Greetz
alcaeus

PS: Gut dass du das mit den 248 MB RAM erst jetzt festgestellt hast. Ich waere sprachlos gewesen