Gelöst: performance probleme beim inserten

Hallo,

ich habe eine PostgresDB 8.3 auf der bereits der Shared und der Maintanance hoch gedreht wurde. Der Server selbst hab 8 GB RAM und 2x4 Cores. Die Festplatten sind schöne schnelle SAS.

Problem ist das ich jetzt auf einer leeren Tabelle mit einem varchar 9 als primary 1mio datensatze schreiben möchte. um die 1 mino zeilen zu erzeugen nutze ich plpgsql welches auch die 9 stellige PID erstellt.

Das ganze dauert fast eine Stunde und der Server hat eine Last von 0. Der PG Prozess schaffen es nichtmal in die TOP. Jetzt stellt sich mir die fragen was muss ich verändern damit der Server anfängt zu arbeiten.

Kann doch nicht so schwer sein die paar Zeilen in die DB zu bekommen.

wie insertest du die denn …

  1. hoffentlich mit einem prepared statement? das waere das mindeste …
  2. außerdem -> autocommit abschalten - selbst transaktionen starten und committen … was weiss ich - immer 1000 oder mehr rows am stueck committen …

jdbc z.B. arbeitet per default mit autocommit … is halt so schoen bequem

ansonsten musst du dir eine etwas andere strategie ausdenken …

es gibt bulk copy … oder du insertest mehrere saetze blockweise … zerlegst die bloecke in der db und verteilst die entsprechend …

das ganze wird bereits in der db als transaction erzeugt.

SELECT create_list(1000000)

Und somit ist es nur 1 SQL und da hilft auch kein Prepared-Stmt.
Es gibt keine Netzwerkstack der aus bremst und dennoch sind die Sachen ultra lahm.

Hier nochmal der Ausschnitt des PLPGSQL

      BEGIN
        INSERT INTO lists (created_at, updated_at) VALUES (NOW(), NOW()) RETURNING id INTO list_id;
        FOR i IN 1..count LOOP
          INSERT INTO tans (tan_list_id, created_at, updated_at) VALUES (list_id,NOW(), NOW());
        END LOOP;
        RETURN list_id;
      END;
CREATE OR REPLACE FUNCTION create_list(count integer)
  RETURNS integer AS
$BODY$
DECLARE list_id INTEGER;
BEGIN
	DROP TABLE IF EXISTS lists;
	DROP TABLE IF EXISTS tans;
	CREATE TABLE lists (
		id serial not null,
		created_at timestamp,
		updated_at timestamp
	);
	CREATE TABLE tans (
		tan_list_id integer,
		created_at timestamp,
		updated_at timestamp
	);
	BEGIN
		INSERT INTO lists (created_at, updated_at) VALUES (NOW(), NOW()) RETURNING id INTO list_id;
		FOR i IN 1..count LOOP
		  INSERT INTO tans (tan_list_id, created_at, updated_at) VALUES (list_id, NOW(), NOW());
		END LOOP;
		RETURN list_id;
	END;
	RETURN 1;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 1;

das dauert bei mir 3 sekunden … fuer 100k rows:

select create_list(100000);

da wird wohl einer deiner trigger auf der tabelle ziemlich teuer sein - womoeglich das berechnen deiner seltsamen nummer

Außerdem ist ein normaler INSERT an der Stelle das teuerste, was man nehmen kann. Man kann mehrere Werte zusammenfassen, man kann Prepared Statements verwenden.

Und man sollte die Laufzeit der Stored Procedure selbst berücksichtigen. Das ist ein reiner Interpreter und dementsprechend langsam.

Und Tans, die aus einem Zeitstempel bestehen? Noch dazu alle aus dem gleichen?

sind nicht alle (statischen) statements in einer stored-procedure bereits prepared?

ich kann mich erinnern, dass prepare / execute in einer stored procedure garnicht funktioniert … ?!?

hmm

sorry hab vergessen zu sagen das die tan automatisch gesetzt wird per trigger

    CREATE FUNCTION generate_tan()
      RETURNS text AS
    $BODY$
      DECLARE
        i int4;
        j int4;
        chars text := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
        result text := '';
      BEGIN
        FOR i IN 1 .. 9 LOOP
          j := int4(random()*35)+1;
          result := result || substring(chars, j, 1);
        END LOOP;
        RETURN result;
      END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;
    
    CREATE FUNCTION set_tan_id()
      RETURNS trigger AS
    $BODY$
      BEGIN
        NEW.id = generate_tan();
        RETURN NEW;
      END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE
      COST 100;

also nochmal zum ablauf

ich sagen das er x tans erstellt werden soll
die pl func macht eine schleife ind x inserts
die inserts sorgen für ein before insert trigger
das füllt die id welches varchar 9 ist (A-Z0-9)

wie gesagt was mich wundert das die db nicht auf last.
was bremmst alles aus? die datenbank idle vor sich hin und
nach ca 1 Stunde ist er endlich durch.

so hoffentlich habe ich nun alles erläutert

mit deinem trigger dauert es bei mit 7 sec fuer 100k …

also irgendwas anderes muss da noch sein - oder etwas lockt ab und an deine table etc pp …

also so extrem duerfte das nicht sein …

oder du hast 100 mio erzeugt und nicht 1 mio :slight_smile:

also die erste 100.000 gehen bei mir aufm notebook so wie auf dem server ähnlich schnell. je mehr daten in die db kommen desto lahmer wird das. aber es kann doch nicht sein, das das prüfen des primarys das inserten so aus bremst? selbst die 10 sekunden für die ersten 100.000 können doch nicht so lahm sein. liegt es evtl an der transaktion? wobei das scheinbar nur bedingt ist, da wie öfters ich 100.000 erstellen es immer lahmer wird.

und die Datebank idled weiterhin vor sich her und das kann ja nicht sein.

Hier 4 Sekunden für 100k, 38 Sekunden für 1M rows … und ich denke, das Problem hat mehrere Ursachen.

Zum einen mal tust du pro Tan einen neuen Funktionsaufruf generieren. pl/pgSQL ist nicht gerade eine performante Sprache. Zum anderen nutzt du bei 1M rows insgesamt 9 Millionen mal die random() Funktion. Das lässt sich sicherlich optimieren. Je nachdem, woher Zufallszahlen bezogen werden, kann es auch einfach sein, dass der Entropie-Pool leer ist. Dann wird gewartet, bis neue Zufallswerte zur Verfügung stehen. Kannst ja mal parallel irgendwas festplattenintensives laufen lassen, einen find oder so.

Solange es Anfragen sind, die bereits im Text stehen, sind die fertig vorbereitet, ja.


ich kann mich erinnern, dass prepare / execute in einer stored procedure garnicht funktioniert … ?!?


CREATE OR REPLACE FUNCTION test_prepared()
          RETURNS VOID
AS $$
DECLARE
  query_str TEXT;
BEGIN

  DROP TABLE IF EXISTS test_prepared;
  CREATE TABLE test_prepared (id SERIAL, data TEXT);

  query_str := 'PREPARE test_plan (TIMESTAMP) AS
                 INSERT INTO test_prepared (data)
                 VALUES ($1::TEXT)';
  EXECUTE query_str;

  query_str := 'EXECUTE test_plan(NOW())';
  EXECUTE query_str;

  RETURN;
END;
$$
LANGUAGE 'plpgsql';

SELECT test_prepared();

Lohnt sich aber effektiv nur, wenn die Anfragen wirklich teuer sind, da pl/pgSQL sowohl das PREPARE wie auch jedes EXECUTE zum SQL-Parser hinunterschicken muss. Andere Programmiersprachen für Stored Procedures sind da … angenehmer. In pl/pgSQL gibt es Kollisionen innerhalb der Syntax, sowohl EXECUTE wie INTO werden anderweitig verwendet.

Echt spitzen Idee, daran habe ich garnicht mehr gedacht. Jetzt habe ich aber mal nur 1Mio Tans mit generate_tan erzeugt also 9Mio random() und das ganze dauerte 22,6 Sekunden auf meinem MacBook Pro.

Somit fällt das schonmal weg leider.

Und auf dem Server dauert das wie lange?

Auf dem Server ist die Performance ca. doppelt so schnell.

Habe aber jetzt mal eine test Tabelle erstellt mit 1 Spalte Varchar9.
Und habe die Tan jetzt mit im INSERT, also nicht per Trigger.

Dann habe 3x100.000 Zeilen erzeugen lassen.

  1. 10 Sekunden
  2. 40 Sekunden
  3. 75 Sekunden

Das kann doch nicht sein oder? Habe

So teuer sieht der Trigger nicht aus. Komisch.

Wenn du das jetzt wieder per Trigger machst, wird es dann wieder langsam?

Ich wollte damit zeigen das auch ohne Trigger das Ganze lahm ist.
Schau mal wieviel Zeit benötigt wird nur bei den ersten 300.000!
Wie mehr Daten in der Tabelle entahlen sind, desto lahmer wird alles.
Und genau das macht mich stuzig. PG ist die :smiley: hoch gelobte DB,
und die kann sich doch sich so ausbremsen lassen.

Beim 3x wo ich 100.000 Einträge mache, ist der Zeitaufwand gegenüber den ersten 100.000 das 7.5fache!!!

Gut, und jetzt lass mal deine Funktion bitte weg und füge einen statischen Wert ein. Von mir aus auch NOW().

Wie sehen die Zeiten dabei aus?

Ich habe gerade mal Testweise eine neue Tabelle erstellt. Wieder nur 1 Spalte vchar9 als pk.

Erster Lauf 1SQL mit 1MIO Datensätze also Massen VALUES :smiley:
Nur 120 Sekunden. Also Super Zeit.

Zweiter Lauf gleiches SQL wieder mit 1 MIO Datensätze
Über 800 Sekunden.

Daher glaube ich das die intere PK Unique prüfung das ganze ausbremst.
Oder ?

Geht nicht da NOW() nicht uniqe ist. Nehme ich die i aus dem LOOP dauert das ganze nur 8 Sekunden. Aber ist ja auch klar, da muss Pg ja nur max 5 Zeichen prüfen ob er unique ist

EDIT: Habe jetzt mal die TAN Erstellen lassen aber nicht in die Tabelle geschrieben. Und auch hier unter 10 Sekunden.

Definitiv “Oder”.

Testcase, Tabelle:

CREATE TABLE test_1m (id BIGINT PRIMARY KEY, data TEXT);

Der Primary Key ist sowieso Unique und hier ein Integer, der hochgezählt wird. Dazu eine Datendatei mit 100.000 Zeilen:

INSERT INTO test_1m (data) VALUES ('some static text');

Die Ergebnisse:
1m10.483s
1m5.425s
1m9.205s

Da ändert sich also nichts.


Kannst du bitte mal ausschließen, dass PG selbst und nicht eine deiner Funktionen die Ursache für die Verzögerung ist?