Обсуждение: [PERFORM] Simple SQL too slow

On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <daviramosrf@gmail.com> wrote:
>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?

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


--
2ndQuadrant - The PostgreSQL Support Company

On 1 July 2017 17:56:13 GMT+01:00, Daviramos Roussenq Fortunato <daviramosrf@gmail.com> wrote:
>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
><andreas@a-kretschmer.de>:
>
>> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <
>> daviramosrf@gmail.com> wrote:
>> >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?
>>
>> 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
>>

Hrm. Settings seems okay (you can increase shared buffers up to 4-6 GB, and also effective_cache_size to 75% of ram,
buti 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 -
queriesare also slower, so i think there is something wrong on os-level. Hard to guess what. 

Regards, Andreas
--
2ndQuadrant - The PostgreSQL Support Company

Hello,

On 07/01/2017 10:39 PM, Daviramos Roussenq Fortunato wrote:
> 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?
>

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

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

On 2 July 2017 02:26:01 GMT+01:00, Daviramos Roussenq Fortunato <daviramosrf@gmail.com> wrote:
>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

--
2ndQuadrant - The PostgreSQL Support Company

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

Le 2 juillet 2017 10:39:09 GMT+02:00, Tomas Vondra <tomas.vondra@2ndquadrant.com> a écrit :
>
>
>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).

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


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