Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dV63k-0005KM-TW for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 01:03:21 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dV63k-0006er-GF for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 01:03:20 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dV63i-0006ef-Tb for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 01:03:18 +0000 Received: from mail-ua0-x234.google.com ([2607:f8b0:400c:c08::234]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dV63e-0007vQ-Ra for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 01:03:17 +0000 Received: by mail-ua0-x234.google.com with SMTP id g40so5396489uaa.3 for ; Tue, 11 Jul 2017 18:03:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=W3N0NDEhnwN5eQ8skGonj41pcSvXvi5i9bz6zE9L7is=; b=YAKo6OS8Xd9BIeFspBqcz3OMfGH03OcZYq0hJGj/sL/TW8ZyOe0v4WtfpAYD8WoYLb sinRyUY5N4jxHhXXzYkN1oBnSl6GXR4dKT+vx8fcKi/opjEIiCaEXwu35cn2SAphyCC1 vM1JoqvCf+LVQywtDgvVIIZdRrsuwaoep7aS3unxcjy1CiBGtGJp+uPxS6XCduIqVIdr LvTxFCt63Do4K+UN//MPSeKSRUdzEc8l2kaRCmYseoTA1nLbSQN7wdXYdwtuOkH3BMTP +J8GeKiL1E1I3mopOdJXe18+gEsIsUeH98psV48Lvhk1pOywhzsSaSpqgCHJlhxoLHSP RZfA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=W3N0NDEhnwN5eQ8skGonj41pcSvXvi5i9bz6zE9L7is=; b=nC4kq71ZiwDa36TgUnIY35bGucJRNNZvtSVoRmq9tycXBKRY0ug8eXL0tuN9N3MriU 1ShY7XJxLaHSHD72lquQhAw5W+Ysl0XF+PCX5poRZASLz1P2mIaPOzyy8OmZa5wf0Nwy MUDUWV/6d2a4Fr6ZMlGeOAJmG6uiBwFPMctJlOUXGVRmcaq+dzkSqhWyhSYFP8IN3O/9 VvMHfqFhmh9yMZK6F0fYk2BBQbR6kAlW0WkbNVpQfNrbTyt3J2Mh+hZPnqw8YiFzws2i NDxZI7HrdlC4uc95zWmcH6fuV+dFwTFDckbCLWmvyyp7Yas6H5rZh/k5iYiGLQ4Pv32Z TxAw== X-Gm-Message-State: AIVw112pCraq8pOFUpT6KDhyJn/RAQvnBUebC7ClUHyRu7CyN0iXhDGv MmeBw7gF1t4lhxU3OCopH/0afMTAJg== X-Received: by 10.159.62.220 with SMTP id n28mr1692399uaj.142.1499821392935; Tue, 11 Jul 2017 18:03:12 -0700 (PDT) MIME-Version: 1.0 Received: by 10.159.56.201 with HTTP; Tue, 11 Jul 2017 18:03:12 -0700 (PDT) In-Reply-To: <8422687a-e7bb-abad-987b-a102573e3979@commandprompt.com> References: <8422687a-e7bb-abad-987b-a102573e3979@commandprompt.com> From: Jeff Janes Date: Tue, 11 Jul 2017 18:03:12 -0700 Message-ID: Subject: Re: Very poor read performance, query independent To: "Joshua D. Drake" Cc: Merlin Moncure , Charles Nadeau , postgres performance list Content-Type: multipart/alternative; boundary="089e0820758c5a40b20554146184" List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --089e0820758c5a40b20554146184 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jul 11, 2017 at 4:42 PM, Joshua D. Drake wrote: > On 07/11/2017 04:15 PM, Merlin Moncure wrote: > >> On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau >> wrote: >> >>> I=E2=80=99m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-g= eneric). >>> 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 fi= o >>> 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 =3D ~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 ;) Or more to the point here, if each random read is 4MB long. Which makes it more like sequential reads, randomly-piecewise, rather than random reads. > but he also isn't going to get 1100 IOPS from 4 10k disks. The average 10= k > disk is going to get around 130 IOPS . If he only has 4 then there is no > way he is getting 1100 IOPS. > I wouldn't be sure. He is using an iodepth of 256 in his benchmark. It wouldn't be all that outrageous for a disk to be able to find 3 or 4 sectors per revolution it can read, when it has that many to choose from. Cheers, Jeff --089e0820758c5a40b20554146184 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On T= ue, Jul 11, 2017 at 4:42 PM, Joshua D. Drake <jd@commandprompt.com= > wrote:
On 07= /11/2017 04:15 PM, Merlin Moncure wrote:
On Mon, Jul 10, 2017 at 9:03 AM, Charles Nadeau
<charles.n= adeau@gmail.com> wrote:
I=E2=80=99m running PostgreSQL 9.6.3 on Ubuntu 16.10 (kernel 4.4.0-85-gener= ic).
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<= br> get random reads of about 200MB/s and 1100IOPS and sequential reads of abou= t
286MB/s and 21000IPS. But when I watch my queries using pg_activity, I get<= br> 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.=C2=A0 =C2=A0 =C2=A01100 iops * 8kb =3D ~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 ;)

Or more to the point here, if each random read is 4MB long.=C2=A0 Which ma= kes it more like sequential reads, randomly-piecewise, rather than random r= eads.=C2=A0
=C2=A0
but he a= lso 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.

I wouldn't b= e sure.=C2=A0 He is using an iodepth of 256 in his benchmark.=C2=A0 It woul= dn't be all that outrageous for a disk to be able to find 3 or 4 sector= s per revolution it can read, when it has that many to choose from.

=C2=A0Cheers,

Jeff
--089e0820758c5a40b20554146184--