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] >
