[[PageOutline]]

= Performance =

== Ingestion measurements ==

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 ===

 * Default postgres parameters:

|| '''Tiling scheme''' || '''Tile size''' || '''Import time''' || '''DB size before''' || '''DB size after''' || '''BLOBs size''' || '''BLOBs size2''' ||
|| 0:31,0:31,0:149 || 460800 || 84 minutes || 184M || '''19416M''' || 267 MB || 18 GB ||

 * 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 = 1MB, wal_writer_delay = 2000ms, checkpoint_segments = 32
   
|| '''Tiling scheme''' || '''Tile size''' || '''Import time''' || '''DB size before''' || '''DB size after''' || '''BLOBs size''' || '''BLOBs size2''' ||
|| 0:31,0:31,0:149 || 460800 || 35 minutes || 184M || '''19416M''' || 267 MB || 18 GB ||

= PostgreSQL tips =

== Total size of BLOBs ==
BLOBs are stored in the `pg_largeobject` in postgres. Each BLOB is divided into rows (pages) of 2048 bytes typically. More info [http://www.postgresql.org/docs/8.4/static/catalog-pg-largeobject.html here]

Query below computes the space that all BLOBs take.
{{{
SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject;
}}}

== Size of individual BLOBs ==
{{{
SELECT 	loid,
        pg_size_pretty(count(*) * 2048)
FROM pg_catalog.pg_largeobject
GROUP BY loid
ORDER BY count(*) DESC;
}}}

== Size of tables ==
{{{
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.schemaname = 'public'
ORDER BY total_size DESC;
}}}

== 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`