Ergebnis 1 bis 9 von 9

Thema: Insert Nested Set

  1. #1
    _rr
    _rr ist offline Neuer Benutzer
    Registriert seit
    07.07.2010
    Beiträge
    9

    Standard Insert Nested Set

    Hallo,
    trotz langem Suchens und Probierens habe ich bisher leider keine Lösung finden können für mein Problem.
    Ich habe eine Nestedset-Tabelle mit erst 8000 Datensätzen. Nun wird über eine Funktion zu den meisten Datensätzen ein Unterpunkt(Ast) unterhalb hinzugefügt. D.h. es laufen pro neuem Datensatz zwei Updates und ein Inserts. Die Funktion liest die notwendigen Daten aus zwei Tabellen aus und ruft eine Funktion auf, die dann unterhalb einfügt.
    So weit so gut, wenn ich das mit einem Datensatz mache geht das wunderbar schnell (ca. 10ms pro eingefügtem Datensatz). Wenn ich aber nun diese Funktion laufen lasse laufen die ersten Datensätze schnell(1 Datensatz pro Sekunde) und wird dann schnell langsamer bis zum fast Stillstand. Ich habe schon sämtliche Tuningtipps für den Postgresserver versucht, leider ohne Erfolg.

    Vielleicht hat jemand von euch eine Idee.

  2. #2
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von _rr Beitrag anzeigen
    Hallo,
    trotz langem Suchens und Probierens habe ich bisher leider keine Lösung finden können für mein Problem.
    Ich habe eine Nestedset-Tabelle mit erst 8000 Datensätzen. Nun wird über eine Funktion zu den meisten Datensätzen ein Unterpunkt(Ast) unterhalb hinzugefügt. D.h. es laufen pro neuem Datensatz zwei Updates und ein Inserts. Die Funktion liest die notwendigen Daten aus zwei Tabellen aus und ruft eine Funktion auf, die dann unterhalb einfügt.
    So weit so gut, wenn ich das mit einem Datensatz mache geht das wunderbar schnell (ca. 10ms pro eingefügtem Datensatz). Wenn ich aber nun diese Funktion laufen lasse laufen die ersten Datensätze schnell(1 Datensatz pro Sekunde) und wird dann schnell langsamer bis zum fast Stillstand. Ich habe schon sämtliche Tuningtipps für den Postgresserver versucht, leider ohne Erfolg.

    Vielleicht hat jemand von euch eine Idee.
    Glaskugel ist grad defekt. Vielleicht kannst Du helfen und Deine Funktion und Tabelle mal zeigen?


    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  3. #3
    _rr
    _rr ist offline Neuer Benutzer
    Registriert seit
    07.07.2010
    Beiträge
    9

    Standard der code dazu

    die Zieltabelle, enthält ca. 2500 Einzelbäume unter root, aber das ist ja eigentlich egal...

    Code:
    CREATE TABLE d_data.d_attribut
    (
      roid bigserial NOT NULL, -- ID
      tree_id integer, -- Baum ID
      rank integer NOT NULL, -- Ebene
      lft bigint NOT NULL, -- links
      rgt bigint NOT NULL, -- rechts
      comments character varying, -- Bemerkung, Kommentar
      y_m_obj_roid bigint, -- roid aus y_meta_object
      fadd_user character varying DEFAULT "current_user"(), -- Benutzer des Eintragens - first add user
      fadd timestamp with time zone DEFAULT now(), -- Zeitstempel des Eintragens - first add
      k_objit_char_roid bigint,
      attribut character varying,
      CONSTRAINT pk_attribut_neu PRIMARY KEY (roid),
      CONSTRAINT k_objit_char_d_att FOREIGN KEY (k_objit_char_roid)
          REFERENCES y_metadata.k_objitem_char (roid) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION,
      CONSTRAINT y_m_obj_d_att FOREIGN KEY (y_m_obj_roid)
          REFERENCES y_metadata.y_meta_object (roid) MATCH SIMPLE
          ON UPDATE NO ACTION ON DELETE NO ACTION
    )
    WITH (
      OIDS=FALSE
    );
    
    CREATE INDEX attindda
      ON d_data.d_attribut
      USING btree
      (attribut);
    
    
    CREATE INDEX index_k_objit_char
      ON d_data.d_attribut
      USING btree
      (k_objit_char_roid);
    
    
    CREATE INDEX index_rank
      ON d_data.d_attribut
      USING btree
      (rank);
    
    
    CREATE INDEX index_tree_id
      ON d_data.d_attribut
      USING btree
      (tree_id);
    
    
    CREATE INDEX index_y_m_obj
      ON d_data.d_attribut
      USING btree
      (y_m_obj_roid);
    
    
    CREATE INDEX indroidda
      ON d_data.d_attribut
      USING btree
      (roid);
    
    CREATE INDEX lftindexda
      ON d_data.d_attribut
      USING btree
      (lft);
    
    
    CREATE INDEX rgtindda
      ON d_data.d_attribut
      USING btree
      (rgt);
    Wie zu sehen ist, habe ich mittlerweile auf jede erdenkliche Spalte einen Index gelegt.

    Die Quelltabelle kann varieren, es sind aber normale Tabellen mit bis zu 40 Spalten und mehreren tausend Datensätzen, sie enthalten alle nur eine Primärschlüssel.

    Die Funktion die das alles erledigen soll:
    Code:
    
    CREATE OR REPLACE FUNCTION y_function.insert_d_attribut_b2til()
      RETURNS void AS
    $BODY$
    	DECLARE
    	ersterloop text;
    	zweiterloop text;
    	insertme text;
    	refcur record;
    	roid_par integer;
    	roid_koic integer;
    	roid_obj integer;
    	tabellenschema text;
    	tabellenname text;
    	methode  bigint;
    
    	refcur_plot record;
    	tabobj_roid integer;
    	attributaustabelle character varying;
    
    
    	
    	BEGIN
    	tabellenschema:='quellschema';
    	tabellenname:='quelltabelle';
    	methode := 233;--Vorklärung
    	ersterloop:='select column_name from information_schema.columns where table_schema=' || quote_literal(tabellenschema) || ' and table_name=' || quote_literal(tabellenname);
    	zweiterloop:='select attribut::integer from d_data.d_attribut where y_m_obj_roid=40 order by attribut::integer DESC';
    	/*welche spalten hat die tabelle?*/
    	for refcur in execute ersterloop  LOOP
    	
    	for refcur_plot in execute zweiterloop LOOP
    	
    	/* HIER WERDEN DIE PARAMETER GESUCHT */
    	roid_par:=roid from y_metadata.y_meta_parameter where (select split_part(ref_l, ' ## ',1))=tabellenname and (select split_part(ref_l, ' ## ',2))=refcur.column_name;
    		
    	roid_koic:= koic.roid from y_metadata.k_objitem_char koic inner join y_metadata.k_object_item koi on koic.k_obj_item_roid=koi.roid inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;
    	
    	roid_obj:=koi.m_ort_roid from y_metadata.k_object_item koi inner join y_metadata.k_item_ref kir on koi.k_item_roid=kir.roid where kir.meth_roid=methode and para_roid=roid_par;
    
    	
    	--for refcur_subplot in select * from d_data.d_attribut where lft>=(SELECT lft from d_data.d_attribut where attribut::integer=refcur_plot.attribut) and rgt<=(SELECT rgt from d_data.d_attribut where attribut::integer=refcur_plot.attribut) LOOP
    
    	tabobj_roid:=roid from d_data.d_attribut where y_m_obj_roid=roid_obj and k_objit_char_roid is null and lft>=(SELECT lft from d_data.d_attribut where attribut='' || refcur_plot.attribut || ''  and k_objit_char_roid is null) and rgt<=(SELECT rgt from d_data.d_attribut where attribut='' || refcur_plot.attribut || ''  and k_objit_char_roid is null);
    	
    	/*diese Abfrage muss an jede Tabelle angepasst werden, um den richtigen Wert zum Plot zu finden */
    
    	execute 'select ' || refcur.column_name || ' from ' || quote_ident(tabellenschema) || '.' ||quote_ident(tabellenname) || ' where num=' || refcur_plot.attribut || ';' into attributaustabelle;
    	if attributaustabelle is null then
    	
    	raise notice 'attributtabelle is null, überspringe';
    	else
    	attributaustabelle:='''' || attributaustabelle || '''';
    
     	insertme:='select y_function.x_da_attribut_unterhalb_rein(''d_data'', ''d_attribut'',' || tabobj_roid || ',null, null,' || roid_obj || ', ' || roid_koic || ', ' || attributaustabelle || ');';
    
    	raise notice 'bin bei spalte: % num: % wert: %',refcur.column_name,refcur_plot.attribut,attributaustabelle;
    	--raise notice '%',insertme;
    	execute insertme;
    
    	end if;
    
    	
    	end loop; --refcur_plot
    
    	end loop;--refcur
    
    
    	END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;
    Es ist sicherlich etwas schwer zu verstehen, da die Funktion, die nachher aufgerufen werden soll mit Parametern gefüttert werden muss, die aus verschiedenen Tabellen kommen. Aber hier noch die augerufene Nestedset-Funktion die unterhalb einfügt:

    Code:
    CREATE OR REPLACE FUNCTION y_function.x_da_attribut_unterhalb_rein(character varying, character varying, bigint, bigint, character varying, bigint, bigint, character varying)
      RETURNS void AS
    $BODY$
    	/*
    	$1=zielschema
    	$2=zieltabelle
    	$3=zielroid unter der eingefügt werden soll
    	$4=tree_id (baum_id);Standard: NULL --> wenn Tabelle keine Spalte baum_id dann nicht berücksichtigt; wenn Spalte baum_id vorhanden: bei NULL wird baum_id von zielroid($3) benutzt, bei 1 wird nächsthöhere baum_id erzeugt
    	$5=comments
    	$6=y_m_obj_roid
    	$7=k_objit_char_roid
    	$8=attribut
    	*/
    	DECLARE
    	ziel_schema ALIAS FOR $1;
    	ziel_tabelle alias for $2;
    	ziel_roid alias for $3;
    	tree_id_neu alias for $4;
    	quelle_comments alias for $5;
    	quelle_y_m_obj_roid alias for $6;
    	quelle_k_objit_char_roid alias for $7;
    	attribut alias for $8;
    
    	baum_id_exists integer;
    	suche_baum_id text;
    	suche_baum_id_int integer;
    	insert_ort text;
    	runme1 text;
    	runme2 text;
    	runme3 text;
    	runme4 text;
    	runme5 text;
    	lastroid integer;
    	curatt refcursor;
    	
    	/*spalten mit möglichen null-werten*/
    	rec_comments character varying;
    	rec_k_objit_char_roid character varying;
    rec_attribut character varying;
    	
    	d_attribut text;
    	BEGIN
    	
    	/*NULL-Werte filtern*/
    	
    	if  quelle_comments is null then
    	rec_comments := 'NULL';
    	else
    	rec_comments := '''' || quelle_comments || '''';
    	end if;
    
    	if  attribut is null then
    	rec_attribut := 'NULL';
    	else
    	rec_attribut := '''' || attribut || '''';
    	end if;
    	if  quelle_k_objit_char_roid is null then
    	rec_k_objit_char_roid:= 'NULL';
    	else
    	rec_k_objit_char_roid:=quelle_k_objit_char_roid;
    	end if;
    
    	/* erstelle abfragen */
    	runme1:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET lft = lft+2 WHERE lft > (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ');';
    	
    	runme2:='UPDATE ' || ziel_schema || '.' || ziel_tabelle || ' SET rgt = rgt + 2 WHERE  rgt >= (SELECT rgt FROM ' || ziel_schema || '. ' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ') ;';
    	
    	runme3:= 'INSERT INTO ' || ziel_schema || '.' || ziel_tabelle || ' (rank,lft,rgt,comments,y_m_obj_roid,k_objit_char_roid,attribut ';
    	
    	runme4 := ')VALUES
    	(
    	(SELECT rank FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')+1,--rank
    	(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-2,--lft
    	(SELECT rgt FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')-1,--rgt
    	' || rec_comments || ', --comments
    	' || quelle_y_m_obj_roid || ',--y_meta_object roid
    	' || rec_k_objit_char_roid ||  ',--k_objitem_char roid
    	' || rec_attribut || '
    	
    	';--bestandteil von runme3
    	
    	runme5 := ');';--bestandteil von runme3
    	
    	if tree_id_neu is null then
    		/*baum_id von zielroid wird übernommen */
    		
    		runme3 := runme3 || ',tree_id' || runme4;
    		runme3 := runme3  || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
    		runme3 := runme3 || runme5;
    
    	elseif tree_id_neu=0 then
    		/*baum_id von zielroid wird übernommen */
    		
    		runme3 := runme3 || ',tree_id' || runme4;
    		runme3 := runme3  || ',(SELECT tree_id FROM ' || ziel_schema || '.' || ziel_tabelle || ' WHERE roid=' || ziel_roid || ')';
    		runme3 := runme3 || runme5;
    	elseif tree_id_neu=1 then
    		--raise notice 'baum_id wird erzeugt';
    		runme3 := runme3 || ',tree_id' || runme4 || ',(SELECT max(tree_id) FROM ' || ziel_schema || '.' || ziel_tabelle || ' )+1' || runme5;
    		else
    		
    	end if;
    	--raise notice 'runme1 jetzt';
    
    	execute runme1;
    	--raise notice 'runme2 jetzt';
    	execute runme2;
    	--raise notice 'runme3 jetzt';
    	execute runme3;
    	--raise notice 'runme3: %', runme3;
    	
          
    	end;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;

  4. #4
    _rr
    _rr ist offline Neuer Benutzer
    Registriert seit
    07.07.2010
    Beiträge
    9

    Standard

    Das ganze ist so aufgebaut, dass ich nachher in der ersten Funktion die Tabelle+Schema angeben kann und dann automatisch der Inhalt aus einer normalen Tabelle in die Nestedsettabelle übertragen wird, in der der Inhalt der Spalte num die oberste Ebene ist, der alte Tabellenname zweite Ebene und der Inhalt der alten Tabelle dort unterhalb eingefügt wird.

    Ich hoffe es wird halbwegs verständlich.

    Ich habe es nach und nach versucht den Überltäter zu finden. Es scheint am längsten zu brauchen, bei den Updates in der Nestedset-Funktion. Das zusammenbauen zum Aufrufen der Funktion geht schnell.

    Ich habe die zu übergebenen Parameter an die Nestedset-Funktion auch in eine Tabelle geschrieben, um sie dann von dort aus wieder aufzurufen, gleiches Problem.

    Er wird halt nach ein paar Datensätzen langsamer, wobei ich glaube das es daran liegen kann, dass Postgres sich die geänderten Daten erst merkt (WAL?) und erst am Ende schreibt. Und da fast jeder vorhandener Datensatz mit jeden neuem Datensatz geändert wird kommen ziemlich viele Änderungen zusammen. Dafür habe ich aber eben schon fast jeden Tuningtrick probiert den ich finden konnte um dieses Problem zu beheben.

    So, ich hoffe es ist verständlich. Danke schon mal im Voraus.

  5. #5
    akretschmer ist offline Super-Moderator
    Registriert seit
    29.10.2006
    Ort
    Nähe Dresden
    Beiträge
    5.337

    Standard

    Zitat Zitat von _rr Beitrag anzeigen
    Das ganze ist so aufgebaut, dass ich nachher in der ersten Funktion die Tabelle+Schema angeben kann und dann automatisch der Inhalt aus einer normalen Tabelle in die Nestedsettabelle übertragen wird, in der der Inhalt der Spalte num die oberste Ebene ist, der alte Tabellenname zweite Ebene und der Inhalt der alten Tabelle dort unterhalb eingefügt wird.

    Ich hoffe es wird halbwegs verständlich.

    Ich habe es nach und nach versucht den Überltäter zu finden. Es scheint am längsten zu brauchen, bei den Updates in der Nestedset-Funktion. Das zusammenbauen zum Aufrufen der Funktion geht schnell.

    Ich habe die zu übergebenen Parameter an die Nestedset-Funktion auch in eine Tabelle geschrieben, um sie dann von dort aus wieder aufzurufen, gleiches Problem.

    Er wird halt nach ein paar Datensätzen langsamer, wobei ich glaube das es daran liegen kann, dass Postgres sich die geänderten Daten erst merkt (WAL?) und erst am Ende schreibt. Und da fast jeder vorhandener Datensatz mit jeden neuem Datensatz geändert wird kommen ziemlich viele Änderungen zusammen. Dafür habe ich aber eben schon fast jeden Tuningtrick probiert den ich finden konnte um dieses Problem zu beheben.

    So, ich hoffe es ist verständlich. Danke schon mal im Voraus.
    Da durchzusteigen hab ich wenig Lust, aber, was mir so aufgefallen ist:
    - warum so viele Indexe? Die zu aktualisieren ist teuer
    - warum so viele BIGINT, wenn es doch nur ein paar tausend Zeilen sind?
    - die verschachtelten EXECUTE - Anweisungen sorgen dafür, daß da jedesmal neu geplant wird, auch das kostet Zeit

    Fakt ist, bei so einer Update-lastigen Aktion entstehen massig tote Tupel, und es ist viel WAL zu schreiben. Ads kann da vielleicht was zum Tuning schreiben, was ich mich frage: wären rekursive Abfragen (PG 8.4+) nicht sinnvoller?

    Andreas
    --
    ads Das hatte dir akretschmer gestern schon gesagt und siehe da sobald du das machst, funktioniert es ;-)
    https://www.xing.com/profile/Andreas_Kretschmer7

  6. #6
    ads
    ads ist offline Super-Moderator
    Registriert seit
    08.02.2006
    Ort
    MD
    Beiträge
    4.020

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Fakt ist, bei so einer Update-lastigen Aktion entstehen massig tote Tupel, und es ist viel WAL zu schreiben.
    Ack.


    Ads kann da vielleicht was zum Tuning schreiben, was ich mich frage: wären rekursive Abfragen (PG 8.4+) nicht sinnvoller?
    Dafür müsste man sich erst mal konkret die Last der Maschine anschauen - hier pauschale Aussagen zu treffen ist wahrscheinlich kontraproduktiv.
    Xandrian: Kurz gesagt: Du hast ein falsches Konzept und willst nicht auf ads hören

    Jetzt NEU: PostgreSQL - Datenbankpraxis für Anwender, Administratoren und Entwickler

    PostgreSQL Service & Support

  7. #7
    _rr
    _rr ist offline Neuer Benutzer
    Registriert seit
    07.07.2010
    Beiträge
    9

    Standard

    Zitat Zitat von akretschmer Beitrag anzeigen
    Da durchzusteigen hab ich wenig Lust, aber, was mir so aufgefallen ist:
    - warum so viele Indexe? Die zu aktualisieren ist teuer
    - warum so viele BIGINT, wenn es doch nur ein paar tausend Zeilen sind?
    - die verschachtelten EXECUTE - Anweisungen sorgen dafür, daß da jedesmal neu geplant wird, auch das kostet Zeit

    Fakt ist, bei so einer Update-lastigen Aktion entstehen massig tote Tupel, und es ist viel WAL zu schreiben. Ads kann da vielleicht was zum Tuning schreiben, was ich mich frage: wären rekursive Abfragen (PG 8.4+) nicht sinnvoller?

    Andreas
    Die Bigint deshalb, weil ich bei dem Nestedset nicht absehen kann, wieviele Datensätze es werden.
    Aber was meinst du mit rekursiven Abfragen ab 8.4?

    Die Maschinenauslastung denke ich ist erstmal noch nicht das Problem, die Ursache liegt sicher in der Funktion.

  8. #8
    castorp ist offline Erfahrener Benutzer
    Registriert seit
    27.06.2009
    Beiträge
    493

    Standard

    Zitat Zitat von _rr Beitrag anzeigen
    Aber was meinst du mit rekursiven Abfragen ab 8.4
    PostgreSQL kann mittels "recursive common table expressions" hierarchische Datenstrukturen relativ einfach abhandeln.

    Wenn Du eine Tabelle der Form:
    Code:
    CREATE TABLE category 
    (
       id                  integer primary key,
       category_name       varchar(100) not null,
       parent_category_id  integer, 
       foreign key (parent_category_id) references category (id)
    );
    hast, kann man die relativ einfach abfragen

    Siehe die Beispiel im Handbuch:
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: WITH Queries

    Oder einfach nach "recursive common table expressions" (oder "recursive CTE") suchen
    A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'

  9. #9
    _rr
    _rr ist offline Neuer Benutzer
    Registriert seit
    07.07.2010
    Beiträge
    9

    Standard NestedSet Problem

    Also ich befürchte das Problem liegt an den vielen Updates von lft/rgt des NestedSet. bei vorhandenen knapp 8000 Zeilen und einem Insert von Zeile 1, dann Zeile 2,...
    bearbeitet Postgres erst 7999 Zeilen mit lft/rgt +2, dann 7998 Zeilen lft/rgt + 2,...

    Das scheint auch der Grund zu sein, weshalb die Abfrage schnell anfängt und dann langsamer wird. Beginne ich den Insert bei Zeile 8000 und dann abwärts geht es anfangs länger schnell, wird aber auch dann langsamer bis ein Insert meherer Minuten dauert.
    Ich habe spaßenshalber mal eine parent-Spalte eingefügt und siehe da, insert sind in weniger als 1 Minute durch.

    Aber falls noch jemand eine Idee hat, ich wäre froh, das ganze auch mal erfolgreich mit NestedSet zu probieren.

    Danke

Stichworte

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  

Links: Tobias Bauer - IT- und Funkdienstleistungen   -   Bilderrahmen-kaufen.de - Der Onlineshop für Bilderrahmen & Fotorahmen   -   Funkforum