* Emmanuel van der Meulen > Roger, assuming the database space as allocated is used up and I > do not want to remove the data, but I have lots more space to > allocate, is there a way to extend allocated space in flight? > Or, does MySQL automatically (and inflight) extend beyond > allocated space upto as much space which is physically > available; I'm referring to the space for the ibdata files.
I think you would need to restart the mysqld server daemon, but this is very fast, and can be done with virtually no downtime. (1-2 seconds is my experience, I suppose this depends on a number of factors, so you should test it on your system.) I should also tell you that I have never used the Innodb table handler. I was not aware of the different file organization compared to MyIsam tables, but I think the answer for your questions are here: <ULR: http://www.mysql.com/doc/I/n/InnoDB_start.html > This is an excerpt of an example my.cnf: [mysqld] # You can write your other MySQL server options here # ... # innodb_data_home_dir = / # Data files must be able to # hold your data and indexes innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M innodb_data_file_path is used to define database partitions or 'tablespace' and define a size for those partitions. This must be changed when your tables are growing beyond the predefined size, and I suppose a restart is needed, but I don't know for sure. > Roger, I managed to place different databases each at alltogether > different locations using the filename.sym option. However, no > matter how much data I loaded, the table sizes stay at 9kb. I don't understand this... how do you know what the size of the table is? As I said, I have never used Inndb, but the tables are stored within the ibdata files, are they not? So, how do you know how many kb? > This lead me to assume that when using the innodb option (for > transactions), that the data is loaded in the ibdata files. If Yes, all data, including indexes, are stored in the ibdata files. > this is the case then how do I place the ibdata files (related to > the innodb option) at different locations for different databases? By setting innodb_data_home_dir to the root path and use a relative path in the innodb_data_file_path parameter, as shown in the example above. This will however not give you different locations for different databases... see below. > > > 3. When using innodb options (for transactions), please advise > > > whether data is stored in the tables or the innodb files? > > > > ehhh... don't know if I understand this... the data is stored in > > tables (db teminology) and the tables (thus also the data) are > > stored in files (os terminology). > > Roger, it seems you are not familiar with the innodb option when using > transactions. That is correct, I have never used Innodb, and I don't use transactions with mysql. > Note from the MySQL PDF format manual, P. 58. "MySQL supports > transactions with the InnoDB and BDB Transactional table handlers. > See Chapter 7 [Table types], page 441". This does not clearify the table/file mixup. A 'table' does not exist in the OS environment, only files, wich of course may contain 'tables' when seen from the db environment... > Or let me rephrase the question; when using the innodb option, > ibdata files get used, and my question is; when using the > innodb option is the data stored in the table files in the database > folder (which do not seem to increase in size no matter how much > data I load), In that case, I would guess the data is not stored there. :) Maybe you are looking at the .frm files? This is the table definitions only. > or is the data stored in the ibdata files for which > I cannot see a way to declare them individually per database. ok, I think I understand now... :) Your _database_ is not innodb, your tables are: type=innodb is an option to the CREATE TABLE statement, not the CREATE DATABASE statement. As far as I can tell from the manual, you can not instruct mysql to keep one innodb table in one particular tablespace, in other words: you can _not_ put different databases on different locations, thus my answer to your questions #2 and #4 was wrong. Sorry! (again, I have never used innodb, there may be some way to this that I don't know about.) > Roger again I'm referring to the ibdata files, and if they are > stored in a different location, please advise how their location > is declared (say each database on a different HDD)? I believe you now have the answer for this, except it is not the _database_ you declare a location for when it comes to innodb tables, it's the tablespace. > > > thus if this is the case, how is data for > > > different databases backed up separately? > > > > Not a problem. > Please advise how I can declare the ibdata files to be at different > locations. See innodb_data_file_path in the example above. -- Roger --------------------------------------------------------------------- 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
