Re: Configuration Recommendations

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Starting to get some quantitative data now. Here is the results from the pgbench scaling:

  pgbench -t 2000 -c 32 -S pgbench

for scales of 2^n where n=0..14 for scale, DB size in MB, and transactions per second:

Scale DB Size   TPS
-------------------
    1     21  65618
    2     36  66060
    4     66  65939
    8    125  66469
   16    245  67065
   32    484  60764
   64    963  64676
  128   1920  68151
  256   3834  65933
  512   7662  50777
 1024  15360  66717
 2048  30720  62811
 4096  61440   5558
 8192 122880   1854

The range 2048-8192 is an area to study in more detail, obviously. Feedback welcome.


Cheers,

Jan


*System*                                 HP ProLiant BL460c G7

*BIOS*                                       HP I27 05/05/2011
*CPU Sockets*                                                2
*Chips*                   Intel(R) Xeon(R) CPU X5650 @ 2.67GHz
                          Intel(R) Xeon(R) CPU X5650 @ 2.67GHz
*CPU Cores*                                                 24
*Kernel Name*                                            Linux
*Kernel Version*                         2.6.32-220.el6.x86_64
*Machine Platform*                                      x86_64
*Processor Type*                                        x86_64
*Operating System*                                   GNU/Linux
*Distribution*                      CentOS release 6.2 (Final)
*Write barriers*                   libata version 3.00 loaded.
*MemTotal*                                          49410668kB
*PAGE_SIZE*                                               4096
*_PHYS_PAGES*                                         12352667
*kernel.shmall*                                        6176333
*kernel.shmmax*                                    25298259968
*kernel.sem*                                  250 32000 32 128
*vm.swappiness*                                              0
*vm.overcommit_memory*                                       2
*dirty_ratio*                                                5
*dirty_background_ratio*                                     2

300GB RAID1 2x15k drive for OS on local storage
*/dev/sda1 RA*                                            4096
*/dev/sda1 FS*                                            ext4 
*/dev/sda1 MO*
*IO Scheduler sda*            noop anticipatory deadline [cfq]

600GB RAID1+0 8x15k drive for $PGDATA on SAN
*/dev/sdb1 RA*                                            4096
*/dev/sdb1 FS*                                             xfs
*/dev/sdb1 MO*       
allocsize=256m,attr2,logbufs=8,logbsize=256k,noatime             
*IO Scheduler sdb*            noop anticipatory deadline [cfq]

300GB RAID1 2x15k drive for $PGDATA/pg_xlog on SAN
*/dev/sde1 RA*                                            4096
*/dev/sde1 FS*                                             xfs
*/dev/sde1 MO*        allocsize=256m,attr2,logbufs=8,logbsize=256k,noatime
*IO Scheduler sde*            noop anticipatory deadline [cfq]


PG Configuration

*PG shared_buffers*                                      16GB

*PG log_line_prefix*                      '%t:%u@%r=>%d:[%p]: '
*PG log_statement*                                        ddl
*PG log_min_duration_statement*                            1s
*PG listen_addresses*                                       *
*PG checkpoint_segments*                                   32
*PG checkpoint_completion_target*                         0.9
*PG max_connections*                                      100
*PG max_fsm_relations*                                     
*PG max_fsm_pages*                                         
*PG wal_buffers*                                         16MB
*PG wal_sync_method*                                open_sync
*PG effective_cache_size*                                32GB
*PG random_page_cost*                                       4
*PG constraint_exclusion*                           partition
*PG work_mem*                                            32MB
*PG maintenance_work_mem*                                 2GB

Attachment: pgbench-select.png
Description: PNG image

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux