mysql implementation question

[Marcus]

Quick question on how I setup my mysql database(s)...

If my setup is such that I have multiple clients, and each client gets
10 tables to store their data, is it better for performance if I put all
of these tables into 1 large database (with a unique identifier
prepended to each one) or create a separate database for each client?
i.e., assuming 1000 clients, I either would have:

A) 1 database and 10,000 tables in it, 10,000 total tables

B) 1000 databases with 10 tables in each, 10,000 total tables

Intuition tells me that the multiple databases would be better for
isolating data between clients, but I am not sure if this would slow
things down.

All the info I found on this online was inconclusive and mostly was
people speculating, so I hoped you all could help more. Thanks!!

Marcus

[Edward Alfert]

Re: mysql implementation question

Marcus <JumpMan222@aol .com> wrote in news:p2gNc.3005 $tw3.1330
@newssvr15.news .prodigy.com:
[color=blue]
> Quick question on how I setup my mysql database(s)...
>
> If my setup is such that I have multiple clients, and each client gets
> 10 tables to store their data, is it better for performance if I put all
> of these tables into 1 large database (with a unique identifier
> prepended to each one) or create a separate database for each client?
> i.e., assuming 1000 clients, I either would have:
>
> A) 1 database and 10,000 tables in it, 10,000 total tables
>
> B) 1000 databases with 10 tables in each, 10,000 total tables
>
> Intuition tells me that the multiple databases would be better for
> isolating data between clients, but I am not sure if this would slow
> things down.
>
> All the info I found on this online was inconclusive and mostly was
> people speculating, so I hoped you all could help more. Thanks!!
>
> Marcus[/color]


You did not say if this was a web based application you were developing or
not. It makes a difference.

If it is non-web based, I would do 1 database with 10,000 tables. That way
you can connect to the database once and perform all sorts of queries
instead of having 1000 connections to the database.

If it is web based, then it depends if every page requested will only
lookup info for 1 client or if it may lookup data for multiple clients at
once.


--
Edward Alfert

http://www.rootmode.com/

Multiple Domain Hosting and Reseller Hosting Plans
Coupon Code (Recurring $5/month Discount): newsgroup

[Jochen Daum]

Re: mysql implementation question

Hi,

On Mon, 26 Jul 2004 23:00:37 GMT, Marcus <JumpMan222@aol .com> wrote:
[color=blue]
>Quick question on how I setup my mysql database(s)...
>
>If my setup is such that I have multiple clients, and each client gets
>10 tables to store their data, is it better for performance if I put all
>of these tables into 1 large database (with a unique identifier
>prepended to each one) or create a separate database for each client?
>i.e., assuming 1000 clients, I either would have:
>
>A) 1 database and 10,000 tables in it, 10,000 total tables
>
>B) 1000 databases with 10 tables in each, 10,000 total tables
>
>Intuition tells me that the multiple databases would be better for
>isolating data between clients, but I am not sure if this would slow
>things down.
>
>All the info I found on this online was inconclusive and mostly was
>people speculating, so I hoped you all could help more. Thanks!![/color]

If security is an issue, the decision between 1 database comes down to
maintenance issues.
- For 1000 databases you will need scripted maintenance , ie. for
backup.
- 1000 databases are easier to move to a new server, or even to be
balanced on multiple servers

If security can be encapsulated into PHP, I would go for 1 database
with 10 tables. This is way easier to maintain.

HTH, Jochen

[color=blue]
>
>Marcus[/color]

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/

[Marcus]

Re: mysql implementation question

Edward Alfert wrote:
[color=blue]
> You did not say if this was a web based application you were developing or
> not. It makes a difference.
>
> If it is non-web based, I would do 1 database with 10,000 tables. That way
> you can connect to the database once and perform all sorts of queries
> instead of having 1000 connections to the database.
>
> If it is web based, then it depends if every page requested will only
> lookup info for 1 client or if it may lookup data for multiple clients at
> once.
>[/color]

Edward,

It is web based using PHP/MySQL. I will never have to access
information for more than 1 client at once, but occassionally I will
have to access 2 diff databases if setup with the multiple db model
(i.e. the client's db and a separate user db or something of the sort.)
Does one method provide better/easier to implement security? Based
upon this info, does it change your opinion?

[Gordon Burditt]

Re: mysql implementation question

>If my setup is such that I have multiple clients, and each client gets[color=blue]
>10 tables to store their data, is it better for performance if I put all
>of these tables into 1 large database (with a unique identifier
>prepended to each one) or create a separate database for each client?[/color]

If you have clients that might be mutually hostile to each other,
(compete with each other, might like to steal data from each other,
or otherwise aren't on the same team) it is better to put each
client in a separate database and allow each client access only to
their own database. This is probably a MUCH bigger issue than
performance. Although MySQL allows table permissions, it is
documented that using only database permisions gives you some
advantage in performance. Also, the clients don't have to worry
about naming their tables to avoid conflicts with other clients'
tables, and there's no issue of trying to figure out who created
an 11th table. And sometimes table names are not easy to change,
depending on what application the clients are running.

I am assuming that no clients write web pages that share each
other's tables. If there is a lot of that, perhaps the shared tables
belong in the same database.

Incidentally, I'd be more inclined to offer clients a database with
a maximum amount of disk space, not a maximum number of tables. It
better fits with the cost of actually providing the service.

Incidentally, how do you count temporary tables? I have discovered
a number of queries that don't work if you don't have CREATE TEMPORARY
TABLES privilege, (even select-only queries), and some of these
might take clients over the table limit.
[color=blue]
>i.e., assuming 1000 clients, I either would have:
>
>A) 1 database and 10,000 tables in it, 10,000 total tables
>
>B) 1000 databases with 10 tables in each, 10,000 total tables
>
>Intuition tells me that the multiple databases would be better for
>isolating data between clients, but I am not sure if this would slow
>things down.[/color]

For MyISAM tables, a table consists of 3 files and a database
consists of a directory. UNIX doesn't do well with really big
directories. I suspect Windows doesn't either. It's best to split
them up. I'm not sure what the situation is for other table types,
which tend to be stored in a big blob of disk space.

Gordon L. Burditt

[Marcus]

Re: mysql implementation question

Gordon Burditt wrote:
[color=blue][color=green]
>>If my setup is such that I have multiple clients, and each client gets
>>10 tables to store their data, is it better for performance if I put all
>>of these tables into 1 large database (with a unique identifier
>>prepended to each one) or create a separate database for each client?[/color]
>
>
> If you have clients that might be mutually hostile to each other,
> (compete with each other, might like to steal data from each other,
> or otherwise aren't on the same team) it is better to put each
> client in a separate database and allow each client access only to
> their own database. This is probably a MUCH bigger issue than
> performance. Although MySQL allows table permissions, it is
> documented that using only database permisions gives you some
> advantage in performance. Also, the clients don't have to worry
> about naming their tables to avoid conflicts with other clients'
> tables, and there's no issue of trying to figure out who created
> an 11th table. And sometimes table names are not easy to change,
> depending on what application the clients are running.
>
> I am assuming that no clients write web pages that share each
> other's tables. If there is a lot of that, perhaps the shared tables
> belong in the same database.
>
> Incidentally, I'd be more inclined to offer clients a database with
> a maximum amount of disk space, not a maximum number of tables. It
> better fits with the cost of actually providing the service.
>
> Incidentally, how do you count temporary tables? I have discovered
> a number of queries that don't work if you don't have CREATE TEMPORARY
> TABLES privilege, (even select-only queries), and some of these
> might take clients over the table limit.
>
>[color=green]
>>i.e., assuming 1000 clients, I either would have:
>>
>>A) 1 database and 10,000 tables in it, 10,000 total tables
>>
>>B) 1000 databases with 10 tables in each, 10,000 total tables
>>
>>Intuition tells me that the multiple databases would be better for
>>isolating data between clients, but I am not sure if this would slow
>>things down.[/color]
>
>
> For MyISAM tables, a table consists of 3 files and a database
> consists of a directory. UNIX doesn't do well with really big
> directories. I suspect Windows doesn't either. It's best to split
> them up. I'm not sure what the situation is for other table types,
> which tend to be stored in a big blob of disk space.
>
> Gordon L. Burditt[/color]

Gordon,

The clients do not know/do not care how many or what kind of tables they
have, it is all behind the scenes for the service and they never even
know about them. Everything they do just interacts through a web
interface and I only gave the 10 table number as a reference. As such
and as the system is setup, they never interact directly with the tables
via sql queries, everything is through forms etc. So I do not think
making sure each client does not access another's information is a huge
deal in this case. Thank you for your input, and any further input
would be appreciated.

[Al C.]

Re: mysql implementation question

Marcus wrote:
[color=blue]
> Quick question on how I setup my mysql database(s)...
>
> If my setup is such that I have multiple clients, and each client gets
> 10 tables to store their data, is it better for performance if I put all
> of these tables into 1 large database (with a unique identifier
> prepended to each one) or create a separate database for each client?
> i.e., assuming 1000 clients, I either would have:
>
> A) 1 database and 10,000 tables in it, 10,000 total tables
>
> B) 1000 databases with 10 tables in each, 10,000 total tables
>
> Intuition tells me that the multiple databases would be better for
> isolating data between clients, but I am not sure if this would slow
> things down.
>
> All the info I found on this online was inconclusive and mostly was
> people speculating, so I hoped you all could help more. Thanks!!
>
> Marcus[/color]

Both methods are poor design. You are creating a maint. nightmare for
yourself. What happens when you need to add a field to one of the tables, or
change it's attributes? You want to do this 10,000 times? No. So you will
need some maint. scripts and you have to make sure you don't screw them up!

Is there any way you can re-design your applicaiton to have one database with
ten tables? Put all your clients in them using identifier keys.

Do you think a bank or a brokerage house creates a new set of tables for each
client? Say a bank needs three tables to handle checking accounts. Do you
think Bank of America with a million depositors has three million tables (or
databases?) No they don't.

Maybe you don't want to put all 10,000 clients into one 10-table database
(although I would.) Perhaps you want to have two databases with 5,000 clients
each. But the concept of one client = one set of tables or one database is
not they way you want to approach this, IMO.

Al

[Gordon Burditt]

Re: mysql implementation question

>>>i.e., assuming 1000 clients, I either would have:[color=blue][color=green][color=darkred]
>>>
>>>A) 1 database and 10,000 tables in it, 10,000 total tables
>>>
>>>B) 1000 databases with 10 tables in each, 10,000 total tables
>>>
>>>Intuition tells me that the multiple databases would be better for
>>>isolating data between clients, but I am not sure if this would slow
>>>things down.[/color]
>>
>>
>> For MyISAM tables, a table consists of 3 files and a database
>> consists of a directory. UNIX doesn't do well with really big
>> directories. I suspect Windows doesn't either. It's best to split
>> them up. I'm not sure what the situation is for other table types,
>> which tend to be stored in a big blob of disk space.
>>
>> Gordon L. Burditt[/color]
>
>Gordon,
>
>The clients do not know/do not care how many or what kind of tables they
>have, it is all behind the scenes for the service and they never even
>know about them. Everything they do just interacts through a web
>interface and I only gave the 10 table number as a reference. As such
>and as the system is setup, they never interact directly with the tables
>via sql queries, everything is through forms etc. So I do not think
>making sure each client does not access another's information is a huge
>deal in this case. Thank you for your input, and any further input
>would be appreciated.[/color]

Ok, if you've got a canned application that provides its own security,
user isolation isn't a big issue.

If you have 1000 users, you have 10,000 (MyISAM) tables and 30,000
files. Assuming a two-level directory structure (MySQL doesn't
really allow anything else) consisting of a directory of databases
and the individual database directories, to optimize directory
searches, you'd like to have sqrt(30000) databases and sqrt(30000)
files in each database. That's 173.2 databases with about 173.2
files in each. Obviously, fractional databases are impractical.
However, if you can cluster the users (e.g. 167 databases with about
6 users (and 180 files) in each database), you might help performance
some. (Maybe your app will allow this as-is; maybe it won't.) Keep
your sanity and put all the tables for a given user in the same
database. You don't have to get the numbers exact. 167 or 200 or
100 databases is closer to optimal than 1000 or 1. And 1000 databases
with 30 files each is still closer to optimal than 1 database with
30000 files.

How many directory entries get searched? On the average, half of
the slots in the directory-of-databases plus half the slots in one
database directory:

1 database with 30000 files 30001 slots (yuuch!!)
1000 db with 30 files (1 user) each 1030 slots 29x faster than 1db
100 db with 300 files (10 users) each 400 slots 75x faster than 1db
143 db with 210 files (7 users) each 353 slots 85x faster than 1db
200 db with 150 files (5 users) each 350 slots 86x faster than 1db
167 db with 180 files (6 users) each 347 slots 86x faster than 1db
173.2 db with 173.2 files (5.77 users) 346.4 slots (can't really do this)

Now, the access consists of a lot more than the directory search,
so the problem with only one database is a lot less dramatic than
it appears here, but it still can be significant.

I do recall having hammered home the big-directory problem when running
USENET over UUCP two decades ago. If the UUCP spool directory
(which was a single directory per system at the time) got too big,
everything ground to a halt because searching it took longer than the
other system would wait.

Gordon L. Burditt

[Marcus]

Re: mysql implementation question

Al C. wrote:
[color=blue]
> Both methods are poor design. You are creating a maint. nightmare for
> yourself. What happens when you need to add a field to one of the tables, or
> change it's attributes? You want to do this 10,000 times? No. So you will
> need some maint. scripts and you have to make sure you don't screw them up!
>
> Is there any way you can re-design your applicaiton to have one database with
> ten tables? Put all your clients in them using identifier keys.
>
> Do you think a bank or a brokerage house creates a new set of tables for each
> client? Say a bank needs three tables to handle checking accounts. Do you
> think Bank of America with a million depositors has three million tables (or
> databases?) No they don't.
>
> Maybe you don't want to put all 10,000 clients into one 10-table database
> (although I would.) Perhaps you want to have two databases with 5,000 clients
> each. But the concept of one client = one set of tables or one database is
> not they way you want to approach this, IMO.
>
> Al[/color]

I don't think I explained the setup well... the reason for the design is
that each one of our clients that gets the 10 or whatever tables which
are used in their account in turn use those tables and interact with
them with THEIR end users, numbers which could range from the hundreds
to thousands. That's why I am trying to isolate sets of tables or
databases. In effect:

Main System
|
Client 1 Client 2 Client 3
| | |
Client 1's End Users Client 2's... etc.


I see your point with the 10 total tables and just having a field that
identifies each client, but my original fear with that was that each
table would get so large that any queries on them would be slowed down
immensely. Would this become an issue?

[Al C.]

Re: mysql implementation question

Marcus wrote:
[color=blue]
>
> I see your point with the 10 total tables and just having a field that
> identifies each client, but my original fear with that was that each
> table would get so large that any queries on them would be slowed down
> immensely.  Would this become an issue?[/color]

Not if you:

1. Keep the tables optimized
2. Make use of indexes
3. Write "fast" queries.

Doing the above is outlined in the MySQL docs.

I may be very wrong, and you should check it out in the MySQL docs, but I
don't think there is a size limit to a table or a database anymore. If you
had a 150 GB drive you could have a 150GB database.

There are many ways to do load balancing but that's not an area I have much
expertise in, so I leave that subject to others.

All I know is that I've never seen an application that has either one database
of 10,000 tables or 10,000 databases with 10 tables each. But nothing
surprises me anymore and maybe with today's technology there is merit to it.
I know that *I* would not want to be the guy who has to maintain the
application!

Let us know what you decide to do.

Al

[Jochen Daum]

Re: mysql implementation question

Hi MArcus,

On Tue, 27 Jul 2004 06:10:42 GMT, Marcus <JumpMan222@aol .com> wrote:
[color=blue]
>Al C. wrote:
>[color=green]
>> Both methods are poor design. You are creating a maint. nightmare for
>> yourself.[/color][/color]
(...)[color=blue]
> Al
>
>I don't think I explained the setup well... the reason for the design is
>that each one of our clients that gets the 10 or whatever tables which
>are used in their account in turn use those tables and interact with
>them with THEIR end users, numbers which could range from the hundreds
>to thousands. That's why I am trying to isolate sets of tables or
>databases. In effect:
>
> Main System
> |
> Client 1 Client 2 Client 3
> | | |
>Client 1's End Users Client 2's... etc.
>
>
>I see your point with the 10 total tables and just having a field that
>identifies each client, but my original fear with that was that each
>table would get so large that any queries on them would be slowed down
>immensely. Would this become an issue?[/color]

Al is right. With proper Indexing, ie on the Clients end users (can be
a unlimited tree structure) the speed is nearly constant and not
depending on the size.

HTH, Jochen

--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.

http://sourceforge.net/projects/phpdbedittk/