List:General Discussion« Previous MessageNext Message »
From:Olaf Stein Date:December 29 2006 3:26pm
Subject:Re: Max size and row numbers
View as plain text  
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" <mysqlsupport@stripped> 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" <steino@stripped>
> To: <mysql@stripped>
> 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/mysql?unsub=1
>> 

-------------------------
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: steino@stripped

Thread
Max size and row numbersOlaf Stein28 Dec
  • Re: Max size and row numbersViSolve DB Team29 Dec
    • Re: Max size and row numbersOlaf Stein29 Dec
      • Re: Max size and row numbersPhilip Mather29 Dec
Re: Max size and row numbersPhilip Mather29 Dec