Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dRTeN-0000XQ-6n for pgsql-performance@arkaria.postgresql.org; Sun, 02 Jul 2017 01:26:11 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dRTeK-00058P-R8 for pgsql-performance@arkaria.postgresql.org; Sun, 02 Jul 2017 01:26:08 +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 1dRTeI-00057o-Tz for pgsql-performance@postgresql.org; Sun, 02 Jul 2017 01:26:07 +0000 Received: from mail-qt0-x236.google.com ([2607:f8b0:400d:c0d::236]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1dRTeF-00038y-FT for pgsql-performance@postgresql.org; Sun, 02 Jul 2017 01:26:05 +0000 Received: by mail-qt0-x236.google.com with SMTP id b40so14556262qtb.2 for ; Sat, 01 Jul 2017 18:26:03 -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; bh=GDdO75vS+IkkE8Q5VFEWoeDchmGaPeOXwuc73Aotkww=; b=oJUvHjFJuldo7SuxH5CdCgZ85b4Eb8SWHbp2XHmR4plR6cXoomD3+6CY3kGef6ENvJ 448xgXUrL5+qpXV3iFo2LoKaArnhl5BsmqMyAxvZj3paVvoJBgfjGAwgO98HqlLmgF2B yO+PeWnWOT39M/3wsk0cN/u8MWLmYfkzbQ/6LH9UvSfEVk5MVdBwPvZIQgwHfkIFUPOF 51eQhz5YctuzkmjmYcbak3Va9+en13W0pfkbNh1NZtTYAbp8p1cr53YHoMknOqJa4Na/ V4i7f07bREbdoK86heBuA9jSTouGFPKGGBE5EKG2LQDOfyu0eUEWDhr5N310JcOq/GNE Laag== 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; bh=GDdO75vS+IkkE8Q5VFEWoeDchmGaPeOXwuc73Aotkww=; b=FT9fDK1aruYrjV3CGPvmYBFVwT2+dZIpf/FOVhLLOGGTJ/BAJYo+sjHh5CxlMGXc5a OjOTA5w8Th6QrCqC+grH5ASOMvzQEJEifdY9zUvjBg+M7LfUkUyJ6ORzbG9MlRSv3jCA NSEH2bMIfKB+8VqDsY9zHmVvA+vrmLupBpZN0834deCpv6qcjNPS5dQjtDM6JWElUv2g Npt6k4i6gSL5dO6Axrekdy0IqU+VdHtm4L0xgWgEZ/kDdGFESb32+lMcOXQDVFg7gnbI 3uIdTZ7ATvJJHg2uCXGmftWYn0sTqWnZvXXGE8C4Bp8HHXs3AVHcdUg/BCZ31s9p5U+V ErWA== X-Gm-Message-State: AKS2vOw57k3wrCQY/zY46SrFq7LLdq39Orn1bf9eV/UHDzXL2/ocJvLk FC7+LxfIcU4+T73sCKLUoh0v5Uu4SNWA X-Received: by 10.200.53.78 with SMTP id z14mr33925870qtb.184.1498958762083; Sat, 01 Jul 2017 18:26:02 -0700 (PDT) MIME-Version: 1.0 Received: by 10.55.64.20 with HTTP; Sat, 1 Jul 2017 18:26:01 -0700 (PDT) In-Reply-To: <81a45651-ebf9-0df2-5351-5aa71055a58f@dalibo.com> References: <623C03A8-ABA5-4BF1-8225-348CF5E6848E@a-kretschmer.de> <7152357d-5cac-20d6-9c08-ea56878c3d03@2ndquadrant.com> <81a45651-ebf9-0df2-5351-5aa71055a58f@dalibo.com> From: Daviramos Roussenq Fortunato Date: Sat, 1 Jul 2017 22:26:01 -0300 Message-ID: Subject: Re: Simple SQL too slow To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary="001a113784fc8c0a2f05534b88e7" 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 --001a113784fc8c0a2f05534b88e7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable I am using pgAdmin for SQL test. Linux: EXPLAIN ANALYZE select * from "Seq Scan on lancamentosteste (cost=3D0.00..49289.74 rows=3D1883774 width= =3D92) (actual time=3D0.016..1194.453 rows=3D1883699 loops=3D1)" "Total runtime: 2139.067 ms" Windows: "Seq Scan on lancamentosteste (cost=3D0.00..49288.67 rows=3D1883967 width= =3D92) (actual time=3D0.036..745.409 rows=3D1883699 loops=3D1)" "Total runtime: 797.159 ms" I did some test reading the disk and monitored with iotop. #hdparm -t /dev/sdc /dev/sdc: Timing buffered disk reads: 730 MB in 3.01 seconds =3D 242.65 MB/sec #hdparm -T /dev/sdc /dev/sdc: Timing cached reads: 9392 MB in 2.00 seconds =3D 4706.06 MB/sec #time sh -c "dd if=3D/dev/zero of=3Dddfile bs=3D8k count=3D250000 && sync";= rm ddfile 250000+0 registros de entrada 250000+0 registros de sa=C3=83=C2=ADda 2048000000 bytes (2,0 GB) copiados, 5,84926 s, 350 MB/s real 0m9.488s user 0m0.068s sys 0m5.488s In the tests monitoring the disk by iotop, it kept constant the reading between 100MB/s to 350MB/s By doing the same monitoring on iotop and running SELECT, the disk reading does not exceed 100kb/s, I have the impression that some configuration of LINUX or Postgres is limiting the use of the total capacity of DISCO. Does anyone know if there is any setting for this? 2017-07-01 18:17 GMT-03:00 Julien Rouhaud : > On 01/07/2017 22:58, Tomas Vondra wrote: > > After that, you need to determine what is the bottleneck. Perhaps the > > resources are saturated by something else running on the system - other > > queries, maybe something else running next to PostgreSQL. Look at top > > and iotop while running the queries, and other system tools. > > > > Another explanation would be network issue. Are they stored in > different locations? And dhoes > > EXPLAIN ANALYZE select * from MINHATABELA > > has similar timings on both environment? > > Also, I didn't see any indication about how exactly were the tests > performed. Was it using psql, pgAdmin or something else ? > > -- > Julien Rouhaud > http://dalibo.com - http://dalibo.org > --=20 Atenciosamente Daviramos Roussenq Fortunato --001a113784fc8c0a2f05534b88e7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
I am using pgAdmin for SQL test.

=
Linux:=C2=A0

EXPLAIN ANALYZE select * from
"Seq Scan on lancamentosteste =C2=A0(cost=3D0.00..49289.74 row= s=3D1883774 width=3D92) (actual time=3D0.016..1194.453 rows=3D1883699 loops= =3D1)"
"Total runtime: 2139.067 ms"

=
Windows:
"Seq Scan on lancamentosteste =C2=A0(cos= t=3D0.00..49288.67 rows=3D1883967 width=3D92) (actual time=3D0.036..745.409= rows=3D1883699 loops=3D1)"
"Total runtime: 797.159 ms&= quot;



I did some tes= t reading the disk and monitored with iotop.

#hdpa= rm -t /dev/sdc

/dev/sdc:
=C2=A0Timing bu= ffered disk reads: 730 MB in =C2=A03.01 seconds =3D 242.65 MB/sec
=C2=A0

#hdparm -T /dev/sdc

/dev/sdc:
=C2=A0Timing cached reads: =C2=A0 9392 MB in =C2=A02= .00 seconds =3D 4706.06 MB/sec
=C2=A0
=C2=A0
= =C2=A0
#time sh -c "dd if=3D/dev/zero of=3Dddfile bs=3D8k co= unt=3D250000 && sync"; rm ddfile
250000+0 registros = de entrada
250000+0 registros de sa=C3=83=C2=ADda
20480= 00000 bytes (2,0 GB) copiados, 5,84926 s, 350 MB/s

real =C2=A0 =C2=A00m9.488s
user =C2=A0 =C2=A00m0.068s
= sys =C2=A0 =C2=A0 0m5.488s


In the t= ests monitoring the disk by iotop, it kept constant the reading between 100= MB/s to 350MB/s

By doing the same monitoring on io= top and running SELECT, the disk reading does not exceed 100kb/s, I have th= e impression that some configuration of LINUX or Postgres is limiting the u= se of the total capacity of DISCO.

Does anyone kno= w if there is any setting for this?
<= br>
2017-07-01 18:17 GMT-03:00 Julien Rouhaud <julien.rouhaud@dalibo.com>:
On 01/07/2017 22:58, Tomas Vondra wrote:
> After that, you need to determine what is the bottleneck. Perhaps the<= br> > resources are saturated by something else running on the system - othe= r
> queries, maybe something else running next to PostgreSQL. Look at top<= br> > and iotop while running the queries, and other system tools.
>

Another explanation would be network issue.=C2=A0 Are they stored in=
different locations?=C2=A0 And dhoes

EXPLAIN ANALYZE select * from MINHATABELA

has similar timings on both environment?

Also, I didn't see any indication about how exactly were the tests
performed.=C2=A0 Was it using psql, pgAdmin or something else ?

--
Julien Rouhaud
http://d= alibo.com - http://dalibo.org



--
Atencio= samente
Daviramos Roussenq Fortunato
--001a113784fc8c0a2f05534b88e7--