On 07/02/2017 03:26 AM, Daviramos Roussenq Fortunato wrote:
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'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.
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?
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
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
