Thanks for the detailed answer. So basically the limitations come from the OS and the file system used.
What is the best file system to use for mysql (not considering the filesize limitations)? Thanks Olaf On 12/29/06 2:25 AM, "ViSolve DB Team" <[EMAIL PROTECTED]> wrote: > Hi, > > The maximum effective table size for MySQL databases is usually determined by > operating system constraints on file sizes, not by MySQL internal limits. > > If you need a MyISAM table that is larger than 4GB in size (and your operating > system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH > and MAX_ROWS options. > > The InnoDB storage engine maintains InnoDB tables within a tablespace that can > be created from several files. This allows a table to exceed the maximum > individual file size. The tablespace can include raw disk partitions, which > allows extremely large tables. The maximum tablespace size is 64TB. > > On Linux 2.2, you can get MyISAM tables larger than 2GB in size by using the > Large File Support (LFS) patch for the ext2 filesystem. On Linux 2.4, patches > also exist for ReiserFS to get support for big files (up to 2TB). However, > the maximum available file size still depends on several factors, one of them > being the filesystem used to store MySQL tables. > > You can check the maximum table size for a table with the SHOW TABLE STATUS > statement or with myisamchk -dv tbl_name. If your large table is read-only, > you can use myisampack to compress it. myisampack usually compresses a table > by at least 50%, so you can have, in effect, much bigger tables. > > Thanks > > Visolve DB Team > > ----- Original Message ----- > From: "Olaf Stein" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Friday, December 29, 2006 4:14 AM > Subject: Max size and row numbers > > >> Hey everyone >> >> I have more of a general question regarding your experience with large >> tables. >> >> I currently have a table (MyISAM, 6 columns, lots of reading access, some >> writing) with about 70.000.000 records, using 2.5GB of diskspace. I am >> running MySQL 5.0.* on a RedHat Enterprise AS 4 system (2 CPU, 8GB Ram). >> >> I just read that the max size for a table is 256TB in a default >> installation. I have basically no experience with tables that big and mine >> is potentially growing to several hundred million records. >> >> First of all, are there theoretical limitations (if the 256TB are correct I >> would be fine with that I guess) in size and number of records? >> >> More importantly, what are the practical limitations and/or pitfalls? Is >> ext3 as filesystem a limiting factor? >> >> If you have experience or know of good links regarding this topic, please >> let me know >> >> Thanks in advance >> >> Olaf >> >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> ------------------------- Olaf Stein DBA Center for Quantitative and Computational Biology Columbus Children's Research Institute 700 Children's Drive phone: 1-614-355-5685 cell: 1-614-843-0432 email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
