On 07/11/2017 04:15 PM, Merlin Moncure wrote:
> On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau
> <charles.nadeau@gmail.com> wrote:
>> I’m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-generic).
>> Hardware is:
>>
>> *2x Intel Xeon E5550
>>
>> *72GB RAM
>>
>> *Hardware RAID10 (4 x 146GB SAS 10k) P410i controller with 1GB FBWC (80%
>> read/20% write) for Postgresql data only:
>>
>> The problem I have is very poor read. When I benchmark my array with fio I
>> get random reads of about 200MB/s and 1100IOPS and sequential reads of about
>> 286MB/s and 21000IPS. But when I watch my queries using pg_activity, I get
>> at best 4MB/s. Also using dstat I can see that iowait time is at about 25%.
>> This problem is not query-dependent.
>
> Stop right there. 1100 iops * 8kb = ~8mb/sec raw which might
> reasonably translate to 4mb/sec to the client. 200mb/sec random
> read/sec on spinning media is simply not plausible;
Sure it is, if he had more than 4 disks ;) but he also isn't going to
get 1100 IOPS from 4 10k disks. The average 10k disk is going to get
around 130 IOPS . If he only has 4 then there is no way he is getting
1100 IOPS.
Using the above specs (4x146GB) the best he can reasonably hope for from
the drives themselves is about 50MB/s add in the 1GB FWBC and that is
how he is getting those high numbers for IOPS but that is because of
caching.
He may need to adjust his readahead as well as his kernel scheduler. At
a minimum he should be able to saturate the drives without issue.
JD
--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
***** Unless otherwise stated, opinions are my own. *****
