Re: Hardware requirements for a PostGIS server - Mailing list pgsql-general

On 11/02/15 13:52, Mathieu Basille wrote:
> Dear PostgreSQL users,
>
> I am posting here a question that I initially asked on the PostGIS
> list [1], where I was advised to try here too (I will keep both lists
> updated about the developments on this issue).
>
> I am currently planning to set up a PostgreSQL + PostGIS instance for
> my lab. Turns out I believe this would be useful for the whole center,
> so that I'm now considering setting up the server for everyone—if
> interest is shared of course. At the moment, I am however struggling
> with what would be required in terms of hardware, and of course, the
> cost will depend on that—at the end of the day, it's really a matter
> of money well spent. I have then a series of questions/remarks, and I
> would welcome any feedback from people with existing experience on
> setting up a multi-user PostGIS server. I'm insisting on the PostGIS
> aspect, since the most heavy requests will be GIS requests
> (intersections, spatial queries, etc.). However, people with similar
> PostgreSQL setup may have very relevant comments about their own
> configuration.
>
> * My own experience about servers is rather limited: I used PostGIS
> quite a bit, but only on a desktop, with only 2 users. The desktop was
> quite good (quad-core Xeon, 12 Go RAM, 500 GB hd), running Debian, and
> we never had any performance issue (although some queries were rather
> long, but still acceptable).
>
> * The use case I'm envisioning would be (at least in the foreseeable
> future):
> - About 10 faculty users (which means potentially a little bit more
> students using it); I would have hard time considering more than 4
> concurrent users;
> - Data would primarily involve a lot (hundreds/thousands) of high
> resolution (spatial and temporal) raster and vector maps, possibly
> over large areas (Florida / USA / continental), as well as potentially
> millions of GPS records (animals individually monitored);
> - Queries will primarily involve retrieving points/maps over given
> areas/time, as well as intersecting points over environmental layers
> [from what I understand, a lot of I/O, with many intermediary tables
> involved]; other use cases will involve working with steps, i.e. the
> straight line segment connecting two successive locations, and
> intersecting them with environmental layers;
>
> * I couldn't find comprehensive or detailed guidelines on-line about
> hardware, but from what I could see, it seems that memory wouldn't be
> the main issue, but the number of cores would be (one core per
> database connection if I'm not mistaken). At the same time, we want to
> make sure that the experience is smooth for everyone... I was advised
> on the PostGIS list to give a look at pgpool (however, UNIX only).
>
> * Is there a difference in terms of possibilities, performance and
> usability between a Linux-based and a MS-based server (from the user
> perspective)? My center is unfortunately MS-centered, and existing
> equipment runs with MS systems... It would thus be easier for them to
> set up a MS-based server. Does it change anything for the user? (I
> insist on the user perspective, since I and others will not admin the
> system, but only use it)
>
> * Does anyone have worked with a server running the DB engine, while
> the DB itself was stored on another box/server? That would likely be
> the case here since we already have a dedicated box for file storage.
> Along these lines, does the system of the file storage box matter
> (Linux vs. MS)?
>
> * We may also use the server as a workstation to streamline PostGIS
> processing with further R analyses/modeling (or even use R from within
> the database using PL/R). Again, does anyone have experience doing it?
> Is a single workstation the recommended way to work with such
> workflow? Or would it be better (but more costly) to have one server
> dedicated to PostGIS and another one, with different specs, dedicated
> to analyses (R)?
>
> I realize my questions and comments may be a confusing, likely because
> of the lack of experience about these issues on my side. I really
> welcome any feedback of people working with PostgreSQL servers (+
> PostGIS ideally!) in a small unit, or any similar setting that could
> be informative!
>
> In advance, thank you very much!
>
> Sincerely,
> Mathieu Basille.
>
>
> [1] Start of the thread here:
> http://lists.osgeo.org/pipermail/postgis-users/2015-February/040120.html
>
When I looked at tuning options for PostgreSQL, I found that there were
limitations mentioned for Microsoft O/S's.

I get the general impression from my reading from multiple sources over
the years, that if you are serious about performance on a server, then
you should prefer Linux to Microsoft.  Note that most servers run Linux,
and that over 95% of the top 500 super computers runs Linux - which is
rather telling about the perception of Microsoft's performance.


Cheers,
Gavin