[postgis-users] Best way to

[Tim Bowden]

On Tue, 2007-11-20 at 14:53 +0530, louvy.joseph at gmail.com wrote:
> Thanks Paul. We are already using multiple instances of geoservers with 
> single postgis server. (We are starting new geoserver dynamically based 
> on the load). At this point, I don't have enough load to saturate the 
> database but I am just preparing myself to a condition where too many 
> geoservers (that would mean too many users behind them in turn) would 
> swamp the single database instance.
> 
> Are there any benchmark numbers for postgresql/postgis on how many 
> simultaneous transactions it can handle? 80% of our data is wfs or wms 
> but 20% is going to be wfs-t updates.
> 
> Can somebody please point me to any benchmark numbers for 
> postgresql/postgis system?

As a general rule, database performance is quite sensitive to the
environment in which it is operating; Including hardware, OS,
configuration, load profile (queries- select, insert, update mix, use of
indexes etc), data structures (de-normalisation etc).  For any given set
of data it is quite possible to get extremely varying performance by
optimising the setup for different types of operations, so generic
benchmarks are going to be of little use.

Your best bet may well be to profile the load you're putting on postgis
and then do performance testing using something like jmeter.  That way
you'll get a reasonable idea of how far you can push your system before
it hits the wall.

Regards,
Tim Bowden

> 
> Thanks again
> Louvy
> 
> Paul Ramsey wrote:
> > louvy.joseph at gmail.com wrote:
> >> We are developing a solution using geoserver with postgis postresql 
> >> server. Out database is going to be huge and our projection is that 
> >> number of simultaneous users will be too huge to be handled by single 
> >> database.
> >
> > And you think you're going to bottleneck at the database? :) Better 
> > check out Geoserver more closely. :)
> >
> >> What are the best practices currently available to handle this? 
> >> Please do comment  on  some of the open  thoughts we have:
> >>
> >> 1) Does it make sense to split the database based on geography? For 
> >> example, single database for each continent or a country. But we have 
> >> concerns on changes needed to our client for handling the region 
> >> boundaries and the features crossing region boundaries.
> >
> > If your features are going to be crossing boundaries, then no, 
> > partitioning on geography is not wise. There are probably other 
> > variables you can partition on. Anything that has a hard 
> > non-overlapping domain is a candidate (user id, for example).
> >
> >> 2)  We are also thinking of multi-master replication solutions. Can 
> >> somebody give opinion of a) Postgres-R  b)  PgCluster  c) Bucardo - 
> >> d) Sequoia ? Which of them is stable? Which is more mature and has 
> >> more community? Which is better: synchronous versus asynchronous 
> >> replication?
> >
> > None of the multi-master technologies for postgres are particularly 
> > mature. But there is no guarantee you actually need multi-master. 
> > Having a single update server and multiple query servers might serve 
> > just as well.
> >
> >> 3) BTW, how many users a single postgres database instance (say on a 
> >> 2GHz Pentium 2GB memory system) can handle?
> >
> > This depends entirely on what they are doing, so an answer is not 
> > possible with this much information.
> >
> > P.
> >
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users