On 1 July 2017 17:56:13 GMT+01:00, Daviramos Roussenq Fortunato <daviramosrf@xxxxxxxxx> 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@xxxxxxxxxxxxxxx>: > >> On 30 June 2017 20:14:33 GMT+01:00, Daviramos Roussenq Fortunato < >> daviramosrf@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
