Subject: Simple SQL too slow

From: Daviramos Roussenq Fortunato <[email-masked]>
Date: Fri, 30 Jun 2017 16:14:33 -0300

Hi List,

I have a Server where a simple SQL is taking a long time to return the
results the Server settings are as follows:

Debian GNU/Linux 7 (wheezy)
CPU: Intel(R) Xeon(R) CPU           E5405  @ 2.00GHz
Mem: 16GB
HD: SSG 120 GB
Postgresql 9.2

postgresql.conf
shared_buffers = 1536MB
work_mem = 32MB
maintenance_work_mem = 960MB
effective_cache_size = 4864MB

I did a test with the following SQL:

select * from MINHATABELA

It took 7 minutes to return the result.

I did the same test on a Server:

Windows Server 2012 Standard
CPU: Intel(R) Xeon(R) CPU           E5-2450  @ 2.10GHz
Mem: 24GB
HD: HD 500 GB
Postgresql 9.2

postgresql.conf Default settings that come with the installation

The same SQL returned in 3 minutes.

The test in both Servers were done bench.

This table has 1888240 records whose size is 458 MB

I believe that in both Servers the response time of this SQL is very high,
but the main thing in LINUX Server has something very wrong, I think it is
something in the settings.

What can I be checking?

-- 
Atenciosamente
Daviramos Roussenq Fortunato

---

From: Andreas Kretschmer <[email-masked]>
Date: Fri, 30 Jun 2017 20:50:45 +0100

>something in the settings.

The query needs a full table scan, so it mainly depends on the speed of your disk. Maybe you have s bloated table. Please check reltuples and relpages from pg_class on both servers and compare.

-- 
2ndQuadrant - The PostgreSQL Support Company

-- 
Sent via pgsql-performance mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

---

From: Daviramos Roussenq Fortunato <[email-masked]>
Date: Sat, 1 Jul 2017 13:56:13 -0300

Debian:

SELECT reltuples::numeric FROM pg_class WHERE oid = 'mytable'::regclass;
retuples=1883770
 --31ms

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname =
'mytable';
pg_relation_filepath=base/1003173/1204921
relpages=30452
--31ms

Windows

SELECT reltuples::numeric FROM pg_class WHERE oid = 'mytable'::regclass;
retuples=1883970
--15ms

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname =
'mytable';
pg_relation_filepath=base/24576/205166
relpages=30449
--16ms

2017-06-30 16:50 GMT-03:00 Andreas Kretschmer <[email-masked]>:

> The query needs a full table scan, so it mainly depends on the speed of
> your disk. Maybe you have s bloated table. Please check reltuples and
> relpages from pg_class on both servers and compare.
>
>
> --

---

From: Andreas Kretschmer <[email-masked]>
Date: Sat, 01 Jul 2017 20:44:06 +0100

On 1 July 2017 17:56:13 GMT+01:00, Daviramos Roussenq Fortunato <[email-masked]> wrote:
>> >but the main thing in LINUX Server has something very wrong, I think
>it
>> >is

Hrm. Settings seems okay (you can increase shared buffers up to 4-6 GB, and also effective_cache_size to 75% of ram, but i think that's not the reason for the bad performance.
Windows contains 50% more ram, maybe better/more caching. But i'm not sure if this can be the reason. The pg_class - queries are also slower, so i think there is something wrong on os-level. Hard to guess what.

Regards, Andreas

---

From: Daviramos Roussenq Fortunato <[email-masked]>
Date: Sat, 1 Jul 2017 17:39:55 -0300

What tests could I do. Rigid Linux disk is much faster than Windows, I
should get a much better perfomace on this Linux.

What test battery do you recommend I do?

2017-07-01 16:44 GMT-03:00 Andreas Kretschmer <[email-masked]>:

> Hrm. Settings seems okay (you can increase shared buffers up to 4-6 GB,
> and also effective_cache_size to 75% of ram, but i think that's not the
> reason for the bad performance.
> Windows contains 50% more ram, maybe better/more caching. But i'm not sure
> if this can be the reason. The pg_class - queries are also slower, so i
> think there is something wrong on os-level. Hard to guess what.
>
> Regards, Andreas
> --

---

From: Tomas Vondra <[email-masked]>
Date: Sat, 1 Jul 2017 22:58:30 +0200

Hello,

On 07/01/2017 10:39 PM, Daviramos Roussenq Fortunato wrote:
> should get a much better perfomace on this Linux. What test battery do 
> you recommend I do?
> 

I'm not sure what you mean by "rigid disk" or "test battery", but I 
agree with Andreas that clearly there's something wrong at the system 
level. It's hard to guess what exactly, but sequential scan on 250MB 
table (computed the relpages values) should only take a few seconds on 
any decent hardware, and not 3 or 7 minutes.

The first thing I would do is running basic system-level tests, for 
example benchmarking storage using fio.

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.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: Julien Rouhaud <[email-masked]>
Date: Sat, 1 Jul 2017 23:17:02 +0200

> 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

---

From: Daviramos Roussenq Fortunato <[email-masked]>
Date: Sat, 1 Jul 2017 22:26:01 -0300

I am using pgAdmin for SQL test.

Linux:

EXPLAIN ANALYZE select * from
"Seq Scan on lancamentosteste  (cost=0.00..49289.74 rows=1883774 width=92)
(actual time=0.016..1194.453 rows=1883699 loops=1)"
"Total runtime: 2139.067 ms"

Windows:
"Seq Scan on lancamentosteste  (cost=0.00..49288.67 rows=1883967 width=92)
(actual time=0.036..745.409 rows=1883699 loops=1)"
"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 = 242.65 MB/sec

#hdparm -T /dev/sdc

/dev/sdc:
 Timing cached reads:   9392 MB in  2.00 seconds = 4706.06 MB/sec

#time sh -c "dd if=/dev/zero of=ddfile bs=8k count=250000 && sync"; rm
ddfile
250000+0 registros de entrada
250000+0 registros de saÃ­da
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?

---

From: Andreas Kretschmer <[email-masked]>
Date: Sun, 02 Jul 2017 05:25:02 +0100

>I am using pgAdmin for SQL test.

Are you using real hardware or is it vitual? Needs the query without explain analyse the same time? Can you try it with psql (THE command line interface)?

Regards, Andreas

---

From: Tomas Vondra <[email-masked]>
Date: Sun, 2 Jul 2017 10:39:09 +0200

On 07/02/2017 03:26 AM, Daviramos Roussenq Fortunato wrote:
> "Seq Scan on lancamentosteste  (cost=0.00..49289.74 rows=1883774 
> width=92) (actual time=0.016..1194.453 rows=1883699 loops=1)"
> "Seq Scan on lancamentosteste  (cost=0.00..49288.67 rows=1883967 
> width=92) (actual time=0.036..745.409 rows=1883699 loops=1)"

I'm really, really confused. In the first message you claimed the 
queries take 7 and 3 minutes, yet here we see the queries taking just a 
few seconds.

> 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.
> 

There is no such setting. But it's possible that the network is very 
slow, so transferring the results from the server to the client takes 
very long. Or that formatting the results in the client takes a lot of 
time (I'm not sure why there'd be a difference between Windows and Linux 
though).

regards

---

From: Nicolas CHARLES <[email-masked]>
Date: Sun, 02 Jul 2017 12:01:44 +0200

>time (I'm not sure why there'd be a difference between Windows and

Could it be that you are doing your queries on pgadmin that is remote from the Linux server, and local to the windows server, hence the difference in perceived performance?

Nicolas

-- 
Envoyé de mon appareil Android avec K-9 Mail. Veuillez excuser ma brièveté.

---

From: Daviramos Roussenq Fortunato <[email-masked]>
Date: Sun, 2 Jul 2017 10:57:49 -0300

REAL HARDWARE.

I ran the same SQL via pgsql it took only 13 seconds.

My bottleneck has everything to be network.

#tcptrack -i eth1

Analyzing the traffic on the network, the speed is only 512Kb / s on port
5432.

# ethtool eth1
Settings for eth1:
        Supported ports: [ TP ]
        Supported link modes:   10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Supported pause frame use: No
        Supports auto-negotiation: Yes
        Advertised link modes:  10baseT/Half 10baseT/Full
                                100baseT/Half 100baseT/Full
                                1000baseT/Full
        Advertised pause frame use: No
        Advertised auto-negotiation: Yes
        Speed: 1000Mb/s
        Duplex: Full
        Port: Twisted Pair
        PHYAD: 1
        Transceiver: internal
        Auto-negotiation: on
        MDI-X: off
        Supports Wake-on: pumbg
        Wake-on: g
        Current message level: 0x00000007 (7)
                               drv probe link
        Link detected: yes

# iptables --list
Chain INPUT (policy ACCEPT)
target     prot opt source               destination

Chain FORWARD (policy ACCEPT)
target     prot opt source               destination

Chain OUTPUT (policy ACCEPT)
target     prot opt source               destination

I tested the file transfer, the port speed did not exceed 512Kb/s on port
22.

I have some limitation on the network.

But I can not figure out why. This linux was installed by me, with only
minimal packages to install postgres.

What can it be?
Well it is identified that the problem is not the postgres, but the
operating systems, maybe I should look for the solution in another list.

2017-07-02 1:25 GMT-03:00 Andreas Kretschmer <[email-masked]>:

> Are you using real hardware or is it vitual? Needs the query without
> explain analyse the same time? Can you try it with psql (THE command line
> interface)?
>
>
> Regards, Andreas
>
> --