Mittwoch, 28. September 2011

Technische Unterschiede PostgreSQL, MySQL, MariaDB - Teil 8

Die vorangegangenen Teile dieser Artikelserie sind hier:

  1. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql.html 
  2. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_30.html
  3. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_31.html
  4. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql.html
  5. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_02.html
  6. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_06.html
  7. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_16.html
SQL-Standard

Sowohl PostgreSQL als auch MySQL versuchen so nah wie möglich am SQL-Standard zu bleiben und soviel wie möglich standardkonform zu implementieren. Beide sind daneben so ehrlich, dass sie in der Dokumentation herausstellen, wann sie vom Standard abweichen.

Die größte Abweichung vom Standard haben beide in der Implementierung von Identifier (Bezeichnern). Der Standard schreibt hier etwas von Identifier sollen per Default nur aus Großbuchstaben bestehen. PostgreSQL fand das schon im letzten Jahrhundert unzeitgemäß und implementierte genau das Gegenteil:

Bei PostgreSQL bestehen Identifier per Default nur aus Kleinbuchstaben. Somit ist PostgreSQL bei Identifiern case-insensitive.

MySQL kämpft hier mit weitaus größeren Problemen. Spalten- und Indexname sowie Namen für Funktionen und Prozeduren werden auch bei MySQL zu Kleinbuchstaben und sind case-insensitive. Trigger hingegen sind case-sensitive. Daneben hat MySQL ein weitaus größeres Problem mit Datenbank / Schema und Tabellennamen. Diese werden direkt auf dem Dateisystem abgelegt und es hängt vom Dateisystem ab, ob sie case-sensitive oder case-insensitive sind. Windows-Dateisysteme sind eigentlich immer case-insensitive und Unix-Dateisystem case-sensitive. Wird unter MacOS UFS genutzt, so sind Tabellen und Datenbanknamen case-sensitive. Wird das MacOS-Dateisystem HFS+ genutzt, sind sie case-insensitive. Durch setzen von lower_case_table_names kann das Verhalten geändert werden.

Als Sprachen, die zur Programmierung von Funktionen und Prozeduren verwendet werden, hat sich MySQL als einziges, der gängigen Systeme an den SQL Standard gehalten und SQL implementiert. Die SQL-Implementierung in PostgreSQL ist eher spärlich und beschränkt sich auf die ganz rudimentären Dinge.

PostgreSQL hat jedoch die eigene Sprache Pl/PgSQL implementiert, mit der sich auch komplexe Funktionen und Prozeduren schreiben lassen.

Mir persönlich ist noch keine produktive Datenbank mit Funktionen und Prozeduren begegnet, in der ich die Funktionen / Prozeduren nicht hätte sowohl in SQL als auch in Pl/PgSQL zusammenstricken können.

Mir sind sowohl in MySQL schon Funktionen / Prozeduren begegnet, bei denen ich mir dachte, dass wäre mit Pl/PgSQL einfacher gewesen als auch Funktionen in PostgreSQL bei denen die Umsetzung in SQL einfacher gewesen wäre. Daher denke ich, SQL versus Pl/PgSQL ist lediglich eine Frage der Gewohnheit.

Anzumerken ist hier - der Hauptunterschied, den der SQL Standard zwischen Funktionen und Prozeduren festlegt ist, dass Prozeduren keinen Rückgabewert haben. PostgreSQL hat das so interpretiert und implementiert, dass Prozeduren einfach Funktionen sind, die VOID zurückgeben. Prozeduren werden daher bei PostgreSQL auch nicht mit CALL aufgerufen, sondern genauso behandelt, wie andere Funktionen auch. Als Beispiel:

SELECT prozedur() statt CALL prozedur()

Neben Pl/PgSQL bietet PostgreSQL noch eine Reihe von anderen Sprachen an, in denen Funktionen geschrieben werden können. Die Palette ist lang und enthält alle gängigen Programmiersprachen wie PHP, Perl, C, Java, Ruby, ....

MySQL bietet die Möglichkeit User Defined Functions in C zu schreiben. Das würde ich persönlich aber eher als Qual als als Wahl bezeichnen.

Beim Thema Funktionen und Prozeduren stellt sich auch schnell die Frage, wo die Logik sinnvoller aufgehoben ist. In der Anwendung selbst oder in der Datenbank:

Ich sah einst eine fürchterlich aufwendige Pl/SQL-Funktion (Oracle) die alle eingebenen Umlauten in HTML-Code (z.b. ä für ä) wandelte. Die Programmiersprache, in der die Anwendung geschrieben war, verfügte bereits schon über eine solche Funktion. Heisst, in der Anwendung selbst war es ein Einzeiler. Warum hier also die Datenbank bemühen, wenn die Anwendung das viel besser kann?

Datenbanklogik gehört in die Datenbank, Anwendungslogik in die Anwendung. Die Grenze hier ist manchmal grau.

Genauso häufig wie man bei vielen MySQL-Anwendungen schreien könnte, weil Datenbanklogik in die Anwendung gepackt wurde, könnte man bei PostgreSQL-Anwendungen schreien, weil Anwendungslogik in die Datenbank gepackt wurde.

Wer wirklich nur Datenbanklogik in die Datenbank packt, kommt mit SQL genauso weit, wie mit Pl/PgSQL.

Constraints sind Bedingungen. Der Standard sieht sechs Constraints vor:
  • Not NULL
  • Unique
  • Default
  • Primary Key
  • Foreign Key
  • Check
PostgreSQL hat alle implementiert, InnoDB alle bis auf Check Constraints.

Beide lösen die Implementierung von Primary Key als NOT NULL Constraint plus Unique Index. Beide lösen die Implementierung von Unique als Unique Index.

Wie wichtig oder unwichtig CHECK Constraints sind, ist anwendungsabhängig.

Check Constraints können häufig mehr Schaden als Nutzen.

Check Constraints helfen nur bei Eingaben - also bei INSERT und UPDATE.

In den meisten Fällen, werden die Eingaben schon in der Anwendung geprüft, bevor sie zur Datenbank geschickt werden. Ein häufiger Grund hierfür ist, SQL-Injections zu vermeiden.

Warum dann also ein Check-Constraint als Bremse einbauen, dass die Eingabe nochmals prüft?

Daneben sind Verbindungen bei PostgreSQL teuer. Warum also eine teure Verbindung aufmachen, um eine Eingaben prüfen zu lassen? Bei Fehlschlag
bräuchte ist dann, nach Eingabekorrektur, nochmals eine Verbindung nötig. Ist es da nicht sinnvoller, die Daten werden in der Anwendung geprüft und gleich richtig übergeben?

Ich persönlich habe bei PostgreSQL, abgesehen von Partitionierung, noch keinen Anwendungsfall für Check-Constraints gesehen, wo die Eingabeprüfung nicht besser in der Anwendung aufgehoben war.
  • NULL bedeutet unbestimmt / unbekannt
  • NULL ist weder 0 noch Leerstring noch 00-00-0000 00:00:00.
  • NULL ist weder gleich noch nicht gleich NULL, sondern NULL.
PostgreSQL ist hier standardkonform. Wird für eine Zelle nichts angegeben und ist die Spalte nicht NOT NULL, so ist die Zelle NULL.

Das ist für die meisten Datentypen bei MySQL auch so. Allerdings kann MySQL mit NULL im Datentyp timestamp nicht umgehen. Hier ist per Default im ersten Timestamp der Default current_timestamp und in folgenden 00-00-0000 00:00:00. Der Datentyp datetime kann mit NULL umgehen und sollte verwendet werden, wenn das Verhalten von Timestamp missfällt.

Indexe sind nicht SQL Standard. Beide, sowohl PostgreSQL als auch MySQL behelfen sich mit Indexen wenn es um UNIQUE Constraints geht. Der Primary Key ist ein Zusammenschluß aus NOT NULL und UNIQUE Constraint. Daher wird hier sowohl für UNIQUE als auch für PRIMARY KEY ein Unique Index angelegt.

Zusätzlich legt InnoDB für jeden Foreign Key automatisch einen Index an.

Fortsetzung folgt ....

Samstag, 17. September 2011

Die Lichtgeschwindigkeit und Synchrone Replikation

Welche Strecke legt das Licht in einem Takt zurück?

Wir erinnern uns:

Die Lichtgeschwindigkeit sind rund 300 Tausend km/s.
Das macht 3 * 10⁸ m/s.

Rechner haben eine Frequenz von 3 GHz.
Das sind rund 3 * 10⁹ Takte.

Wieviel Meter legt jetzt das Licht in einem Takt zurück?

3 * 10⁸  m/s : 3 * 10⁹ Hz = 0,1 m = 10 cm

Da das rund gerechnet ist kann man auch sagen 10 cm sind 4 Zoll.

Two Phase Commit

Beim Two Phase Commit wird ...

       1.1. ein Prepare gesendet
       1.2. ein Ready zurückgesendet
       2.1. ein Commit gesendet
       2.2. ein ACK zurückgesendet

Das sind vier Wege - die zurückgelegt werden.

10 cm : 4 Wege = 2,5 cm/Weg oder auch rund 1 Zoll.

Eine CPU hat üblicher Weise 1" x 1" im Quadrat. 

Quelle: Kristian Köhntopp auf der PHP Unconference in Hamburg 2011
Eine verdammt kurze Strecke oder?

Synchrone Replikation

Hier gibt es zwei Theorien:
  1. ACK wird erst dann zurückgesendet, wenn die Daten garantiert permanent auf der anderen Seite gespeichert sind
  2. ACK wird schon dann zurückgesendet, wenn die Daten auf der anderen Seite angekommen sind.
Ein Datentransfer kann nicht schneller sein, als die Lichtgeschwindigkeit (noch nicht). In der ersten Theorie kommt daneben noch die mechanische Trägheit der Festplatte als Bremse hinzu.

Wie oben gezeigt, legt das Licht während eines Taktes nur 10 cm zurück.

Hier müssten mindestens zwei Wege zurückgelegt werden:
  1. Die Daten senden
  2. ACK zurücksenden
10 cm : 2 = 5 cm

Wenn der Replikationsserver 15000 km entfernt steht - müssten 30000 km zurückgelegt werden.

Lichtgeschwindigkeit: 3 * 10⁵ km/s

3 * 10⁵ km/s : 3 * 10⁴ km = 10 Transaktionen pro Sekunde

Bei 150 km Entfernung kommt man so auf 1000 Transaktionen pro Sekunde.

Aber alles nur in der Theorie - weil in der Praxis noch andere Faktoren das ganze ausbremsen.

Die Frage die sich daraus ergibt:

Will man wirklich synchrone Replikation oder ist asynchrone Replikation ausreichend?

Freitag, 16. September 2011

Technische Unterschiede PostgreSQL, MySQL, MariaDB - Teil 7

Die vorangegangenen Teile dieser Artikelserie sind hier:
  1. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql.html 
  2. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_30.html
  3. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_31.html
  4. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql.html
  5. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_02.html
  6. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_06.html 
Wie ist das jetzt genau mit den Transaktionen und dem Redo?

Aufgrund vieler Nachfragen zu genau diesem Thema, werde ich es vorziehen und schon heute darauf tiefer eingehen.

Wie bereits erwähnt loggen sowohl PostgreSQL als auch InnoDB abgeschlossene Transaktionen. Ebenfalls bereits erwähnt hatte ich, dass bei PostgreSQL die Blöcke in der Regel 8 kB groß sind und bei InnoDB 16 kB.

Checkpoints sorgen bei beiden dafür, dass die Änderungen permanent gespeichert werden.

Geänderte Blöcke zwischen zwei Checkpoints werden dirty pages genannt.

InnoDB überträgt nur die geänderten Bereiche eines geänderten Blocks in das Log.

Wird bei PostgreSQL nach einem Checkpoint zum ersten Mal ein Block geändert, dann schreibt PostgreSQL den gesamten Block ins Redo-Log.

Wird ein Block dann nochmal geändert, bevor der nächste Checkpoint passiert, dann wird bei PostgreSQL, genau wie InnoDB nur die Änderung festgehalten.

Bei einem Checkpoint schreiben beide die dirty pages auf die Platte.

InnoDB schreibt dabei die dirty pages zweimal auf die Platte. Zuerst einmal in den Double Write Buffer und danach an die eigentliche Position. Auf diese Weise hat InnoDB eine Kopie der Page, die es zur Rekonstruktion verwenden kann, falls der Server während des Schreibens an die eigentliche Position crashed (z.B. bei einem Stormausfall).

PostgreSQL hat keinen Double Write Buffer. Es schreibt die dirty pages direkt an die eigentliche Position. Da PostgreSQL bei der ersten Änderung nach einem Checkpoint die Page komplett ins Redo-Log geschrieben hat, hat es hier eine Kopie der Page, auf die es zurückgreifen kann, wenn beim Schreiben der Strom ausgefallen ist. Zur Rekonstruktion angelt sich PostgreSQL die Page aus dem Redo und patched die weiteren Änderungen, die weiter hinten im Redo-Log stehen, einfach in die Page hinein.

Das speichern der gesamten Page im Redo ersetzt bei PostgreSQL den Double Write Buffer von InnoDB.

Je weniger Checkpoints umso mehr einzelne geänderte Zeilen stehen im Redo und umso länger dauert somit die Rekonstruktion. Bei PostgreSQL lassen sich die Checkpoints konfigurieren. InnoDB nutzt andere Algorithmen hier und die Konfiguration der Checkpoints ist nicht so ohne weiteres möglich.

Fortsetzung folgt ....

Dienstag, 6. September 2011

Technische Unterschiede PostgreSQL, MySQL, MariaDB - Teil 6

Die vorangegangenen Teile dieser Artikelserie sind hier:
  1. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql.html  
  2. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_30.html
  3. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_31.html
  4. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql.html
  5. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql_02.html
Wann nehme ich welches System?
Diese Frage bekomm ich häufig gestellt und ich versuche stets hier genau abzuwägen und unabhängig zu empfehlen.

Die Frage lässt sich nicht pauschal beantworten. Zum einen hängt es von der Anwendung ab. Es gibt durchaus Anwendungsfälle bei denen das eine oder das andere besser geeignet ist. Eine genaue Anlyse der Pflichtenhefte der Anwendungen, für die die Datenbank erstellt werden soll, ist hier notwendig.

Ich stelle leider heutzutage auch immer häufiger bei dieser Frage - nachdem ich mir die Anwendung habe erklären lassen - fest, dass relationale Datenbanken eine solche Machtposition erreicht haben, dass gerne vergessen wird, dass es noch andere Datenbankkonzepte gibt, die ggf. besser für die spezielle Anwendung geeignet wären, als ein relationales Konzept.

Die erste Frage ist daher - brauch ich ein relationales Datenbanksystem? Ist PostgreSQL bzw. MySQL nicht oversized? Reicht mir vielleicht SQLite? Sollte ich vielleicht mal im NoSQL-Bereich schauen, ob es nicht was gibt, was besser für meinen Anwendungsfall geeignet ist? Muß ich große Blobs wirklich in einer relationalen Datenbank ablegen, oder reicht die Speicherung in einem Dateisystem (was übrigens auch nichts anderes ist als eine Datenbank, jedoch nach hirarchischem und nicht nach relationalem Konzept).

Wenn relational das beste für die Anwendungen ist - dann ist zu prüfen, ob PostgreSQL oder MySQL hier Vorteile haben, die für die Anwendung entscheidend sind. Ein paar Aspekte, wann wer besser zu welcher Anwendung passt, ergeben sich vielleicht später aus den technischen Unterschieden, die ich noch erläutern werden.

Die meisten gängigen Anwendungsfälle bewegen sich jedoch in Bereichen, in denen PostgreSQL und MySQL bei richtiger Verwendung gleich stark sind und es somit völlig egal ist, welches eingesetzt wird.

In solchen Fällen spielen andere Aspekte eine Rolle.

Als aller erstes stellt sich dann die Lizenzfrage.

Die PostgreSQL-Lizenz ist BSD. Jeder darf es nutzen, jeder darf es ändern, jeder darf es weiterverteilen.

MySQL hat eine Duallicense. Zum einen GPL aber, sobald MySQL zusammen mit einer anderen Anwendung ausgeliefert wird, ist Obacht geboten. Hier geht es um die Clientlibrary. Ist die Anwendung selbst nicht unter GPL, ist der Kauf einer Lizenz von Oracle erforderlich.

MariaDB kommt aus dieser Nummer natürlich nicht heraus. Legal umgehen lässt sich der Kauf von teuren Oracle Lizenzen jedoch, durch die Verwendung der Drizzle-Clientlibrary, die unter BSD steht, und die durchaus auch mit MySQL bzw. MariaDB funktioniert.

Ist die Lizenzfrage kein Thema, dann schaue ich mir den Anwender selbst an:

Was passt besser zu ihm? Womit kommt er besser klar? Womit mag er glücklicher werden? Was ist intuitiver für ihn?

Der Anwender sollte sich daneben noch selbst fragen:

Wie sieht es im Community-Support aus?

Hier bin ich zu sehr befangen. Ich rate hier jedem, sich selbst ein Bild zu machen. Einfach versuchen, den Community Support selbst zu finden und ggf. in andere Fälle schauen: Wie wurde geholfen? Gefällt mir wie geholfen wurde? Welcher Community Support macht auf mich den besseren Eindruck? Mit welcher Community komme ich besser klar?

Ich kann hier nur anmerken, dass sowohl MySQL/MariaDB als auch PostgreSQL so stark mit deutschsprachigen Entwicklern unterwandert sind, dass zumindestens der Community Support im IRC (irc.freenode.net) in den deutschen Channeln (#postgresql-de, #mysql.de) und hinter den deutschen Mailinglisten ein enormes Knowhow besitzt, so dass hier nicht auf englisch ausgewichen werden muß. Der deutsche Support ist daneben noch ruhiger und alles andere als überfüllt.

Auch könnte der Anwender sich Gedanken darum machen, was ist, wenn ich professionelle Hilfe brauche? Wen kann ich anheuern? Wem vertraue ich?

Pauschal lässt sich die Frage, wann nehme ich was, in keinem Fall beantworten. Hier ist stets eine individuelle Analyse nötig, die ggf. sogar über die Anwendung hinaus geht.

WERBUNG für professionelle Hilfe

Da ich die Mitarbeiter von SkySQL alle persönlich kenne und ihr Knowhow - empfehle ich natürlich für professionelle MySQL- und MariaDB-Dienstleistung SkySQL.

Für professionelle PostgreSQL-Dienstleistungen gibt es natürlich keine bessere Firma als 2ndQuadrant (Eigenwerbung darf sein).

2ndQuadrant und SkySQL besitzen deutschsprachige Techniker und Ingenieure / Informatiker die Schulungen und Consulting (technische Beratung durch Fachleute mit tiefen technischen Kenntnissen) durchführen.

Man hörte früher immer wieder: "MySQL hat den besten 24/7 Support der Welt."

Die Magie dahinter ist einfach durchschaubar. Der Support wurde von den Entwicklern selbst durchgeführt. Egal ob es eine einfache oder komplexe Frage war, man bekam die Antwort stets direkt von den Experten. Man wurde nicht erst in ein Callcenter geroutet, in welchem der Callcenter-Mitarbeiter eine Liste von Frage durchging. Man landete sofort und umgehend bei den Experten.

Da die Experten weltweit verstreut saßen, und somit natürlich bedingt zu unterschiedlichen Zeiten hellwach und aufnahmebereit waren, klingelte man hier niemandem nachts aus dem Bett und musste auch nicht erst abwarten, bis sich der erwachte gesammelt hatte, sondern man bekam die Hilfe von hellwachen und ausgeschlafenden Australiern bzw. Amerikanern.

Es gibt nur einen kleinen Haken bei diesem System - so ein 24/7 Support kann nur in englischer Sprache funktionieren.

Hinter SkySQL stecken gleich mehrere ehemalige MySQL Manager aus Services und Support. Daneben sind einige der Gurus des MySQL-Support, ihren ehemaligen Chefs gefolgt und arbeiten heute für SkySQL. SkySQL bietet MySQL-und MariaDB-Support nach dem oben beschriebenen, guten und bewährten System an.

2ndQuadrant schöpft hier aus meiner Erfahrung im MySQL-Support und unser 24/7-PostgreSQL-Support ist ziemlich identisch gestrickt. Unser Support wird ausschließlich von den PostgreSQL Experten durchgeführt. Bei uns kann es Ihnen durchaus passieren, dass Sie direkt von einem unserer beiden Ober-Gurus (Simon Riggs und Greg Smith) höchstpersönlich bedient werden.

Freitag, 2. September 2011

Technische Unterschiede PostgreSQL, MySQL, MariaDB - Teil 5

Die vorangegangenen Teile dieser Artikelserie sind hier:
  1. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql.html  
  2. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_30.html  
  3. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_31.html  
  4. http://miraspostgresqlwelt.blogspot.com/2011/09/technische-unterschiede-postgresql.html
Andere arbeiten lassen
Man könnte PostgreSQL unterstellen es sei faul. Man könnte PostgreSQL aber
genausogut unterstellen, es sei verdammt clever.

Einer meiner Professoren sagte einst, Erfinder und Entwickler sind von Natur aus faul, denn Erfindern und Entwickler, entwickeln Dinge, die das Leben einfacher machen sollen. Die Hauptinitiative hierfür ist meist Faulheit.

Egal - in jedem Fall lässt PostgreSQL eine wesentliche Aufgabe von anderen machen, um die sich MySQL selbst kümmert:

Wie werden Texte sortiert? Wie passen Symbole in Form von Buchstaben zueinander? Welches Symbole sind die passenden Kleinbuchstaben zu den Symbolen der Großbuchstaben? Für die Antwort dieser Fragen gibt es einen fachspezifischen Oberbegriff: Collations.

Wirft man Symbole, die irgendwie logisch zusammen passen, auf einen Haufen, so spricht man von Charsets. Ein Haufen an Symbolen kann zum Beispiel das lateinische Alphabet, das arabische Alphabet oder römische Zahlen sein.

Werden den jeweiligen Symbolen in dem Haufen bestimmte Bits und Bytes zugeordnet, spricht der Fachmann von Encoding. Die Begriffe Charset
und Encoding werden gerne durcheinandergeworfen. Zumal häufig Charsets und dazugehörige Encoding auch noch den gleichen Namen haben. In der Regel wird aus dem Zusammenhang schnell deutlich, was gemeint ist.

Encodings und Charsets begegnen uns in der Computerwelt immer und immer wieder. Die meisten Anwender bekommen davon jedoch wenig mit. Es funktioniert einfach. Doch das es funktioniert, ist manchmal ein kleines Wunder. Wer hier bei Datenbanken nicht aufpasst, kann sich schnell seine Datensätze irreparable zerschießen. Sowohl bei PostgreSQL als auch bei MySQL. Das gilt für alle mir bekannten Datenbanksysteme.

Ich wollte hier aber auf Collations hinaus:

Collations regeln zum einen die Sortierung. Also zum Beispiel ist dort geregelt, dass einem 'a' ein 'b' folgt. Aber auch, dass ein Großes 'A' gemeinsam mit dem kleinen 'a' sortiert wird oder erst alle Großbuchstaben, dann alle kleinen.

Daneben ist in einer Collation festgehalten, welche großen Buchstaben zu welchen kleinen passen. Also das 'A' und 'a' zusammengehören. Oder, wenn die Sprache es so vorgibt, vielleicht auch 'Y' und 'i'. Wie sieht ein 'ß' in Großbuchstaben dargestellt? Auch diese Frage wird hier beantwortet.

Als drittes ist hier festgelegt - was noch wie zusammengehört. Hier brauch ich nicht in die Ferne schweifen. Die Collation regelt ob ein 'ä' als 'ae' oder als 'a' ausgelegt wird.

MySQL hat diverse Collations, Charsets (Encodings) selber implementiert. PostgreSQL bedient sich hier einfach der Implementation der libc.

Als Experte für Lokalisierung und Globalisierung könnte ich ein ganzes Buch über dieses Thema schreiben. Vorallem, über die Beschwerden, die zu meinen MySQL-Zeiten bei mir auf dem Tisch landeten.

Allein für deutsch gibt es mindestens vier unterschiedliche Sortierregelungen. Die Schweizer und die Österreicher sortieren anders als die Deutschen, und in Deutschland gibt es dann auch noch gleich zwei verschiedene Regeln (wir nennen sie gern Dudensortierung und Telefonbuchsortierung).

Reichlich deutsche Anwender beschwerten sich über die deutsche Sortierung von
MySQL. Das war noch einfach für mich, weil ich konnte ihnen einfach die DIN
(5007) unter die Nase halten und zeigen, MySQL macht es richtig.

Bei einigen anderen Sprachen war es wesentlich schlimmer. Eine Sortiernorm konnen wir nicht ausfindig machen. Scheinbar gibt es hier keine Norm. Jeder Anwender hatte eine andere Theorie was richtig und was falsch ist. Belegen konnten die Anwender Ihre Theorien nicht.

Daneben gibt es Unicode und einen Unicode Standard der sich ebenfalls mit Sortierung, Groß-/Kleinschreibung und Buchstabenersetzung befasst.

Eines ist hier sicher - allen recht machen, kann man es einfach nicht.

Glaubt mir, das selbst implementieren bringt mehr Ärger als Nutzen. Auch bei PostgreSQL bekomme ich diese Art von Beschwerden, doch wenn ich hier sage, dafür ist libc verantwortlich, nehmen die meisten es einfach hin.

Der Anwender kann bei MySQL sogar seine eigenen Collation bauen. Wer gerne ein X nach dem D statt nach dem W sortieren möchte, kann dafür eine eigene Collation bauen.

Meine Erfahrung hier ist jedoch, dass 99,8 % der Anwender, die sich jemals mit dem Gedanken befasst haben, eine eigene Collation zu bauen, sich am Ende, aufgrund der Komplexität die das Bauen eine Collation mit sich bringt (vor allem im Unicode Bereich), doch mit dem zufrieden gegeben haben, was vorhanden
ist.

PostgreSQL bedient sich hier der libc. Bei PostgreSQL wird das Encoding / Collation einmalig, bei der Initialisierung der Intanz festgelegt und es gilt danach für den gesamten Cluster (Instanz) und kann nachträglich nicht geändert werden. Bei MySQL kann ich es nach belieben ändern, allerdings müssen Indexe, die hier auf Spalten liegen, bei denen der Charset geändert wurde, natürlich neu erstellt werden.

Mit PostgreSQL 9.1 lassen sich collations auch Spaltenweise festlegen.

Gemischte Collations in einer Instanz habe ich allerdings auch bei MySQL bislang eher selten gesehen. Die meisten Datenbanken nutzten durchgehend eine Collation. Mir fallen durchaus Anwendungsfälle ein, bei denen unterschiedliche Collations pro Instanz vorkommen könnten, doch genau bei diesen Anwendungen stellt sich mir dann auch gleich die Frage "will man das wirklich in einer relationalen Datenbank machen oder doch lieber NoSQL?".

Fortsetzung folgt ....


Donnerstag, 1. September 2011

Technische Unterschiede PostgreSQL, MySQL, MariaDB - Teil 4

Die vorangegangenen Teile dieser Artikelserie sind hier:
  1. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql.html 
  2. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_30.html 
  3. http://miraspostgresqlwelt.blogspot.com/2011/08/technische-unterschiede-postgresql_31.html 
Grobe architektonische Unterschiede

Prozess versus Thread

PostgreSQL ist prozessbasiert, MySQL ist threadbasiert.

REDO statt UNDO und REDO

PostgreSQL macht REDO, InnoDB UNDO und REDO.

Write Ahead Log

Sowohl bei PostgreSQL als auch InnoDB loggen Transaktionen.

PostgreSQL schaut hier, welche Blöcke sich geändert haben und übernimmt die gesamten Blöcken in das Log.

InnoDB überträgt nur die geänderten Bereiche eines geänderten Blocks in das Log.

Die Blöcke hier sind in der Regel bei PostgreSQL 8 kB und bei InnoDB 16 kB.

Was ist eigentlich eine Datenbank?

Was genau eine Datenbank ist, ist im SQL-Standard festgelegt. Der SQL Standard
spricht von Datenbank, Schema, Tabelle und Spalte.

Datenbanken sind unabhängig von einander. Objekte einer Datenbank dürfen keine Verbindungen zu Objekten anderer Datenbanken haben.

Schemas sind Objekte einer Datenbank.

Tabellen sind Objekte in einem Schema und Spalten Objekte innerhalb einer Tabelle.

Objekte eines Schemas dürfen Verbindungen zu Objekten in anderen Schemas haben.

Ich vergleich das gern mit Bauausschreibungen oder LaTex. Bauabschnitt, Los, Titel, Position bzw. Chapter, Section, Subsection, Subsubsection. Nur bei ganz großen Bauten ist die Unterteilung nach Losen erforderlich. Unterschiedliche Bauabschnitte kommen ebenfalls nur in sehr großen Bauvorhaben vor. Die meisten Bauausschreibungen wird im Bauabschnitt genau das eine Gebäude genannt, das gebaut wird, Lose gibt es nicht, sondern die Untergliederung der Ausschreibung enthält sofort Titel und Positionen.

Chapter in LaTeX habe ich persönlich nur für meine Diplomarbeit benötigt. Alle anderen Dokumente, die ich bis heute verfaßt habe, kamen mit Section, Subsection und Subsubsection aus.

Eine MySQL-Datenbank ist ein Schema. Eine Instanz ist eine Datenbank.

Der Begriff Datenbank führt bei PostgreSLern und MySQLern immer wieder gern zu Mißverständnissen. Wenn MySQLer von Datenbank sprechen, meinen Sie i.d.R. Schema. Wenn PostgreSQLer von Schema sprechen, meinen sie das, was in MySQL eine Datenbank ist.

Nicht jede PostgreSQL-Datenbank nutzt die Hirarchie Schema. Auch wenn PostgreSQL mehrere Datenbanken verwalten kann, so sieht man häufig nur eine einzige pro Instanz. Die Unterteilung in Schemas ist ein nettes Feature für Datenbanken mit vielen Tabellen. Es macht die Datenbank zum einen übersichtlicher und zum anderen erspart es eine Menge Tipparbeit bei der Vergabe von Zugriffsrechten (GRANT auf das Schema statt auf die einzelnen Tabellen).

Bei kleinen und mittleren Datenbanken wird bei Postgres aber gerne auf die Unterteilung in Schemas verzichtet und stattdessen lediglich Datenbank und dann
die Tabellen angelegt. Gibt es kein Schema, liegen die Tabellen bei PostgreSQL in dem Schema "public".

Bei MySQL ist der Begriff Datenbank ein Alias auf Schema. Der MySQLer legt
quasi ein Schema an, in welchem er die Tabellen ablegt. Aber - auch bei MySQL
findet man häufig nur ein Schema pro Instanz.

Daraus folgt, dass es in den meisten Fällen einfach eine Bezeichnung für eine Datensammlung ist, in der dann Tabellen liegen. Aufpassen sollte man hier nur,
wenn von einem zum anderen migriert oder repliziert wird.

Von MySQL zu PostgreSQL sollte man sich überlegen, ob man das MySQL Schema als PostgreSQL Datenbank anlegt und einfach auf die Hirarchiestufe Schema in Postgres verzichtet - oder ob man der PostgreSQL-Datenbank einen eigenen Namen gibt und das Schema als Schema dieser Datenbank implementiert. Bei den meisten Fällen, die mir hier bislang untergekommen sind, war ersteres sinnvoller.

Von PostgreSQL zu MySQL sollte man schauen, ob die Datenbank eine Schemaunterteilung hat. Wenn ja, sind die einzelnen Schemas in MySQLDB einzelne Datenbanken/Schemas. Wenn nicht, dann sollte der Datenbankname von PostgreSQL der Datenbank-/Schemaname von MySQL werden. Achtung hier bei der Verwendung von Tools. Immer wieder sehe ich hier, dass die MySQL Datenbank am Ende "public" heisst.

Fortsetzung folgt ....