bytea Performance sehr langsam

Hallo Forum,

ein bekanntes Problem mit Diskussionen auch hier im Forum: Die Performance bei Binärdaten (bytea).

http://www.pg-forum.de/programmierung/2735-bytea-large-object-performance.html

Ich habe eine Dokumenten-Datenbank, die bisher unter MySQL 5.0 läuft und möchte diese gerne nach Postgres migrieren. Die DB beinhaltet ca. 1 Mio. Datensätze. Die Tabellen sind so strukturiert, daß die Metainformationen in separaten Tabellen vorliegen und die Tabelle mit den BLOBs nur aus zwei Spalten besteht (Primärschlüssel und BLOB). Die Datenlänge liegt im Bereich zwischen 10 kB - 5 MB. Idealtpypisch sind PDFs im Bereich 50-100 kB und JPG-Bilder um die 3 MB. Die BLOBs werden mit Perl und PHP abgefragt. DB-Server und Scripte liegen auf demselben Host, die Verbindung läuft jeweils über TCP-Standardport. Socket-Verbindung ist keine Alternative.

Die folgenden Performance-Betrachtungen gelten für eine Bilddatei von ca. 3 MB.

MySQL kommt damit gut zurecht, eine Abfrage einer dauert deutlich unter einer halben Sekunde.

Bei Postgres 8.3.7 dauert die Anfrage aus PHP 3,6 Sekunden (mit microtime gemessen, jeweils vor und nach pg_query(), ohne pg_fetch_array()).

Aus dem Kommandozeilentool psql heraus stellt sich die Sache so dar:

EXPLAIN ANALYZE
SELECT dokumente.doc.*
FROM dokumente.doc
WHERE dokumente.doc.id=663538;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Index Scan using doc_pkey on doc  (cost=0.00..8.27 rows=1 width=36) (actual time=0.017..0.019 rows=1 loops=1)
   Index Cond: (id = 663538)
 Total runtime: 0.054 ms
(3 Zeilen)

Mein Problem ist nun, daß ich gar nicht richtig erkennen kann, wo der Zeitverlust eintritt. Ich vermute, daß die Zeitangabe in psql unrealistisch ist, und der BLOB (bytea, STORAGE: EXTENDED) gar nicht geholt wird.

Hingegen bekomme ich ziemlich genau für gute 3 Sekunden, in denen PHP die Anfrage gegen Postgres stellt, eine Auslastung des Postgres-Servers auf einem Kern von knapp 100 % und die schnell wiederholte Prozess-Statistik

SELECT * from pg_stat_activity ;

zeigt den Query für diesen Zeitraum auch als laufend an.

Der Postgres-Dokumentation habe ich entnommen, daß Postgres Binärdaten stets “Escaped” zurückliefert.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Binary Data Types

“Bytea octets are also escaped in the output. In general, each “non-printable” octet is converted into its equivalent three-digit octal value and preceded by one backslash. Most “printable” octets are represented by their standard representation in the client character set. The octet with decimal value 92 (backslash) has a special alternative output representation.”

Das deckt sich mit meiner Beobachtung, denn die String-Länge der ankommenden Daten ist gegenüber der originalen Dateigröße (3 MB) nun auf ca. 8 MB angewachsen.

Natürlich kostet der erhöhte Traffic, der durch das Escaping verursacht wird, seine Zeit. Dann würde aber Postgres nicht bei 100 % Auslastung stehen. Vielmehr vermute ich, daß der größte Teil der Zeit beim Escaping verlorengeht. Daher gingen meine Überlegungen auch nicht in Richtung TOASTing oder LO-Speicherung. Das ganze steht aber im Widerspruch zu einer anderen Beobachtung: PHP braucht für das Unescaping der 8 MB mit der Funktion pg_unescape_bytea() nur 0,07 Sekunden. Die Menge der Datensätze kann als Problem ausgeschlossen werden, denn es sind erst 20 Test-Datensätze in der Tabelle gespeichert.

Meine Frage lautet nun: Läßt sich Postgres dergestalt konfigurieren, daß ich die Binärdaten ohne Escaping abfragen kann? Schön wäre das übrigens auch für das Senden an den Server bei einem INSERT. Liegt das Problem woanders, als beim Escaping?

Vielen Dank für Eure Hilfe

Wenn du die Daten nicht escapest, tauchen unter Umständen Null-Bytes (binär 0) oder andere schädliche Zeichen auf, die für Probleme sorgen. Nicht nur in PostgreSQL sondern auch in anderen Schichten, die dazwischen liegen.

Was du allerdings mal probieren könntest: nimm kein ByteA sondern TEXT, wandle die Daten schon in der Anwendung in irgendwas ungefährliches um (z. B. base64) und komprimiere sie auch bereits in der Anwendung.

Dann musst du mittels Storage Parameter nur noch dafür sorgen, dass die TEXT-Spalte nicht mehr in PostgreSQL automatisch komprimiert wird:

ALTER TABLE ... ALTER COLUMN ... SET STORAGE EXTERNAL;

Daran habe ich auch schon gedacht. Da ich diesen Aufwand noch scheue, würde ich die Datenbank lieber unter MySQL stehen lassen. Das geht in diesem Fall ganz gut, weil die Dokumenten-Datenbank gut gekapselt im System steht und nur per Webservices gespeichert und abgefragt wird. Schöner wäre es natürlich, alles von MySQL nach Postgres portieren zu können.

Glubst Du den auch, daß Postgres tatsächlich 3 Sekunden für das Escaping benötigt, wenn PHP die Umkehroperation in 0,07 Sekunden schafft?

Nein, glaube ich nicht. Aber ich habe zuwenig Informationen, um das konkret herauszufinden. Als ersten Ansatz würde ich das sowieso auf einem anderen Weg verifizieren, ob das an der DB und nicht an PHP oder einem Zwischenschritt liegt.

Jein.

Nein, die Zeitangabe ist realistisch, und ja, es wird kein BLOB geholt. Du fragst vermutlich die Tabelle mit den Metadaten ab, denn Dein Resultat ist nur 36 Byte breit.


Andreas

Hallo Andreas,

das SELECT-Statement wird tatsächlich an die BLOB-Tabelle gestellt, die nur 2 Spalten hat:

  • ID (Typ integer, primary key)
  • bytecode (Typ bytea)

Wenn ich das SELECT ohne EXPLAIN ANALYZE absetze, bekomme ich in psql erst eine ganze Weile fortlaufende Leerzeichen und danach fortlaufende Minus-Zeichen. Das Server-Log zeigt bei allen Abfragen keinen Fehler an.

Um PHP als Fehlerquelle auszuschließen, habe ich zwei Terminals mit psql und noch ein top aufgemacht. Das eine führt das SELECT-Statement auf den BLOB aus, das andere Zeigt mir die Prozess-Statistik von Postgres und top zeigt mir die Betriebssystem-Statistik. Und auch hier bekomme ich für über 3 Sekunden die Volllast zu sehen.

Sorry, mein Fehler. Explain Analyse zeigt da nur das an, was in der Tabelle selbst steht und nicht in der TOAST-Tabelle.

Okay.

Andreas

Aha! Könnte das die Ursache sein? TOAST komprimiert doch? Vielleicht dauert das entpacken so lange? Bei MySQL weiß ich, daß die Daten unkomprimiert ins filesystem wandern.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: TOAST

“The TOAST code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB) or no more gains can be had. During an UPDATE operation, values of unchanged fields are normally preserved as-is; so an UPDATE of a row with out-of-line values incurs no TOAST costs if none of the out-of-line values change.”

Wenn TOAST tatsächlich versucht, die 3 MB Bilder auf unter 2 kB zu komprimieren, dürfte LZ in die höchste Komrimierstufe gehen.


  • PLAIN prevents either compression or out-of-line storage; furthermore it disables use of single-byte headers for varlena types. This is the only possible strategy for columns of non-TOAST-able data types.
  • EXTENDED allows both compression and out-of-line storage. This is the default for most TOAST-able data types. Compression will be attempted first, then out-of-line storage if the row is still too big.
  • EXTERNAL allows out-of-line storage but not compression. Use of EXTERNAL will make substring operations on wide text and bytea columns faster (at the penalty of increased storage space) because these operations are optimized to fetch only the required parts of the out-of-line value when it is not compressed.
  • MAIN allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other way to make the row small enough.)

Der Storage-Typ “EXTENDED” dürfte dann für mich und für diese Tabelle völlig ungeeignet sein, denn die BLOBs wurde ja extra von den Metadaten getrennt und in einer eigenen Tabelle gespeichert. Wie kann ich also den Storage-Typ auf PLAIN oder EXTERNAL stellen?

ALTER TABLE doc ALTER bytecode SET STORAGE PLAIN;

Ich werde mal ein paar Performance-Tests machen und mich danach melden.

Möglich, ja.

Der Storage-Typ “EXTENDED” dürfte dann für mich und für diese Tabelle völlig ungeeignet sein, denn die BLOBs wurde ja extra von den Metadaten getrennt und in einer eigenen Tabelle gespeichert. Wie kann ich also den Storage-Typ auf PLAIN oder EXTERNAL stellen?

ALTER TABLE.

Andreas

ALTER TABLE doc ALTER bytecode SET STORAGE PLAIN;
ALTER TABLE doc ALTER bytecode SET STORAGE EXTERNAL;

Brachte jeweils keine Verbeserung. Langsam gehen mir die Ideen aus… :confused:

Hast Du die Abfragen mal mit pgAdmin oder z.B. einem Java/JDBC basiertem Tool gemacht, welches die BLOBs auch wirklich auf den Client lädt?

Vielleicht liegt es ja an dem Perl/PHP Interface?

Die kurze Fassung:

Das Problem wurde durch die SSL-Verschlüsselung verursacht, die in der Serverkonfiguration auf “true” gesetzt war. Obwohl die Verbindung von PHP zu Postgres nicht explizit SSL angefordert hat, wurde eine SSL-Verbindung aufgebaut.
Die ausführliche Fassung.

In PHP baut die funktion pg_connect() eine Verbindung zu Postgres auf.

<?php
$db_host   = 'localhost';     # empty string connects via socket
$db_port   = '5432';
$db_user   = 'user';
$db_pass   = 'pass';
$db_dbname = 'db';
$db_schema = 'dokumente';

$db_conn_string = "host='$db_host' port='$db_port' dbname='$db_dbname' user='$db_user' password='$db_pass' ";
$db_conn = pg_connect($db_conn_string);
pg_query("SET search_path TO '$db_schema';");
?>

Zunächst habe ich die Socket-Verbindung probiert, wobei sich die Zeit für das Holen des BLOBs/BYTEA von 3,6 Sekunden auf 0,1 Sekunde reduzierte. MySQL brauchte dafür 0,01 Sekunden. Ein tcpdump zeigte dann, daß postgres bei TCP-Verbindungen sofort anfing, Daten zu senden, das jedoch vergleichsweise langsam. Also fiel der Verdacht auf SSL. Nur hatte hier niemand gesagt, daß eine SSL-Verbindung aufgebaut werden soll.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: Secure TCP/IP Connections with SSL

“PostgreSQL has native support for using SSL connections to encrypt client/server communications for increased security. […] With SSL support compiled in, the PostgreSQL server can be started with SSL enabled by setting the parameter ssl to on in postgresql.conf. The server will listen for both standard and SSL connections on the same TCP port, and will negotiate with any connecting client on whether to use SSL. By default, this is at the client’s option, …”

Also scheint PHP standardmäßig eine SSL-Verbindung aufzubauen, sofern nicht der Parameter “sslmode” auf den Wert “disable” gesetzt wird.

PHP: pg_connect - Manual

“The values accepted by pg_connect’s sslmode argument are: disable, allow, prefer, require”

Soll der Server also mit “ssl = true” laufen, um für SSL-Verbindungen offen zu sein, müßte die Verbindung ohne SSL aus PHP wie folgt aufgebaut werden:

$db_conn_string = "host='$db_host' port='$db_port' dbname='$db_dbname' user='$db_user' password='$db_pass' sslmode='disable' ";

Ich habe den Server jetzt mit “ssl = false” konfiguriert. Einerseits müßte in allen Applikationen SSL explizit ausgeschaltet werden, andererseits zwingt die native SSL-Verschlüsselung Postgres derart in die Knie, daß eine vertrauliche Verbindung eher mit einem Tunnel gesichert wird.

Vielen Dank für Eure Hilfe!
ratio

Manchmal liegen die Probleme ganz woanders …

Nun, wenn du natürlich pro Verbindung einmal SSL-Handshake machen musst, wird das teuer. Das stimmt. Auf die Ecke wäre ich allerdings auch nicht auf Anhieb gekommen.