Eure Performance-Erfahrungen mit datenlastigen Tabellen

[pax78]

Hallo an alle,

ich plane gerade eine sehr datenintensive Anwendung. Grundsätzlich geht es um das Tracking von bestimmten mobilen Apps. Dazu senden diese Apps alle 30 Sekunden eine Statusmeldung die in eine MySQL Tabelle eingetragen werden soll. Ich kalkuliere mit einer maximalen Userzahl von etwa 1000. Somit komme ich auf eine geschätzte Maximalzahl von etwa 3 Millionen Zeilen pro Tag. Die Tabelle hat um die 10 Spalten.

Die MySQL Serverhardware ist ein 2GHz Quadcore mit 8GB RAM.

Meine Fragen nun, da meine bisherigen SQL Erfahrungen alle eine Nummer kleiner waren:
Kann ein normales MySQL problemlos mit sowas umgehen?
Ab wieviel Zeilen muss ich bei Abfragen mit spürbaren Performanceverlusten rechnen?
Welche Bordmittelmöglichkeiten habe ich, das zu optimieren, ohne die Hardware aufzubohren?

Vielen Dank!

 

[akretschmer]

Hallo an alle,

ich plane gerade eine sehr datenintensive Anwendung. Grundsätzlich geht es um das Tracking von bestimmten mobilen Apps. Dazu senden diese Apps alle 30 Sekunden eine Statusmeldung die in eine MySQL Tabelle eingetragen werden soll. Ich kalkuliere mit einer maximalen Userzahl von etwa 1000. Somit komme ich auf eine geschätzte Maximalzahl von etwa 3 Millionen Zeilen pro Tag. Die Tabelle hat um die 10 Spalten.

Eher wenig. Für PostgreSQL. MySQL? Ich weiß nicht ... wie lange sollen die Daten in der Tabelle bleiben?

Meine Fragen nun, da meine bisherigen SQL Erfahrungen alle eine Nummer kleiner waren:
Kann ein normales MySQL problemlos mit sowas umgehen?
Ab wieviel Zeilen muss ich bei Abfragen mit spürbaren Performanceverlusten rechnen?

Hängt von den Abfragen ab.

Welche Bordmittelmöglichkeiten habe ich, das zu optimieren, ohne die Hardware aufzubohren?

MySQL gegen was richtiges tauschen.

 

[Distrilec]

Was mir so spontan dazu einfällt:
1. Wie lange willst du deine Tracking Daten in der Tabelle halten?
Musst du diese überhaupt über längere Zeit aufbewahren?
2. Weißt du wie man einen Index erstellt?
3. Kannst du Daten archivieren (Separate Tabelle die alle "alten" Daten enthält bzw. Partitionierung der eigentlichen Tabelle)?
4. Muss es unbedingt alle 30sec sein? Das ist echt ne riesen Menge an Daten...


Spätestens bei einem Full Table Scan geht dir der Storage in die Knie...

 

[akretschmer]

4. Muss es unbedingt alle 30sec sein? Das ist echt ne riesen Menge an Daten...

Es gibt GPS-Tracking-Systeme, die bei einer Fahrt mit dem Auto um eine Kurve gleich mal 6 Records erzeugen. Wenn Du ein paar Tausend Kunden hast (und das ist in der Branche noch nicht viel) kannst Du Dir ausmalen, was da zusammenkommt. Und Du mußt solche Daten mind. 3 Jahre aufheben. Dabei landest Du sehr schnell im Bereich von einigen Milliarden Datensätzen (ich kenne so ein System) und mit MySQL ganz schnell am Baum.

 

[pax78]

Erst mal danke für die schnellen Reaktionen.

Wie lange die Daten in der Table bleiben müssen ist eher ne Komfortfrage. Im Notfall wäre es möglich, die nur tagesaktuell zu halten. Das würde aber im Rest der Anwendung deutlich mehr Aufwand bedeuten. Wunschziel wäre ein Monat.

@akretschmer:
Die Abfragen wären mittelmäßig komplex. 3-4 Whereklauseln, ein oder zwei Group By. Im Prinzip ist das eine Kreuztabelle mit extra Daten, aber die m:n Relation wird softwareseitig gemacht, das heißt, beim Select stehen die relevanten IDs schon fest und kommen ins where.

PostgreSQL
Kannst du mir mehr darüber sagen? Insbesondere was den Lernaufwand angeht? Mit MySQL komm ich ganz gut klar, aber schon bei MS-SQL Server krieg ich regelmäßig Schreikrämpfe. Wo liegen die Vorteile gegenüber MySQL und wie eng ist die Syntax verwand? (Ich weiss, google erzählt mir das auch nach einiger Zeit, aber wenn du es anpreist, hast du vielleicht auch Lust, mehr darüber zu sagen).

@Distrilec
Ich kann und werde Daten archivieren, die Frage, die sich mir in der Planung stellt ist: wie oft werde ich zwingend müssen?
Ich kenn Indexe in Form von Keys als Primär und Fremdschlüssel. Aber dass man zur Performancesteigerung Indizieren kann hab ich bisher nur raunen gehört. Wenn du dazu einen guten Link hast, bitte her damit.
Partitionierung von Tabellen hab ich noch gar nicht gehört. Wie gesagt, ich habe das erste mal mit so einer Datenmenge zu tun. Bisher ging alles ganz gut mit der 0815 Standartinstallation

 

[akretschmer]

PostgreSQL
Kannst du mir mehr darüber sagen? Insbesondere was den Lernaufwand angeht? Mit MySQL komm ich ganz gut klar, aber schon bei MS-SQL Server krieg ich regelmäßig Schreikrämpfe. Wo liegen die Vorteile gegenüber MySQL und wie eng ist die Syntax verwand? (Ich weiss, google erzählt mir das auch nach einiger Zeit, aber wenn du es anpreist, hast du vielleicht auch Lust, mehr darüber zu sagen).

Alles, was reines SQL ist und MySQL kann, kann auch PostgreSQL. MySQL erlaubt Dinge, die gegen die SQL-Spec verstoßen, diese Dinge wirst Du in PostgreSQL nicht zum laufen bekommen. Beispiele:

• inkonsistente Daten, ein 0000-00-00 als DATE bekommst in PG nicht hin
• kapottes SQL, ein select col1, col2, col3 from foo group by col1 wird scheitern
• ein 'create table foo(i int check (i < 10))' wird Dir, Überraschung, bei einem i größer 10 einen Fehler bringen

Neben diesen 'Nachteilen' hast halt noch weitere Dinge, die anders sind:

• deutlich mehr Indextypen
• funktionale und partielle Indexe
• einen kostenbasierten Optimizer
• deutlich mehr Sprachumfang (z.B. Common Table Expressions, rekursive Abfragen)
• coole Features wie KNN-Index-Suche (google mal nach KNN), Range-Datentypen, Exclusion Constraints
• die Möglichkeit, innerhalb der DB mit Sprachen wie perl, plv8 und lolcode (und viele mehr) zu programmieren
• eine ganze Latte weiterer Datentypen und Funktionen dazu, z.B. HSTORE (key-Value), JSON(B), Netzwerktypen
• Erweiterungen wie z.B. PostGIS (rechnen mit 2,3 und 4 - dimensionalen Koordinaten in zig versch. Koordinatensystemen
• eine super gute Community
• die beste Lizenz, die es gibt: BSD

Das mal so auf die schnelle, Reihenfolge ist keine Wertung.

 

[Distrilec]

Partitionierung:
Die physikalische Trennung der Tabelle in kleinere Teile... Funktioniert allerdings nur wenn deine Auswertungen und Abfragen nur einen recht neuen Teil der Daten benötigen.

Zusammen mit ein paar richtig gesetzten Indizes (ich hoffe so ist richtig ) wirst du keinen merkbaren Unterschied zwischen 1 Datensatz und 200 Millionen Datensätzen sehen

 

[akretschmer]

Partitionierung:
Die physikalische Trennung der Tabelle in kleinere Teile... Funktioniert allerdings nur wenn deine Auswertungen und Abfragen nur einen recht neuen Teil der Daten benötigen.

Zusammen mit ein paar richtig gesetzten Indizes (ich hoffe so ist richtig ) wirst du keinen merkbaren Unterschied zwischen 1 Datensatz und 200 Millionen Datensätzen sehen

Depends. Die Partitionierung muß auch zur Abfrage passen. Und Partitionierung macht auch nur Sinn ab mind. 10 Millionen Rows per Kind-Tabelle.

 

[Distrilec]

Und Partitionierung macht auch nur Sinn ab mind. 10 Millionen Rows per Kind-Tabelle.

@akretschmer Die 10 Millionen hat man schnell bei 3 Millionen Datensätzen pro Tag... Nach Graf Zahl macht das nämlich 3,334 Tage

Die Partitionierung muß auch zur Abfrage passen.

Und deswegen schrieb ich auch:

Funktioniert allerdings nur wenn deine Auswertungen und Abfragen nur einen recht neuen Teil der Daten benötigen.

Dann hätte man nämlich nur die tagesaktuellen Daten abfragen können

Hängt aber wirklich stark von den Auswertungen ab.

 

[pax78]

Nochmal danke für die kompetenten Antworten. Ich werde ein wenig zum Thema PosgreSQL, Indexing und Partitionierung recherchieren und morgen oder übermorgen vermutlich noch ein paar präzisere Fragen stellen.

 

[ukulele]

Die Herausforderung wird nicht im Speichern der Datensätze sondern in der Auswertung liegen.

[...] aber die m:n Relation wird softwareseitig gemacht, das heißt, beim Select stehen die relevanten IDs schon fest und kommen ins where.

Das klingt für mich ziemlich unklug bzw. sogar problematisch. So musst du Daten aus einer Tabelle abfragen um dann dein WHERE mit IDs zu füttern um wieder die Datenbank abzufragen. Die DB selbst sollte damit schneller umgehen können als dein Client und muss nicht soviele Daten schicken.

Eventuell macht es Sinn, deine Daten z.B. Nachts zu "verarbeiten" um überflüssige Informationen weg zu werfen und Auswertungen oder dergleichen zu erstellen, der LHC speichert ja auch nicht alle Daten für immer. Das hängt aber ganz von Anwendungen ab.