Re: InnoDB file size...
From the InnoDB manual: Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows can be physically removed only in a purge operation after they are no longer needed in transaction rollback or consistent read. Perhaps that helps. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: I-A.Kotopoulos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, 20 June, 2003 04:36 Subject: InnoDB file size... I am currently working on some experiments and have to deal with some millions of tuples. As you can imagine indexing is essentiall. However My InnoDB file has grown to over 3.6G and fragmentation is sometimes a big problem. Additionally the file keeps its size even after dropping the 'big' tables. So, any suggestions in order to optimize the space usage? thanx in advance aik_b -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB File Size
Hi! I just tried with 3.23.48 on Linux-2.4.16-SMP-64GB (ext2 I think), and a 5000 MB data file was created. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Gurupartap Davis [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Saturday, February 09, 2002 2:32 AM Subject: Re: InnoDB File Size Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: InnoDB File Size
Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: InnoDB File Size
Hi! From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: InnoDB File Size
Hi! I just tried with 3.23.48 on Linux-2.4.16-SMP-64GB (ext2 I think), and a 5000 MB data file was created. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com -Original Message- From: Gurupartap Davis [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Saturday, February 09, 2002 2:32 AM Subject: Re: InnoDB File Size Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: InnoDB File Size
Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: InnoDB File Size
On Friday 08 February 2002 16:32, Gurupartap Davis wrote: Yes, I saw that before...My filesize was limited to 2GB before, but now with a 2.4 kernel and reiserfs I _should_ be allowed to create a 20GB data file now, right? So something's wrong... as I said before, I have successfully created a 9GB file on this machine before, so the filesystem isn't to blame for a 4GB limitation... If you are using one of the default installations of ReiserFS from SuSE, it will be in the old (3.5) format. This will limit the size of files to 2GB even if you are using a new kernel and Glibc. If you remounted with -o conv, you will be able to create _new_ files that exceed 2GB, but I think that any existing file will be limited to 2GB. One way to test this would be to copy (not rename!) the table files to a new name, remove the old ones and rename the new ones back to the old names. Of course, this will work better if you have MySQL down at the time :-) It is also possible that the table size has been set when you created the tables. Look up MAX_ROWS (MAXROWS?) in the online manual. Best, Kyle -- Quicknet's MicroTelco fax and voice service has just added another carrier giving MicroTelco users more low rates to choose from. This new carrier is Altair Telecom - a low cost, worldwide voice telephony carrier that is available by up-grading at no additional cost to Internet SwitchBoard v6.0 or MicroTelco Gateway v2.5. http://www.quicknet.net/download/ Cut costs, Fax smart. Use iPrint2Fax worldwide and save! == FREE software download available at www.iPrint2Fax.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: InnoDB File Size
Hi! From http://www.innodb.com/ibman.html : MySQL/InnoDB-3.23.44, November 2, 2001 You can define foreign key constraints on InnoDB tables. An example: FOREIGN KEY (col1) REFERENCES table2(col2). You can create 4 GB data files in those file systems that allow it. Thus 4 G files should be available also on 4.0.1. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com Gurupartap Davis wrote in message ... I'm using MySQL 4.0, trying to set up some innodb tables with several 20GB data files, but mysql is telling me that the files must be = 4096MB This is on a RedHat 7.2 box with redhat's 2.4.9-13 enterprise kernel on a reiserfs filesystem... I don't think there should be such a low limit...I accidentally made a 9GB log file the other day. Is this a limit of innodb? Is there somewhere I can adjust this limit? Thanks -Partap Davis Syncrasy, LLC - 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: innodb file size usage
Hi, how much of the 600MB actually is used at the moment I'm sure there'a a better way to do it, but this one works: mysql show table status like 'your_innodb_table' \G ... Comment: InnoDB free: 3739648 kB Best Regards, Sasa - 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
