On Sun, Sep 11, 2011 at 6:17 PM, Tomas Vondra <t...@fuzzy.cz> wrote: > Dne 12.9.2011 00:44, Anthony Presley napsal(a): > > We've currently got PG 8.4.4 running on a whitebox hardware set up, > > with (2) 5410 Xeon's, and 16GB of RAM. It's also got (4) 7200RPM > > SATA drives, using the onboard IDE controller and ext3. > > > > A few weeks back, we purchased two refurb'd HP DL360's G5's, and > > were hoping to set them up with PG 9.0.2, running replicated. These > > machines have (2) 5410 Xeon's, 36GB of RAM, (6) 10k SAS drives, and > > are using the HP SA P400i with 512MB of BBWC. PG is running on an > > ext4 (noatime) partition, and they drives configured as RAID 1+0 > > (seems with this controller, I cannot do JBOD). I've spent a few > > hours going back and forth benchmarking the new systems, and have set > > up the DWC, and the accelerator cache using hpacucli. I've tried > > accelerator caches of 25/75, 50/50, and 75/25. > > Whas is an 'accelerator cache'? Is that the cache on the controller? > Then give 100% to the write cache - the read cache does not need to be > protected by the battery, the page cache at the OS level can do the same > service. >
It is the cache on the controller. I've tried giving 100% to that cache. > Provide more details about the ext3/ext4 - there are various data modes > (writeback, ordered, journal), various other settings (barriers, stripe > size, ...) that matter. > ext3 (on the old server) is using CentOS 5.2 defaults for mounting. ext4 (on the new server) is using noatime,barrier=0 > According to the benchmark I've done a few days back, the performance > difference between ext3 and ext4 is rather small, when comparing equally > configured file systems (i.e. data=journal vs. data=journal) etc. > > With read-only workload (e.g. just SELECT statements), the config does > not matter (e.g. journal is just as fast as writeback). > > See for example these comparisons > > read-only workload: http://bit.ly/q04Tpg > read-write workload: http://bit.ly/qKgWgn > > The ext4 is usually a bit faster than equally configured ext3, but the > difference should not be 100%. > Yes - it's very strange. > > To start with, I've set the "relevant" parameters in postgresql.conf > > the same on the new config as the old: > > > > max_connections = 150 shared_buffers = 6400MB (have tried as high as > > 20GB) work_mem = 20MB (have tried as high as 100MB) > > effective_io_concurrency = 6 fsync = on synchronous_commit = off > > wal_buffers = 16MB checkpoint_segments = 30 (have tried 200 when I > > was loading the db) random_page_cost = 2.5 effective_cache_size = > > 10240MB (have tried as high as 16GB) > > > > First thing I noticed is that it takes the same amount of time to > > load the db (about 40 minutes) on the new hardware as the old > > hardware. I was really hoping with the faster, additional drives and > > a hardware RAID controller, that this would be faster. The database > > is only about 9GB with pg_dump (about 28GB with indexes). > > > > Using pgfouine I've identified about 10 "problematic" SELECT queries > > that take anywhere from .1 seconds to 30 seconds on the old > > hardware. Running these same queries on the new hardware is giving me > > results in the .2 to 66 seconds. IE, it's twice as slow. > > > > I've tried increasing the shared_buffers, and some other parameters > > (work_mem), but haven't yet seen the new hardware perform even at > > the same speed as the old hardware. > > In that case some of the assumptions is wrong. For example the new RAID > is slow for some reason. Bad stripe size, slow controller, ... > > Do the basic hw benchmarking, i.e. use bonnie++ to benchmark the disk, > etc. Only if this provides expected results (i.e. the new hw performs > better) it makes sense to mess with the database. > > Tomas > -- Anthony Presley
