Ich versuch mal mit den Grundlagen anzufangen:
- 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).
- 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).
- In der MySQL-Dokumentation gibt es ein ganzes Kapitel, welches sich der Optimierung widmet.
Lies es und versteh es.
- 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