Re: Amazon High I/O instances - Mailing list pgsql-general
| From | Sébastien Lorion |
|---|---|
| Subject | Re: Amazon High I/O instances |
| Date | |
| Msg-id | CAGa5y0N7YamD9Ut-A6aRCCSBC4acHw+DDpMbrj3pFeUDznvjkw@mail.gmail.com Whole thread Raw |
| In response to | Re: Amazon High I/O instances (Sébastien Lorion <sl@thestrangefactory.com>) |
| Responses |
Re: Amazon High I/O instances
(Sébastien Lorion <sl@thestrangefactory.com>)
|
| List | pgsql-general |
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)
zpool iostat (db pool)
pool alloc free read write read write
db 11.1G 387G 0 6.62K 0 62.9M
vmstat
top
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
>>>
>>>
>>
>
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
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
>>>
>>>
>>
>
pgsql-general by date: