CoreData : PostgreSQL-9.1 Server Tuning This page last changed on Jul 03, 2013 by mccann. ### PostgreSQL-9.1 Server Tuning Configuring a Postgres server to efficiently serve data is an involved exercise, but one that can be successful if a deliberate strategy is followed. This page exists to track setup and configuration changes and their results with the goal of making informed decisions on tuning a server for a particular application. We'll start with a STOQS database running on a VM configured according to WHD-16322:
    Request_Submitted_By: mccann@mbari.org
    VM_Name: pgtest
    VM_Purpose: To test PostgeSQL tuning parameters for current and future geospatial databases
    VM_Expiration: 1 year
    VM_Support: NOT_IS_Supported
    Alt_Administrator: Mike McCann
    Project_Number:
    VM_OS: CentOS 6 - Recommended for Linux
    RAM: 4
    Addl_RAM_Reason: Additional RAM needed to cache database requests and this needs to be closer to the production server value of 64 GB.
    VM_disk: 25 GB total space is fine, but I request 4 separate devices for mounting different "disks" for optimal database performance. Here is the requested layout:

    / (root) - Operating system [10 GB - or what you think is really needed]
    $PGDATA - Database data [10 GB]
    $PGDATA/pg_xlog - DB Write Ahead Log (WAL) [3 GB]
    Tablespace - Temporary files [2 GB]

    VM_Comments: I request root (or sudo to everything) on the system.

    The system really is temporary.  Once the production PostgreSQL server is in place and configured (by September 2013) this server will no longer be needed,
#### 6 May 2013 After the VM was setup the following was done: Installed PostgreSQL-9.1 and postgis via yum(1) Initialized cluster with "/sbin/service postgresql-9.1 initdb" Moved px_xlog, pg_tblspace, data to their own partitions Current disk/directory layout on pgtest:
    [root@pgtest 9.1]# mount -t ext4
    /dev/mapper/vg_templatecentos64-lv_root on / type ext4 (rw)
    /dev/sda1 on /boot type ext4 (rw)
    /dev/sdb1 on /pgdata type ext4 (rw)
    /dev/sdc1 on /pg_xlog type ext4 (rw)
    /dev/sdd1 on /tablespace type ext4 (rw)
    [root@pgtest 9.1]# ls -l
    total 8
    drwx------ 2 postgres postgres 4096 Apr  2 09:24 backups
    lrwxrwxrwx 1 root     root       12 May  6 16:02 data -> /pgdata/data
    -rw------- 1 postgres postgres 1749 May  6 16:03 pgstartup.log
    [root@pgtest 9.1]# cd data
    [root@pgtest data]# ls -l
    total 84
    drwx------ 6 postgres postgres  4096 May  6 16:05 base
    drwx------ 2 postgres postgres  4096 May  6 16:12 global
    drwx------ 2 postgres postgres  4096 May  6 15:29 pg_clog
    -rw------- 1 postgres postgres  4232 May  6 15:29 pg_hba.conf
    -rw------- 1 postgres postgres  1636 May  6 15:29 pg_ident.conf
    drwx------ 2 postgres postgres  4096 May  6 15:33 pg_log
    drwx------ 4 postgres postgres  4096 May  6 15:29 pg_multixact
    drwx------ 2 postgres postgres  4096 May  6 16:03 pg_notify
    drwx------ 2 postgres postgres  4096 May  6 15:29 pg_serial
    drwx------ 2 postgres postgres  4096 May  6 16:30 pg_stat_tmp
    drwx------ 2 postgres postgres  4096 May  6 15:29 pg_subtrans
    lrwxrwxrwx 1 root     root        21 May  6 15:57 pg_tblspc -> /tablespace/pg_tblspc
    drwx------ 2 postgres postgres  4096 May  6 15:29 pg_twophase
    -rw------- 1 postgres postgres     4 May  6 15:29 PG_VERSION
    lrwxrwxrwx 1 root     root        16 May  6 15:38 pg_xlog -> /pg_xlog/pg_xlog
    -rw------- 1 postgres postgres 19146 May  6 15:29 postgresql.conf
    -rw------- 1 postgres postgres    71 May  6 16:03 postmaster.opts
    -rw------- 1 postgres postgres    81 May  6 16:03 postmaster.pid
Starting on changing default system configurations (previous .conf file moved to <file>.YYMMDD file, copied to <file> and edited): /etc/sysctl.conf: vm.swappiness = 0 and vm.overcommit_memory = 2 /etc/fstab: Set noatime on the database partitions /etc/rc.local: blockdev --setra 8192 /dev/sdb1 (pgdata) #### 7 May 2013 Postgresql & system firewall configuration changes to allow connections across the network (see diffs from .130507 conf files): Edited /var/lib/pgsql/9.1/data/pg_hba.conf Edited /var/lib/pgsql/9.1/data/postgresql.conf Executed "sudo python /usr/bin/system-config-firewall" and added 5432:tcp & 5432:udp From machine odss-test: Added stoqs_september2012_pgtest to ~odssadm/dev/stoqshg/privateSettings pointing to the database on pgtest Added stoqsadm user and created stoqs_september2012 database according to instructions in the STOQS INSTALL file Started "~odssadm/dev/stoqshg/loaders/CANON/loadCANON_september2012.py 1 stoqs_september2012_pgtest" at 7 May 2013 1550 PDT Monitoring vmstat 1 and iostat 1 on pgtest vmstat showing no swap activity. Good. iostat showing ~215 tps and ~3500 Blk_wrtn/s to sdc, which is /pg_xlog every second iostat showing 64 to 500 Blk_wrtn/s every 3 seconds or so to sdb, which is /pgdata #### 8 May 2013 stoqs_march2013_s database copied to pgtest from odss-test by Neil using pg_dump / pg_restore to serve as a good test database that has 3,381,366 data values. Initial tests shows comparable performance between the databases accessible from the stoqs ui at http://odss-test.shore.mbari.org/canon/stoqs_march2013_s/query/ and http://odss-test.shore.mbari.org/canon/stoqs_march2013_s_pgtest/query/. Direct queries from psql on each machine are also comparable, e.g.:
    On odss-test:
    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=629955.51..638407.16 rows=3380659 width=20) (actual time=6082.363..6971.959 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: external merge  Disk: 112424kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55347.59 rows=3380659 width=20) (actual time=0.020..390.344 rows=3381814 loops=1)
     Total runtime: 7133.318 ms
    (5 rows)

    On pgtest:
    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=630174.15..638628.68 rows=3381814 width=20) (actual time=6214.177..7099.968 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: external merge  Disk: 112424kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..398.572 rows=3381814 loops=1)
     Total runtime: 7266.122 ms
    (5 rows)
##### Changing shared memory from initial value of 32 MB to 2 GB (half of all physical memory) First execute script from Page 101 in Postgres 9.0 High Performance book:
    <154 pgtest.shore.mbari.org /u/mccann> cat ./pg_smmem.sh
    #!/bin/bash
    # simple shmsetup script
    page_size=`getconf PAGE_SIZE`
    phys_pages=`getconf _PHYS_PAGES`
    shmall=`expr $phys_pages / 2`
    shmmax=`expr $shmall \* $page_size`
    echo kernel.shmmax = $shmmax
    echo kernel.shmall = $shmall

    <155 pgtest.shore.mbari.org /u/mccann> ./pg_smmem.sh
    kernel.shmmax = 2009198592
    kernel.shmall = 490527
Modified system and postgres .conf files: /etc/sysctl.conf with kernel values from above postgresql.conf with shared_buffers = 1 GB (I tried setting it to 2 GB, but got an error when restarting postgresql-9.1; our test db is only 778 MB anyway) No joy for immediate increase in performance. After rebooting the "explain analyze select * from stoqs_measuredparameter order by datavalue;" query still takes about 7.5 seconds. Installed postgres contrib package in order to get tools for peeking at Postgres's memory cache: "yum install postgresql91-contrib.x86_64". Continuing to read Chapter 5... #### 10 May 2013 Some work_mem setting tests:
    stoqs_march2013_s=# show work_mem;
     work_mem
    ----------
     1MB
    (1 row)

    stoqs_march2013_s=# set work_mem='354MB';
    SET
    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=7276.353..7659.802 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: external sort  Disk: 112400kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.048..382.085 rows=3381814 loops=1)
     Total runtime: 7828.067 ms
    (5 rows)

    stoqs_march2013_s=# set work_mem='355MB';
    SET
    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2399.499..2833.767 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: quicksort  Memory: 362509kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..357.892 rows=3381814 loops=1)
     Total runtime: 2998.478 ms
    (5 rows)
Bingo! Passing a threshold to 355MB of work_mem lets the sort happen as a quicksort in memory decreasing query time by better than half! Suggestions at http://stackoverflow.com/questions/8106181/work-mem-in-postgresql-on-linux and http://www.depesz.com/2011/07/03/understanding-postgresql-conf-work_mem/ are that the application can temporarily set the work_mem to an appropriate value for the queries that need more than the default value and then reset it when the query is done. Setting it globally to a large value can cause unneeded use of work_mem leading to out of memory errors. #### 13 May 2013 Following up on 8 May 2013 note, finishing pg_buffercache install. Note: instructions on p. 105 are not correct for pgsql-9.1. This completes the install and tests that it is installed:
    postgres=# create extension pg_buffercache;
    CREATE EXTENSION
    postgres=# \dx
                          List of installed extensions
          Name      | Version |   Schema   |           Description
    ----------------+---------+------------+---------------------------------
     pg_buffercache | 1.0     | public     | examine the shared buffer cache
     plpgsql        | 1.0     | pg_catalog | PL/pgSQL procedural language
    (2 rows)

    postgres=# select name,setting,unit,current_setting(name) from pg_settings where name='shared_buffers';
          name      | setting | unit | current_setting
    ----------------+---------+------+-----------------
     shared_buffers | 131072  | 8kB  | 1GB
    (1 row)
#### 14 May 2013 Following one of the suggestions from the PERFORM list, it seems that setting random_page_cost to 1 (from the default of 4) helps improve an actual query performed by the STOQS UI. With default random_page_cost = 4:
    stoqs_march2013_s=# set random_page_cost=4;
    SET
    stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
           stoqs_parameter.name AS parameter__name,
           stoqs_parameter.standard_name AS parameter__standard_name,
           stoqs_measurement.depth AS measurement__depth,
           stoqs_measurement.geom AS measurement__geom,
           stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
           stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
           stoqs_measuredparameter.datavalue AS datavalue,
           stoqs_parameter.units AS parameter__units
    FROM stoqs_parameter p1,
         stoqs_measuredparameter
         INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
         INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
         INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
         INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
         INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
         INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
    WHERE (p1.name = 'sea_water_sigma_t')
          AND (mp1.datavalue > 25.19)
          AND (mp1.datavalue < 26.01)
          AND (mp1.parameter_id = p1.id)
          AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
          AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
          AND stoqs_parameter.name IN ('fl700_uncorr')
          AND stoqs_measurement.depth >= -1.88
          AND stoqs_platform.name IN ('dorado')
          AND stoqs_measurement.depth <= 83.57)
    ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
                                                                                                    QUERY PLAN

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    -------------------------
     Sort  (cost=10741.41..10741.42 rows=1 width=1282) (actual time=358.452..358.452 rows=0 loops=1)
       Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
       Sort Method: quicksort  Memory: 25kB
       ->  Hash Join  (cost=3002.89..10741.40 rows=1 width=1282) (actual time=358.445..358.445 rows=0 loops=1)
             Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
             ->  Nested Loop  (cost=2983.69..10722.19 rows=3 width=954) (actual time=358.269..358.269 rows=0 loops=1)
                   ->  Nested Loop  (cost=2983.69..9617.36 rows=191 width=946) (actual time=12.788..296.022 rows=20170 loops=1)
                         ->  Hash Join  (cost=2983.69..8499.07 rows=193 width=842) (actual time=12.778..223.137 rows=20170 loops=1)
                               Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
                               ->  Nested Loop  (cost=2982.38..8478.47 rows=4628 width=24) (actual time=12.752..188.772 rows=197746 loops=1)
                                     ->  Nested Loop  (cost=2982.38..4862.37 rows=512 width=4) (actual time=12.733..25.939 rows=20170 loops=1)
                                           ->  Seq Scan on stoqs_parameter p1  (cost=0.00..1.30 rows=1 width=4) (actual time=0.001..0.007 rows=1 loops=1)
                                                 Filter: ((name)::text = 'sea_water_sigma_t'::text)
                                           ->  Bitmap Heap Scan on stoqs_measuredparameter mp1  (cost=2982.38..4854.40 rows=534 width=8) (actual time=12.724..21.223 rows=20170 loops
    =1)
                                                 Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
                                                 ->  BitmapAnd  (cost=2982.38..2982.38 rows=534 width=0) (actual time=12.326..12.326 rows=0 loops=1)
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_datavalue  (cost=0.00..259.54 rows=12292 width=0) (actual time=5.938..5.938 r
    ows=23641 loops=1)
                                                             Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_parameter_id  (cost=0.00..2719.38 rows=147035 width=0) (actual time=5.847..5.
    847 rows=34750 loops=1)
                                                             Index Cond: (parameter_id = p1.id)
                                     ->  Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter  (cost=0.00..6.98 rows=7 width=20) (actual time=0.003..0.
    005 rows=10 loops=20170)
                                           Index Cond: (measurement_id = mp1.measurement_id)
                               ->  Hash  (cost=1.30..1.30 rows=1 width=826) (actual time=0.010..0.010 rows=1 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                     ->  Seq Scan on stoqs_parameter  (cost=0.00..1.30 rows=1 width=826) (actual time=0.004..0.008 rows=1 loops=1)
                                           Filter: ((name)::text = 'fl700_uncorr'::text)
                         ->  Index Scan using stoqs_measurement_pkey on stoqs_measurement  (cost=0.00..5.78 rows=1 width=116) (actual time=0.003..0.003 rows=1 loops=20170)
                               Index Cond: (id = stoqs_measuredparameter.measurement_id)
                               Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
                   ->  Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint  (cost=0.00..5.77 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=20170)
                         Index Cond: (id = stoqs_measurement.instantpoint_id)
                         Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone))
             ->  Hash  (cost=18.82..18.82 rows=30 width=336) (actual time=0.165..0.165 rows=7 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 1kB
                   ->  Hash Join  (cost=1.09..18.82 rows=30 width=336) (actual time=0.073..0.162 rows=7 loops=1)
                         Hash Cond: (stoqs_activity.platform_id = stoqs_platform.id)
                         ->  Seq Scan on stoqs_activity  (cost=0.00..16.77 rows=177 width=66) (actual time=0.006..0.048 rows=177 loops=1)
                         ->  Hash  (cost=1.07..1.07 rows=1 width=278) (actual time=0.018..0.018 rows=1 loops=1)
                               Buckets: 1024  Batches: 1  Memory Usage: 1kB
                               ->  Seq Scan on stoqs_platform  (cost=0.00..1.07 rows=1 width=278) (actual time=0.012..0.015 rows=1 loops=1)
                                     Filter: ((name)::text = 'dorado'::text)
     Total runtime: 358.698 ms
    (42 rows)
With random_page_cost = 1:
    stoqs_march2013_s=# set random_page_cost=1;
    SET
    stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
           stoqs_parameter.name AS parameter__name,
           stoqs_parameter.standard_name AS parameter__standard_name,
           stoqs_measurement.depth AS measurement__depth,
           stoqs_measurement.geom AS measurement__geom,
           stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
           stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
           stoqs_measuredparameter.datavalue AS datavalue,
           stoqs_parameter.units AS parameter__units
    FROM stoqs_parameter p1,
         stoqs_measuredparameter
         INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
         INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
         INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
         INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
         INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
         INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
    WHERE (p1.name = 'sea_water_sigma_t')
          AND (mp1.datavalue > 25.19)
          AND (mp1.datavalue < 26.01)
          AND (mp1.parameter_id = p1.id)
          AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
          AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
          AND stoqs_parameter.name IN ('fl700_uncorr')
          AND stoqs_measurement.depth >= -1.88
          AND stoqs_platform.name IN ('dorado')
          AND stoqs_measurement.depth <= 83.57)
    ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
                                                                                                   QUERY PLAN

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    ------------------------
     Sort  (cost=3930.09..3930.09 rows=1 width=1282) (actual time=185.676..185.676 rows=0 loops=1)
       Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
       Sort Method: quicksort  Memory: 25kB
       ->  Hash Join  (cost=1678.86..3930.08 rows=1 width=1282) (actual time=185.656..185.656 rows=0 loops=1)
             Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
             ->  Nested Loop  (cost=1677.55..3928.72 rows=10 width=464) (actual time=185.626..185.626 rows=0 loops=1)
                   ->  Hash Join  (cost=1677.55..3926.60 rows=1 width=456) (actual time=185.625..185.625 rows=0 loops=1)
                         Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
                         ->  Nested Loop  (cost=1667.82..3916.85 rows=7 width=128) (actual time=185.548..185.548 rows=0 loops=1)
                               ->  Nested Loop  (cost=1667.82..3075.30 rows=507 width=120) (actual time=12.112..111.871 rows=20170 loops=1)
                                     ->  Nested Loop  (cost=1667.82..2211.81 rows=512 width=4) (actual time=12.098..26.814 rows=20170 loops=1)
                                           ->  Seq Scan on stoqs_parameter p1  (cost=0.00..1.30 rows=1 width=4) (actual time=0.003..0.011 rows=1 loops=1)
                                                 Filter: ((name)::text = 'sea_water_sigma_t'::text)
                                           ->  Bitmap Heap Scan on stoqs_measuredparameter mp1  (cost=1667.82..2203.83 rows=534 width=8) (actual time=12.088..20.804 rows=20170 loops
    =1)
                                                 Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
                                                 ->  BitmapAnd  (cost=1667.82..1667.82 rows=534 width=0) (actual time=11.707..11.707 rows=0 loops=1)
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_datavalue  (cost=0.00..157.26 rows=12292 width=0) (actual time=5.885..5.885 r
    ows=23641 loops=1)
                                                             Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_parameter_id  (cost=0.00..1507.11 rows=147035 width=0) (actual time=5.265..5.
    265 rows=34750 loops=1)
                                                             Index Cond: (parameter_id = p1.id)
                                     ->  Index Scan using stoqs_measurement_pkey on stoqs_measurement  (cost=0.00..1.67 rows=1 width=116) (actual time=0.003..0.003 rows=1 loops=2017
    0)
                                           Index Cond: (id = mp1.measurement_id)
                                           Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
                               ->  Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint  (cost=0.00..1.65 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=20170)
                                     Index Cond: (id = stoqs_measurement.instantpoint_id)
                                     Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone)
    )
                         ->  Hash  (cost=9.35..9.35 rows=30 width=336) (actual time=0.071..0.071 rows=7 loops=1)
                               Buckets: 1024  Batches: 1  Memory Usage: 1kB
                               ->  Nested Loop  (cost=0.00..9.35 rows=30 width=336) (actual time=0.057..0.064 rows=7 loops=1)
                                     ->  Seq Scan on stoqs_platform  (cost=0.00..1.07 rows=1 width=278) (actual time=0.004..0.006 rows=1 loops=1)
                                           Filter: ((name)::text = 'dorado'::text)
                                     ->  Index Scan using stoqs_activity_platform_id on stoqs_activity  (cost=0.00..7.83 rows=35 width=66) (actual time=0.049..0.052 rows=7 loops=1)
                                           Index Cond: (platform_id = stoqs_platform.id)
                   ->  Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter  (cost=0.00..2.02 rows=7 width=20) (never executed)
                         Index Cond: (measurement_id = stoqs_measurement.id)
             ->  Hash  (cost=1.30..1.30 rows=1 width=826) (actual time=0.023..0.023 rows=1 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 1kB
                   ->  Seq Scan on stoqs_parameter  (cost=0.00..1.30 rows=1 width=826) (actual time=0.016..0.020 rows=1 loops=1)
                         Filter: ((name)::text = 'fl700_uncorr'::text)
     Total runtime: 185.850 ms
    (40 rows)
Query went from 0.36 seconds to 0.18 seconds. Based on this the random_page_cost setting in postgresql.conf was changed from 4 to 1.01 on pgtest. Comparing similar queries between http://odss-test.shore.mbari.org/canon/stoqs_march2013_s_pgtest/query/ and http://odss-test.shore.mbari.org/canon/stoqs_march2013_s/query/ shows no appreciable improvement in performance on the pgtest database. #### 15 May 2013 Preparing for when the STOQS application will be setting work_mem for the queries that need it I have set up logging of temp files that are created when there is not enough work_mem for sorts to be done in memory. The instructions are in the book and on this page: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server PostgreSQL 8.3 and later In 8.3 you can use log_temp_files to figure out if sorts are using disk instead of fitting in memory. In earlier versions, you might instead just monitor the size of them by looking at how much space is being used in the various $PGDATA/base/<db oid>/pgsql_tmp files. You can see sorts to disk happen in EXPLAIN ANALYZE plans as well. For example, if you see a line like "Sort Method: external merge Disk: 7526kB" in there, you'd know a work_mem of at least 8MB would really improve how fast that query executed, by sorting in RAM instead of swapping to disk. The change to the postgresql.conf file was made today with the previous version saved to postgresql.conf.130515 #### 16 May 2013 Another post to the pgsql-perform mail list asking for opinions on a more conventional server configuration:
    From: Mike McCann <mccann@mbari.org>
    Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
    Date: May 13, 2013 3:36:03 PM PDT
    To: pgsql-performance@postgresql.org
    Cc: Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>, Julien Cigar <jcigar@ulb.ac.be>,
    Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com>

    On May 7, 2013, at 4:21 PM, Jeff Janes wrote:

    On Thu, May 2, 2013 at 6:35 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
    On Thu, May 2, 2013 at 5:11 PM, Mike McCann <mccann@mbari.org> wrote:
    > Hello,
    >
    > We are in the fortunate situation of having more money than time to help
    > solve our PostgreSQL 9.1 performance problem.
    >
    > Our server hosts databases that are about 1 GB in size with the largest
    > tables having order 10 million 20-byte indexed records. The data are loaded
    > once and then read from a web app and other client programs.  Some of the
    > queries execute ORDER BY on the results. There are typically less than a
    > dozen read-only concurrent connections to any one database.

    I wouldn't count on this being a problem that can be fixed merely by throwing money at it.

    How many rows does any one of these queries need to access and then ORDER BY?

    ...

    >
    > HP ProLiant DL360p Gen 8
    > Dual Intel Xeon 2.4GHz 4-core E5-2609 CPUs
    > 64GB RAM
    > 2x146GB 15K SAS hard drives
    > 3x200GB SATA SLC SSDs
    > + the usual accessories (optical drive, rail kit, dual power supplies)

    If your DB is 1G, and will grow to 10G then the IO shouldn't be any
    problem, as the whole db should be cached in memory.

    But it can take a surprisingly long time to get it cached in the first place, from a cold start.

    If that is the problem, pg_prewarm could help.

    Cheers,

    Jeff

    Thank you everyone for your suggestions.

    It's clear that our current read performance was not limited by hardware.  An 'explain analyze' for a sample query is:

    stoqs_march2013_s=# show work_mem;
     work_mem
    ----------
     1MB
    (1 row)

    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=541002.15..549456.68 rows=3381814 width=20) (actual time=6254.780..7244.074 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: external merge  Disk: 112424kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.011..354.385 rows=3381814 loops=1)
     Total runtime: 7425.854 ms
    (5 rows)

    Increasing work_mem to 355 MB improves the performance by a factor of 2:

    stoqs_march2013_s=# set work_mem='355MB';
    SET
    stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
                                                                    QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2503.078..2937.130 rows=3381814 loops=1)
       Sort Key: datavalue
       Sort Method: quicksort  Memory: 362509kB
       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
     Total runtime: 3094.601 ms
    (5 rows)

    I tried changing random_page_cost to from 4 to 1 and saw no change.

    I'm wondering now what changes might get this query to run in less than one second.  If all the data is in memory, then will faster CPU and memory be
    the things that help?

    We have an alternate (a bit more conventional) server configuration that we are considering:

    HP ProLiant DL360p Gen 8
    Dual Intel Xeon 3.3GHz 4-core E5-2643 CPUs
    128GB PC3-12800 RAM
    16x146GB 15K SAS hard drives
    HP Smart Array P822/2GB FBWC controller + P420i w/ 2GB FBWC
    + the usual accessories (optical drive, rail kit, dual power supplies)

    All suggestions welcomed!

    -Mike

    --
    Mike McCann
    Software Engineer
    Monterey Bay Aquarium Research Institute
    7700 Sandholdt Road
    Moss Landing, CA 95039-9644
    Voice: 831.775.1769  Fax: 831.775.1736 http://www.mbari.org
The responses:
    From: Mike McCann <mccann@mbari.org>
    Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
    Date: May 13, 2013 4:58:17 PM PDT
    To: Jeff Janes <jeff.janes@gmail.com>
    Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>,
    Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>

    On May 13, 2013, at 4:24 PM, Jeff Janes wrote:

    > On Mon, May 13, 2013 at 3:36 PM, Mike McCann <mccann@mbari.org> wrote:
    >
    > Increasing work_mem to 355 MB improves the performance by a factor of 2:
    >
    > stoqs_march2013_s=# set work_mem='355MB';
    > SET
    > stoqs_march2013_s=# explain analyze select * from stoqs_measuredparameter order by datavalue;
    >                                                                 QUERY PLAN
    > ------------------------------------------------------------------------------------------------------------------------------------------
    >  Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual time=2503.078..2937.130 rows=3381814 loops=1)
    >    Sort Key: datavalue
    >    Sort Method: quicksort  Memory: 362509kB
    >    ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14 rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
    >  Total runtime: 3094.601 ms
    > (5 rows)
    >
    > I tried changing random_page_cost to from 4 to 1 and saw no change.
    >
    > I'm wondering now what changes might get this query to run in less than one second.

    I think you are worrying about the wrong thing here.  What is a web app going to do with 3,381,814 rows, once it obtains them?  Your current testing
    is not testing the time it takes to stream that data to the client, or for the client to do something meaningful with that data.

    If you only plan to actually fetch a few dozen of those rows, then you probably need to incorporate that into your test, either by using a LIMIT, or
    by using a mock-up of the actual application to do some timings.

    Also, what is the type and collation of the column you are sorting on?  non-'C' collations of text columns sort about 3 times slower than 'C' collation does.

    If all the data is in memory, then will faster CPU and memory be the things that help?

    Yes, those would help (it is not clear to me which of the two would help more), but I think you need to rethink your design of sending the entire database
    table to the application server for each page-view.

    Cheers,

    Jeff

    Hi Jeff,

    The datavalue column is double precision:

    stoqs_march2013_s=# \d+ stoqs_measuredparameter
                                                  Table "public.stoqs_measuredparameter"
         Column     |       Type       |                              Modifiers                               | Storage | Description
    ----------------+------------------+----------------------------------------------------------------------+---------+-------------
     id             | integer          | not null default nextval('stoqs_measuredparameter_id_seq'::regclass) | plain   |
     measurement_id | integer          | not null                                                             | plain   |
     parameter_id   | integer          | not null                                                             | plain   |
     datavalue      | double precision | not null                                                             | plain   |
    Indexes:
        "stoqs_measuredparameter_pkey" PRIMARY KEY, btree (id)
        "stoqs_measuredparameter_measurement_id_parameter_id_key" UNIQUE CONSTRAINT, btree (measurement_id, parameter_id)
        "stoqs_measuredparameter_datavalue" btree (datavalue)
        "stoqs_measuredparameter_measurement_id" btree (measurement_id)
        "stoqs_measuredparameter_parameter_id" btree (parameter_id)
    Foreign-key constraints:
        "stoqs_measuredparameter_measurement_id_fkey" FOREIGN KEY (measurement_id) REFERENCES stoqs_measurement(id) DEFERRABLE INITIALLY DEFERRED
        "stoqs_measuredparameter_parameter_id_fkey" FOREIGN KEY (parameter_id) REFERENCES stoqs_parameter(id) DEFERRABLE INITIALLY DEFERRED
    Has OIDs: no

    Thanks for the suggestion and advice to examine the web app performance.  We've actually taken quite a few steps to optimize how the web app works. The example query
    I provided is a simple worst-case one that we can use to help us decide on the proper hardware.  An actual query performed by the web app is:

    stoqs_march2013_s=# explain analyze SELECT stoqs_measuredparameter.id,
    stoqs_march2013_s-#        stoqs_parameter.name AS parameter__name,
    stoqs_march2013_s-#        stoqs_parameter.standard_name AS parameter__standard_name,
    stoqs_march2013_s-#        stoqs_measurement.depth AS measurement__depth,
    stoqs_march2013_s-#        stoqs_measurement.geom AS measurement__geom,
    stoqs_march2013_s-#        stoqs_instantpoint.timevalue AS measurement__instantpoint__timevalue,
    stoqs_march2013_s-#        stoqs_platform.name AS measurement__instantpoint__activity__platform__name,
    stoqs_march2013_s-#        stoqs_measuredparameter.datavalue AS datavalue,
    stoqs_march2013_s-#        stoqs_parameter.units AS parameter__units
    stoqs_march2013_s-# FROM stoqs_parameter p1,
    stoqs_march2013_s-#      stoqs_measuredparameter
    stoqs_march2013_s-#      INNER JOIN stoqs_measurement ON (stoqs_measuredparameter.measurement_id = stoqs_measurement.id)
    stoqs_march2013_s-#      INNER JOIN stoqs_instantpoint ON (stoqs_measurement.instantpoint_id = stoqs_instantpoint.id)
    stoqs_march2013_s-#      INNER JOIN stoqs_parameter ON (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
    stoqs_march2013_s-#      INNER JOIN stoqs_activity ON (stoqs_instantpoint.activity_id = stoqs_activity.id)
    stoqs_march2013_s-#      INNER JOIN stoqs_platform ON (stoqs_activity.platform_id = stoqs_platform.id)
    stoqs_march2013_s-#      INNER JOIN stoqs_measuredparameter mp1 ON mp1.measurement_id = stoqs_measuredparameter.measurement_id
    stoqs_march2013_s-# WHERE (p1.name = 'sea_water_sigma_t')
    stoqs_march2013_s-#       AND (mp1.datavalue > 25.19)
    stoqs_march2013_s-#       AND (mp1.datavalue < 26.01)
    stoqs_march2013_s-#       AND (mp1.parameter_id = p1.id)
    stoqs_march2013_s-#       AND (stoqs_instantpoint.timevalue <= '2013-03-17 19:05:06'
    stoqs_march2013_s(#       AND stoqs_instantpoint.timevalue >= '2013-03-17 15:35:13'
    stoqs_march2013_s(#       AND stoqs_parameter.name IN ('fl700_uncorr')
    stoqs_march2013_s(#       AND stoqs_measurement.depth >= -1.88
    stoqs_march2013_s(#       AND stoqs_platform.name IN ('dorado')
    stoqs_march2013_s(#       AND stoqs_measurement.depth <= 83.57)
    stoqs_march2013_s-# ORDER BY stoqs_activity.name ASC, stoqs_instantpoint.timevalue ASC;
                                                                                                    QUERY PLAN

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    --------------------------
     Sort  (cost=10741.41..10741.42 rows=1 width=1282) (actual time=770.211..770.211 rows=0 loops=1)
       Sort Key: stoqs_activity.name, stoqs_instantpoint.timevalue
       Sort Method: quicksort  Memory: 25kB
       ->  Hash Join  (cost=3002.89..10741.40 rows=1 width=1282) (actual time=770.200..770.200 rows=0 loops=1)
             Hash Cond: (stoqs_instantpoint.activity_id = stoqs_activity.id)
             ->  Nested Loop  (cost=2983.69..10722.19 rows=3 width=954) (actual time=770.036..770.036 rows=0 loops=1)
                   ->  Nested Loop  (cost=2983.69..9617.36 rows=191 width=946) (actual time=91.369..680.072 rows=20170 loops=1)
                         ->  Hash Join  (cost=2983.69..8499.07 rows=193 width=842) (actual time=91.346..577.633 rows=20170 loops=1)
                               Hash Cond: (stoqs_measuredparameter.parameter_id = stoqs_parameter.id)
                               ->  Nested Loop  (cost=2982.38..8478.47 rows=4628 width=24) (actual time=91.280..531.408 rows=197746 loops=1)
                                     ->  Nested Loop  (cost=2982.38..4862.37 rows=512 width=4) (actual time=91.202..116.140 rows=20170 loops=1)
                                           ->  Seq Scan on stoqs_parameter p1  (cost=0.00..1.30 rows=1 width=4) (actual time=0.002..0.011 rows=1 loops=1)
                                                 Filter: ((name)::text = 'sea_water_sigma_t'::text)
                                           ->  Bitmap Heap Scan on stoqs_measuredparameter mp1  (cost=2982.38..4854.40 rows=534 width=8) (actual time=91.194..109.846 rows=20170 loop
    s=1)
                                                 Recheck Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision) AND (parameter_id = p1.id))
                                                 ->  BitmapAnd  (cost=2982.38..2982.38 rows=534 width=0) (actual time=90.794..90.794 rows=0 loops=1)
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_datavalue  (cost=0.00..259.54 rows=12292 width=0) (actual time=62.769..62.769
     rows=23641 loops=1)
                                                             Index Cond: ((datavalue > 25.19::double precision) AND (datavalue < 26.01::double precision))
                                                       ->  Bitmap Index Scan on stoqs_measuredparameter_parameter_id  (cost=0.00..2719.38 rows=147035 width=0) (actual time=27.412..2
    7.412 rows=34750 loops=1)
                                                             Index Cond: (parameter_id = p1.id)
                                     ->  Index Scan using stoqs_measuredparameter_measurement_id on stoqs_measuredparameter  (cost=0.00..6.98 rows=7 width=20) (actual time=0.008..0.
    017 rows=10 loops=20170)
                                           Index Cond: (measurement_id = mp1.measurement_id)
                               ->  Hash  (cost=1.30..1.30 rows=1 width=826) (actual time=0.012..0.012 rows=1 loops=1)
                                     Buckets: 1024  Batches: 1  Memory Usage: 1kB
                                     ->  Seq Scan on stoqs_parameter  (cost=0.00..1.30 rows=1 width=826) (actual time=0.007..0.010 rows=1 loops=1)
                                           Filter: ((name)::text = 'fl700_uncorr'::text)
                         ->  Index Scan using stoqs_measurement_pkey on stoqs_measurement  (cost=0.00..5.78 rows=1 width=116) (actual time=0.004..0.004 rows=1 loops=20170)
                               Index Cond: (id = stoqs_measuredparameter.measurement_id)
                               Filter: ((depth >= (-1.88)::double precision) AND (depth <= 83.57::double precision))
                   ->  Index Scan using stoqs_instantpoint_pkey on stoqs_instantpoint  (cost=0.00..5.77 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=20170)
                         Index Cond: (id = stoqs_measurement.instantpoint_id)
                         Filter: ((timevalue <= '2013-03-17 19:05:06-07'::timestamp with time zone) AND (timevalue >= '2013-03-17 15:35:13-07'::timestamp with time zone))
             ->  Hash  (cost=18.82..18.82 rows=30 width=336) (actual time=0.151..0.151 rows=7 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 1kB
                   ->  Hash Join  (cost=1.09..18.82 rows=30 width=336) (actual time=0.035..0.145 rows=7 loops=1)
                         Hash Cond: (stoqs_activity.platform_id = stoqs_platform.id)
                         ->  Seq Scan on stoqs_activity  (cost=0.00..16.77 rows=177 width=66) (actual time=0.005..0.069 rows=177 loops=1)
                         ->  Hash  (cost=1.07..1.07 rows=1 width=278) (actual time=0.014..0.014 rows=1 loops=1)
                               Buckets: 1024  Batches: 1  Memory Usage: 1kB
                               ->  Seq Scan on stoqs_platform  (cost=0.00..1.07 rows=1 width=278) (actual time=0.008..0.012 rows=1 loops=1)
                                     Filter: ((name)::text = 'dorado'::text)
     Total runtime: 770.445 ms
    (42 rows)

    We assume that steps taken to improve the worst-case query scenario will also improve these kind of queries.  If anything above pops out as needing better planning please let us know that too!

    Thanks,
    Mike

    --
    Mike McCann
    Software Engineer
    Monterey Bay Aquarium Research Institute
    7700 Sandholdt Road
    Moss Landing, CA 95039-9644
    Voice: 831.775.1769  Fax: 831.775.1736 http://www.mbari.org
Another response:
    From: Scott Marlowe <scott.marlowe@gmail.com>
    Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
    Date: May 13, 2013 6:09:58 PM PDT
    To: Mike McCann <mccann@mbari.org>
    Cc: Jeff Janes <jeff.janes@gmail.com>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>,
    Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>

    On Mon, May 13, 2013 at 5:58 PM, Mike McCann <mccann@mbari.org> wrote:

    > We assume that steps taken to improve the worst-case query scenario will
    > also improve these kind of queries.  If anything above pops out as needing
    > better planning please let us know that too!

    Bad assumption. If your real workload will be queries like the one
    here that takes 700 ms, but you'll be running 10,000 of them a second,
    you're tuning / hardware choices are going to be much different then
    if your query is going to be the previous 7 second one. Use realistic
    queries, not ones that are nothing like what your real ones will be.
    then use pgbench and its ability to run custom sql scripts to get a
    REAL idea how your hardware performs. Note that if you will run the
    slow query you posted like once a minute and roll it up or cache it
    then don't get too worried about it. Pay attention to the queries that
    will add up, in aggregate, to your greatest load.
Not a whole lot of opinions except that tuning a server requires diligent work and performance is something one must do some work to achieve, and cannot be had by simply throwing money at the problem. #### 20 May 2013 A few more responses over the weekend:
    From: Greg Smith <greg@2ndQuadrant.com>
    Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
    Date: May 19, 2013 7:44:16 PM PDT
    To: Mike McCann <mccann@mbari.org>
    Cc: pgsql-performance@postgresql.org, Yuri Levinsky <yuril@celltick.com>, Scott Marlowe <scott.marlowe@gmail.com>, Julien Cigar <jcigar@ulb.ac.be>,
    Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com>

    On 5/13/13 6:36 PM, Mike McCann wrote:
    >   stoqs_march2013_s=# explain analyze select * from
    >   stoqs_measuredparameter order by datavalue;
    >
    >   QUERY PLAN
    >   ------------------------------------------------------------------------------------------------------------------------------------------
    >     Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual
    >   time=2503.078..2937.130 rows=3381814 loops=1)
    >       Sort Key: datavalue
    >       Sort Method: quicksort  Memory: 362509kB
    >       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14
    >   rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
    >     Total runtime: 3094.601 ms
    >   (5 rows)

    > I tried changing random_page_cost to from 4 to 1 and saw no change.

    Have you tried putting an index by datavalue on this table?  Once you've done that, then changing random_page_cost will make using that index look less expensive.
    Sorting chews through a good bit of CPU time, and that's where all of your runtime is being spent at--once you increase work_mem up very high that is.

    > I'm wondering now what changes might get this query to run in less than
    > one second.  If all the data is in memory, then will faster CPU and
    > memory be the things that help?

    You're trying to fix a fundamental design issue with hardware.  That usually doesn't go well.  Once you get a box big enough to hold the whole database in RAM,
    beyond that the differences between server systems are relatively small.

    --
    Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
    PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
and
    From: Scott Marlowe <scott.marlowe@gmail.com>
    Subject: Re: [PERFORM] Hardware suggestions for maximum read performance
    Date: May 19, 2013 8:57:32 PM PDT
    To: Greg Smith <greg@2ndquadrant.com>
    Cc: Mike McCann <mccann@mbari.org>, "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>, Yuri Levinsky <yuril@celltick.com>,
    Julien Cigar <jcigar@ulb.ac.be>, Arjen van der Meijden <acmmailing@tweakers.net>, Jeff Janes <jeff.janes@gmail.com>

    On Sun, May 19, 2013 at 8:44 PM, Greg Smith <greg@2ndquadrant.com> wrote:
    >On 5/13/13 6:36 PM, Mike McCann wrote:
    >>
    >>   stoqs_march2013_s=# explain analyze select * from
    >>   stoqs_measuredparameter order by datavalue;
    >>
    >>   QUERY PLAN
    >>
    >>------------------------------------------------------------------------------------------------------------------------------------------
    >>     Sort  (cost=422106.15..430560.68 rows=3381814 width=20) (actual
    >>   time=2503.078..2937.130 rows=3381814 loops=1)
    >>       Sort Key: datavalue
    >>       Sort Method: quicksort  Memory: 362509kB
    >>       ->  Seq Scan on stoqs_measuredparameter  (cost=0.00..55359.14
    >>   rows=3381814 width=20) (actual time=0.016..335.745 rows=3381814 loops=1)
    >>     Total runtime: 3094.601 ms
    >>   (5 rows)
    >>
    >> I tried changing random_page_cost to from 4 to 1 and saw no change.
    >
    >
    > Have you tried putting an index by datavalue on this table?  Once you've
    > done that, then changing random_page_cost will make using that index look
    > less expensive.  Sorting chews through a good bit of CPU time, and that's
    > where all of your runtime is being spent at--once you increase work_mem up
    > very high that is.

    This++ plus cluster on that index if you can.
#### 2 July 2013 We made the decision made to purchase a server with a FusionIO card. Server is now set up on the network with name "kraken". Here is the confluence page for kraken's configuration settings. Setting up ability to run pgbench-tools on all the various servers. This may be done per user account by first installing from git and following the instructions in README.rst; however, the scripts need to run commands requiring createdb and vacuum privileges, so it's best to run as user postgres. So, logging into pgtest as user postgres and getting things set up:
    -bash-4.1$ pwd
    /var/lib/pgsql
    -bash-4.1$ mkdir dev
    -bash-4.1$ cd dev
    -bash-4.1$ git clone git://git.postgresql.org/git/pgbench-tools.git
    Initialized empty Git repository in /var/lib/pgsql/dev/pgbench-tools/.git/
    remote: Counting objects: 108, done.
    remote: Compressing objects: 100% (106/106), done.
    remote: Total 108 (delta 54), reused 0 (delta 0)
    Receiving objects: 100% (108/108), 24.61 KiB, done.
    Resolving deltas: 100% (54/54), done.

    -bash-4.1$ createdb results
    -bash-4.1$ createdb pgbench

    -bash-4.1$ cd pgbench-tools/
    -bash-4.1$ psql -f init/resultdb.sql -d results
    psql:init/resultdb.sql:1: ERROR:  table "timing" does not exist
    CREATE TABLE
    CREATE INDEX
    psql:init/resultdb.sql:12: ERROR:  table "tests" does not exist
    psql:init/resultdb.sql:29: NOTICE:  CREATE TABLE will create implicit sequence "tests_test_seq" for serial column "tests.test"
    psql:init/resultdb.sql:29: NOTICE:  CREATE TABLE will create implicit sequence "tests_set_seq" for serial column "tests.set"
    CREATE TABLE
    psql:init/resultdb.sql:31: ERROR:  table "testset" does not exist
    psql:init/resultdb.sql:36: NOTICE:  CREATE TABLE will create implicit sequence "testset_set_seq" for serial column "testset.set"
    CREATE TABLE
    INSERT 0 1
    psql:init/resultdb.sql:40: ERROR:  table "test_bgwriter" does not exist
    CREATE TABLE

    -bash-4.1$ psql -c "UPDATE testset SET info='Initial test set' WHERE set=1" -d results
    UPDATE 1
I think those ERROR messages can be ignored because the following tests work. The gnuplot package is needed for producing graphs of the results:
    sudo -c "yum install gnuplot"
Edit the config file with hard path to pgbench and reduced number of scale and client runs so that we have a quick execution to just test things:
    -bash-4.1$ mv config config.130702
    -bash-4.1$ cp config.130702 config
    -bash-4.1$ vi config
    -bash-4.1$ diff config.130702 config
    8c8
    < PGBENCHBIN=`which pgbench`
    ---
    > PGBENCHBIN=/usr/pgsql-9.1/bin/pgbench
    42,44c42,47
    < SCALES="1 10 100 1000"
    < SETCLIENTS="1 2 4 8 16 32"
    < SETTIMES=3
    ---
    > SCALES="1"
    > SETCLIENTS="1"
    > SETTIMES=1
    > #SCALES="1 10 100 1000"
    > #SETCLIENTS="1 2 4 8 16 32"
    > #SETTIMES=3
Edit scripts to use "local socket" connection (removing "-h $TESTHOST") for the postgres user:
    -bash-4.1$ diff runset.130702 runset
    19c19
    <     TESTPSQL="psql -h $TESTHOST -U $TESTUSER -p $TESTPORT -d $TESTDB"
    ---
    >     TESTPSQL="psql -U $TESTUSER -p $TESTPORT -d $TESTDB"
    24c24
    <     $PGBENCHBIN -i -s $SCALE -h $TESTHOST -U $TESTUSER -p $TESTPORT $TESTDB
    ---
    >     $PGBENCHBIN -i -s $SCALE -U $TESTUSER -p $TESTPORT $TESTDB

    -bash-4.1$ diff benchwarmer.130702 benchwarmer
    84,85c84,85
    < TESTPSQL="psql -h $TESTHOST -U $TESTUSER -p $TESTPORT -d $TESTDB"
    < RESULTPSQL="psql -h $RESULTHOST -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"
    ---
    > TESTPSQL="psql -U $TESTUSER -p $TESTPORT -d $TESTDB"
    > RESULTPSQL="psql -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"
    158c158
    < $PGBENCHBIN -f $BASEDIR/$TESTDIR/$SCRIPT -s $SCALE -l -n $TESTLEN -U $TESTUSER -h $TESTHOST -p $TESTPORT -c $CLIENTS $WORKERS $TESTDB > results.txt &
    ---
    > $PGBENCHBIN -f $BASEDIR/$TESTDIR/$SCRIPT -s $SCALE -l -n $TESTLEN -U $TESTUSER -p $TESTPORT -c $CLIENTS $WORKERS $TESTDB > results.txt &

    -bash-4.1$ diff webreport.130702 webreport
    10c10
    < RESULTPSQL="psql -h $RESULTHOST -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"
    ---
    > RESULTPSQL="psql -U $RESULTUSER -p $RESULTPORT -d $RESULTDB"
And now a test run looks like:
    -bash-4.1$ ./runset
    Removing old pgbench tables
    DROP TABLE
    VACUUM
    Creating new pgbench tables
    NOTICE:  table "pgbench_branches" does not exist, skipping
    NOTICE:  table "pgbench_tellers" does not exist, skipping
    NOTICE:  table "pgbench_accounts" does not exist, skipping
    NOTICE:  table "pgbench_history" does not exist, skipping
    creating tables...
    10000 tuples done.
    20000 tuples done.
    30000 tuples done.
    40000 tuples done.
    50000 tuples done.
    60000 tuples done.
    70000 tuples done.
    80000 tuples done.
    90000 tuples done.
    100000 tuples done.
    set primary key...
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_branches_pkey" for table "pgbench_branches"
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_tellers_pkey" for table "pgbench_tellers"
    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "pgbench_accounts_pkey" for table "pgbench_accounts"
    vacuum...done.
    Run set #1 of 1 with 1 clients scale=1
    Running tests using: psql -U postgres -p 5432 -d pgbench
    Storing results using: psql -U postgres -p 5432 -d results
    Cleaning up database pgbench
    TRUNCATE TABLE
    VACUUM
    CHECKPOINT
    INSERT 0 1
    This is test 3
    Script select.sql executing for 1 concurrent users...
    UPDATE 1
    transaction type: Custom query
    scaling factor: 1
    query mode: simple
    number of clients: 1
    number of threads: 1
    duration: 60 s
    number of transactions actually processed: 547599
    tps = 9126.622772 (including connections establishing)
    tps = 9127.071214 (excluding connections establishing)
    Worst latency results:
    2018
    2311
    2370
    3155
    3476
    Could not find/open font when opening font "arial", using internal non-scalable font
    Could not find/open font when opening font "arial", using internal non-scalable font

    Could not find/open font when opening font "arial", using internal non-scalable font
    Warning: empty x range [1:1], adjusting to [0.99:1.01]
    Warning: empty y range [9228:9228], adjusting to [9135.72:9320.28]
    Warning: empty y2 range [21:21], adjusting to [20.79:21.21]
    Could not find/open font when opening font "arial", using internal non-scalable font
    Warning: empty x range [1:1], adjusting to [0.99:1.01]
    Warning: empty y range [9228:9228], adjusting to [9135.72:9320.28]
    Could not find/open font when opening font "arial", using internal non-scalable font
    Warning: empty x range [1:1], adjusting to [0.99:1.01]
    Warning: empty y range [1:1], adjusting to [0.99:1.01]
    Warning: empty z range [9228:9228], adjusting to [9135.72:9320.28]
#### 3 July 2013 Making the results visible on an internally accessible web page. Turn on httpd and make an alias to the results directory that is produced above:
    sudo /bin/bash
    chkconfig httpd on
    /sbin/service httpd start
Confirm that http://pgtest.shore.mbari.org/ is visible. It wasn't because the firewall on pgtest was preventing it. A call to Pat Allen fixed this. Next, edit /etc/httpd/conf/httpd.conf to add the alias and make the postgres home dir world executable (don't do this on a production system!) so that the apache httpd process can serve the directory:
    # diff httpd.conf.130703 httpd.conf
    559a560,569
    > # Expose our pgbench-toolbox results
    > Alias /pgbench /var/lib/pgsql/dev/pgbench-tools/results
    >
    > <Directory "/var/lib/pgsql/dev/pgbench-tools/results">
    >     Options Indexes FollowSymLinks
    >     AllowOverride None
    >     Order allow,deny
    >     Allow from all
    > </Directory>
    >

    # chmod +x /var/lib/pgsql
The pgbench-toolbox results can now be shared at http://pgtest.shore.mbari.org/pgbench/ Comments:
Edit config file to change checkpoint_segments and checkpoint_completion_target
    -bash-4.1$ diff postgresql.conf postgresql.conf.130703
    178c178
    < checkpoint_segments = 10		# in logfile segments, min 1, 16MB each
    ---
    > #checkpoint_segments = 3		# in logfile segments, min 1, 16MB each
    180c180
    < checkpoint_completion_target = 0.9	# checkpoint target duration, 0.0 - 1.0
    ---
    > #checkpoint_completion_target = 0.5	# checkpoint target duration, 0.0 - 1.0
Changed due to log
    LOG:  checkpoints are occurring too frequently (2 seconds apart)
    HINT:  Consider increasing the configuration parameter "checkpoint_segments".
Posted by drolicheck at Jul 03, 2013 16:33
July 17 2013 Install pgfouine for log analyzer
     sudo -c "yum install pgfouine
Change date.timezone in /etc/php.ini file, errors will arise if this is left at default value.
    -bash-4.1$ diff php.ini php.ini.130717
    946c946
    < date.timezone = America/Los_Angeles
    ---
    > ;date.timezone =
Run a pgbench test to generate some query traffic.
    -bash-4.1$ ./pgbench -T 60 -c 4 pgbench
    starting vacuum...end.
    transaction type: TPC-B (sort of)
    scaling factor: 160
    query mode: simple
    number of clients: 4
    number of threads: 1
    duration: 60 s
    number of transactions actually processed: 34324
    tps = 571.932873 (including connections establishing)
    tps = 572.082084 (excluding connections establishing)
Run pgfouine on todays log file to make sure it works. *note: The command starts with pgfouine.php not just pgfouine.
    -bash-4.1$ pgfouine.php -logtype stderr -file /var/lib/pgsql/9.1/data/pg_log/postgresql-Wed.log

    #####  Overall statistics  #####

    Number of unique normalized queries: 1
    Number of queries:     1
    Total query duration:  0.0s

    #####  Queries by type  #####

    SELECT:    1    100.0%

    #####  Queries that took up the most time (N)  #####

    1) 0.0s - 1 - select count(*) from pgbench_branches
    --

    #####  Slowest queries  #####

    1) 0.00 s - select count(*) from pgbench_branches
    --

    #####  Most frequent queries (N)  #####

    1) 1 - 0.0s - select count(*) from pgbench_branches
    --

    #####  Slowest queries (N)  #####

    1) 0.00 - 1 - select count(*) from pgbench_branches
    --
Posted by drolicheck at Jul 17, 2013 17:16
Edit postgresql.conf log_filename value so we don't overwrite log files every week.
    -bash-4.1$ diff postgresql.conf.130723 postgresql.conf
    296c296
    < log_filename = 'postgresql-%a.log'	# log file name pattern,
    ---
    > log_filename = 'postgresql-%Y%m%d.log'	# log file name pattern,
Example of old file name format compared to new format. Old format:
    postgresql-Fri.log
New format:
    postgresql-20130723.log
Posted by drolicheck at Jul 23, 2013 15:22

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