[postgis-users] strange benchmark results

[Mark Cave-Ayland]

Andreas Brandl wrote:

>> As a quick test, I would suggest upgrading to the new Windows
>> StackBuilder packages since they contain GEOS 3.2.0 which had a number
>> of memory leaks fixed and see if you see the same behaviour on Test 1,
>> i.e. slowly increasing query times.
> 
> This one is in the 'queue'.

Okay, great. Let us know how you get on.

>> I'd also be interested to see your results of just Test 1 on a Linux
>> OS too to see whether it is an OS specific issue that you are seeing.
> 
> I executed the benchmark on Debian Lenny, 32-bit. Same machine, same config, same data, same benchmark. Quite astonishing results [1].
> 
> Not only the effect is another one (please see graphs), the queries are much faster on Debian than on WinXP. But this only concerns those queries shown here, other queries behave 'normal' (i.e. some faster, some slower, no 'increasing effect'). I additionally validated and compared the query results, they are the same.

 From personal experience, I find that Windows is not a particularly 
performant server platform. A while back I had a production Windows 
server checking out a source tree from SVN in just under 60s. The less 
powerful development Linux workstation did the same checkout in 3s.

I spent some time with the SVN guys online, and we managed to get the 
Windows checkout to 40s by disabling the anti-virus software. However 
the conclusion was that the poor performance was caused by 
inefficiencies in NTFS. Personal testing a small while back has shown 
that just by using Windows instead of Linux for a PostgreSQL database 
introduces an immediate 25% performance penalty into the application.

In other words, I believe that due to differences in the filesystem and 
OS architecture that the performance will never be equal. However I am 
surprised by the inconsistency of queries on the Windows side. Was your 
test box a Windows server or workstation edition? And did you attempt to 
optimise it for server rather than workstation use?

A couple of things that have caused performance issues in the past on 
Windows which I've seen go through the list are bad virus checking 
software and delays being introduced by the IP QoS scheduler. However, I 
don't profess to being a Windows tuning expert so it may be more 
valuable to post some of your results on the pgsql-performance 
PostgreSQL list where more knowledgeable Windows users can help you out.

> I noticed the different PROJ Versions (postgis at winxp having Rel. 4.6.1, postgis at debian having Rel. 4.7.1) but as I am not using any projection (default SRID -1) I suppose this does not matter here. Another difference is that I used StackBuilder on Windows and compiled everything from source on debian.

As you point out, the PROJ library shouldn't be called for the example 
queries you are giving and so I wouldn't expect this to have much of an 
effect yet.


HTH,

Mark.

-- 
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063

Sirius Labs: http://www.siriusit.co.uk/labs