I'm not sure if you have a problem with one large InnoDB table or one
large file. With InnoDB you can specify multiple files, limit the size
of each file and even specify the location of each file. So you could
limit your InnoDB files to say 1GB each (or lower/higher) and specify
which disk (or partition, directory, etc.) each file will be stored on,
sort of creating a RAID setup. You would still have one table, but it
would be stored in separate files and InnoDB would fill them up
sequentially. That can give you the same effect of creating multiple
tables, but without the need for application logic to query multiple
tables. Just make sure you extend the table space (i.e. specify another
file) when you need to.
http://dev.mysql.com/doc/mysql/en/Adding_and_removing.html
On Dec 1, 2004, at 12:39 AM, Alok Gore wrote:
Thanks a lot for the quick response :)
We are not using MyISAM tables. All our tables are InnoDB tables. The
rational behind this decision is that the database is expected to get
hundreds of insert queries per second, so we want the row level locking
of InnoDB to speed this up. (I should have mentioned this in the first
mail. Sorry for that). Having one large InnoDB table is not a good
option because it'll throttle the performance of selects and inserts
and
will be a hindrance
The option of replication has been looked in to.We are not relying on
the mysql replication for load balancing or data backup.These things
are
done by the application layers.
I had looked in to the mysql cluster. It does not cover all the use
cases that we have in mind and it is not flexible enough to give us
more
control over how data is stored and restored in case of node failures.
So it again boils down to same two questions:
1) What is a better option: (Having hundreds of databases or having a
single database with thousands of tables).
On Tue, 2004-11-30 at 22:38, Brent Baisley wrote:
If you are hitting file size limits, you probably want to look into
using the InnoDB table type. That will allow you to work around file
size limits and have a database of just about any size you need. You
won't end up having a 30GB file, but multiple smaller files which will
be transparent to your application. InnoDB tables are probably your
best place to look for a solution.
If you must use MyISAM table types, perhaps look into the RAID option
for MyISAM tables. Although that will still have it's limits, mainly
because of index size limits.
If you are worried about load, look into replication. One machine will
act as the master where all updates will occur, but then any of your
replication machines can handle queries. You could setup a round robin
DNS to automatically split the load or go with something even more
sophisticated. You also might see what you can find on MySQL clusters,
although this is a fairly new feature (this year) and I myself have
not
read up on it.
On Nov 30, 2004, at 10:17 AM, Alok Gore wrote:
Hi All,
I tried digging for this information in the archives but could not
find anything.
I am in to developing an app. that uses very high amount of data
(Close to 80 GB per machine). It has 3-4 logical tables. But I have
to
partition them in to multiple tables because the mysql table size is
limited by the system file size (even if I enable large file size
support, I don't want to have 30 GB large tables). I see two options
here.
1) Have a single database and create one table-set (set of 3-4
tables,
each of them representing one partition of the logical table) every
time
the table size grows beyond a certain limit(say 100MB). But this
way, I
might end up having thousands of tables in a single database.
2) Create one mysql-database for each table-set.This way, I'll end up
having hundreds of databases in the mysql data directory.
Is any one of these two methods preferable over the other because of
the
way mysql caches the information ? In other words, which one of the
above mentioned options exerts a heavier load on the mysql server ?
One more parallel question is, because I have so many databases in my
data directory, is it a good decision to run multiple mysql server
instances (Divide the data space in to multiple partitions and have
one
mysql server instance handle one of those data partitions) ? I am
thinking abt this because
1)As the number of tables/databases grows, mysql server will have to
open more files in order to serve requests. And because of the limit
on
max number of open files by a process, it will be forced to close
some
tables to open other tables.
2) In general, the resources(like memory and CPU) allocated to a
process
are limited by the OS and it would reach the limit as the load grows.
Having multiple mysql server instances could help in those cases, I
guess. (Even though mysql is multi-threaded it's eventually one
process
running multiple threads)
But I couldn't find any use cases where people run multiple mysql
server
instances for performance improvements.
Am I missing something?
Thanks in advance.
-Alok.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
