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