Changes between Version 3 and Version 4 of Performance

Timestamp:

May 29, 2013, 8:55:54 AM (11 years ago)

Author:

Dimitar Misev

Comment:

—

Performance

@@ -1 +1 @@
 [[PageOutline]]
 
-= Performance =
+= Guidelines =
 
-== Ingestion measurements ==
+= Benchmarks =
+
+== Ingestion ==
 
 Below are the results of ingesting one dataset using various tilings and postgres parameters.
 
-The dataset is 150 png rgb images of size 1024x608, so that each image is 1867776 bytes uncompressed, or 280 MB.
+=== Regular tiling ===
 
-=== Regular tiling ===
+The dataset is 150 png rgb images of size 1024x608, so that each image is 1867776 bytes uncompressed, or 280 MB in total.
 
  * Default postgres parameters:
@@ -21 +23 @@
 || 0:31,0:31,0:149 || 460800 || 35 minutes || 184M || '''19416M''' || 267 MB || 18 GB ||
 
+=== Regular tiling + VACUUM ===
+
+The dataset is 50 png rgb images of size 1024x608, so that each image is 1867776 bytes uncompressed, or 89 MB in total.
+
+VACUUM = `VACUUM FULL pg_largeobject`
+
+ * Changed parameters in postgresql.conf: shared buffers = 1024 MB, temp_buffers = 8MB, fsync = off, synchronous_commit = off, wal_sync_method = fsync, full_page_writes = off, ''wal_buffers = 8MB, wal_writer_delay = 10000ms, checkpoint_segments = 16''
+   
+|| '''Tiling scheme''' || '''Tile size''' || '''Import time''' || '''DB size before''' || '''DB size after''' || '''BLOBs size''' || '''BLOBs size2''' || '''BLOBs after VACUUM'''
+|| 0:31,0:31,0:149 || 460800 || 7m 34s || 47M || '''2464M''' || 267 MB || 1776 MB || 96MB || 
+
 = PostgreSQL tips =
 
@@ -29 +42 @@
 {{{
 SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
+}}}
+
+== Total size of BLOBs 2 ==
+{{{
+SELECT tablename,
+       pg_size_pretty(size) AS size_pretty,
+       pg_size_pretty(total_size) AS total_size_pretty
+FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size,
+                pg_total_relation_size(schemaname||'.'||tablename) AS total_size
+      FROM pg_tables) AS TABLES
+WHERE TABLES.tablename = 'pg_largeobject'
+ORDER BY total_size DESC;
 }}}
 
@@ -40 +65 @@
 }}}
 
-== Size of tables ==
+== Size of RASBASE tables ==
 {{{
 SELECT tablename,
@@ -53 +78 @@
 
 == Reset WAL ==
-1. `pg_controldata $PGDATA | egrep '(NextXID|NextOID)'`
-{{{
-Latest checkpoint's NextXID:          0/946
-Latest checkpoint's NextOID:          18464
-}}}
 1. `service stop postgresql`
-1. `sudo -u postgres pg_resetxlog -o 18464 -x 946 $PGDATA`
+1. `sudo -u postgres pg_resetxlog $PGDATA`