public inbox for [email protected]
help / color / mirror / Atom feedFrom: Charles Nadeau <[email protected]>
To: Joshua D. Drake <[email protected]>
Cc: Merlin Moncure <[email protected]>
Cc: postgres performance list <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Wed, 12 Jul 2017 09:30:16 +0200
Message-ID: <CADFyZw4diKqonCmcnC=ac7VMEcJLu-LaYkH84uFq+E_hQ1Ugzg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADFyZw7aGoD0AaStxdyHByR5Qta=M5wx0v=iptKLhPUp+EOKvA@mail.gmail.com>
<CAHyXU0zhpFtoOHgC8Fvb92rs038ofc0VBP7aKVnv1=rsLCxazQ@mail.gmail.com>
<[email protected]>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
Joshua,
I use noop as the scheduler because it is better to let the RAID controller
re-arrange the IO operation before they reach the disk. Read ahead is set
to 128:
charles@hpdl380g6:~$ cat /sys/block/sdc/queue/read_ahead_kb
128
charles@hpdl380g6:~$ cat /sys/block/sdc/queue/scheduler
[noop] deadline cfq
Thanks!
Charles
On Wed, Jul 12, 2017 at 1:42 AM, Joshua D. Drake <[email protected]>
wrote:
> On 07/11/2017 04:15 PM, Merlin Moncure wrote:
>
>> On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau
>> <[email protected]> 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. *****
>
--
Charles Nadeau Ph.D.
http://charlesnadeau.blogspot.com/
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Very poor read performance, query independent
In-Reply-To: <CADFyZw4diKqonCmcnC=ac7VMEcJLu-LaYkH84uFq+E_hQ1Ugzg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox