Re: Amazon High I/O instances

I recreated the DB and WAL pools, and launched pgbench -i -s 10000. Here are the stats during the load (still running):

iostat (xbd13-14 are WAL zpool)

device     r/s   w/s    kr/s    kw/s qlen svc_t  %b

xbd8       0.0 471.5     0.0 14809.3   40  67.9  84

xbd7       0.0 448.1     0.0 14072.6   39  62.0  74

xbd6       0.0 472.3     0.0 14658.6   39  61.3  77

xbd5       0.0 464.7     0.0 14433.1   39  61.4  76

xbd14      0.0   0.0     0.0     0.0    0   0.0   0

xbd13      0.0   0.0     0.0     0.0    0   0.0   0

xbd12      0.0 460.1     0.0 14189.7   40  63.4  78

xbd11      0.0 462.9     0.0 14282.8   40  61.8  76

xbd10      0.0 477.0     0.0 14762.1   38  61.2  77

xbd9       0.0 477.6     0.0 14796.2   38  61.1  77

zpool iostat (db pool)
pool        alloc   free   read  write   read  write
db          11.1G   387G      0  6.62K      0  62.9M

vmstat

procs      memory      page                    disks     faults         cpu

 r b w     avm    fre   flt  re  pi  po    fr  sr ad0 xb8   in   sy   cs us sy id

 0 0 0   3026M    35G   126   0   0   0 29555   0   0 478 2364 31201 26165 10  9 81

top

last pid:  1333;  load averages:  1.89,  1.65,  1.08      up 0+01:17:08  01:13:45

32 processes:  2 running, 30 sleeping

CPU: 10.3% user,  0.0% nice,  7.8% system,  1.2% interrupt, 80.7% idle

Mem: 26M Active, 19M Inact, 33G Wired, 16K Cache, 25M Buf, 33G Free

On Wed, Sep 12, 2012 at 9:02 PM, Sébastien Lorion <sl@thestrangefactory.com> wrote:
>
> One more question .. I could not set wal_sync_method to anything else but fsync .. is that expected or should other choices be also available ? I am not sure how the EC2 SSD cache flushing is handled on EC2, but I hope it is flushing the whole cache on every sync .. As a side note, I got corrupted databases (errors about pg_xlog directories not found, etc) at first when running my tests, and I suspect it was because of vfs.zfs.cache_flush_disable=1, though I cannot prove it for sure.
>
> Sébastien
>
>
> On Wed, Sep 12, 2012 at 8:49 PM, Sébastien Lorion <sl@thestrangefactory.com> wrote:
>>
>> Is dedicating 2 drives for WAL too much ? Since my whole raid is comprised of SSD drives, should I just put it in the main pool ?
>>
>> Sébastien
>>
>>
>> On Wed, Sep 12, 2012 at 8:28 PM, Sébastien Lorion <sl@thestrangefactory.com> wrote:
>>>
>>> Ok, make sense .. I will update that as well and report back. Thank you for your advice.
>>>
>>> Sébastien
>>>
>>>
>>> On Wed, Sep 12, 2012 at 8:04 PM, John R Pierce <pierce@hogranch.com> wrote:
>>>>
>>>> On 09/12/12 4:49 PM, Sébastien Lorion wrote:
>>>>>
>>>>> You set shared_buffers way below what is suggested in Greg Smith book (25% or more of RAM) .. what is the rationale behind that rule of thumb ? Other values are more or less what I set, though I could lower the effective_cache_size and vfs.zfs.arc_max and see how it goes.
>>>>
>>>>
>>>> I think those 25% rules were typically created when ram was no more than 4-8GB.
>>>>
>>>> for our highly transactional workload, at least, too large of a shared_buffers seems to slow us down, perhaps due to higher overhead of managing that many 8k buffers.    I've heard other read-mostly workloads, such as data warehousing, can take advantage of larger buffer counts.
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> john r pierce                            N 37, W 122
>>>> santa cruz ca                         mid-left coast
>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>