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.
