Re: Postgres scalability and performance on windows

> Hi all,
>
>
>
> I have a postgres installation thats running under 70-80% CPU usage
> while
>
> an MSSQL7 installation did 'roughly' the same thing with 1-2% CPU load.
>
>
>
> Here's the scenario,
>
> 300 queries/second
>
> Server: Postgres 8.1.4 on win2k server
>
> CPU: Dual Xeon 3.6 Ghz,
>
> Memory: 4GB RAM
>
> Disks: 3 x 36gb , 15K RPM SCSI
>
> C# based web application calling postgres functions using npgsql 0.7.
>
> Its almost completely read-only db apart from fortnightly updates.
>
>
>
> Table 1 - About 300,000 rows with simple rectangles
>
> Table 2 - 1 million rows
>
> Total size: 300MB
>
>
>
> Functions : Simple coordinate reprojection and intersection query +
> inner join of table1 and table2.
>
> I think I have all the right indexes defined and indeed the performance
> for queries under low loads is fast.

(cut)

> SQL server caches all the data in memory which is making it faster(uses
> about 1.2GB memory- which is fine).
>
> But postgres has everything spread across 10-15 processes, with each
> process using about 10-30MB, not nearly enough to cache all the data and
> ends up doing a lot of disk reads.
>
> I've read that postgres depends on OS to cache the files, I wonder if
> this is not happenning on windows.
>
> In any case I cannot believe that having 15-20 processes running on
> windows helps. Why not spwan of threads instead of processes, which
> might
>
> be far less expensive and more efficient. Is there any way of doing
> this?

Hi Gopal,

It sounds as if you are using PostGIS to store your geometries, and yes it
sounds as if something is not performing as it should. Please post your
configuration (along with information about the versions of PostGIS you
are using) to the postgis-users list at http://postgis.refractions.net.
You will also need to supply the output of EXPLAIN ANALYZE for some of
your queries in order to help determine exactly where the bottleneck is in
your application.

Kind regards,

Mark.