The Problem: We’ve built an application where each user has a large,
always-growing amount of data – thousands of records per user with a
variety of data types. Right now, everybody’s data is in a single
MySQL database which is working great, but we expect to soon outgrow
this setup, and are exploring the best ways to partition the data.
The Questions: Because our app is only ever dealing with one user’s
data at a time, would it make any kind of sense to create a separate
database for each user? Not a separate server per user, just a
separate database. We know we’d be working against conventions to
make it work, and would be limited by the number of inodes on the
server.
How expensive is it to setup MySQL connections on-the-fly? We
couldn’t use connection pooling in ActiveRecord, since that would mean
having to maintain thousands of connections at once. Has anyone tried
something like this? We know it sounds crazy but are wondering if it
conceivably falls under the category “so crazy it just might work”.
If there are some showstoppers, can anyone recommend a partitioning
scheme that works well with Rails? MySQL 5.1 and later seems to have
pretty impressive horizontal partitioning. The main reason we haven’t
jumped on that is because we’re wondering what happens when you need
to make a schema change. Does the whole server have to come offline,
or can it be done partition-by-partition?
-Mike Subelsky
PS If anyone else has been wondering about this, I found a great
article on maintaining five different PostgreSQL databases for a
single app:
http://tomayko.com/weblog/2007/04/13/rails-multiple-connections