RE: General database questions
* Emmanuel van der Meulen Roger, I attempted adding additional space with a new file under innodb_data_file_path as follows; Before: innodb_data_file_path = ibdata1:50M;ibdata2:50M After : innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M Stopped and restarted MySQL, but this did nothing. Could you please point me! I have no idea, try URL: http://www.mysql.com/doc/I/n/InnoDB.html Maybe someone on the list who have actually used the inndb tables could give you a hint...? Knowing almost nothing about MySQL InnoDB, I looked at the .frm file, thinking they are the data files, and they have a file size of 9kb. :) Mystery solved. This is also my understanding. It scares me. Thus when using MySQL with InnoDB, all data of all databases on my different website stages, viz., PROD, QA, DEV would share the same InnoDB dataspace for data. yes, but this is not so bad, is it...? Thus PROD data is at risk. why? And furthermore, data cannot be backed up separately. Yes, it can, the mysqldump utility will address the _database_, but you can not do backups of individual databases by simply copying the files on the OS level, like you can with myisam tables. I feel this is an oversight of MySQL InnoDB. And let me add further, my concern is that when I eventually go live, with a HSP, that my data would thus be shared further with other websites hosted on the same server. It is not a very big difference between one 'tablespace' (or multiple, possibly spanning multiple disks) sharing multiple databases, and one (or more) disks with separate databases in separate files. The database partition can be seen as a kind of filesystem... Surely I'm overlooking something. Could this please be logged as a major issue? It is an issue, but I don't think it is a big issue. InnoDB sharing the same dataspace, and therefore different unrelated databases's data being at risk. What exactly do you see as a risk? -- 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
RE: General database questions
Hello Roger, [snip] Roger, I attempted adding additional space with a new file under innodb_data_file_path as follows; Before: innodb_data_file_path = ibdata1:50M;ibdata2:50M After : innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M Stopped and restarted MySQL, but this did nothing. Could you please point me! I have no idea, try URL: http://www.mysql.com/doc/I/n/InnoDB.html Maybe someone on the list who have actually used the inndb tables could give you a hint...? I got this to work, I suspect a typo resulted that it did not work the first time. It was a matter of adding the extra ibdata file (as above), stop/start, and viola, space was added. [snip-1] This is also my understanding. It scares me. Thus when using MySQL with InnoDB, all data of all databases on my different website stages, viz., PROD, QA, DEV would share the same InnoDB dataspace for data. yes, but this is not so bad, is it...? [snip-2] Thus PROD data is at risk. why? [snip-3] And furthermore, data cannot be backed up separately. Yes, it can, the mysqldump utility will address the _database_, but you can not do backups of individual databases by simply copying the files on the OS level, like you can with myisam tables. [snip-4] What exactly do you see as a risk? Well, i.r.o. the matter of risk, if different websites's data is in one set of files, and especially with DEV, where programs which still have bugs or with finger trouble, could damage the data in general for PROD system; also they would interfere with PROD throughput etc. I have an extremely good backup program which I would stick to; now it'll be fine with different databases each at a separate location. At some point, I'll also look at mysqldump, thank you. NB. In the meantime, I also posted this issue to InnoDB's Heikki Tuuri, who confirmed my concerns - but only if used in one MySQL server instance; he advised the actual way to handle this, is to run separate MySQL server instances. I'm busy setting this up. With different instances, each instance could have its dedicated datadir etc. I use Win2k Pro; struggling with multiple instances; MySQL does not see the /mysql/data/my.cnf - for some reason it only sees c:/winnt/my.ini and c:/my.cnf. I started another thread, 'Multiples instances of MySQL' asking for assistance. BTW, Heikki's response; [snip] It is better to run a different instance altogether of mysqld for production and development systems. Development will inevitably cause disturbance to a production system. The same with co-hosting: high load on one database will make others freeze. [snip-1] It is not a very big difference between one 'tablespace' (or multiple, possibly spanning multiple disks) sharing multiple databases, and one (or more) disks with separate databases in separate files. The database partition can be seen as a kind of filesystem... [snip-2] It is an issue, but I don't think it is a big issue. Sorted with finesse with multiple instances. Anyway, Roger thank you for all your assistance. And BTW, the more I work with MySQL, the more I'm enjoying it. Kind regards Emmanuel - 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: General database questions
* Emmanuel van der Meulen I got this to work, I suspect a typo resulted that it did not :) Well, i.r.o. the matter of risk, if different websites's data is in one set of files, and especially with DEV, where programs which still have bugs or with finger trouble, could damage the data in general for PROD system; also they would interfere with PROD throughput etc. Yes, but is there really a big difference as long as the data is on the same disk computer? I would keep PROD on a separate physical machine... If the databases are in the same physical files or not will only matter if the inndb format itself is insecure, as far as I can see... and if you actually could destroy one table by doing some illegal operation to another table with innodb, I would simply not use it in PROD... :) Anyway, Roger thank you for all your assistance. And BTW, the more I work with MySQL, the more I'm enjoying it. HTH. :) -- Roger, also enjoying mysql - 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: General database questions
Hello Heikki, Thank you very much for this note and your valued assistance. I have the different instances of MySQL running with your proposal to add the lot to a bat file. And yes, if such a option as you propose exists, others would possibly get multiple instances / my.cnf files working more easily. BTW, I wasn't aware that I could place the InnoDB options as options on the command line. They are not mentioned as command line options in chapter 4.1.1. So it was extremely helpful that you gave me that pointer. Either way thank you again for an excellent feature and also for your clear assistance. Kind regards Emmanuel - 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: General database questions
Hello Roger, [snip] Yes, but is there really a big difference as long as the data is on the same disk computer? I would keep PROD on a separate physical machine... Yes, either on a separate physical machine or a different HDD (to curtail costs, I'd first go for HDD). If the databases are in the same physical files or not will only matter if the inndb format itself is insecure, as far as I can see... and if you actually could destroy one table by doing some illegal operation to another table with innodb, I would simply not use it in PROD... :) I'm certain InnoDB is 100% safe. My concern is probably related to being unfamiliar with MySQL/InnoDB. However, I take Heikki's point of the access impact of DEV on PROD - never thought of that. BTW, Heikki also assisted me to get multiple instances of MySQL going. So all is exactly as per my requirements. Until another time. Kind regards Emmanuel - 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: General database questions
* Emmanuel van der Meulen I'm certain InnoDB is 100% safe. My concern is probably related to being unfamiliar with MySQL/InnoDB. However, I take Heikki's point of the access impact of DEV on PROD - never thought of that. This impact also very much applies to the cpu... I would highly recomend spending a few $ on a DEV server... doesn't need to be expensive, any old box capable of running linux and mysql will do. BTW, Heikki also assisted me to get multiple instances of MySQL going. So all is exactly as per my requirements. Great! :) -- 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
RE: General database questions
Hello Roger; [snip] * Emmanuel van der Meulen I'm certain InnoDB is 100% safe. My concern is probably related to being unfamiliar with MySQL/InnoDB. However, I take Heikki's point of the access impact of DEV on PROD - never thought of that. This impact also very much applies to the cpu... I would highly recomend spending a few $ on a DEV server... doesn't need to be expensive, any old box capable of running linux and mysql will do. Point taken, also got me thinking, thank you. Kind regards Emmanuel - 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: General database questions
* Emmanuel van der Meulen 1. Assuming the website grows very large with high volumes and a database exceeds disk space, please advise what is the remedy? Add HW or remove data...? Not sure if I understand your question... 2. Seeing as for the said website I use transactions, thus also the innodb options, please advise whether I can place individual databases at different locations. Yes, you can. Note; it seems the filename.sym, does not apply to the innodb files, therefore only the database table definitions are placed in the filename.sym redirecting location. I don't understand this, sorry. 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). 4. Please advise, with different databases do they all share one set of innodb files - No, each database is stored in separate file folders. thus if this is the case, how is data for different databases backed up separately? Not a problem. -- 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
RE: General database questions
Hello Roger, Thank you for your note and replies. Please see my further points inline; On 29 December 2001 16:21, Roger Baklund wrote; * Emmanuel van der Meulen 1. Assuming the website grows very large with high volumes and a database exceeds disk space, please advise what is the remedy? Add HW or remove data...? Not sure if I understand your question... 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. 2. Seeing as for the said website I use transactions, thus also the innodb options, please advise whether I can place individual databases at different locations. Yes, you can. Thank you, please see next point. Note; it seems the filename.sym, does not apply to the innodb files, therefore only the database table definitions are placed in the filename.sym redirecting location. I don't understand this, sorry. 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. This lead me to assume that when using the innodb option (for transactions), that the data is loaded in the ibdata files. If this is the case then how do I place the ibdata files (related to the innodb option) at different locations for different databases? 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. 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. 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), or is the data stored in the ibdata files for which I cannot see a way to declare them individually per database. 4. Please advise, with different databases do they all share one set of innodb files - No, each database is stored in separate file folders. 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)? 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. Thank you again for your assistance. Kind regards Emmanuel - 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: General database questions
* 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
RE: General database questions
Hello Roger, Thank you for your note and feedback. With your assistance and reading up on InnoDB, I'm getting closer. Further inline; On 29 December 2001 22:21, Roger Baklund wrote; [snip-1] 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.) [snip-2] I should also tell you that I have never used the Innodb table handler. [snip-3] 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 attempted adding additional space with a new file under innodb_data_file_path as follows; Before: innodb_data_file_path = ibdata1:50M;ibdata2:50M After : innodb_data_file_path = ibdata1:50M;ibdata2:50M;ibdata3:50M Stopped and restarted MySQL, but this did nothing. Could you please point me! Roger, I managed to place different databases each at altogether 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? Knowing almost nothing about MySQL InnoDB, I looked at the .frm file, thinking they are the data files, and they have a file size of 9kb. 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. Thank you for clarifying. 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. This is also my understanding. It scares me. Thus when using MySQL with InnoDB, all data of all databases on my different website stages, viz., PROD, QA, DEV would share the same InnoDB dataspace for data. Thus PROD data is at risk. And furthermore, data cannot be backed up separately. I feel this is an oversight of MySQL InnoDB. And let me add further, my concern is that when I eventually go live, with a HSP, that my data would thus be shared further with other websites hosted on the same server. Surely I'm overlooking something. Could this please be logged as a major issue? [snip] That is correct, I have never used Innodb, and I don't use transactions with mysql. This being the case, a further thank you for assisting. [snip] 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. Yes I was. Thank you for clarifying. [snip] 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.) Again thank you for confirming and clarifying. Roger, thank you for all your assistance so far. I'm clearer on several things. Could you possibly assist me in taking the issue further; that with InnoDB sharing the same dataspace, and therefore different unrelated databases's data being at risk. Kind regards - 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
