Migration von Mysql zu Postgresql?

[SeeksTheMoon]

Ich habe hier eine MySQL 4.0.24 Datenbank mit über 4 Millionen Einträgen in 37 Tabellen laufen.
Diverse Perl-Scripte arbeiten damit und machen teilweise auch html-Ausgaben, die sehr groß sein können.

Bestimmte Anfragen dauern dabei sehr lange, allerdings weiß ich nicht ob das die Datenbank, das Script oder der Browser schuld ist.
Beispiel: Eine Menge Infos holen, in einer Schleife zu einer html-tabelle machen und diese zeilenweise ausgeben.

Ich habe zwar gelesen dass Mysql mit sehr fetten Daten umgehen kann, allerdings muss das nicht bedeuten, dass Mysql dabei auch noch schnell ist.
Würde sich ein Wechsel zu Postgres 7.4.7 geschwindigkeitsmäßig lohnen?
(Neuere Versionen sind bei Debian stable nicht dabei)
Wir wollen auch mehr Ausfall-Sicherheit durch Replikation auf mehreren Rechnern haben. PG und Mysql können das, allerdings konnte ich noch nicht ausmachen, wie gut das bei den beiden klappt, da ich bei PG keine Doku dazu gefunden habe.
Kann dazu jemand was sagen?

Wie gut klappt die Migration? Bei PG ist ein Tool dabei, das von Mysql die Daten portiert, habe ich gelesen. Hat schon jemand damit gearbeitet?

[mwanaheri]

ok, ich bin ein Postgres-Fan, aber ich würde vermuten, dass der Flaschenhals bei den Scripten liegt. Vielleicht gibt es da Optimierungsmöglichkeiten? Bei Java ist der typische Flaschenhals in solchen Fällen das Zusammensetzen von Strings statt StringBuffern. Gibt es da bei Perl vielleicht ähnliches?

[Christoph]

Würde sich ein Wechsel zu Postgres 7.4.7 geschwindigkeitsmäßig lohnen?
(Neuere Versionen sind bei Debian stable nicht dabei)

Nimm auf jeden Fall die aktuelle Postgres-Version. Lässt sich unter Debian problemlos aus den Sourcen kompilieren und installieren. Hier ist eine ausführliche Installationsanleitung:
http://lionel.kr.hs-niederrhein.de/~dalitz/data/lehre/DBS/postgres_install.pdf

Grundsätzlich würde ich vermuten, dass ein Wechsel geschwindigkeitsmäßig was bringt, da Postgres mehr Optimierungsmöglichkeiten bietet (z.B. partielle Indizes usw.) und bei komplexen Abfragen als schneller gilt. Allerdings musst Du natürlich rausfinden warum die Statemants langsam sind (z.B. mit EXPLAIN). Häufigster Grund sind fehlende bzw. nicht benutzte Indizes und dass zu viele elementare Statements ausgeführt werden (ein komplexes Statement ist in der Regel im Client-Server Betrieb erheblich schneller als viele kleine).

Zur Replikation: da ist bei PG glaube ich eine Lösung im contrib-Verzeichnis der Distribution dabei.

Zur Migration: Probleme bereitet oft, dass MySQL sich nicht an den SQL-Standard hält (double quotes statt single quotes, String-Concatenation als logisches oder usw.).

[Turbohummel]

Hm. Zur Analyse großer Datenmengen ist PostGres wohl die schlechteste Wahl, die du treffen kannst. PostGres ist perfekt für Transaktionssysteme gebaut, in der in einer Verbindung sehr viele kleine Querys mit verhältnismäßig wenig Datensätzen ins Such- und Ergebnismenge abgesetzt werden.
-> Ideal für Programme mit persistenter Datenbankanbindung.

MySQL ist eher für wenige kleine bis mittelgroße Abfragen geeignet, die von vielen verschiedenen Verbindungen kommen -> ideal für Webservices.

Die beste Wahl für deine Problemstellung stellt wohl MaxDB (ebenfalls von MySQL) dar. Diese Datenbank ist speziell für Data-Warehouses gebaut, also für die Analyse großer Datenmengen.

Wenns was kommerzielles sein darf, ist der MS SQLServer top.

[Christoph]

Beste Wahl = XYZ, Schlechteste Wahl=ABC

Mit so pauschalen Urteilen wäre ich vorsichtig. Ich sehe nicht, wieso MySQL für größere Datenmengen gedacht ist als Postgres (die Artikel die ich dazu gelesen habe, deuten eher auf das Gegenteil hin, insbesondere bei komplexeren Auswertungen). Und warum MS SQL-Server "top" oder besser als Oracle oder DB2 sein soll, lässt sich so pauschal wohl auch kaum beurteilen.

[Turbohummel]

Ich berufe mich dabei auf Benchmarks meines Praktikumsbetriebes, die ich in dieser Zeit durchgeführt habe (genau Zahlen kann ich keine veröffentlichen):
Am Ende hatten wir 2 mögliche Lösungen:
Eine "zweiteilung":
- MySQL am Frontend (war da mit Abstand die schnellste Lösung)
- MaxDB für die Auswertungen

oder den SQLServer. Beide Lösungen war etwa gleich schnell (die Rechenzeit für den Import der Daten stand nachts sowieso zur Verfügung).

PostGres war bei den Auswertungen nicht viel schneller als MySQL, aber bei den "kleinen" Transaktionen doch um einiges langsamer.

Der Support bei Oracle ist misserabel (da hat man schon böse Sachen gehört), und DB2 kostet (mit Verlaub) ein Schweinegeld.

[SeeksTheMoon]

Datenbankverbindungen gibts immer nur von einem Rechner (aber möglicherweise mehrere gleichzeitig von verschiedenen Scripten).
Mit Indices arbeite ich nicht, weil ich davon keine Ahnung habe. Ich hab schon gelesen dass die einiges bringen. (Steht auf meiner Lern-Liste schon ganz oben *g*)
Als Datenbank kommt nur eine freie in Betracht (die natürlich unter Linux laufen muss weil alle Server von uns unter Linux laufen) und neben Mysql bleibt (neben ein paar anderen "Mauerblümchen"-DBs wie Firebird) eigentlich nur Postgres über.

Eigentlich haben wir nicht so komplexe Datenstrukturen in der Datenbank, es gibt nur ein paar Tabellen die sehr voll sind und dann gibt es in den Abfragen viele Sortierungen und manchmal auch recht große joins (ich hab eine Script-generierte Abfrage gesehen, die so aussieht:



SELECT (Liste mit 20 Werten) FROM (tabelle) LEFT
JOIN (tabelle) ON ((...) AND (...) AND (...)) LEFT JOIN (tabelle) ON ((...)
AND (...) AND (...)) LEFT JOIN (tabelle) ON ((...) AND (...) AND (...)) LEFT
JOIN (tabelle) AS (...) ON ((...)) LEFT JOIN usw usw usw
Das Teil ist der Killer, das geht ca 10 Zeilen so :D
Query time 302. Das ist ein bissel lang, aber auch verständlich...

[elrond]

mit postgres bist du auf alle fälle gut bedient. sobald du dein wissen um diese db erweiters wirst du relativ schnell feststellen können, dass esperformanceunterschiede nur noch in spezielfällen gibt. mal zugunsten von mysql mal ist pg vorn...

pg bietet aber einige dinge, die für einen stabilen betrieb unerlässlich sind. das sind zb. transaktionen, auch wenn du sie nicht explizit benutzt. diese sind zum erzeugen eine _konsistsnten_ datensicherung im laufenden betrieb notwendig.

sobald deine anwendungen komplexter werden wirst du funktionalitäten wie views und sequenzes zu schätzen wissen. ;)

noch ein tip: wie schon oben angedeutet, benutz am besten die pg-version 8.1

[Turbohummel]

Views kann MySQL 5 auch, fehlende Transaktionen ist natürlich ein triftiger Grund, aber dafür kann man ja gegebenenfalls auf den alternativen Tabellentyp InnoDB (oder so) zurückgreifen.

Was würde denn gegen MaxDB sprechen? Ist ebenfalls eine freie Lösung, läuft auch unter Linux. Und grade diese Art von "giganto-Querys" führt MaxDB am schnellsten aus.

[SeeksTheMoon]

ich glaube das wird nichts mit dem Umstieg (egal auf was):
Ich habe die Datenbank so konfiguriert, dass sie mit InnoDB als Backend laufen soll und da sind schon wichtige, große, unwartbare, nicht von mir geschriebene Scripte ausgerastet bis ich das InnoDB wieder ausgestellt habe... :(

Die Query Time von oben ist übrigens nicht korrekt, sondern folgendes:
1.306.260 rows in set (16 min 31.20 sec)

Mal sehen was man so mit Indizes machen kann, sonst hilft nur noch schnellere Hardware um diese Klump-Datenbank laufen zu lassen... (ist schon ne fette Kiste)

[BLUESCREEN3D]

Für einige Spalten einen Index anzulegen wird erstmal vermutlich wesentlich mehr bringen als der Umstieg auf ein anderes DBMS.

Lies am besten mal http://de.wikipedia.org/wiki/Datenbankindex und leg für Spalten, die oft in WHERE-Bedingungen auftauchen, jeweils einen Index an.
Dadurch wird dann bei Abfragen die Suche der passenden Datensätze beschleunigt.

[Turbohummel]

Jap. Und die die häufig in "ORDER BY", GROUP BY bzw. HAVINGs ebenfalls.

Außerdem würde ich prüfen, ob du die 10000 Ergebniszeilen wirklich in HTML reinschreiben musst, oder ob eine Aufteilung in mehrere Seiten da nicht auch den Zweck erfüllen würde.

[SeeksTheMoon]

ok, so wie ich das sehe wird im Code aber so ziemlich alles abgefragt was man fragen kann.
Inzwischen konnte ich die ganzen Tabellen doch noch nach InnoDB konvertieren.
Gibts ne Möglichkeit an eine Statistik der Datenbank zu kommen?

[Turbohummel]

Gibts ne Möglichkeit an eine Statistik der Datenbank zu kommen?
Was für ne Statistik?

[SeeksTheMoon]

Welche Daten wie oft aufgerufen wurden.
Wenn ich nämlich in den Code sehe, dann lohnt es sich nicht Indizes zu erstellen, weil so ziemlich alles Querbeet abgefragt wird was in der Datenbank drin steht, deshalb brauche ich Statistiken dafür; am besten von der DB selber.

[Christoph]

Welche Daten wie oft aufgerufen wurden.


Bei Postgres gibts eine Loglevel-Einstellung; da kann man auch einstellen, dass jedes Statement protokolliert wird (würde ich aber nur kurzzeitig machen, sonst laufen Dir die Logs über). Ähnliches sollte es für MySql auch geben.

[sticky bit]

Hab das grad mal überflogen und möchte mal bzgl. der Indizes meinen Senf dazu geben. Ich denke auch, dass das echt das erste sein sollte was du ausprobierst bevor du daran denkst, das DBMS zu wechseln.

Gerade erst erlebt:
Wir hatten enorme Performance-Probleme mit zwei Datenbank-Prozeduren, eine zur Bewertung von Kunden und eine zur Verkäufer-Zuordnung.
Mit den richtigen Indizes war es möglich die erste welche zuerst ca. 2,5 h für ihre Aufgabe gebraucht hat und teilweise zwischendrin ganz hängen geblieben ist auf ca. 0,25 h runter zu bringen, zweitere von ca. 4 h auf 0,3 h. Wobei man fairer Weise dazu sagen muss, dass wir bei zweiterer noch ein bisschen mit Snapshots getrickst haben und noch nicht so ganz klar ist wieviel der Refresh für die Dinger im Live-Betrieb wieder frisst und evtl. wieder dazu zu rechnen ist. Trotzdem, solche Zahlen sprechen denk ich für sich, war selbst verwundert wieviel das doch aus machen kann!

[elrond]

sobald die datenmengen etwas grösser sind, ist faktor 10 für performanceverbesserung durch indizes nix exotisches...