Thread: write barrier question
I'm just starting the process of trying to tune new hardware, which is 2x quad core xeon, 48GB RAM, 8x300GB SAS 15K drives in RAID 1+0, 2x72GB 15K SAS drives in RAID 1 for WAL and system. It is a PERC 6/i card with BBU. Write-back cache is enabled. The system volume is ext3. The large data partition is ext4. current config changes are as follows (but I've been experimenting with a variety of settings): default_statistics_target = 50 # pgtune wizard 2010-08-17 maintenance_work_mem = 1GB # pgtune wizard 2010-08-17 constraint_exclusion = on # pgtune wizard 2010-08-17 checkpoint_completion_target = 0.9 # pgtune wizard 2010-08-17 effective_cache_size = 36GB # sam work_mem = 288MB # pgtune wizard 2010-08-17 wal_buffers = 8MB # pgtune wizard 2010-08-17 #checkpoint_segments = 16 # pgtune wizard 2010-08-17 checkpoint_segments = 30 # sam shared_buffers = 11GB # pgtune wizard 2010-08-17 max_connections = 80 # pgtune wizard 2010-08-17 cpu_tuple_cost = 0.0030 # sam cpu_index_tuple_cost = 0.0010 # sam cpu_operator_cost = 0.0005 # sam #random_page_cost = 2.0 # sam It will eventually be a mixed-use db, but the OLTP load is very light. ETL for the warehouse side of things does no updates or deletes. Just inserts and partition drops. I know that default_statistics_target isn't right for a warehouse workload, but I haven't gotten to the point of tuning with a production workload, yet, so I'm leaving the pgtune default. When running pgbench on a db which fits easily into RAM (10% of RAM = -s 380), I see transaction counts a little less than 5K. When I go to 90% of RAM (-s 3420), transaction rate dropped to around 1000 ( at a fairly wide range of concurrencies). At that point, I decided to investigate the performance impact of write barriers. I tried building and running the test_fsync utility from the source distribution but really didn't understand how to interpret the results. So I just tried the same test with write barriers on and write barriers off (on both volumes). With barriers off, I saw a transaction rate of about 1200. With barriers on, it was closer to 1050. The test had a concurrency of 40 in both cases. From what I understand of the write barrier problem, a misbehaving controller will flush the cache to disk with every barrier, so I assume performance would drop a heck of a lot more than 13%. I assume the relatively small performance reduction is just contention on the write barriers between the 40 backends. I was hoping someone could confirm this (I could test on and off with lower concurrency, of course, but that will take hours to complete). It occurs to me that the relatively small drop in performance may also be the result of the relatively small db size. Our actual production db is likely to be closer to 200% of RAM, but the most active data should be a lot closer to 90% of RAM. Anyway, I could test all of this, but the testing takes so long (I'm running 30 minutes per test in order to get any kind of consistency of results) that it is likely faster to just ask the experts. I'd also welcome any other tuning suggestions. Thanks --sam
On 8/18/10 12:24 PM, Samuel Gendler wrote: > With barriers off, I saw a transaction rate of about 1200. With > barriers on, it was closer to 1050. The test had a concurrency of 40 > in both cases. I discovered there is roughly 10-20% "noise" in pgbench results after running the exact same test over a 24-hour period ona machine with no other activity. Be sure you run your tests enough times to get good statistics unless you're lookingat much larger differences. Craig
Samuel Gendler wrote: > When running pgbench on a db which fits easily into RAM (10% of RAM = > -s 380), I see transaction counts a little less than 5K. When I go to > 90% of RAM (-s 3420), transaction rate dropped to around 1000 ( at a > fairly wide range of concurrencies). At that point, I decided to > investigate the performance impact of write barriers. At 90% of RAM you're probable reading data as well, not only writing. Watching iostat -xk 1 or vmstat 1 during a test should confirm this. To find the maximum database size that fits comfortably in RAM you could try out http://github.com/gregs1104/pgbench-tools - my experience with it is that it takes less than 10 minutes to setup and run and after some time you get rewarded with nice pictures! :-) regards, Yeb Havinga
I am. I was giving mean numbers On Wed, Aug 18, 2010 at 12:56 PM, Craig James <craig_james@emolecules.com> wrote: > On 8/18/10 12:24 PM, Samuel Gendler wrote: >> >> With barriers off, I saw a transaction rate of about 1200. With >> barriers on, it was closer to 1050. The test had a concurrency of 40 >> in both cases. > > I discovered there is roughly 10-20% "noise" in pgbench results after > running the exact same test over a 24-hour period on a machine with no other > activity. Be sure you run your tests enough times to get good statistics > unless you're looking at much larger differences. > > Craig > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
On Wed, Aug 18, 2010 at 1:25 PM, Yeb Havinga <yebhavinga@gmail.com> wrote: > Samuel Gendler wrote: >> >> When running pgbench on a db which fits easily into RAM (10% of RAM = >> -s 380), I see transaction counts a little less than 5K. When I go to >> 90% of RAM (-s 3420), transaction rate dropped to around 1000 ( at a >> fairly wide range of concurrencies). At that point, I decided to >> investigate the performance impact of write barriers. > > At 90% of RAM you're probable reading data as well, not only writing. > Watching iostat -xk 1 or vmstat 1 during a test should confirm this. To find > the maximum database size that fits comfortably in RAM you could try out > http://github.com/gregs1104/pgbench-tools - my experience with it is that it > takes less than 10 minutes to setup and run and after some time you get > rewarded with nice pictures! :-) Yes. I've intentionally sized it at 90% precisely so that I am reading as well as writing, which is what an actual production environment will resemble.
2010/8/19 Samuel Gendler <sgendler@ideasculptor.com>: > On Wed, Aug 18, 2010 at 1:25 PM, Yeb Havinga <yebhavinga@gmail.com> wrote: >> Samuel Gendler wrote: >>> >>> When running pgbench on a db which fits easily into RAM (10% of RAM = >>> -s 380), I see transaction counts a little less than 5K. When I go to >>> 90% of RAM (-s 3420), transaction rate dropped to around 1000 ( at a >>> fairly wide range of concurrencies). At that point, I decided to >>> investigate the performance impact of write barriers. I have just tested a similar hardware (32GB RAM), I have around 15k tps with a database wich fit in RAM (around 22GB DB). The request and schema are very simple. My advice is too use a pgbouncer on a separate server as well as any benchmark. For very heavy bench I use 'tsung' benchmark tool. Also some of your postgresql.conf params looks strange : shared_buffer >8GB is probably working less well. You may want to fine tune OS for Dirty cache (and dirty write) and/or increase bgwriter agressivity. It may happen that your overall performance are better with a mount -o sync option ! (depend of your write usage, see iostat -x 2, especially the Wtps, during checkpoints) Perc6i handle well the write cache and you can use 'nobarrier' mount option too but mounting the filesystem 'sync' is not that bad (because the perc6 will buffer). >> >> At 90% of RAM you're probable reading data as well, not only writing. >> Watching iostat -xk 1 or vmstat 1 during a test should confirm this. To find >> the maximum database size that fits comfortably in RAM you could try out >> http://github.com/gregs1104/pgbench-tools - my experience with it is that it >> takes less than 10 minutes to setup and run and after some time you get >> rewarded with nice pictures! :-) > > Yes. I've intentionally sized it at 90% precisely so that I am > reading as well as writing, which is what an actual production > environment will resemble. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support