Ankündigung

Einklappen
Keine Ankündigung bisher.

Relationale Datenbank Jährlich 30 Millionen neue Datensätze

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

  • Relationale Datenbank Jährlich 30 Millionen neue Datensätze

    Hi Leute,

    Ich suche einen Weg, wie ich vermeiden kann, dass die Latenzen der Abfrage, ob es einen bestimmten Datensatz schon git, zu groß werden. bzw. ich suche einen Weg das Problem der Einzelfragen zu umgehen.

    Die Tabelle, welche ich anfrage ist mit ca. 30 Millionen Datensätzen gefällt, wenn der Import fertig ist. Sie besteht nur aus 5 Relationsbezügen ( 5 mal Integer-Zahlen ), die auf andere Tabellen zeigen. Falls der Datensatz existiert, brauch ich die ID, um jene wieder als Relation für eine 3. Tabelle zu verwenden. Im Speicher vorhalten hatte ich auch schon probiert, was jedoch nach 2 Mio. Daten scheiterte.

    Hat jemand grundlegende Ansätze? Der Datenimport sollte sich im einstelligen Bereich von Stunden bewegen. Derzeit sind es knapp 2 Tage.
    Der Flaschenhals ist hauptsächlich, das Nachsehen ob bereits Daten existieren.


  • #2
    Welches DBMS?
    Welche Engine (MyISAM, InnoDB, ..)?
    Mit welcher Query passiert das derzeit?
    Hast du schon mit EXPLAIN laufen lassen?
    Welche Indizies (Indexe) hast du gesetzt?
    ...
    Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
    PHP.de Wissenssammlung | Kein Support per PN

    Kommentar


    • #3
      Korrektur! Nicht die Latenz zum Server drosselt sondern die Ausführungszeit. ( c.a 700ms bei 2 Mio. Einträgen )

      DBMS : PostgreSQL
      Abfrage über PDO aus PHP heraus
      Query z.B:
      Code:
      SELECT   nr
      FROM   (*unkenntlich gemacht*)_adressen
      WHERE   plz = 1 AND     ort = 1 AND     ortsteil = 1 AND     strasse = 1 AND     hausnummer = 1;
      Jeder Spaltenwert ist eine Referenz auf je eine andere Tabelle.

      Was ich noch nicht weiss:
      * was EXPLAIN ist
      * Engine muss ich suchen, so das zu finden ist.


      Muss auch dazu sagen, das das hier ein Testsystem ist auf dem ich nur eingeschränkte Rechte habe. Bin zudem in DBs noch recht unerfahren.

      Kommentar


      • #4
        Zitat von CodeDesigner Beitrag anzeigen
        Was ich noch nicht weiss:
        * was EXPLAIN ist
        Deine "nomale" Query mit EXPLAIN vorangestellt laufen lassen.
        Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
        PHP.de Wissenssammlung | Kein Support per PN

        Kommentar


        • #5
          FROM (*unkenntlich gemacht*)_adressen
          LOL : )

          https://use-the-index-luke.com/de/sql/vorwort Auf der Seite kannst du grundlegende Infos zur Indexsetzung bei Datenbanken nachlesen.
          tl;dr: In deinem Fall könnte es schon helfen einen Index anzulegen der die Felder plz, ort, ortsteil, strasse und hausnummer (gleiche Reihenfolge wie sie im Where-Statement abgefragt werden beachten) umfasst.
          Relax, you're doing fine.
          RTFM | php.de Wissenssammlung | Datenbankindizes | Stay fit

          Kommentar


          • #6
            Zitat von CodeDesigner Beitrag anzeigen

            Was ich noch nicht weiss:
            * was EXPLAIN ist
            Die Anzeige des Ausführungsplanes. Mittels EXPLAIN <hier deine Abfrage> bekommst den Plan angezeigt (mit Schätzwerten etc.) und mit EXPLAIN ANALYSE <hier Deine Abfrage> wird die Abfrage ausgeführt und u.a. reale Zeiten und reale Ergebnissmengen angezeigt. Das ist DIE Methode, um Performanceprobleme in PG zu untersuchen. Wenn Du Google nach "explaining explain" befragst, findest einige PDFs und Vorträge zu Explain.


            * Engine muss ich suchen, so das zu finden ist.
            PostgreSQL hat keine Engines wie MySQL. (naja, mit Ausnahmen, aber das lasse ich mal jetzt weg)

            Bei Deiner Abfrage würde ein Index über alle im Where vorkommenden Spalten massiv helfen. Allerdings müßte man prüfen, ob die Where-Condition immer so ist oder auch anders aussehen kann.
            Falls Du PostgreSQL 10 hast, könnte darüber hinaus eine zusätzliche Statistik über die Spalten plz, ort, ortsteil helfen, da diese funktional voneinander abhängig sind. Das wird (vermutlich) nicht diese Abfrage beschleunigen, wohl aber andere, komplexere Abfragen - falls Du solche hast.


            PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

            Kommentar


            • #7
              Ich glaub ich hab eine gute Lösung gefunden.
              Da ich ja diese Daten Zeilenweise einlese, zum Glück aber sortiert, nach PLZ vorliegen habe, als CSV-Datei, kann ich immer einen Daten-Block ( Array ) einer PLZ im Speicher vorhalten. Ich muss halt die Primärschlüssel PHP-seitig zuweisen. Das Schreiben der SQL-Blöcke vermeidet zu viele Anfragen an die DB. Ich bilde da Teilweise DB-Typisches im Speicher ab, aber ich sehe keine andere sinnvolle Lösung.

              Kommentar


              • #8
                wenn die Daten als CSV vorliegen kannst die auch direkt via COPY einlesen. Geht gefühlt 1000 mal schneller.
                PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                Kommentar


                • #9
                  also um mal die Ausführungszeit zu messen, ich habe eine Tabelle mit folgendem Aufbau und 31 Millionen Records:

                  Code:
                  test=*# \d codedesigner;
                                                   Tabelle »public.codedesigner«
                     Spalte   |   Typ   | Sortierfolge | NULL erlaubt? |               Vorgabewert                
                  ------------+---------+--------------+---------------+------------------------------------------
                   id         | integer |              | not null      | nextval('codedesigner_id_seq'::regclass)
                   plz        | bigint  |              |               |
                   ort        | bigint  |              |               |
                   ortsteil   | bigint  |              |               |
                   strasse    | bigint  |              |               |
                   hausnummer | bigint  |              |               |
                  Indexe:
                      "idx_codedesigner" btree (plz, ort, ortsteil, strasse, hausnummer)
                  
                  test=# select reltuples from pg_class where relname = 'codedesigner';
                    reltuples  
                  -------------
                   3.09994e+07
                  (1 Zeile)
                  Deine Abfrage braucht 0.119ms:

                  Code:
                  test=*# explain analyse select * from codedesigner where plz = 4211 and ort = 10815 and ortsteil = 5432 and strasse = 98765 and hausnummer = 23456;
                                                                             QUERY PLAN                                                           
                  --------------------------------------------------------------------------------------------------------------------------------
                   Index Scan using idx_codedesigner on codedesigner  (cost=0.56..8.59 rows=1 width=44) (actual time=0.076..0.076 rows=0 loops=1)
                     Index Cond: ((plz = 4211) AND (ort = 10815) AND (ortsteil = 5432) AND (strasse = 98765) AND (hausnummer = 23456))
                   Planning time: 4.507 ms
                   Execution time: 0.119 ms
                  (4 Zeilen)
                  
                  test=*#
                  PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                  Kommentar


                  • #10
                    Zitat von akretschmer Beitrag anzeigen
                    wenn die Daten als CSV vorliegen kannst die auch direkt via COPY einlesen. Geht gefühlt 1000 mal schneller.
                    Das geht auch mit relationalen Bezügen?
                    Da müsste auf der DB-Ebene auch nur wieder mit Logik geschaut werden ob es bestimmte Datensätze gibt. Da die DB aber mit der Festplatte und nicht mit Speicher arbeitet wäre das wohl eher 100 mal langsamer.

                    Kommentar


                    • #11
                      Hrm. Ich weiß nicht genau, was Du machst.
                      PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                      Kommentar


                      • #12
                        Ich abe es so verstanden, dass die CSV Daten nicht bedingungslos eingelesen werden sollen, sondern vor dem Insert auf Dubletten geprüft werden soll.

                        Ich würde wahrscheinlich mit einer Puffertabelle zum Einlesen arbeiten (mit copy) und von da aus möglichst elegant z.B. eine Millionen Blöcke in die DB schieben.
                        Vielleicht geht das halbwegs flott und geräusch los mit Except Konstruktionen, vielleicht auch per Procedure, die PK Violations einfach (Ver)schluckt, falls hinreichend sicher ist, dass nur wenige Dubletten da sind. Vielleicht auch einfach die Schnittmenge aus Bestand und Puffertabelle bilden und vorm Masseninsert die Schnittmenge aus der Puffertabelle löschen.



                        Aber vielleicht hab ich das Problem auch falsch verstanden.

                        Kommentar


                        • #13
                          Die Datenbank bzw. Tabellen sind auf Relationen aufgebaut. Hat 2 Tabellen mit je 30 Mil. Einträgen und die 7 relations-Tabellen schwanken von 1 - 500.000 Einträgen.
                          Die CSV-Daten werden nacheinander eingelesen. Dubletten sind eher extrem selten.
                          Ich habe durch PHP-Seitiges Cache-Magagement und Aufteilung in Phasen einen unglaublichen Datendurchsatz erreicht. Der Import läuft jetzt von 2000 - 20.000 Datensätze pro Sekunde. Im Mittel ca. 4000 DS/sec . Vor der Optimierung waren es 20 - 400 DS/sec, was aber auch von der bereits eingelesenen Menge abhängt.

                          Beim Testen hatte ich 1 Mil. DS in 8 Min in der DB.

                          Ich hab das so organisiert:
                          Die DB-Klasse schreibt große Tabellen bei den ständig Insert ausgeführt wird alle Daten Blockweise zu je 1000. Ist der DS fertig wirf immer der Rest mit meine flush-Funktion weggesendet. Das gleiche mache ich ähnlich in der Import.php. Da werden unabhängig davon die Daten Blockweise auf Grundlage der Postleitzahl vorgehalten um den schnellen Check im RAM auszuführen.

                          Doppelte Einträge werden vermieden, weil ich mit auch Blockweise Daten aus der DB in den Speicher hole. Das hat den Vorteil, dass ich umfassend testen und ggf. auch mal einen Vorgang abbrechen kann um danach fortzufahren.

                          Das war ne Hammer Arbeit.
                          Ich hoffe es ist für Außenstehende grob nachvollziehbar.

                          Ich denk mal, dass es Langfristig besser ist, sich mit direkter Datenbankprogrammierung zu befassen, da sicher noch einige rauszuholen ist.

                          Kommentar


                          • #14
                            Zitat von CodeDesigner Beitrag anzeigen
                            Das war ne Hammer Arbeit.
                            Ich hoffe es ist für Außenstehende grob nachvollziehbar.

                            Ich denk mal, dass es Langfristig besser ist, sich mit direkter Datenbankprogrammierung zu befassen, da sicher noch einige rauszuholen ist.
                            Nun mit dem letzten Satz hast Du Deine Eingangsfrage wohl selber beantwortet.

                            Auch wenn es grob nachvollziehbar ist, was du machst. Im Detail hab ich es nicht verstanden. Welche Daten fließen wo hin?
                            Ich denke dass man solch einen Massenimport mit einer handvoll SQL Statements sehr schnell erledigen kann. Auch mit Prüfungen wie Dublettencheck etc.. Das ist zunächst reines SQL. Wenn es so läuft, wie man möchte kann man es in eine Procedure packen und von irgendwo aufrufen.

                            Kommentar

                            Lädt...
                            X