469,903 Members | 1,503 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

mysql implementation question

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

Jul 17 '05 #1
10 1697
Marcus <Ju********@aol.com> wrote in news:p2gNc.3005$tw3.1330
@newssvr15.news.prodigy.com:
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

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

Jul 17 '05 #2
Hi,

On Mon, 26 Jul 2004 23:00:37 GMT, Marcus <Ju********@aol.com> wrote:
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!!
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


Marcus


--
Jochen Daum - Cabletalk Group Ltd.
PHP DB Edit Toolkit -- PHP scripts for building
database editing interfaces.
http://sourceforge.net/projects/phpdbedittk/
Jul 17 '05 #3
Edward Alfert wrote:
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,

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?

Jul 17 '05 #4
>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?
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.
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.


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
Jul 17 '05 #5
Gordon Burditt wrote:
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?

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.

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.

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


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.

Jul 17 '05 #6
Marcus wrote:
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


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
Jul 17 '05 #7
>>>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.

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


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.


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
Jul 17 '05 #8
Al C. wrote:
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


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?

Jul 17 '05 #9
Marcus wrote:

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?


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

Jul 17 '05 #10
Hi MArcus,

On Tue, 27 Jul 2004 06:10:42 GMT, Marcus <Ju********@aol.com> wrote:
Al C. wrote:
Both methods are poor design. You are creating a maint. nightmare for
yourself.
(...) 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?


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/
Jul 17 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by aaron | last post: by
reply views Thread by Kolie Kay | last post: by
reply views Thread by John R | last post: by
133 posts views Thread by jonathan | last post: by
3 posts views Thread by Try Kret | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.