You're right, it wont decrease the physical size, but only free up the space within file to optimize the tablespace, in case, you want to check the size of this data file, you can remove autoextend from:
innodb_data_file_path = ibdata1:10M:autoextend and specify the size limit in the place of 10M, but i guess, if you specify the size to less than 790M (which is the current size of your datafile), to say 500M and the space is free in that file, it will resize it. That's the behaviour of Oracle datafiles (believe me, i'm oracle certified!). try it, and let me know as i dont have my database on innodb yet. 'Tablespace is part of your database. database consists of at least one tablespace. it's basically used to restrict users from seeing other user's data. like, you can assign a tablespace to a user and none else (ofcourse other than root) can see the data. For more info, have a look at: http://www.mysql.com/doc/en/InnoDB_File_space.html Enjoy Nitin ----- Original Message ----- From: "Leo Huang" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, November 04, 2003 8:00 PM Subject: Re: InnoDB Questions > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Thank you very much for your reply, Nitin. > > I did read the Adding_and_removing in the manual, but it says > "Currently you cannot remove a datafile from InnoDB. To decrease the > size of your database you have to use `mysqldump' to dump all your > tables, create a new database, and import your tables to the new > database." > It does reduce the size of the database(similar to optimize for > MyISAM), but it doesn't reduce the size of the file. > > Could you explain a bit what is a "tablespace"? > > Thanks, > Leo > > /etc/my.cnf: > [mysqld] > datadir=/var/lib/mysql > set-variable=max_connections=300 > innodb_data_file_path = ibdata1:10M:autoextend > default-table-type=InnoDB > set-variable = innodb_buffer_pool_size=512M > set-variable = innodb_additional_mem_pool_size=10M > set-variable = innodb_log_file_size=10M > set-variable = innodb_log_files_in_group=3 > set-variable = innodb_log_buffer_size=8M > innodb_flush_log_at_trx_commit=1 > > > [mysql.server] > user=mysql > basedir=/usr > > [safe_mysqld] > err-log=/var/log/mysqld.log > pid-file=/var/run/mysqld/mysqld.pid > > > > - ----- Original Message ----- > From: "Nitin" <[EMAIL PROTECTED]> > To: "Leo Huang" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Wednesday, November 05, 2003 1:01 AM > Subject: Re: InnoDB Questions > > > > Hello, > > > > first things first, you cann't resize your datafiles without > shutting down > > your database. if it's ok with you, have a look at > > http://www.mysql.com/doc/en/Adding_and_removing.html > > > > you may want to have a look at you my.cnf file, stored in mysql data > dir or > > in /etc dir, for the default options specified there fo the datafile > with: > > > > innodb_data_file_path > > > > Yes, you can add data file, just add another entry to above option. > option > > entry is self-explainatory. > > > > At last, ibdata1, ibdata2.... are actual data files used to store > actual > > data. one or more of these files are attached to one tablespace and > one file > > cant span across tablespaces. > > > > ib_logfile0, ib_logfile1 are log files, which are used to log sql > statements > > applied to database. these files are used to restore data in case of > any > > crash or mishap. > > > > for further info, have a look at: > > http://www.mysql.com/doc/en/InnoDB_start.html > > > > Enjoy > > Nitin > > > > > > ----- Original Message ----- > > From: "Leo Huang" <[EMAIL PROTECTED]> > > To: <[EMAIL PROTECTED]> > > Sent: Tuesday, November 04, 2003 6:28 PM > > Subject: InnoDB Questions > > > > > > > Hello, > > > > > > I have a few questions about InnoDB. > > > I am new to InnoDB, and just converted my MyISAM tables into > InnoDB a > > > few days ago. > > > I notice that it generates these files > > > > > > -rw-rw---- 1 mysql mysql 2560 Nov 2 13:07 > > > ib_arch_log_0000000000 > > > -rw-rw---- 1 mysql mysql 790626304 Nov 4 20:44 ibdata1 > > > -rw-rw---- 1 mysql mysql 10485760 Nov 4 20:44 > ib_logfile0 > > > -rw-rw---- 1 mysql mysql 10485760 Nov 4 20:44 > ib_logfile1 > > > -rw-rw---- 1 mysql mysql 10485760 Nov 3 00:02 > ib_logfile2 > > > > > > But how can those files work together? > > > > > > In InnoDB documentation, it suggests to add another file ibdata2 > to > > > get higher performance. Can I do that now, after I have created > the > > > ibdata1 and used it for a while? > > > > > > The most important thing is I deleted a 300M database, but the > ibdata1 > > > remains the same size. MyPHPAdmin says 330,000KB free. How can I > make > > > the data file smaller? > > > > > > I will be really appreciated if someone can briefly describe > what's > > > happening to those files or point me to some articles. > > > > > > Thanks a lot, > > > Leo > > > > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > -----BEGIN PGP SIGNATURE----- > Version: PGP 8.0.2 > > iQA/AwUBP6e4GMJH0J7PNsMYEQIXIQCdGgQEyxFfJ3Vk8wZBNIz7FT7ilF8AoIDN > h21IQZ8ozOUeELhvWSpznyTI > =H/2E > -----END PGP SIGNATURE----- > > > -- > 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]
