CoreData : PostgreSQL Server kraken setup This page last changed on Jun 27, 2013 by mccann. ### PostgreSQL Server kraken setup This page is for recording significant configuration settings for the dedicated PostgreSQL server kraken. It exists in concert with the wiki page for the pgtest server where various system and PostgreSQL parameters are tuned for optimal performance on a ESX virtual machine. The parameters for kraken will be deferent than for pgtest; however, pgtest provides a useful test environment for proposed changes. #### Hardware Documentation FusionIO "disk drive" Release Notes FusionIO "disk drive" User Guide for Linux Attachments: ioMemory_VSL_3.2.3_User_Guide_for_Linux.pdf (application/pdf) ioMemory_VSL_3.2.3_Release_Notes.pdf (application/pdf) kraken_sar_1aug2013.txt (text/plain) Comments:
    sudo yum install gnuplot
Posted by mccann at Jul 10, 2013 16:55
Configuring kraken to run pgbench-tools as user mccann on kraken:
    cd /var/www/html
    sudo mkdir pgbench-tools
    sudo chown postgres pgbench-tools
Copy pgbench-tools from its special configuration on pgtest for passwordless connection to the database, as postgres@pgtest:
    cd /var/lib/pgsql/dev/pgbench-tools
    tar cvf /tmp/pgbench-tools.tar *
Moved the .tar file from pgtest to kraken, then install in the web directory rather than in postgres's home directory. As postgres on kraken:
    cd /var/www/html/pgbench-tools
    cp /tmp/pgbench-tools.tar .
    tar xvf pgbench-tools.tar
Setup pgbench-tools databases and run an initial test as postgres@kraken:
    createdb results
    createdb pgbench
    cd /var/www/html/pgbench-tools
    psql -f init/resultdb.sql -d results
    sudo yum install postgresql91-contrib        # as mccann (needed for the pgbench binary)
    ./runset
Configure web server on kraken to serve up results from pgbench-tools. First, submit help ticket to open up port 80 for http on kraken.
    sudo chkconfig httpd on
    sudo /sbin/service httpd start
Posted by mccann at Jul 11, 2013 11:17
Modifying system parameters that have been tested on pgtest. Here are the changes:
    <197 kraken.shore.mbari.org //etc> diff sysctl.conf.130712 sysctl.conf
    40a41,53
    >
    > # Swappiness set to 0 per p.81 in PostgreSQL 9.0 High Performance book. mpm - 12 July 2013
    > vm.swappiness = 0
    > # Reduce tendency to allocate more ram than the system has. mpm - 12 July 2013
    > vm.overcommit_memory = 2
    >
    > # Setting for  shared memory = 1/2 physical RAM = 64 GB. mpm 12 July 2013
    > ##kernel.shmmax = 67649847296
    > ##kernel.shmall = 16516076
    >
    > # Set shared memory to 4 GB - a better value for PostgreSQL on large memory systems. mpm 12 July 2013
    > kernel.shmmax = 4228112384
    > kernel.shmall = 1032254

    <198 kraken.shore.mbari.org //etc> diff fstab.130712 fstab
    11,13c11,13
    < /dev/mapper/vg_kraken-LogVol02 /pg_xlog_spinning           ext4    defaults        1 2
    < /dev/mapper/vg_kraken-LogVol04 /pgdata_spinning            ext4    defaults        1 2
    < /dev/mapper/vg_kraken-LogVol03 /tablespace_spinning        ext4    defaults        1 2
    ---
    > /dev/mapper/vg_kraken-LogVol02 /pg_xlog_spinning           ext4    defaults,noatime        1 2
    > /dev/mapper/vg_kraken-LogVol04 /pgdata_spinning            ext4    defaults,noatime        1 2
    > /dev/mapper/vg_kraken-LogVol03 /tablespace_spinning        ext4    defaults,noatime        1 2
    22,24c22,24
    < /dev/mapper/iomemory_vg-iomemVol1 /pg_xlog      ext4    defaults,noauto,discard 0 0
    < /dev/mapper/iomemory_vg-iomemVol2 /tablespace   ext4    defaults,noauto,discard 0 0
    < /dev/mapper/iomemory_vg-iomemVol3 /pgdata       ext4    defaults,noauto,discard 0 0
    ---
    > /dev/mapper/iomemory_vg-iomemVol1 /pg_xlog      ext4    defaults,noatime,noauto,discard 0 0
    > /dev/mapper/iomemory_vg-iomemVol2 /tablespace   ext4    defaults,noatime,noauto,discard 0 0
    > /dev/mapper/iomemory_vg-iomemVol3 /pgdata       ext4    defaults,noatime,noauto,discard 0 0

    <199 kraken.shore.mbari.org //etc> diff rc.local.130712 rc.local
    7a8,9
    > blockdev --setra 8192 /dev/mapper/vg_kraken-LogVol04
    > blockdev --setra 8192 /dev/mapper/iomemory_vg-iomemVol3
pgbench-tools results at: http://kraken.shore.mbari.org/pgbench-tools/results.130715.AfterOSConfigChanges_tests_61_132/index.htm Posted by mccann at Jul 12, 2013 22:16
Modified postgresql.conf based on lessons learned on pgtest. Here's the diff:
    -bash-4.1$ diff postgresql.conf.130716 postgresql.conf
    59c59,60
    < #listen_addresses = 'localhost'               # what IP address(es) to listen on;
    ---
    > # Allow connections from mbari hosts inside our firewall - mpm 7 May 2013
    > listen_addresses = '*'                        # what IP address(es) to listen on;
    64c65,66
    < max_connections = 100                 # (change requires restart)
    ---
    > # Changed max_connections from 100 to 10 corresponding to increase in work_mem - mpm 22 May 2013
    > max_connections = 10                  # (change requires restart)
    109c111
    < shared_buffers = 32MB                 # min 128kB
    ---
    > shared_buffers = 3GB                  # min 128kB
    118c120,122
    < #work_mem = 1MB                               # min 64kB
    ---
    >
    > # Changed from 1 MB to 400MB to support large STOQS sorts - mpm 22 May 2013
    > work_mem = 400MB                      # min 64kB
    174c178,179
    < #checkpoint_segments = 3              # in logfile segments, min 1, 16MB each
    ---
    > # pgbench-tools indicated high checkpoint frequency, adjust with guidance from p.137.  dr 3 July 2013
    > checkpoint_segments = 10              # in logfile segments, min 1, 16MB each
    176c181
    < #checkpoint_completion_target = 0.5   # checkpoint target duration, 0.0 - 1.0
    ---
    > checkpoint_completion_target = 0.9    # checkpoint target duration, 0.0 - 1.0
    244c249,251
    < #random_page_cost = 4.0                       # same scale as above
    ---
    >
    > # Changed to 1.01 per Greg Smith's suggestion on postgresql-perform list - seems to improve STOQS mp query performance - mpm 14 May 2013
    > random_page_cost = 1.01                       # same scale as above
    359c366,367
    < #log_min_duration_statement = -1      # -1 is disabled, 0 logs all statements
    ---
    > # Log all queries taking longer than 1 second. mpm 15 July 2013
    > log_min_duration_statement = 1000     # -1 is disabled, 0 logs all statements
    377c385,387
    < #log_line_prefix = ''                 # special values:
    ---
    > # Set timestamp for log files to be compatible with pgFouine. mpm 15 July 2013
    > log_line_prefix = '%t [%p]: [%l-1] user=%u, db=%d'
    >                                       # special values:
    399c409,410
    < #log_temp_files = -1                  # log temporary files equal or larger
    ---
    > # Turned on logging of temp files.  See $PGDATA/base/<db oid>/pgsql_tmp for what a DB is doing - mpm 15 May 2013
    > log_temp_files = 0                    # log temporary files equal or larger
    482c493,494
    < #timezone = '(defaults to server environment setting)'
    ---
    > # Severs running STOQS databases must have timezone set to 'GMT'
    > timezone = 'GMT'
pgbench-tools results at: http://kraken.shore.mbari.org/pgbench-tools/results.130716.AfterPostgresql.confChanges_tests_133_204/index.htm (as expected, no appreciable changes in performance) Posted by mccann at Jul 16, 2013 12:05
Installed STOQS on kraken in /u/stoqsadm/dev/stoqshg_kraken. First time installing on a system with limited sudo privileges. The 'make install's for gdal and mapserver required help tickets for real root to do. Several additional packages needed to be installed and some problems were discovered when running loaders/loadTestData.py. Details documented in these change sets: https://code.google.com/p/stoqs/source/detail?r=fa16cdaf4acd4e7d4dd793c9eee9ca9319d21ff0 https://code.google.com/p/stoqs/source/detail?r=be90b1e585156e3816e488fe3f10f49f2852ace9 Posted by mccann at Jul 17, 2013 16:28
Fixed problems with stoqs install on kraken: Mapserver needed to be rebuilt with the configure argument: --with-postgis=/usr/pgsql-9.1/bin/pg_config and then copied to /var/www/cgi-bin Was getting "undefined symbol: GDALSetRasterUnitType" when trying to load web page. This was caused by Django not being able to find the locally built gdal. Instructions to add "export LD_LIBRARY_PATH='/usr/local/lib'" to the httpd startup script was added to the PRODUCTION file. We also needed to add this to the httpd startup script: export GDAL_DATA='/usr/share/gdal/'. Posted by mccann at Jul 19, 2013 14:45
Doing a little exercise of STOQS on kraken. So far performance is similar to odss-staging. Turned down log_min_duration to 100ms in order to see some queries logged. Installing pgFouine to analyze postgresql logs:
    sudo yum install pgfouine
Edit timezone setting in /etc/php.ini:
    > diff /etc/php.ini.130723 /etc/php.ini
    946c946
    < ;date.timezone =
    ---
    > date.timezone = America/Los_Angeles
Create a web directory for pgFouine reports and test running it:
    sudo mkdir /var/www/html/pgfouine
    sudo chown postgres /var/www/html/pgfouine
    pgfouine.php -logtype stderr -file /var/lib/pgsql/9.1/data/pg_log/postgresql-Mon.log -format html > /var/www/html/pgfouine/InitialTest.html
Results viewable at http://kraken.shore.mbari.org/pgfouine/InitialTest.html Posted by mccann at Jul 23, 2013 09:42
Configured Postgresql logging for production operation with datestamped log files and decreased log_min_duration to 10ms to capture more STOQS query activity:
    $ diff postgresql.conf.130723 postgresql.conf
    297c297
    < log_filename = 'postgresql-%a.log'    # log file name pattern,
    ---
    > log_filename = 'postgresql-%Y%m%d.log'        # log file name pattern,
    367c367
    < log_min_duration_statement = 100      # -1 is disabled, 0 logs all statements
    ---
    > log_min_duration_statement = 10               # -1 is disabled, 0 logs all statements
pgFouine now:
    pgfouine.php -logtype stderr -file /var/lib/pgsql/9.1/data/pg_log/postgresql-20130723.log -format html > /var/www/html/pgfouine/postgresql-20130723.html
gives: http://kraken.shore.mbari.org/pgfouine/postgresql-20130723.html The pgfouine.php command is something we can put into cron, or just run it when we need to and examine the reports in http://kraken.shore.mbari.org/pgfouine/. Posted by mccann at Jul 23, 2013 15:23
Last Thursday afternoon we exercised kraken's FusionIO drives with multiple parallel loads of STOQS campaign data. About 6 parallel loads were started around 2pm local time. Here is the sar report for that day as seen with this command:
    sar -f /var/log/sa/sa01 -b -d > kraken_sar_1aug2013.txt
kraken_sar_1aug2013.txt The highest %util was 12.59% at 2:20 PM. We're barely scratching the surface with the capabilities of this machine. Posted by mccann at Aug 05, 2013 16:19

Document generated by Confluence on Feb 04, 2026 08:22