You could also have a single database, but have a copy of all the
tables in a separate schema for each user. It means each user is
separated still, but you don't have to manage multiple databases and
opening / closing etc.
A single big database is always easier to manage. Coz with schema
changes you don't need to update the same table 1000 times. Done that,
not fun :). But I guess if you managed it properly it wouldn't be a big
deal.
Or you could have a single big database and put some views on top of it
to filter by user, so each user has their own view of the database?. Not
sure how that would work, but just an idea :).
Ryan
On 5/09/2010 10:14 PM, Donzo wrote:
I'm a complete noob to H2 so I'm seeking opinions based on real H2
experience / knowledge.
We're about to build a web app with potentially 1000's of users of
which ~1% or less will be active at any time. Each user's data, ~1M to
10M, is completely isolated: users access their own data and never any
other user's data. For several reasons, we'd like to avoid a single
large database containing all user data. If possible (ie if the
overhead on cpu, mem, disk, etc for database open, connections, close,
etc is not forbidding), we'd like to have many small databases each
containing the data for just one user (a sharded database design).
We've looked at Python+SQLite (which looks acceptable so far) but are
also now looking at Java/Scala+H2. We'll probably be hosting on
Joyent.com Smartmachines so memory is our greatest constraint, then
disk, then cpu.
Here's what we're thinking so far. Have one H2 database per user.
Create our own DataSource class (for use with Spring JdbcTemplate sub-
classes, etc) that returns a connection to the current user's proper
H2 database. Several alternatives on this are:
--ALT-1: DataSource creates a new connection for each web request with
H2 setting of DB_CLOSE_DELAY=0 (the default) meaning the database must
also be opened each time. (What is the overhead cost of opening an H2
database?)
--ALT-2: DataSource creates a new connection for each web request with
H2 setting of DB_CLOSE_DELAY=900 meaning the database will remain
opened for 15 minutes. (What is the overhead cost of keeping an H2
database open?)
--ALT-3: DataSource caches the connections itself (creating a new one
when necessary). (How much overhead cost is saved by not creating new
connections for each web request?)
--ALT-4: Forget creating our own DataSource.
org.h2.jdbcx.JdbcConnectionPool can pool connections for different
databases. A sub-class of org.h2.jdbcx.JdbcConnectionPool can be made
to create new connections and return connections specific to the
current web user (we haven't investigated this yet).
--ALT-5: Forget sharding. Just put all the data in a single database.
Would appreciate your thoughts on this ... especially on any other
considerations we should be aware of. Many thanks in advance!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
