Performance Bei Riesen Tabelle

[NeoPrince]

Hi

Ich Habe Eine Tabelle mit momentan ca. 50 milionen Logdaten -> ca 5GB und das Werden Täglich mehr.
Hab schon Diverse optimierungen vorgenommen die Im Internet vorgeschlagen werden, Engine auf Inno DB umgestellt, Datenbank läuft in einer Ramdisk, Optimierung der Datentypen in der Tabelle etc.
Ich hab mich jetzt Dazu Entschlossen Das Problem zu beheben indem ich Eine Strukturänderung vornehme, Kleinere Tabellen sollten ja auch mehr Performance bringen.
Die Tabelle Sieht momentan volgendermaßen aus:

Stringnummer; Logdaten(4 felder mit messdaten); Timestamp

Jeder String bringt ca. alle 5 minuten einen eintrag in die Tabelle.
es gibt momentan ca 1000 Strings die in ca. 30. Anlagen zusammengeschlossen sind. das heißt in 5 minuten kommen ca 1000 neue Datensätze dazu.
Die Überlegung war nun:
Entweder ich lege für jeden Monat eine Neue Log Tabelle an,
Oder Ich lege für jede Anlage eine Neue Tabelle An,

Die Frage Ist nun, ob von euch einer Schon Erfahrung mit so großen Datenmengen hat und ob es für soetwas evtl bessere vorgehensweisen gibt. Ich bin für alle verbesserungsvorschläge offen.

 

[ukulele]

Erfahrungen kann ich leider nicht bieten befasse mich mit kleinen Datenbeständen. Generell bezweifle ich aber das viele kleine Tabellen die Sache besser machen denn SQL ist eigentlich dafür gemacht große Datenmengen zu speichern und zu "organisieren", zumindest nach meinem Verständniss.

Was genau ist denn das Performance Problem, das Schreiben oder das Lesen der Daten?

Ich hätte noch Ideen:
1) Index, wirst du vermutlich schon nutzen
2) überflüssige alte Daten löschen oder archivieren oder nur noch die Auswertung speichern (ähnlich wie im CERN) aber das ist natürlich immer eine Frage des Anwendungsgebietes
3) Partitioning - habe ich mich noch nicht so mit befasst aber nach meinem Verständniss kann man damit bei großen Datenmengen Performance gewinnen
4) Anderes DBMS - MySQL ist sicherlich viel genutzt und auch in großen Unternehmen a la Google einsatzfähig. Aber für ich kann mir vorstellen das PostgreSQL hier noch mehr zu bieten hat

 

[akretschmer]

Hi

Ich Habe Eine Tabelle mit momentan ca. 50 milionen Logdaten -> ca 5GB und das Werden Täglich mehr.
Hab schon Diverse optimierungen vorgenomme die Im Internet vorgeschlagen werden, Engine auf Inno DB umgestellt, Datenbank läuft in einer Ramdisk, Optimierung der Datentypen in der Tabelle etc.
Ich hab mich jetzt Dazu Entschlossen Das Problem zu beheben indem ich Eine Struckturänderung vornehme, Kleinere Tabellen sollten ja auch mehr Performance bringen.
Die Tabelle Sieht momentan volgendermaßen aus:

Stringnummer; Logdaten(4 felder mit messdaten); Timestamp

Jeder String bringt ca. alle 5 minuten einen eintrag in die Tabelle.
es gibt momentan ca 1000 Strings die in ca. 30. Anlagen zusammengeschlossen sind. das heißt in 5 minuten kommen ca 1000 neue Datensätze dazu.
Die Überlegung war nun:
Entweder ich lege für jeden Monat eine Neue Log Tabelle an,
Oder Ich lege für jede Anlage eine Neue Tabelle An,

Die Frage Ist nun, ob von euch einer Schon Erfahrung mit so großen Datenmengen hat und ob es für soetwas evtl bessere vorgehensweisen gibt. Ich bin für alle verbesserungsvorschläge offen.

Was ist denn genau das Problem? Dazu hast du nix gesagt. 50 Milluonen sind kein Thema, ich betreue eine Anwendung, wo aller 2 Tage etwa so viel reinkommt, seit Jahren. Ist aber, siehe auch @ukulele , PostgreSQL. Also, was ist Dein Problem? Explain ist bekannt?

 

[NeoPrince]

Das Problem sind Abfragen auf diese Tabelle.
Die Dauern jeh nach dem wie sie gejoint sind mehrere Minuten. Das reinschreiben ist kein Problem.
Explain sagt mir nichts Googelgoogel...

zu ukulele
1: Indizes hab ich
2: Das Mit dem Nur Auswertung Speichern ist ne gute idee. das Kann ich teilweise machen.
3: Partitioning sagt mir auch nichts muss ich mal googeln
4: Anderes DBMS ist leider keine Option für das Projekt aber ich werd es mir für die Zukunft merken

Gruß und danke für die Vorschläge

 

[akretschmer]

Das Problem sind Abfragen auf diese Tabelle.
Die Dauern jeh nach dem wie sie gejoint sind mehrere Minuten. Das reinschreiben ist kein Problem.
Explain sagt mir nichts Googelgoogel...

zu ukulele
1: Indizes hab ich
2: Das Mit dem Nur Auswertung Speichern ist ne gute idee. das Kann ich teilweise machen.
3: Partitioning sagt mir auch nichts muss ich mal googeln
4: Anderes DBMS ist leider keine Option für das Projekt aber ich werd es mir für die Zukunft merken

Gruß und danke für die Vorschläge

Also, Urlaub ist zu Ende, mit dem Handy zu antworten ist nicht sooo der Brüller. Zu Deinen Fragen:

Partitioning: macht Sinn, wenn die Abfragen dazu passen. Stell Dir vor, Du fragst ab, ob eine Zahl geteilt durch 3 einen Rest von X mit X in (0,1,2) hat. Das ist Deine Where-Condition. Da kannst Du Deine Tabelle foo in 3 Teiltabellen splitten, foo_0, foo_1 und foo_2. Dazu noch einen INSERT-Trigger. In PG mal als Schnellschuß so:

test=# create table foo (id serial primary key, value int);
NOTICE:  CREATE TABLE will create implicit sequence "foo_id_seq" for serial column "foo.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
Time: 9,728 ms
test=*# create table foo_0 (check (value %3 = 0)) inherits (foo);
CREATE TABLE
Time: 1,112 ms
test=*# create table foo_1 (check (value %3 = 1)) inherits (foo);
CREATE TABLE
Time: 1,138 ms
test=*# create table foo_2 (check (value %3 = 2)) inherits (foo);
CREATE TABLE
Time: 1,261 ms
test=*# create or replace function insert_foo() returns trigger as $$begin execute 'insert into foo_' || (new.value % 3)::text || ' (value) values(' || new.value || ')'; return null; end; $$ language plpgsql;
CREATE FUNCTION
Time: 0,401 ms
test=*# create trigger trg1 before insert on foo for each row execute procedure insert_foo();CREATE TRIGGER
Time: 0,263 ms

Wenn wir jetzt eine Zahl eingeben:

test=*# insert into foo (value) values(4711);
INSERT 0 0
Time: 0,742 ms
test=*# select * from foo;
 id | value
----+-------
  2 |  4711
(1 row)

Time: 0,372 ms
test=*# select * from only foo;
 id | value
----+-------
(0 rows)

Time: 0,202 ms
test=*# select * from only foo_1;
 id | value
----+-------
  2 |  4711
(1 row)

so landet diese in der Teiltabelle foo_1, wie oben gezeigt. Bei einer Abfrage, welche einer der Partitionierungsregel passende WHERE-Condition hat, wird nun nur foo (immer) und foo_X abgefragt, was man schön via EXPLAIN sehen kann:

test=*# explain select * from foo where value % 3 = 1;
  QUERY PLAN
-----------------------------------------------------------------------
 Result  (cost=0.00..42.10 rows=12 width=8)
  ->  Append  (cost=0.00..42.10 rows=12 width=8)
  ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=8)
  Filter: ((value % 3) = 1)
  ->  Seq Scan on foo_1 foo  (cost=0.00..42.10 rows=11 width=8)
  Filter: ((value % 3) = 1)
(6 rows)

Also, foo_0 und foo_2 werden nicht durchsucht, nur foo und foo_1, weil die DB 'weiß', daß nur dort Values mit der Condition drin sein können, nicht in foo_0, nicht in foo_2.

Achtung: das gezeigte ist in PostgreSQL gemacht.

Fazit:

• Partitionierung verteilt Daten auf unterschiedliche Tabellen, nach außen 'sehe' ich meine Haupttabelle (welche aber eigentlich immer leer ist). Nur mit dieser arbeite ich normalerweise aber. Die abgeleiteten Kindtabellen fasse ich 'normalerweise' nicht direkt an. (es gibt Ausnahmen)
• macht nur Sinn, wenn ich Abfragen habe, die die Partitionierung nutzen können.
• lohnt sich erst ab mind. 1 - 10 Millionen Records pro Kindtabelle (Faustregel in PG)
• EXPLAIN ist ein Werkzeug, um zu sehen, wie die DB die Abfrage ausführt. MySQL kann das zwar auch, ist aber sehr eingeschränkt.
• MySQL und Partitionierung? Ich weiß es nicht
• Da Du beharrlich verschweigst, wie Deine Abfragen konkret aussehen, können wir auch nicht mehr weiter helfen