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]
