Changes between Version 4 and Version 5 of Performance


Ignore:
Timestamp:
May 29, 2013, 9:39:22 AM (11 years ago)
Author:
Dimitar Misev
Comment:

—

Legend:

Unmodified
Added
Removed
Modified

  • Performance

    v4 v5  
    2727The dataset is 50 png rgb images of size 1024x608, so that each image is 1867776 bytes uncompressed, or 89 MB in total.
    2828
    29 VACUUM = `VACUUM FULL pg_largeobject`
    30 
    3129 * 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''
    3230   
    33 || '''Tiling scheme''' || '''Tile size''' || '''Import time''' || '''DB size before''' || '''DB size after''' || '''BLOBs size''' || '''BLOBs size2''' || '''BLOBs after VACUUM'''
     31|| '''Tiling scheme''' || '''Tile size''' || '''Import time''' || '''DB size before''' || '''DB size after''' || '''BLOBs size''' || '''BLOBs size2''' || '''BLOBs after [#VACUUMtables VACUUM]'''
    3432|| 0:31,0:31,0:149 || 460800 || 7m 34s || 47M || '''2464M''' || 267 MB || 1776 MB || 96MB ||
     33
    3534
    3635= PostgreSQL tips =
    … …  
    7877
    7978== Reset WAL ==
     79[http://www.postgresql.org/docs/9.1/static/wal-intro.html Write-ahead logging] is used to ensure data integrity. In postgres these logs are stored in pg_xlog and it's [http://www.postgresql.org/docs/9.1/static/wal-internals.html recommended] to put them in a separate disk. To reset the logs:
    80801. `service stop postgresql`
    81811. `sudo -u postgres pg_resetxlog $PGDATA`
     82
     83== Remove orphaned BLOBs ==
     84Orphaned BLOBs are BLOBs that are stored but they are not referenced by oid from any table in the database. To clear them up use [http://www.postgresql.org/docs/8.4/static/vacuumlo.html vacuumlo]
     85 * `vacuumlo RASBASE`
     86
     87== VACUUM tables ==
     88Tuples that have been deleted or obsoleted are normally not physically deleted, and thus the space continues to be still used, until a `VACUUM` is performed on the database or specific tables. `VACUUM` alone doesn't free up the disk space for further use by the OS, `VACUUM FULL` is necessary for this. In rasdaman it's most important to do periodic VACUUM on the BLOBs table:
     89 * psql -d RASBASE -c "VACUUM FULL pg_largeobject"
     90
     91or
     92
     93 * vacuumdb -d RASBASE -t "pg_largeobject" -f