1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Rep Power

    DB size is 30GB by the file system is ~250GB in size.


    I am a newbie to postgresql DB's and also to this forum. I just took over an application that has postgresql DB 8.1 as the back end DB.

    The DB from application is only 30 GB, but the data filesystem is ~250 GB with 99% utilized.

    I tried to search and see if i can fix it, but my changes resulted nothing.

    Below is my configuration: postgresql.conf

    listen_addresses = '*'
    max_connections = 100
    shared_buffers = 13108 # min 16 or max_connections*2, 8KB each
    temp_buffers = 2000 # min 100, 8KB each
    max_prepared_transactions = 0 # can be 0 or more
    work_mem = 8192 # min 64, size in KB
    maintenance_work_mem = 81920 # min 1024, size in KB
    max_fsm_pages = 5367136 # min max_fsm_relations*16, 6 bytes each
    max_fsm_relations = 10000 # min 100, ~70 bytes each
    checkpoint_segments = 200
    archive_command = 'cp -i "%p" /databackup/dpa/WAL/"%f"' # command to use to archive a logfile
    effective_cache_size = 32768 # typically 8KB each
    redirect_stderr = on # Enable capturing of stderr into log
    log_directory = 'pg_log' # Directory where log files are written
    log_filename = 'postgresql-%a.log' # Log file name pattern.
    log_truncate_on_rotation = on # If on, any existing log file of the same
    log_rotation_age = 1440 # Automatic rotation of logfiles will
    log_rotation_size = 0 # Automatic rotation of logfiles will
    stats_start_collector = on
    stats_command_string = off
    stats_block_level = off
    stats_row_level = off
    stats_reset_on_server_start = off
    autovacuum = on # enable autovacuum subprocess?
    autovacuum_naptime = 60 # time between autovacuum runs, in secs
    autovacuum_vacuum_threshold = 2000 # min # of tuple updates before
    autovacuum_analyze_threshold = 700 # min # of tuple updates before
    lc_messages = 'en_US.UTF-8' # locale for system error message
    lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
    lc_numeric = 'en_US.UTF-8' # locale for number formatting
    lc_time = 'en_US.UTF-8' # locale for time formatting
    Also if i look at which folder is consuming a lot of storage space.

    4.0K ./pg_xlog/archive_status
    818M ./pg_xlog
    4.0K ./base/16386/pgsql_tmp
    4.4G ./base/16386
    3.4M ./base/10793
    3.4M ./base/20881
    3.7M ./base/1
    3.5M ./base/10792
    4.0K ./base/16387/pgsql_tmp
    232G ./base/16387
    3.7M ./base/20882
    236G ./base
    97M ./pg_clog
    4.0K ./pg_twophase
    12K ./pg_multixact/offsets
    12K ./pg_multixact/members
    28K ./pg_multixact
    136K ./pg_subtrans
    4.0K ./pg_tblspc
    132K ./pg_log
    532K ./global
    237G .
    [root@strg-1 base]# du -h
    4.0K ./16386/pgsql_tmp
    4.4G ./16386
    3.4M ./10793
    3.4M ./20881
    3.7M ./1
    3.5M ./10792
    4.0K ./16387/pgsql_tmp
    232G ./16387
    3.7M ./20882
    236G .
    [root@strg-1 base]#
    Any help on this issue is much appreciated.

  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Rep Power
    It is not uncommon for databases to requiremany times their own size in diskspace, because of a neat feature called "indexes". If you have a 10GB table and you index the majority of it's columns in four different ways, yo will create around 10GB of diskspace per index; 40GB of extra space will be required.

    The PostgreSQL Wiki has a few queries that show exactly how much space is required for each element:

    Another reason may be bloating; if there has not been enouch vacuumm activity your datafiles may contain lots of empty space that is never reclaimed:

    And to find out if/where there is bloating going on:

    Whatever you do, do not blindly start running vacuum and definately do not remove any indexes to save space. PostgreSQL can track index usage over time so you can see exactly which indexes are used and which are not.

IMN logo majestic logo threadwatch logo seochat tools logo