Обсуждение: [PERFORM] Simple SQL too slow
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
Daviramos Roussenq Fortunato
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
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>:
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.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?
--
2ndQuadrant - The PostgreSQL Support Company
Atenciosamente
Daviramos Roussenq Fortunato
Daviramos Roussenq Fortunato
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
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 <andreas@a-kretschmer.de>:
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.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
>>
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--
2ndQuadrant - The PostgreSQL Support Company
Atenciosamente
Daviramos Roussenq Fortunato
Daviramos Roussenq Fortunato
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
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?
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
> 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
Atenciosamente
Daviramos Roussenq Fortunato
Daviramos Roussenq Fortunato
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é.
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 <andreas@a-kretschmer.de>:
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
Atenciosamente
Daviramos Roussenq Fortunato
Daviramos Roussenq Fortunato