On 10/01/2010 06:51 PM, Frank Church wrote:
> I want to migrate a lot of single user databases onto a single server
> to allow the users to access them remotely, instead of on the
> workstations. The databases are quite small and even the most heavily
> used ones only have at most a few hundred records added to them
> everyday.
>
> The problem is they have a high polling rate, about every 5 secs. If I
> migrate them to a central servers how much hardware will be needed for
> them to cope?
A big question here is how the polling is done. Do the polling clients
create new connections for each poll? Or do they keep an idle connection
around?
If they disconnect between queries, you may want to use a connection
pool like PgPool or similar; see the wiki for options. The pool will
help reduce the cost of repeated backend startups by keeping backends
around when the clients disconnect.
Either way, you'll want enough RAM to keep the "hot" data (or at least
the indexes for that data) in memory, with a comfortable margin. If
that's impossible to achieve, look at using partial indexes or other
things to permit you to maintain small indexes that will fit in memory,
because you're going to need it.
Lots of (preferably fast) disks are a big bonus if your data set sees
frequent writes or is too big to fit in memory. Disk storage is less
critical if your whole dataset fits comfortably in memory and doesn't
have heavy write activity.
As for CPU: it depends on if you can fit the whole dataset into memory,
and how complex your queries are. If you're doing fairly simple queries
without too much CPU-intensive calculation, or your dataset doesn't fit
in RAM so the CPU will be waiting on the disks, then there's not much
point to a fast CPU. OTOH, if your data set fits in RAM, the CPU and the
speed of the RAM will become the limiting factors for query execution speed.
> My initial approach was to create views for each user and use
> updateable views and/or triggers so that the applications can work
> transparently simply by changing the names of the tables to the names
> of the views, but I am wondering whether it will be much simpler to
> use individual databases instead.
There are security trade-offs there, as well as efficiency ones. If you
use one database you have much more ability to pool connections, which
is great when dealing with polling. Unfortunately, Pg backends can't
switch users, so pooling won't do you anywhere near as much good if
you're using database-level user access control.
You might want to consider isolating the tables into different schema of
the same database. You can use search_path to make this largely
transparent, especially with per-user search paths if you're using db
user level security.
--
Craig Ringer