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
10  1808 
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
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/
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?
>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 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.
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
>>>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
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?
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
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/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: aaron |
last post by:
I have a question about (i think) joining.
If I have a table in a database that has this info:
key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1
the favorite icecream...
|
by: anders |
last post by:
What is the better approach in MySQL from a performance point of view?
I have approx 1000 stock market fond titles that I want to insert in
MySQL on daily basis and keep them in there for some...
|
by: Kolie Kay |
last post by:
I am not posting as a troll, rather I am looking to get some information for
an implementation that I plan to use with mysql. My first question is why go
with sql server or mysql, beyond the...
|
by: John R |
last post by:
I found this old thread while looking for some type of compression
solution myself. Long story short, I contacted Lester and ended up
writing an LZO solution just as Mark had mentioned below....
|
by: jonathan |
last post by:
hey all,
I realize that this question might pop up from time to time, but I
haven't seen it a while and things might of changed, so -
Right now (July 2004) how does mysql stand up in...
|
by: Bing Wu |
last post by:
Hi all,
I am running a database containing large datasets:
frames: 20 thousand rows,
coordinates: 170 million row.
The database has been implemented with:
IBM DB2 v8.1
|
by: Try Kret |
last post by:
Hello,
Any good documents on how to connect and manipulate data stored in MySQL.
Faithfully yours,
Try.
|
by: Dave Smithz |
last post by:
Hi there,
A new potential client currently run there database on using MySQL and PHP,
it has about 7000 records and runs rock solid over the web.
However they need much more functionality and...
|
by: Good Man |
last post by:
Hi there
I've noticed some very weird things happening with my current MySQL setup
on my XP Laptop, a development machine.
For a while, I have been trying to get the MySQL cache to work....
|
by: alf |
last post by:
Hi,
is it possible that due to OS crash or mysql itself crash or some e.g.
SCSI failure to lose all the data stored in the table (let's say million
of 1KB rows). In other words what is the worst...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
