Single database versus shared database for same table structure?

We are planning to host data for 50 to 100 different companies on a
single server. Each company will have their own set of data, but exact
same table structure.

Consider these solutions below:

1. Single database for all companies (companyid on each table)
2. Separated database for each company

From experienced DBAs what is most appropriate recommendation that you
can give for these two solutions above in terms of :

a) scalability (more servers)
b) maintainability (easy to deploy upgrades and avoid global mistakes)
c) performance (threading/IO)

Rod

On Tue, 10 Jun 2008 14:28:31 +0200, Rodusa <rlueneberg@gmail.com> wrote:

> We are planning to host data for 50 to 100 different companies on a
> single server. Each company will have their own set of data, but exact
> same table structure.
>
> Consider these solutions below:
>
> 1. Single database for all companies (companyid on each table)

Aaargh!
Unless the companies are related in some database-way, seperate databases
would be best. And never use a tablename to hold any data of importance
(so no company id) save for just an descriptive name, and possible post /
prefixes to avoid conflicts when employing different packages.

> 2. Separated database for each company

Go for it.

> From experienced DBAs what is most appropriate recommendation that you
> can give for these two solutions above in terms of :
>
> a) scalability (more servers)

Seperate databases, just move them if needed.

> b) maintainability (easy to deploy upgrades and avoid global mistakes)

Seperate databases,
- tablenames will be identical, making it easier to deploy scripts using
those tables
- an upgrade will work identicallly on all databases, however, it's easy
to upgrade a few and leave others alone if need be.
- easy backups of the data of just 1 company.
- many, many more

> c) performance (threading/IO)

Doesn't make much difference I'd think.
--
Rik Wasmus
....spamrun finished

Thanks. Looks like separating is a better alternative specially in
terms scalability. This one weights a lot. But I am still not
convinced that threading if about the same for both solutions,
specially when you have lots of users doing inserts and updates
causing table locks.

Just curious, I know this is not a DBA question but I am also
interested to know one more thing:

Is it a good idea or common practice to separate (cgi scripts like
php, perl, etc) in case we choose to use separated databases?

Rod

>We are planning to host data for 50 to 100 different companies on a
>single server. Each company will have their own set of data, but exact
>same table structure.

Think also: security. Is it accurate that each company will want
the exclusive right to change/use their own data? That you won't
have queries that want to use data from more than one company? If
so, the security issue favors one-database-per-company.

You can have multiple tables in a single database with the
tables named in a pattern like:
companyname_%

That's clumsy and it requires substituting the company name into
every query. You can set up MySQL security for different tables.

MySQL security doesn't deal with separately protecting different
rows in the same table differently.

On the other hand, if the different companies need to cross-reference
each other, and you ever have to go look at data for every company,
those queries could slow down significantly with one-database-per-company.

>Consider these solutions below:
>
>1. Single database for all companies (companyid on each table)

Is that companyid *IN* each table (a column named companyid in each
table), or companyid as part of the name of each table?


>2. Separated database for each company
>
>From experienced DBAs what is most appropriate recommendation that you
>can give for these two solutions above in terms of :
>
>a) scalability (more servers)

You can put each separate database on a separate server, up to the
point that you have problem handling a single company's server on
a single powerful machine.

>b) maintainability (easy to deploy upgrades and avoid global mistakes)

Database-per-company makes it easy to also do *TWO* databases-per-company
(test and production). Using a test database as a victim, you can
write a script to do all the schema upgrades and new tables in a
script, debug it, and then apply it in batch mode to each database
when it's down for maintenance.

There is also the possibility of using a "new production" and "old
production" database, where changing the database name does most
of the work of a switchover.

>c) performance (threading/IO)

MySQL tends to use only one index per table in a given query. It
tends to slow things down if it would have liked to use both companyid
and something else (like, say, invoice date) and has to choose
between them. Now, you can get around that with compound indexes,
but it takes more disk space (and probably more I/O) if EVERY index
is compound with companyid.

On 10 Jun, 22:47, gordonb.81...@burditt.org (Gordon Burditt) wrote:
> MySQL tends to use only one index per table in a given query. *It
> tends to slow things down if it would have liked to use both companyid
> and something else (like, say, invoice date) and has to choose
> between them. *Now, you can get around that with compound indexes,
> but it takes more disk space (and probably more I/O) if EVERY index
> is compound with companyid.

But does using more disk space really matter. Having a MySQL database
uses more disk space than say flat files. You make the choice to use
an RDBMS instead of flat files bcause of trade offs in useability and
ease of coding. Designing your application correctly and using the
correct indexes is just an extension of this.

>> MySQL tends to use only one index per table in a given query. *It
>> tends to slow things down if it would have liked to use both companyid
>> and something else (like, say, invoice date) and has to choose
>> between them. *Now, you can get around that with compound indexes,
>> but it takes more disk space (and probably more I/O) if EVERY index
>> is compound with companyid.
>
>But does using more disk space really matter.

It depends on the application. If it's running on an old version
of Linux that can't be changed which has a 4GB file size limitation,
you're putting data from all the companies in one data file, AND
making the index bigger, getting you closer to the limit. If you've
got a whole 500GB drive for the data, and an OS that can use it,
and the data is currently 1GB for 10 companies and you hope to handle
up to 100 companies, you might not care at all.

>Having a MySQL database
>uses more disk space than say flat files. You make the choice to use
>an RDBMS instead of flat files bcause of trade offs in useability and
>ease of coding. Designing your application correctly and using the
>correct indexes is just an extension of this.

Bigger indexes do have a cost in CPU and disk i/o. It takes more CPU
and disk i/o to search one, and it takes more to update one. This
cost may be negligible or significant depending on the application.

What I think is MUCH more significant, if the data from individual
companies is truly independent and never needs to be used in combination
with data from other companies, is that administrative tasks like
restoring backups for one company only are made much harder using
standard tools for the all-in-one-table approach. Such an operation
probably interferes with use of other company data during the restore
due to locking. If this is an application where availability 24x7 is
important, this could be a big disadvantage.