Re: Configuration Recommendations - Mailing list pgsql-performance

Below is the hardware, firmware, OS, and PG configuration pieces that I'm settling in on. As was noted, the local storage used for OS is actually two disks with RAID 10. If anything appears like a mistake or something is missing, I'd appreciate the feedback.

I'm still working on the benchmarks scripts and I don't have good/reliable numbers yet since our SAN is still very busy reconfiguring from the 2x4 to 1x8. I'm hoping to get them running tomorrow when the SAN should complete its 60 hours of reconfiguration.

Thanks, again, for all the great feedback.


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 RAID10 2x15k drive for OS on local storage
*/dev/sda1 RA*                                            4096
*/dev/sda1 FS*                                            ext4 
*/dev/sda1 MO*

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

300GB RAID 10 2x15k drive for $PGDATA/pg_xlog on SAN
*IO Scheduler sdb*            noop anticipatory deadline [cfq]
*/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*                                            64MB
*PG maintenance_work_mem*                                 2GB




On Wed, Apr 25, 2012 at 3:29 PM, Shaun Thomas <sthomas@peak6.com> wrote:
>
> On 04/25/2012 02:46 AM, John Lister wrote:
>
>> Hi, I'd be grateful if you could share any XFS performance tweaks as I'm
>> not entirely sure I'm getting the most out of my setup and any
>> additional guidance would be very helpful.
>
>
> Ok, I'll give this with a huge caveat: these settings came from lots of testing, both load and pgbench based. I'll explain as much as I can.
>
> For initializing the XFS filesystem, you can take advantage of a few settings that are pretty handy.
>
> * -d agcount=256 - Higher amount of allocation groups works better with multi-CPU systems. We used 256, but you'll want to do tests to confirm this. The point is that you can have several threads writing to the filesystem simultaneously.
>
> * -l lazy-count=1 - Log data is written more efficiently. Gives a measurable performance boost. Newer versions set this, but CentOS 5 has the default to 0. I'm not sure about CentOS 6. Just enable it. :)
>
> * -l version=2 - Forces the most recent version of the logging algorithm; allows a larger log buffer on mount. Since you're using CentOS, the default value is still probably 1, which you don't want.
>
> And then there are the mount options. These actually seemed to make more of an impact in our testing:
>
> * allocsize=256m - Database files are up to 1GB in size. To prevent fragmentation, always pre-allocate in 256MB chunks. In recent 3.0+ kernels, this setting will result in phantom storage allocation as each file is initially allocated with 256MB until all references have exited memory. Due to aggressive Linux inode cache behavior, this may not happen for several hours. On 3.0 kernels, this setting should be removed. I think the 2.6.35 kernel had this backported, so *TEST THIS SETTING BEFORE USING IT!*
>
> * logbufs=8 - Forces more of the log buffer to remain in RAM, improving file deletion performance. Good for temporary files. XFS often gets knocked for file deletion performance, and this brings it way up. Not really an issue with PG usage, but handy anyway. See logbsize.
>
> * logbsize=256k - Larger log buffers keep track of modified files in memory for better performance. See logbufs.
>
> * noatime - Negates touching the disk for file accesses. Reduces disk IO.
>
> * attr2 - Opportunistic improvement in the way inline extended attributes are stored on-disk. Not strictly necessary, but handy.
>
>
> I'm hoping someone else will pipe in, because these settings are pretty "old" and based on a CentOS 5.5 setup. I haven't done any metrics on the newer kernels, but I have followed enough to know allocsize is dangerous on new systems.
>
> Your mileage may vary. :)
>
>
> --
> Shaun Thomas
> OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
> 312-444-8534
> sthomas@peak6.com
>
> ______________________________________________
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance