Re: Configuration Recommendations
| От | Jan Nielsen |
|---|---|
| Тема | Re: Configuration Recommendations |
| Дата | |
| Msg-id | CANxH4hEApmZSMqsBbzgSi3khnkonMwn+8cCxzzAZOFt7Lpzheg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Configuration Recommendations (Jan Nielsen <jan.sture.nielsen@gmail.com>) |
| Ответы |
Re: Configuration Recommendations
Re: Configuration Recommendations Re: Configuration Recommendations |
| Список | pgsql-performance |
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
*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
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
Вложения
В списке pgsql-performance по дате отправления:
Предыдущее
От: Richard JonesДата:
Сообщение: Partitioned/inherited tables with check constraints causing slower query plans