[postgis-users] Rv: Postgis for Postgres 64 bits

[Darrel Maddy]

I am no db expert, indeed I am new to PG and postgis so I guess I have some preconceptions (and a lot to learn)! If you are correct though it would suggest there is little point to having a 64 bit version - so why do they do it?

On a matter of performance however I do have a (probably dumb) question.  I have two machines running the same version of PG/PostGIS2 under windows 64 with the following hardware:

Machine 1:  intel 980X, 12Gb RAM and Agility SSD (running in SATAII mode as SATAIII is not supported on the motherboard)
Machine 2: intel 2600k (overclocked to 4.4GHz), 16Gb RAM and Corsair Force 4 SSD running over SATAIII

I have a table of 11 rasters (3601x3134, originally tifs) each tiled into 1000x1000 blocks and I have a simple geometry shape file. My first ever SQL query looks like this:

SELECT rid, gid, ST_Value(rast, 1, geom), filename, ST_X(geom) As X_coord, ST_Y(geom) As Y_coord 
FROM public.rasters, public.mypoints As A 
WHERE ST_Intersects(rasters.rast,1, A.geom)
ORDER BY rid ASC ;

as I am interested in drawing cross-sections of the 'height' data contained within the grids.  Each intersect draws data from only one tile.

Now the query works as intended (apologies if it can be done better) but the timings are:

1) ~35s
2) ~235s
(n.b. these are the best timings - I did try different tiling sizes but both smaller and larger took longer)

Now this result baffles me as I expected the 2600k to be much closer as watching a CPU monitor the code does not seem to be heavily multithreaded.

I would be grateful for any suggestions as to why the 2600k is performing badly in comparison (and of course I would welcome any suggestions that might speed up the result - noting that ultimately this extraction could have as many as 250,000 rasters to work on.

Thanks

Darrel

ps sorry for asking questions at the weekend but this is the only time I get to work on this stuff!

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Andy Colson
Sent: 04 February 2012 19:52
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Rv: Postgis for Postgres 64 bits

On 02/04/2012 01:14 PM, Darrel Maddy wrote:
> Dear Regina,
>
> I would be happy to contribute $100 into any fund which secures a 64-bit windows build of PostGIS2 -especially as I am about to install it on a machine with 64Gb RAM :)
>
> Count now at 1 ?
>
> Thanks
>
> Darrel
> http://www.ncl.ac.uk/gps/staff/profile/darrel.maddy#tab_profile
>
>


I really wonder if its faster.  I'd bet not.  If you consider the architecture, I'd bet, for all but the very largest of datasets, 32 bit PG running on top 64 bit os is faster.

Consider: you want the os disk cache to be as large as possible.  You want a bunch of shared mem and I'd bet around 2 gig would be a sweet spot.  More shared mem wouldn't necessarily be a benefit. (There are upper bounds recommended for shared_buffers, especially on windows).

PG spawns a separate client for each connection, so each one of them could access up to 2 gig.  (so things like work_mem could be large).

And last, 32bit programs are faster because they move less data around (32bit pointers instead of 64bit).

Taken all together PostMaster + (clients * postgres.exe) + os cache could easly be more than 2 gig using 32 bit PG on 64 bit windows.

The only benefit of using 64bit PG would be a larger shared mem.  The online manual says: "The useful range for shared_buffers on Windows systems is generally from 64MB to 512MB"

http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html

The best use of ram, is as a disk cache, which 64 bit windows should use as much as possible (well, I assume).  After that there is nothing in Postgres (Per Client) that is going to run faster with more than 2 gig of ram.

Has anyone ever done any benchmarks?

-Andy
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users