Ankündigung

Einklappen
Keine Ankündigung bisher.

datenbankdesign - server .csv abfragen, werte speichern

Einklappen

Neue Werbung 2019

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • datenbankdesign - server .csv abfragen, werte speichern

    Hi!

    ich hätte gern eure Meinung gewusst was das Datenbankdesign angeht..
    Folgendes: ich möchte von mehreren servern (von 59 bis 100 und mehr..) eine csv datei holen und diese in einer datenbank speichern.

    die daten werden jeweils pro server im schnitt alle 10 bis 30 sec aktualisiert (d.h. alle 10 bis 30sec wird ein datensatz in die datenbank gespeichert)
    gehen wir mal von einem server aus so sind dies bei einem intervall von 30sec 3000 datensätze. bei 100 servern also täglich 300.000. (und nach 10 tagen dementsprechend viel...)

    sinnvollerweise wären es dann pro server eine t_data tabelle..

    was meint ihr? was würdet ihr ggf. anders machen?


    t_servers (id, name, ip)
    t_data_serverid(id, value1 value2, errormsg, etc..)

    eben für jeden server eine eigene data tabelle..

  • #2
    Also ich sehe jetzt keinen Grund pro Server eine Tabelle zu machen... Mit entsprechen guten Indices, findet die Datenbank die Daten auch schnell.

    Code:
    CREATE TABLE server (
     server_id INT NOT NULL,
     name VARCHAR(60),
     ip VARCHAR(60),
     PRIMARY KEY (server_id)
    )
    
    CREATE TABLE server_daten (
     daten_id INT NOT NULL,
     server_id INT NOT NULL,   // eventuell FOREIGN KEY je nach MySQL Storage Engine
     wert1 INT,
     wert2 varchar(100),
     ...
     PRIMARY KEY (daten_id)
    )
    
    CREATE INDEX sx_server_daten_01 ON server_daten(server_id,daten_id);
    Wichtig ist, dass die Tabelle server_daten nicht mit unnötigen Spalten "aufgeblasen" wird. Stichwort "Faktentabellen".

    Soweit mal meine Idee.

    Grüße
    Thomas

    Kommentar


    • #3
      Wichtig ist, dass die Tabelle server_daten nicht mit unnötigen Spalten "aufgeblasen" wird. Stichwort "Faktentabellen".
      verteh ich jetzt nicht ganz faktentabelle = tabelle mit ganz vielen messwerten?

      nun in meinem falle könnten es bis zu 15 spalten werden.. anfang sind es 10
      PHP-Code:
      daten_id (INT), 
      server_id (INT), 
      last_entry_DB1(varchar!!), 
      lastentry_DB2(varchar!!), 
      lastentry_DB3(varchar!!), 
      Programm1 Version (varchar(25), 
      Programm2 Version (varchar(25), 
      Anzahl Nutzer (UNSIGNED SHORT INT), 
      Datenmenge UL (UNSIGNED INT), 
      Datenmenge DL (UNSIGNED INT),
      errormsg(varchar//z.b. server nicht erreichbar. datendatei nicht gefunden, socket konnte nicht erstellt werden etc... 
      DB1 bis 3 sind die verschiedenen datenbanken auf dem server, von denen daten ausgelesen werden und als csv gespeichert (diese datei hole ich vom server ab..)
      last_entry_DBX ist i.D.R. ein Datum. wenn kein Wert vorhanden ist steht drin warum (z.b. Programm1 läuft auf diesem server nicht.. etc.)
      der rest ist ja selbst erklärend.

      von daher.. optimal ist die tabelle nicht.. (ein Feld DATE wäre schöner aber dann kann ich zusätzliche infos zu diesem feld nicht mehr speichern..)

      daher finde ich seperate tabellen fast besser... (bei einer datenmenge von über 5 Millionen Einträgen (24h*60min*2 (alle30sec) *60 server *30 Tage) wäre ja nach einem Monat die Datenbank ziemlich dicht befüllt....

      Kommentar


      • #4
        Faktentabelle ist ein Begriff aus dem Data-Warehouse-Bereich. Da wird eine Faktentabelle mit vielen Dimensionstabellen definiert um trotz der immensen Datenmenge einen möglichst schnellen und flexiblen Zugriff auf die Daten zu garantieren.

        Code:
        daten_id (INT), 
        server_id (INT), 
        last_entry_DB1(varchar!!), 
        lastentry_DB2(varchar!!), 
        lastentry_DB3(varchar!!), 
        Programm1 Version (varchar(25), 
        Programm2 Version (varchar(25), 
        Anzahl Nutzer (UNSIGNED SHORT INT), 
        Datenmenge UL (UNSIGNED INT), 
        Datenmenge DL (UNSIGNED INT),
        errormsg(varchar),
        createtime
        Die fett markierten Spalten lassen sich vielleicht noch normalisieren und die Spalte errormsg (sofern nicht häufig ein Fehler auftritt) gleich in eine zusätzliche Tabelle 1:1 verknüpfen. Ziel ist es möglich kurze Spalten (VARCHAR) zu benötigen. Dann wird die Datenmenge kompakt gehalten.

        Ergänzung:
        Eventuell ist eine Partitionierung nach "Datum" siehe meine neue Spalte createtime sinnvoll. Also eine Trennung nach Monat, Quartal oder Jahr etc.)

        Soweit mal ein paar Ideen.

        Grüße
        Thomas

        Kommentar


        • #5
          ja createtime kommt auf jedenfall noch mit in die tabelle mit rein...

          errormsg sollte so gut wie immer leer bleiben...


          ich halte es aber aufgrund der datenmenge trotzdem sinnvoller jeden server in eine eigene tabelle zu packen... (wenn ich z.b. nur zwei drei server auswerten muss brauch ich nicht die ganze t_data zu durchsuchen... )

          was die normalisierung angeht:

          die last_entry einträge würde ich nicht in eine seperate tabelle packen (da gibts einfach zuviele unterschiedliche timestamps...)

          die programmversion könnte man schon in eine seperate packen.. so wäre es ein varchar weniger... (aber wenn ich sowieso jeden server eine eigene t_data gebe eigentl überflüssig)

          Kommentar


          • #6
            Frage: Wie sollen die Daten später ausgewertet werden?
            Eher pro server_id oder für einen bestimmten Zeitraum? Wenn für einen bestimmten Zeitraum, dann brauchst Du eventuell einen JOIN über viele Tabellen oder viele UNION Verknüpfungen. Dann wird es eventuell auch nicht performanter.

            Grüße
            Thomas

            Kommentar


            • #7
              1.) immer den letzten eintrag, i.d.R. alle server

              2.) wobei es ich gerne ein formular hätte mit dem ich sagen kann "datensätze der eltzten x tage bei server 1 und 2 oder bei server 1 bis 10"

              vorrangig punkt 1

              Kommentar


              • #8
                Zitat von taurus Beitrag anzeigen
                1.) immer den letzten eintrag, i.d.R. alle server
                Dann tritt der Fall ein, bei dem Du immer viele JOINs oder UNION brauchst, wenn pro Server eine Tabelle existiert. Wäre zu testen, was schneller ist. Ein SQL mit ca. 50-100 JOINs oder eine grosse Tabelle. Ich vermute die große Tabelle ist dann schneller.

                Grüße
                Thomas

                Kommentar


                • #9
                  mhh aber eine tabelle mit 5 millionen einträgen.. kann das noch gut gehn?

                  bzw.. müsste ich dann jede woche einträge älter als 30tage in ein archiv schieben.. so müsste das ja gehn

                  Programm1 Version (varchar(25),
                  Programm2 Version (varchar(25),
                  werde ich dann in einer extra tabelle deklarieren und hier nur die programm_id benutzen

                  bei lastentry lohnt es sich nicht oder was meinst du? da hätte ich ja dann eine tabelle die massig timestamps hat.. einer anders als der andere..

                  Kommentar


                  • #10
                    Summa summarum: Ob es 100 Tabellen mit je 100.000 Sätzen sind oder 1 Tabelle mit 10 Millionen bleibt in der Datenmenge die zu verwalten sind gleich. Am besten mal mit generierten Testdaten ausprobieren und mit EXPLAIN die Queries auf Geschwindigkeit abklopfen.

                    Die "alten" Daten in eine Archiv-Tabelle schieben ist sicherlich sinnvoll.

                    Der Primary Key sollte vielleicht big_int sein, sollst kommt Autoincement zu früh an seine Grenzen, wenn "jahrelang" Daten eingefügt werden und der PK immer größer wird.

                    Grüße
                    Thomas

                    Kommentar


                    • #11
                      was bewirkt denn der foreign key (außer dass ich nur datensätze einfügen kann deren server_id existiert) auch etwas hinsichtlich performance?

                      was "EXPLAIN" angeht.. zeigt mir das nicht die zeit an die das query gebraucht hat? (bzw. was beudete das "keymap" in der letzten zeile??)

                      table type possible_keys key key_len ref rows Extra
                      et ALL PRIMARY NULL NULL NULL 74
                      do ALL PRIMARY NULL NULL NULL 2135
                      et_1 ALL PRIMARY NULL NULL NULL 74
                      tt ALL AssignedPC, NULL NULL NULL 3872
                      ClientID,
                      ActualPC
                      range checked for each record (key map: 35)
                      ich werde mir dann morgen mal ein testcase bauen.. bin gespannt was dabei rauskommt

                      übrigens.. der primary key in server_daten wird ja größer und größer (auch wenn ich die alten daten archiviere)... irgendwann muss ich die db dann aber aufräumen (und den PK wieder auf null setzten..)
                      wie funktioniert das?
                      bzw. vgl. dieser thread
                      was ist die beste möglichkeit sagen wir alle 2 Monate 10 Millionen Daten zu löschen(oder zu archivieren)

                      so das wärs schonmal danke für die tipps

                      Kommentar


                      • #12
                        Zitat von taurus Beitrag anzeigen
                        was bewirkt denn der foreign key (außer dass ich nur datensätze einfügen kann deren server_id existiert) auch etwas hinsichtlich performance?
                        Wird ein FOREIGN KEY (FK) für MySQL angelegt, sichert dies zum Einen die Datenintegrität und zweitens legt MySQL automatisch auf die Spalte (Spalten) des FK einen Index. Damit hilft es auch der Abfragegeschwindigkeit.
                        Natürlich InnoDB vorausgesetzt. Mit MyIsam gibt es keine FK.

                        Zitat von taurus Beitrag anzeigen
                        was "EXPLAIN" angeht.. zeigt mir das nicht die zeit an die das query gebraucht hat? (bzw. was beudete das "keymap" in der letzten zeile??)
                        EXPLAIN zeigt den Zugriffspfad, wie "einfach" MySQL die Daten findet. Die ca. Verarbeitungsgeschwindigkeit wird dann ermittelt, wenn die Abfrage tatsächlich ausgeführt wird. Am Besten mit einem kleinen PHP-Skript die Zeit messen.
                        Code:
                        mysql> EXPLAIN SELECT * FROM ..
                        +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
                        | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra |
                        +----+-------------+------------+------+---------------+----------+---------+-------+------+-------+
                        ...
                        Wie EXPLAIN zu interpretieren ist, steht hier:
                        MySQL :: MySQL 5.1 Referenzhandbuch :: 7.2.1 EXPLAIN-Syntax (Informationen über ein SELECT erhalten)

                        Du kannst die Ausgabe von EXPLAIN gut formatiert oder als Bild zeigen, dann schaue ich mal drüber.


                        Zitat von taurus Beitrag anzeigen

                        ich werde mir dann morgen mal ein testcase bauen.. bin gespannt was dabei rauskommt

                        übrigens.. der primary key in server_daten wird ja größer und größer (auch wenn ich die alten daten archiviere)... irgendwann muss ich die db dann aber aufräumen (und den PK wieder auf null setzten..)
                        wie funktioniert das?
                        bzw. vgl. dieser thread
                        was ist die beste möglichkeit sagen wir alle 2 Monate 10 Millionen Daten zu löschen(oder zu archivieren)
                        Eine andere Idee wäre es, die Tabelle "server_daten" beim Archivieren einfach umzubenennen in beispielweise "server_daten_2010Q1" und eine neue Tabelle mit dem letzten PK neu anlegen. Geht am Schnellsten.

                        Es gibt ja mehrere Möglichkeiten zum Löschen

                        a) INSERT INTO server_daten_2010Q1.... SELECT ... FROM server_daten
                        plus anschließendem DELETE FROM server_daten

                        b) SELECT INTO OUTFILE und anschließend LOAD DATA in
                        server_daten_2010Q1 mit anschließendem DELETE FROM server_daten

                        c) Tabellen umbenennen (siehe oben)


                        Du kannst ja mal berechnen, wie lange es dauert, bis Du an die Max-Grenze eines BIG_INT PK kommst.

                        Grüße
                        Thomas

                        Kommentar


                        • #13
                          Mal ne Frage Thomas, wie kann man denn "einfach" eine MySQL-Tabelle umbenennen?

                          Kommentar


                          • #14
                            @hpf

                            Laut Handbuch mit

                            Code:
                            RENAME TABLE tbl_name TO new_tbl_name
                            [, tbl_name2 TO new_tbl_name2] ...
                            Zu bedenken wäre natürlich auch die abhängigen Tabellen umzubenennen. Dass sollte natürlich auch mal getestet werden.

                            Grüße
                            Thomas

                            Kommentar


                            • #15
                              Achso, da lag ich falsch. Aber bei Datenbanken geht es nicht so einfach.

                              Kommentar

                              Lädt...
                              X