Re: Experience with many schemas vs many databases

[undisclosed user <lovetodrinkpepsi@xxxxxxxxx>]

The app is very similar to wordpress MU. Each user has the same schema but different data. The app uses the same codebase for every user. Users do not have direct access to data. Currently, the DB is 90% r / 10% w and about 80GB MyISAM. Most of the queries are simple (75%)...the rest are joins (25%). I am using myisam but I have too many concurrency and table crash issues...  Mysql Fulltext search is horrible and causes a lot of lockups....tsearch2 seems like a good solution for us.

Basically, I want:

1. Good concurrency / decent performance

2. Data integrity

3. Fast Search

4. Ability to backup per user

Backing up data by user is required for my solution. A lot of times, users screw up and they want to rollback to a previous state. 

If I were to do a database per user, the backup/restore would be very straight-forward. I believe backup/restore procedure is similar for schemas (let me know if I am wrong here)? If I were to do a single schema/database, is it possible to get data per user and back it up? Select user rows, copy to a temp table/db, backup? 

Thanks,

Frank

On Sun, Nov 15, 2009 at 1:11 PM, John R Pierce <pierce@xxxxxxxxxxxx> wrote:

undisclosed user wrote:

If I were to switch to a single DB/single schema format shared among all users , how can I backup each user individually?

depending on how many tables, etc, I suppose you could use a seperate series of SELECT statements ...
but if this is a requirement, it certainly puts constraints on how you organize your data.   without a much deeper knowlege of your application, data, and requirements, its kind of hard to give any sort of recommendations.   you mentioned myISAM, so I gather this data isn't at all transactional, nor is relational integrity a priority.