Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dVC8X-0004Jn-Dg for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 07:32:41 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dVC8W-0002LY-Rl for pgsql-performance@arkaria.postgresql.org; Wed, 12 Jul 2017 07:32:40 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dVC6c-0007Sh-60 for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 07:30:42 +0000 Received: from mail-qt0-x233.google.com ([2607:f8b0:400d:c0d::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dVC6Y-0000Rb-GM for pgsql-performance@postgresql.org; Wed, 12 Jul 2017 07:30:40 +0000 Received: by mail-qt0-x233.google.com with SMTP id i2so11862982qta.3 for ; Wed, 12 Jul 2017 00:30:38 -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=U2/BEgIqvZEvLkJMIGY3WR4jC4LYZVIrHbvxuV0jyRc=; b=VyXrssm7WZiOAAufjTTeLpEsyhoTIsfFL4lobwBnyioYhjeo8beNiR4duc688H6i6p zHZt9QOUNQhMr8YgUT7vP+shSh5axiRKe+Nx+zZbT3xoa51uP3b76gnDVXRb7825PPvk VROItagYOagV+fWA3cYdi4LM+yQ6Q91j2FSvGhBsutJOxnGBxLGv+d8SOnn8ZGjZ87Of nq6JtSm0oQhhc9hLjiVAsUByL7l8uZO7O3wyKY2U+x2zt8aQ148tuu8J4xlZAvpOaZT0 hcZs+US9ShPp5BtU8kHjRiBolUxL7s0h5Gk7xiHs4kfyyFzfY5UaiC0Vz29hUkE1ciud a3/A== 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=U2/BEgIqvZEvLkJMIGY3WR4jC4LYZVIrHbvxuV0jyRc=; b=B2w7bCHORCUnomUV7EUx5XkySrr8NsmDef9UC6nQzck22lJQj4fPrghIXXbykHUcOs Y7FuPqu1aowjGioHNNFlNWwiK7s2pTuFqCHChXUao90LOuaREZTDbjLOAyRe11+TVJnJ 7Zgb/eSmYij0VtlHSZ5niyWbsXSWDJiFe4A/ahLSIb13nwi6KF8Pq7ccGEZWp8cI/Gh2 Ir0V0AE02ZNlXrvGNmrtNkIC4glCJXyPHcHzEJ6gbDNeRiuW4HiMbE0N66lXjMIMtZ6D 9afH4eNjTKq+/f1sua/xsP84jd/UWuQA8ild+/WjmvZ45Db/6lfkwPERCb+J3qCV5LTB 68iw== X-Gm-Message-State: AIVw1129xsv2+GN/SC0gSrD3FrhoS1p5iVdh56f5ResGSUfI0jG34PiL 4wvcZ8mWnmLfEwPFm2OOXCxOq9PpwA== X-Received: by 10.237.43.134 with SMTP id e6mr1130598qtd.131.1499844637196; Wed, 12 Jul 2017 00:30:37 -0700 (PDT) MIME-Version: 1.0 Received: by 10.12.157.11 with HTTP; Wed, 12 Jul 2017 00:30:16 -0700 (PDT) In-Reply-To: <8422687a-e7bb-abad-987b-a102573e3979@commandprompt.com> References: <8422687a-e7bb-abad-987b-a102573e3979@commandprompt.com> From: Charles Nadeau Date: Wed, 12 Jul 2017 09:30:16 +0200 Message-ID: Subject: Re: Very poor read performance, query independent To: "Joshua D. Drake" Cc: Merlin Moncure , postgres performance list Content-Type: multipart/alternative; boundary="001a114ecf00d188de055419ca0f" 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 --001a114ecf00d188de055419ca0f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 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 ;) but he also isn't going to get > 1100 IOPS from 4 10k disks. The average 10k disk is going to get around 1= 30 > 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. ***** > --=20 Charles Nadeau Ph.D. http://charlesnadeau.blogspot.com/ --001a114ecf00d188de055419ca0f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Joshua,

I use noop as the sc= heduler because it is better to let the RAID controller re-arrange the IO o= peration before they reach the disk. Read ahead is set to 128:
charles@hpdl3= 80g6:~$ cat /sys/block/sdc/queue/read_ahead_kb
128
char= les@hpdl380g6:~$ cat /sys/block/sdc/queue/scheduler
[noop] deadli= ne cfq=C2=A0
Thanks!

Charle= s

On W= ed, Jul 12, 2017 at 1:42 AM, 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 ;) but he also isn't going to g= et 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 th= e 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 m= inimum he should be able to saturate the drives without issue.

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptin= c

PostgreSQL Centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://pgconf.us
*****=C2=A0 =C2=A0 =C2=A0Unless otherwise stated, opinions are my own.=C2= =A0 =C2=A0*****



--
Charles= Nadeau Ph.D.
http://charlesnadeau.blogspot.com/
--001a114ecf00d188de055419ca0f--