PostgreSQL Skripts für „Testen und Skalierbarkeit“

Dieser Abschnitt enthält die create und insert Anweisungen sowie den PL/pgSQL Code um den Skalierbarkeitstest aus Kapitel 3, „Performance und Skalierbarkeit“ in einer PostgreSQL Datenbank durchzuführen.

Der Test muss auf einer Tabelle laufen, die groß genug ist, um Caching-Effekte auszuschließen. Abhängig von der Umgebung, muss die Tabelle eventuell noch größer angelegt werden, um ein vergleichbares Ergebnis zu erhalten.

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
   id2     NUMERIC NOT NULL
);

Bemerkungen:

• Es gibt keine Primärschlüssel um die Datengenerierung einfach zu halten.

• Die Tabelle wird anfangs ohne Index angelegt (folgt später).

• Die Tabelle hat keine JUNK-Spalte, da auf die Tabelle während des Tests nicht zugegriffen wird.

INSERT INTO scale_data
SELECT sections.*, gen.*
     , CEIL(RANDOM()*100) 
  FROM GENERATE_SERIES(1, 300)     sections,
       GENERATE_SERIES(1, 900000) gen
 WHERE gen <= sections * 3000;

Bemerkungen:

• Der Code erzeugt 300 Sektionen. In deiner Umgebung brauchst Du eventuell noch mehr. Wenn Du die Anzahl der Sektionen erhöhst, musst du auch den zweiten Generator erweitern. Er muss mindestens 3000 x <anzahl der Sektionen> Zeilen erzeugen.

• Die Tabelle benötigt einige Gigabytes.

CREATE INDEX scale_slow ON scale_data (section, id1, id2);

ALTER TABLE scale_data CLUSTER ON scale_slow;
CLUSTER scale_data;

Bemerkungen:

• Der Index benötigt ebenfalls einige Gigabyte.

• PostgreSQL kennt keinen Index-Only-Scan in Version 9.0.3. Das heisst, dass es nicht möglich ist den Test nur am Index durchzuführen, ohne auf die Tabelle zuzugreifen. Die Tabelle wird daher nach der Indexreihenfolge geclustered, um die Auswirkung des Tabellenzugriffes möglichst gering zu halten.

• Das Clusteren der Tabelle kann sehr lange dauern.

CREATE OR REPLACE FUNCTION test_scalability
   (sql_txt VARCHAR(2000), n INT)
   RETURNS SETOF RECORD AS
$$
DECLARE
   tim   INTERVAL[300];
   rec   INT[300];
   strt  TIMESTAMP;
   v_rec RECORD;
   iter  INT;
   sec   INT;
   cnt   INT;
   rnd   INT;
BEGIN
   FOR iter  IN 0..n LOOP
      FOR sec IN 0..300 LOOP
         IF iter = 0 THEN
           tim[sec] := 0;
           rec[sec] := 0;
         END IF;
         rnd  := CEIL(RANDOM() * 100);
         strt := CLOCK_TIMESTAMP();

         EXECUTE 'select count(*) from (' || sql_txt || ') tbl'
            INTO cnt
           USING sec, rnd;

         tim[sec] := tim[sec] + CLOCK_TIMESTAMP() - strt;
         rec[sec] := rec[sec] + cnt;

         IF iter = n THEN
            SELECT INTO v_rec sec, tim[sec], rec[sec];
            RETURN NEXT v_rec;
         END IF;
      END LOOP;
   END LOOP;

   RETURN;
END;
$$ LANGUAGE plpgsql;

Bemerkungen:

• Die TEST_SCALABILITY.RUN Funktion liefert eine Tabelle als Ergebnis.

• Die Anzahl der Sektionen ist hard-coded (300, hervorgehoben).

• Die Anzahl der Wiederholungen ist konfigurierbar.

Das folgende select ruft die Funktion auf, und übergibt die Testabfrage als Parameter:

SELECT *
  FROM test_scalability('SELECT * '
                      ||  'FROM scale_data '
                      || 'WHERE section=$1 '
                      ||   'AND id2=$2', 10)
       AS (sec INT, seconds INTERVAL, cnt_rows INT);

Die Gegenprobe, mit einem besseren Index:

CREATE INDEX scale_fast ON scale_data (section, id2, id1);

ALTER TABLE scale_data CLUSTER ON scale_fast;
CLUSTER scale_data;

SELECT *
  FROM test_scalability('SELECT * '
                      ||  'FROM scale_data '
                      || 'WHERE section=$1 '
                      ||   'AND id2=$2', 10)
       AS (sec INT, seconds INTERVAL, cnt_rows INT);

Bemerkungen:

• Zum Vergleich ist es notwendig, die Tabelle nach dem neuen Index zu Clustern. Das kann wiederum sehr lange dauern.