Re: 2 GB limit reached
On Tue, 08 Jan 2002 20:03:07 -0500 Dennis [EMAIL PROTECTED] wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. You could use a mysqld that been configured with --with-raid then do something along the lines of: ALTER TABLE bigtable RAID_TYPE=STRIPED RAID_CHUNKS=16 RAID_CHUNKSIZE=524288; This splits the file into 16 chunks and stripes the data across them - if all 16 chunks are going to be on the same disk then I guess you'd want a very large chunk size (like the 512meg above) so that your disk heads aren't continually seeking :) Also bear in mind that you'll need 2gig free to perform the above operation since all it really does is create a new table for you and copy the data across. The 2gig limit is a problem that I'm going to hit fairly shortly - perhaps someone with a little more knowledge can tell me what the performance will be like using mysql's raid rather than OS large file support? Also where can one find good information about linux large file support - on my slackware 8, 2.4.17, ext2 testbox I can create 4 gig files using dd but mysql failed to create a table greater than that size (not quite sure why it's 4gig rather than 2gig - suggests something's working :). Regards, Chris -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 3:31 PM To: [EMAIL PROTECTED] Subject: RE: 2 GB limit reached We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis -- Chris Wilson [EMAIL PROTECTED] http://www.wapmx.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
At 11:26 PM 01/08/2002, you wrote: On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. Can you at least mysqldump the data out to a file? No, but that might be cumbersome with 4 million records. :-) db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
In the last episode (Jan 08), Dennis said: We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? If you can SELECT from it, create a new MYISAM table with the RAID option, or create an INNODB table with multiple 2gb tablespace files. Then INSERT INTO newtable SELECT * FROM oldtable. If you can't select from it, you might have to run myisamchk to repair the table, or transfer the table to an OS that does not have the 2gb limit and split the table there. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
Enable large file support: [root@xxx array0]# du -h bigfile 2.9Gbigfile [root@xxx array0]# ls -al bigfile -rw-rw-r--1 root root 30 Jan 9 11:06 bigfile [root@xxx array0]# uname -a Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 10:52 AM To: [EMAIL PROTECTED] Subject: Re: 2 GB limit reached At 11:26 PM 01/08/2002, you wrote: On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. Can you at least mysqldump the data out to a file? No, but that might be cumbersome with 4 million records. :-) db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
At 12:09 PM 01/09/2002, Dan Nelson wrote: In the last episode (Jan 08), Dennis said: We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? If you can SELECT from it, create a new MYISAM table with the RAID option, or create an INNODB table with multiple 2gb tablespace files. Then INSERT INTO newtable SELECT * FROM oldtable. If you can't select from it, you might have to run myisamchk to repair the table, or transfer the table to an OS that does not have the 2gb limit and split the table there. -- Dan Nelson [EMAIL PROTECTED] is the 2GB limit only in linux 2.2? (ie does 2.4 not have that limit?). Running a 2.4 kernel is the easiest option in that case. Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
On Wed, Jan 09, 2002 at 12:23:27PM -0500, Dennis wrote: is the 2GB limit only in linux 2.2? (ie does 2.4 not have that limit?). Running a 2.4 kernel is the easiest option in that case. Right. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 6 days, processed 161,378,983 queries (276/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
As I said in my message (although I've still not had time to look further into this) this will not solve your problem completely. My 2.4.17 testbox will happily create 4gig flies - but as soon as my mysql myisam table reached 4gig I got a Table full error when trying to insert. Going InnoDB is probably the best solution, from what I've seen so far - will mysql switch to make that the default table type at some point in the future? Enable large file support: [root@xxx array0]# du -h bigfile 2.9Gbigfile [root@xxx array0]# ls -al bigfile -rw-rw-r--1 root root 30 Jan 9 11:06 bigfile [root@xxx array0]# uname -a Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 10:52 AM To: [EMAIL PROTECTED] Subject: Re: 2 GB limit reached At 11:26 PM 01/08/2002, you wrote: On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. Can you at least mysqldump the data out to a file? No, but that might be cumbersome with 4 million records. :-) db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Chris Wilson [EMAIL PROTECTED] http://www.wapmx.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
I've had no problems with MySQL RAID, and the performance is excellent as long as you have your indices set up properly. One caveat I might add is that MySQL RAID does not apply to index files (.MYI). If your index files are going to grow 4 GB, you'll need to consider alternate filesystems anyway. For your reference, I'm running v3.23.42. My database has one primary MERGE table which collates the data from 12 month-specific tables. Each of those month-specific tables uses MySQL RAID with the following options: MAX_ROWS=20 PACK_KEYS=1 RAID_TYPE=striped RAID_CHUNKS=32 RAID_CHUNKSIZE=256000 There are currently over 42 million entries in the MERGE table, occupying over 54 GB of disk. There are also various smaller lookup tables, etc. -- coop The 2gig limit is a problem that I'm going to hit fairly shortly - perhaps someone with a little more knowledge can tell me what the performance will be like using mysql's raid rather than OS large file support? Also where can one find good information about linux large file support - on my slackware 8, 2.4.17, ext2 testbox I can create 4 gig files using dd but mysql failed to create a table greater than that size (not quite sure why it's 4gig rather than 2gig - suggests something's working :). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
Hi, I had this too - in the documentation, although maybe not highlighted enough, there are instructions for overcoming 4G - you have to set MAX_ROWS for you table to a 'large' number to tell mysqld to use 4 byte pointers for the file. Regards Quentin -Original Message- From: Chris Wilson [mailto:[EMAIL PROTECTED]] Sent: Thursday, 10 January 2002 7:07 a.m. To: Johnny Withers Cc: [EMAIL PROTECTED] Subject: Re: 2 GB limit reached As I said in my message (although I've still not had time to look further into this) this will not solve your problem completely. My 2.4.17 testbox will happily create 4gig flies - but as soon as my mysql myisam table reached 4gig I got a Table full error when trying to insert. Going InnoDB is probably the best solution, from what I've seen so far - will mysql switch to make that the default table type at some point in the future? Enable large file support: [root@xxx array0]# du -h bigfile 2.9Gbigfile [root@xxx array0]# ls -al bigfile -rw-rw-r--1 root root 30 Jan 9 11:06 bigfile [root@xxx array0]# uname -a Linux [hidden] 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 09, 2002 10:52 AM To: [EMAIL PROTECTED] Subject: Re: 2 GB limit reached At 11:26 PM 01/08/2002, you wrote: On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. Can you at least mysqldump the data out to a file? No, but that might be cumbersome with 4 million records. :-) db - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Chris Wilson [EMAIL PROTECTED] http://www.wapmx.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
I just found out that this is Freebsd 4.1 running v3.22.23 of mySQL. Is there an issue with v3.22 related to this? Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
In the last episode (Jan 09), Dennis said: I just found out that this is Freebsd 4.1 running v3.22.23 of mySQL. Is there an issue with v3.22 related to this? http://www.mysql.com/doc/T/a/Table_size.html says that 3.22 was limited to 2gb or 4gb, depending on the OS. Upgrading to 3.23 will let you use one of the newer table types. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. Eric Mayers -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 3:31 PM To: [EMAIL PROTECTED] Subject: RE: 2 GB limit reached We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
If it is on a Linux machine (yes, yes) look into the file size limitation of the OS! (assuming you are talking about a single table?)... On Tue, Jan 08, 2002 at 06:30:59PM -0500, Dennis wrote: We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: 2 GB limit reached
At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. db Eric Mayers -Original Message- From: Dennis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 08, 2002 3:31 PM To: [EMAIL PROTECTED] Subject: RE: 2 GB limit reached We have a database that seems to have grown too large, and now any operation fails on it. How can we fix this? Dennis - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 2 GB limit reached
On Tue, Jan 08, 2002 at 08:03:07PM -0500, Dennis wrote: At 07:07 PM 01/08/2002, you wrote: Dennis, You may want to look into using InnoDB tables. I believe InnoDB tables are immune to the 2gb limit (which usually comes from the filesystem). Also, InnoDB claims that the innodb tables are faster than MyISAM tables in some cases. See www.innodb.com or http://www.mysql.com/doc/I/n/InnoDB_overview.html for further detail. thanks, but that doesnt tell me how to recover THIS filethe right answer is use a different OS, but thats out of my control here. Can you at least mysqldump the data out to a file? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 6 days, processed 134,113,561 queries (249/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
