Re: Simple SQL too slow

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(at)a-kretschmer(dot)de>:

> On 1 July 2017 17:56:13 GMT+01:00, Daviramos Roussenq Fortunato <
> daviramosrf(at)gmail(dot)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(at)a-kretschmer(dot)de>:
> >
> >> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato <
> >> daviramosrf(at)gmail(dot)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, 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
> --
> 2ndQuadrant - The PostgreSQL Support Company
>

--
Atenciosamente
Daviramos Roussenq Fortunato