Re: Experience with many schemas vs many databases

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(at)hogranch(dot)com> 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.
>
>
>
>
>
>
>
>
>