Re: Max size and row numbers
Olaf, That's a very broad question depending upon your exact requirements to be honest. So long as you've considered file size limitations and you've opted for a decent RAID system there's not too much more to worry about, try and buy the fastest disks possible obviously i.e. 15,000 RPM ones that are specifically designed for RAID setups etc... Regards, Phil There is a RAID now an there will definitely one in the new setup. As far as the max file sizes from the file systems go I am not worried after all I read. Thanks for those links btw. Besides the size though, what should I pay attention to when selecting the file system Thanks Olaf On 12/29/06 11:31 AM, "Philip Mather" <[EMAIL PROTECTED]> wrote: Olaf, 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 The "best" is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil - 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]
Re: Max size and row numbers
Olaf, 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 The "best" is probably ZFS if you really are intent on make things huge, http://en.wikipedia.org/wiki/Zettabyte_File_System but check out the very useful http://en.wikipedia.org/wiki/Comparison_of_file_systems page as well. Dunno about speed-wise, but you'd end up in an argument centering around RAID probably? Regards, Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max size and row numbers
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: > 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]
Re: Max size and row numbers
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: 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] >
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]
