Re: INNODB Question
Tripp, ibdata files never shrink. You can try this to free up space: * Take a mysqldump of all tables that you may need, * delete the ibdata files, * Rebuild your tables by importing the dump. Using innodb_file_per_table, will freed the disk space whenever you run optimize table or delete the table. Performance wise, using individual tablespaces may help in I/O load balancing, if tablespaces are spread over disks. --Ravi Tripp Bishop wrote: Howdy all, I'm trying to clean up a development server so that we can retask it. I tried dropping a very large but no longer necessary database that uses INNODB tables exclusively. Unfortunately the disk usage on the box didn't change. We're using a completely default MySQL 5.0.15 install on the box (a small centos linux box) so we don't have innodb_file_per_table set in a my.cnf file. So I've got two basic questions... First, how can I get rid of the current ibdata file? It's taking up a lot of space. Do any of the mysql databases (information schema and mysql) use INNODB tables? What's the correct procedure for freeing up this disk space? Second, are there still issues with innodb_file_per_table and indexes or has that been eradicated? It seems like it was a problem early on in the mysql 4.1 life cycle. I just wanna be sure. Also, Does innodb_file_per_table enhance performance (even if the tablespaces are all on the same physical disk)? Just curious. Cheers, Tripp __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb question
At 3:25 PM -0700 9/17/03, Hsiu-Hui Tseng wrote: After specify the variablies for innodb in my.cnf file, I started mysql server. Why I did not see the creating output? But I can see it in err file. If there is an error file, that's where you'll see the output. If you mean you want to see it on the console, then you should start the server in such a way that diagnostic output is sent there rather than to the error log. On Windows, start it like this from the command line: mysqld --console On Unix, start the server like this from the command line *without* using mysqld_safe or mysql.server: mysqld thanks Hsiu-Hi -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB question(s)
Hi Edward, Thank you for the reply. I really appreciate the response,but I was thinking into a different direction. I was hoping that perhaps additionally to the normal backup procedure that there is a shortcut or a trick which would quickly allow you to fix that specific issue (f.e. recover the orginal dbname). Best regards Nils Valentin Tokyo/Japan 2003 6 10 14:55Becoming Digital : http://www.innodb.com/ibman.html#Backing_up See the section on Forcing Recovery. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 10 June, 2003 01:40 Subject: InnoDB question(s) O.K MySQL List This one is on me, a bit funny, a bit strange but with a serious background. You are allowed to laugh ;-). Assuming that somebody has the clever idea to rename the folder for a InnoDB database f.e. on the command line base (mv command for Linux). Now the mysqld server startsup and of course he will not be able to access any table , data etc. he will get something like below: mysql use sam_member_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'discountplan' Didn't find any fields in table 'members' Didn't find any fields in table 'rank' Didn't find any fields in table 'relations' Database changed Then he would try to access one of the tables f.e. like this: mysql describe members - ; ERROR 1016: Can't open file: 'members.InnoDB'. (errno: 1) 1) Is it possible to reconstruct which name the database originally had before the folder was renamed on the command line ? 2) Is it possible to access or release (delete) the captured data related to the not anymore useable database ? Of course I could step through the log files trying to find the correct CREATE DATABASE dbname statement, but having seen the innodb log file sizes I would appreciate another solution ;-) PS: Didn't happen with serious data, just when fooling around with mysql. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB question(s)
http://www.innodb.com/ibman.html#Backing_up See the section on Forcing Recovery. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 10 June, 2003 01:40 Subject: InnoDB question(s) O.K MySQL List This one is on me, a bit funny, a bit strange but with a serious background. You are allowed to laugh ;-). Assuming that somebody has the clever idea to rename the folder for a InnoDB database f.e. on the command line base (mv command for Linux). Now the mysqld server startsup and of course he will not be able to access any table , data etc. he will get something like below: mysql use sam_member_db Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'discountplan' Didn't find any fields in table 'members' Didn't find any fields in table 'rank' Didn't find any fields in table 'relations' Database changed Then he would try to access one of the tables f.e. like this: mysql describe members - ; ERROR 1016: Can't open file: 'members.InnoDB'. (errno: 1) 1) Is it possible to reconstruct which name the database originally had before the folder was renamed on the command line ? 2) Is it possible to access or release (delete) the captured data related to the not anymore useable database ? Of course I could step through the log files trying to find the correct CREATE DATABASE dbname statement, but having seen the innodb log file sizes I would appreciate another solution ;-) PS: Didn't happen with serious data, just when fooling around with mysql. -- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB question
Hello. On Fri 2002-12-06 at 11:46:16 -0500, [EMAIL PROTECTED] wrote: From what I've been reading in the MySQL documentation, 3.23.43b InnoDB features foreign key constraints, which is great! But from the MySQL 4.1 wishlist items found here (scroll to the bottom): http://www.mysql.com/products/mysql-4.0/index.html They mention hopes to support foreign key integrity rules. So maybe I'm showing my ignorance here, but I have to ask, what is the difference between foreign key constraints and foreign key integrity rules? Regarding http://www.mysql.com/doc/en/TODO_MySQL_4.1.html, I think they mean support of foreign key constraints for MyISAM tables. HTH, Benjamin. -- [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: InnoDB Question
This implies that I have to preguess how large each data file will be. Correct. However, all InnoDB tables will share this space automatically. (Corrolary: A single table will automatically span several InnoDB data files if need be.) Now, I understand with MyISAM tables that they just grow until they fill up your filesystem or hit some OS limit such as the 2Gb limit in Linux 2.4.X. Also correct. InnoDB has a notion of a tablespace, which is a pre-allocated chunk of space on disk set aside for InnoDB tables. Among other benefits, this means you don't have to worry about filesystem fragmentation hurting the performance of your DB access. 1) If I setup InnoDB with a 500Mb data file then suddenly run out of space could I: * seemlessly just stack another data file by adjusting my startup options Yes. You can keep adding files as often as needed. Before we had our current DB server I had to do this twice -- I first allocated 2GB to InnoDB, and we started to run low, so I allocated another 2GB, and when we started to run low again, I tossed in another 2GB (at which point we had used most of the storage capacity of the machine)... This does however require manually editing the my.conf and restarting MySQL. * seemlessly just make the data file larger by changing the file size (i.e. don't add another datafile, just increase the file size) Qualified no. You cannot change the size of an InnoDB data file after it has been created. However, recent versions of InnoDB/MySQL include an AutoExtend option (I think that's what it's called). One InnoDB file may be flagged as AutoExtend, and this file *will* automatically grow as needed (in 8MB chunks IIRC). I would generally advise against using this option however, and simply preallocate sufficient space. If you are dedicating a machine to running only MySQL and you are using only InnoDB tables then you should be able to pretty much use all the available disk space for your InnoDB tablespace. As an example, our InnoDB tablespace on our (new) main DB server is 32GB, spread across 2 separate hard drives. A 3rd hard drive contains the OS, MySQL itself, the transaction journals/logs, and what few MyISAM tables we have. -JF - 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 Question
David, Wednesday, September 04, 2002, 9:34:55 AM, you wrote: From the online manual I see: DL -- DL innodb_data_file_path DL Paths to individual data files and their sizes. The full directory path DL to each data file is acquired by concatenating innodb_data_home_dir to DL the paths specified here. The file sizes are specified in megabytes, DL hence the 'M' after the size specification above. Do not set a file size DL bigger than 4000M, and on most operating systems not bigger than 2000M. DL InnoDB also understands the abbreviation 'G', 1G meaning 1024M. The sum DL of the sizes of the files must be at least 10 MB. DL -- DL This implies that I have to preguess how large each data file will be. DL Now, I understand with MyISAM tables that they just grow until they DL fill up your filesystem or hit some OS limit such as the 2Gb limit in DL Linux 2.4.X. DL A few questions: DL 1) If I setup InnoDB with a 500Mb data file then suddenly run out of DL space could I: DL * seemlessly just stack another data file by adjusting my startup DL options Yes. DL * seemlessly just make the data file larger by changing the file size DL(i.e. don't add another datafile, just increase the file size) No. DL 2) Is there a way to make this more dynamic? DL * what if a user wants to add a 10Mb blob into my database, but the DL datafile has only 5Mb of space left for data... DL(with MyISAM tables they just seem to grow as needed) Since 3.23.50/4.0.2 you can specify datafile as auto-extending: http://www.mysql.com/doc/en/InnoDB_start.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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 question
Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:26 PM To: Demirchyan Oganes-AOD098 Subject: Re: InnoDB question Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [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: InnoDB question
Oganes, It sounds like what you want is row-level-locking. This is a feature of InnoDB tables. It allows users to write to a table while other users are reading from the same table. Of course, they cannot read and write the same rows simultaniously. Have you tried to do this and had some problem? If so you need to be much more specific about the problem you're encountering. So, to answer your question: You can already do this. Eric Mayers Software Engineer I Captus Networks -Original Message- From: Demirchyan Oganes-AOD098 [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:34 PM To: '[EMAIL PROTECTED]' Subject: RE: InnoDB question Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [EMAIL PROTECTED] -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, February 25, 2002 2:26 PM To: Demirchyan Oganes-AOD098 Subject: Re: InnoDB question Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Hello everyone, I have this database, I'm using InnoDB type tables. I wanted to know the following: How can I manipulate the tables, so that db supports multiple users trying to access the same table for writing or reading purposes at the same time. Thanks, Oganes Demirchyan Motorola Life Science 757 S.Raymond Pasadena, CA 91105 Tel: 626-584-5900 email: [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 - 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 question
On Mon, Feb 25, 2002 at 02:44:45PM -0800, Eric Mayers wrote: Oganes, It sounds like what you want is row-level-locking. This is a feature of InnoDB tables. It allows users to write to a table while other users are reading from the same table. Of course, they cannot read and write the same rows simultaniously. That's not entirely true. Readers don't generally don't wait on writers (and vice-versa). 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.47-max: up 18 days, processed 573,142,672 queries (360/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
