bytea/Large-Object Performance

Hallo PostgreSQL-Forum,

ich bin gerade dabei eine Anwendung auf PostgreSQL zu migrieren. Diese Anwendung macht viel mit blobs (bzw. bytea in PostgreSQL). Ich habe nun folgendes Testprogramm geschrieben:

	public void doTest() {
		try {
			Class.forName("org.postgresql.Driver").newInstance();
			String cs =
				"jdbc :p ostgresql://localhost:5432/testcontent?user=postgres"+
				"&password=XXXXX&useUnicode=true" +
				"&characterEncoding=ISO-8859-1";
			
			FileInputStream fis = new FileInputStream("/a-big-file");
			
			Connection conn = DriverManager.getConnection(cs);
			conn.setAutoCommit(false);
			
			//clean up
			PreparedStatement pstmtT =
				(PreparedStatement) conn
					.prepareStatement("truncate table test_blob");
			
			pstmtT.execute();
			pstmtT.close();
			pstmtT = null;

			
			int len = 524288;
			byte[] buffer = new byte[len];

			long seqId = 0L;
			long time = System.currentTimeMillis();
			long amount = 0L;

			int c = 0;
			while (true) {
				int read = fis.read(buffer, 0 ,len);
				if (read > 0) {
					amount += read;
					ByteArrayInputStream bais =
						new ByteArrayInputStream(buffer, 0, read);
					
					PreparedStatement pstmt =
						(PreparedStatement) conn
							.prepareStatement("insert into test_blob (ID, testblob) values (?,?)");
					
					pstmt.setLong(1, seqId);
					pstmt.setBinaryStream(2, bais, (int) read);
					
					pstmt.executeUpdate();
					bais.close();
					
					seqId++;
					pstmt.close();
					pstmt = null;
				}
				else {
					break;
				}

			}

			fis.close();
			
			long timeEnd = System.currentTimeMillis();
			System.err.println("Time used: "+(timeEnd-time)+" for "+(amount/1024L/1024L)+" MB");
			
			conn.commit();
			timeEnd = System.currentTimeMillis();
			System.err.println("Time used: "+(timeEnd-time)+" for "+(amount/1024L/1024L)+" MB");
			
			conn.close();
		}
		catch (Exception e) {
			e.printStackTrace(System.err);
		}
	}

CREATE TABLE test_blob ( id INTEGER NOT NULL, testblob BYTEA NULL DEFAULT NULL, PRIMARY KEY (id));

Die dazugehörige Tabelle:

Also das Programm macht folgendes: Es ließt eine Datei (137 MB) vom Filesystem und schreibt in die obige Tabelle immer 512KB Blöcke dieser Datei.


Die Performance ist dabei folgende (auf einem recht modernen 3 GHz PC mit 2GB RAM SATA Platten):

Windows: 137 MB in ca. 60 Sekunden
Linux: 137 MB in ca. 23 Sekunden

Exakt derselbe Test mit einer MySQL-InnoDB Datenbank liefert folgende Werte:

Windows: 137 MB in ca. 17 Sekunden
Linux: 137 MB in ca. 13 Sekunden

Ich habe das ganze auch mal mit LargeObjects und OID versucht, die Zahlen sind dabei aber dieselben.

Was mir auch noch aufgefallen ist: Es kommt darauf an, was ich als Blöcke in das bytea reinschreibe, wenn ich ein ZIP File reinschreibe, dauert es sehr lange (siehe oben), wenn ich aber ein TXT-File oder einen buffer mit lauter NULLEN reinschreiben ist es viel viel schneller. Kann es sein, dass die Datenbank beim Speichern irgendwas komprimiert?

Fazit ist: dass die Performance für die geplante Migration ein echtes Problem darstellt, ich hoffe es kann mir jemand ein paar Tips geben, was ich hier machen kann, um die Performance zu steigern.

Viele Grüße

Oliver Schulze

Ab und an fragen Leute nach, ob PG unter OS x besser läuft als unter OS y.

Exakt derselbe Test mit einer MySQL-InnoDB Datenbank liefert folgende Werte:

Windows: 137 MB in ca. 17 Sekunden
Linux: 137 MB in ca. 13 Sekunden

Mmh.

Was mir auch noch aufgefallen ist: Es kommt darauf an, was ich als Blöcke in das bytea reinschreibe, wenn ich ein ZIP File reinschreibe, dauert es sehr lange (siehe oben), wenn ich aber ein TXT-File oder einen buffer mit lauter NULLEN reinschreiben ist es viel viel schneller. Kann es sein, dass die Datenbank beim Speichern irgendwas komprimiert?

Ja, PG komprimiert das, nennt sich TOAST. Schaust Du hier:
PostgreSQL: Documentation: Manuals: PostgreSQL 8.3: TOAST

Du kannst ja mal versuchen, an den Parametern zu drehen.

Fazit ist: dass die Performance für die geplante Migration ein echtes Problem darstellt, ich hoffe es kann mir jemand ein paar Tips geben, was ich hier machen kann, um die Performance zu steigern.

Viele Grüße

Oliver Schulze

Es wird oft empfohlen, große Daten (Dateien) im Filesystem zu speichern. Einer der Gründe ist die bessere Performance. Falls Du dennoch dies so machen willst, dann drehe an den TOAST-Parametern und an den Speicher-Parametern (work_mem und so).

Andreas

der empfehlung schliess ich mich an

weiterer vorteil - man kann auch so mal an die dateien ran - so man denn will

Hallo Andreas,

erstmal vielen Dank für die Super-Schnelle Antwort!

Ich schau mir das mit dem Toast an und werde hier berichten.

Noch was zum Thema ins Filesystem schreiben: das ist für uns keine Alternative, da ja sonst die Transaktionsfähigkeit auf der Strecke bleibt. Wenn die DB ein Rollback machen sollte, dann tut das Filesystem das ja natürlich nicht, von daher ist es sehr wichtig auch die Daten in die Datenbank zu packen.

das ist kein argument … du haettest eine referenz in deiner tabelle auf die datei … wenn die transaktion fehlschlaegt, haettest du im unguenstigsten fall die datei-leiche in deiner “datei-bank” … genauso wie du daten-leichen (verlorene tupel) in deiner db hast - wenn die transaktion fehlschlägt.

beides zeugs, was die DB nicht “kennt” …

daher gibt es so funktionen vakuum … eine ähnliche funktion muesstest du dir halt auch fuer die “datei-bank” bauen

man kann die ganze geschichte beliebig einfach bzw. kompliziert machen … ausschlaggebend ist IMMER was die DB ueber die dateien weiss.

natuerlich kann jeder der zugriff auf die “datei-bank” hat, auch irgendwelche dateien löschen … ich kann auch die DB-dateien vom file-system löschen wenn der pg-server offline ist …

das sind allesamt adminstrative probleme … (berechtigung)

Ok, ich habe nun ein paar Tests durchgeführt und zum Beispiel mal die Komprimierung und die Auslagerung in die TOAST-Tabellen abgeschalten (habe dann halt nur noch 8 KB Blöcke gespeichert).

Das Ergebnis ist, dass es absolut keinen Geschwindigkeitsvorteil bringt.

Gibt es eine Möglichkeit die Block-Größe für ein intern gespeichertes bytea Feld zu erhöhen, also mehr als 8KB zuzulassen?

Gibt es sonst noch Möglichkeiten, einfach byte-Blöcke performant in die DB zu speichern? Wichtig ist: Transaktionssicherheit und eben auch Backup durch DUMP… was beim Filesystem absolut nicht gegeben ist.

vielleichst suchst du an der falschen stelle … das koennte acuh alles am client-server protokoll liegen … die client lib von pg arbeitet per default glaube ich im sog “text-modus” … ob das auch fuer den LO-support gilt weiss ich nicht … d.h. der ganze stream wird / wuerde in text encoded (z.B. hex) und an den server geschickt …

dazu kommt vielleicht auch noch das komprimieren der connection-streams was den vorteil bei der gut komprimierbaren datei erklaeren koennte

wie gesagt … is nur so ein gedanke …

mit was arbeitest du denn client-seitig?

Hallo,

ich arbeite Client-Seitig mit dem Java-JDBC-Treiber Version 8.3-603:

			Class.forName("org.postgresql.Driver").newInstance();
			String cs =
				"jdbc :p ostgresql://localhost:5432/testcontent?user=postgres"+
				"&password=XXXXX&useUnicode=true" +
				"&characterEncoding=ISO-8859-1";

und mit Java 1.4… Die beschriebenen Parameter (text-mode, etc…), wo finde ich diese? Davon habe ich noch nie etwas gehört…

das kann man eigentlich nur auf API-ebene

PostgreSQL: Documentation: Manuals: PostgreSQL 8.2: Command Execution Functions



PQexecParams is like PQexec, but offers additional functionality: parameter values can be specified separately from the command string proper, and query results can be requested in either text or binary format. PQexecParams is supported only in protocol 3.0 and later connections; it will fail when using protocol 2.0.

tja … die entscheidung bleibt dem jdbc-treiber ueberlassen, ob man den entsprechend konfigurieren kann weiss ich nicht - wenn es ueberhaupt daran liegt …

dich interessiert aber die “andere” richtung … da wuerde ich mal schauen, wie du die LOs zur DB schickst … vielleicht gibt es extra Klassen fuer PG fuer LO-support … denn ich denke, im normal-betrieb wird der jdbc treiber die binaeren geschichten so escapen, dass sie ueber “normale” statements zur DB transferiert werden koennen.

hinweis:
es gibt auch unterschiedliche klassen fuer PreparedStatements … das normale ist ein JDBC-prepared statement … vielleicht bringt dir das server-seitige PreparedStatement bei dir mehr

vielleicht ist das aber auch alles quark was ich erzaehle - ich weiss ja nicht wie du die LOs zur DB schickst

hier ein paar alternativen aus der PG-doku

Chapter*7.*Storing Binary Data
[edit] … äääh … ich sehs doch - gerade nochmal - unten im OP …

dann solltest du dir den obigen ruhig mal artikel antun …

und ggf. mal nach PG und LargeObject googeln

Hallo,

ich habe mir bereits den Source Code des JDBC Treibers angeschaut und dort Ausgaben eingebaut. Und habe gesehen, dass letztendlich direkt der Binär-Strom an die Datenbank über einen Socket gestreamt wird. Bis dahin wurde auch nichts kodiert/escaped.

Ich gebe das ganze auch im Code als setBinaryStream an und über eine Bind-Variable. Und das wird auch direkt an den Datenbank Socket gestreamt…