Re: [MySQL] innodb_file_per_table / apple workers and logic
Even more stuff inline there Actually, the gas tank is a good analogy. There is limited volume in a vehicle which must contain the tank. In this analogy, the vehicle must have space for not just fuel but passengers, cargo, engine, transmission, etc. The fact that the tank may grow so large it displaces other items from the vehicle is appropriate to the original situation (no room left on disk). Right, thanks for explaining that. I was about to say that it would be pretty cool if a gas tank would shrink as you travel. That would open an immense amount of opportunities, like picking up an extra passenger when you are about to run out of gas... something like that. I am sorry if you didn't see the larger picture she was trying to present. Mmm, he, the last that I checked. But yes. There was a larger picture, thanks to you obviated. There are expenses to maintaining separate files per table that you do not have for the larger, more inclusive tablespaces. Individual tablespaces can become so numerous that your system may run out of file handles to operate them all, for example. All of those file names may clog your directory/folder system making it much slower to randomly access any one file, as another example. While it is true that recovering unused space may be useful to restore disk space, it is also true that allocating and deallocating disk space is an expensive action. It is much more efficient in the long run to leave an expanded file in its larger state than it would be to constantly be shrinking it to a minimal size. Also, since the user required that much space at least once before (by their query or usage patterns) it is reasonable to assume that the same working space (or more) will be used again in the future. So this puts the 'design decision' squarely on the side of 'always increase, never decrease' in order to minimize the disk allocation costs associated with operating the InnoDB storage engine. There are other storage options (MyISAM, Archive, CSV, etc) in the event this behavior of InnoDB is more than you want to deal with. The default to NOT use individual tablespaces is related to the need to potentially adjust OS-level limits to handle the additional file volumes. If that is not a problem for you and your admins, more power to you. For desktop users, however, that may not be an option they can use. So the default remains at 0 until the support for it becomes much more common among Linux user accounts. I understand the ideas like reusing space and file handles issues. I can explain what I find convenient to use. I would generally start using a database with unknown amount of tables and unknown space consumption without InnoDB tablespace, specifically for the reasons like reserving and optimizing space and file handles.I do not want to end up with 1 files of 100 bytes each instead of one of 100M. I would then come up with an expected size of the database and preallocate space for it with using InnoDB files of limited size, f.e. 8G. I do not like it that the files grow to arbitrary sizes, like 100G, because it then becomes difficult to manage (f.e. back them up or copy somewhere). For this reason, I try not to let files autoextend beyond a certain limit. In such cases that it does cross the limit of say 8G, I cannot add another file because the database would need to be down for that. That's why I would prefer having an online datafile add option. As for the InnoDB tablespaces option, the only use I can think about when using it is when some massive changes are being done to the tables. When that happens, I would temporarily turn the tablespaces on, perform whatever I need to perform, and then turn them off. The benefit that I might get out of this is the ability to get rid of space for the temporary tables should I have had so many of them that I would not be ever able to reuse the space within regular InnoDB files. The declared ability of the InnoDB tablespaces, to import tables and move them around between databases, or the backup/recovery using the feature, I find hard to use because of table consistency reasons. By default, I have to assume that a table should be consistent with other tables, so unless I have a way to know that this table would be in sync with others (or the application does not care), I won't be able to import the tables. Overall, I see that MySQL tried to make individual files units of recovery, but it would be more useful if such units would be not separate tables, but datafiles in the common space. When saying that, I mostly convey the experience of using MySQL in the corporate space. I realize that MySQL has other use cases and of course I am not saying that it was designed incorrectly or unwisely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: [MySQL] innodb_file_per_table / apple workers and logic
Allright, that will do, I think? This is a MySQL mailinglist, let's not have it devolve into vendor rants. The defaults may or may not be sensible, but they're documented, and there's as much to say for sensible defaults as there is for not changing defaults between releases. Let's leave it at that, please. - Original Message - From: Reindl Harald h.rei...@thelounge.net To: mysql@lists.mysql.com Sent: Wednesday, 30 November, 2011 9:16:51 AM Subject: [MySQL] innodb_file_per_table / apple workers and logic the defaults should not needed to bex explained they should be expected WHO THE FUCK is comparing computers with a gas tank? sorry but this is bullshit and can only come from @apple.com workers which is the comapny who sold over years much to expensive X-Serve with f**ng Hitatchi Deskstar disks and it seems you have never seen a real computer with server-hardware nor had to pay one as we migrated to dbmail our mail-volume was the double size as now two years later because there was one customer exceeding all limits and on the old machines where no quotas and we decided to get rid of him because this and other reasons and better do not start a braindead discussion why there was no quotas because the old machine was a apple x-serve with a f**ing eudora mail server and was migrated by me to a real server and it was not my decision use your companies crap much too long fact is that because using files_per_table i could reduce the datasize to the half and on the slave is running a weekly backup and one last-week accessable via imap for imapsync so we have only here FOR copies of the data additionally a daily offsite-backup is running oh and not to forget: VMware-Data-Recovery and last but not list two external 4 TB disks (full encrypted), one inhouse and one outside to cover cases where the SAN storage or whatever is damaged damned we are speaking about DEFAULTS making life hard or not so hard and apple is one of the companies which are resposible that more and more braindead zombies are runnin out there by telling the world that nobody should have to read any documentation - and now your arguments are it is a dead end - well, it's your companies philosophy -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [MySQL] innodb_file_per_table / apple workers and logic
Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. Forgot to say. Opinions said here are mine and aren't official position of my company. I have also not said anything about Apple products. I did not even imply using OSX as the OS for MySQL. I appreciate that the above could be given some understanding and, if not too much to ask, respect. I didn't have a pleasure to use dbmail. I presume it does something with mail users. Thinking logically, if I got rid of my biggest mail user, I might eventually get another user, even bigger one, which would consume the same space vacated by the deceased user. So why would I want to give up the space then? sorry but this is bullshit and can only come from @apple.com workers which is the comapny who sold over years much to expensive X-Serve with f**ng Hitatchi Deskstar disks and it seems you have never seen a real computer with server-hardware nor had to pay one as we migrated to dbmail our mail-volume was the double size as now two years later because there was one customer exceeding all limits and on the old machines where no quotas and we decided to get rid of him because this and other reasons and better do not start a braindead discussion why there was no quotas because the old machine was a apple x-serve with a f**ing eudora mail server and was migrated by me to a real server and it was not my decision use your companies crap much too long fact is that because using files_per_table i could reduce the datasize to the half and on the slave is running a weekly backup and one last-week accessable via imap for imapsync so we have only here FOR copies of the data additionally a daily offsite-backup is running oh and not to forget: VMware-Data-Recovery and last but not list two external 4 TB disks (full encrypted), one inhouse and one outside to cover cases where the SAN storage or whatever is damaged There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? If the people do not have the knowledge to do exports/imports, the brand new and cool file management feature will not help them either. damned we are speaking about DEFAULTS making life hard or not so hard Oh, we do? So we kinda consider it as given that MySQL has to wiggle out his way to suit our task, without us bothering about it. That would be so nice... Essentially, the instructions how to use that feature are written in the same book as the instructions how to do export/imports. If we consider it as given that people would never read, it is a dead end. and apple is one of the companies which are resposible that more and more braindead zombies are runnin out there by telling the world that nobody should have to read any documentation - and now your arguments are it is a dead end - well, it's your companies philosophy Such philosophy is not Apple's. It is common sense. However, it only applies to the products, all components of which are controlled by the vendor. For example, if you buy yourself a TV, lucky you, you want to be able to turn it on and off without reading the documentation. But the database server is NOT the product to which you want to apply such logic. It is part of other things that cannot be controlled by the vendor: for example OS below or application on top. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: [MySQL] innodb_file_per_table / apple workers and logic
Am 30.11.2011 19:13, schrieb Karen Abgarian: Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. what answer do you expect comparing a database with a gas tank while the gas tank is the hard-drive? if i take some gas out of the tank (hard-drive) i expect that there is space for new one There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. my frustration is people like you comparing a database with a gas tank while not understand that the gas tank is the underlying hard-disk if you stop make laughable comparison you will not get back frustration Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? have i said this? a default which makes it unable to free no longer used space is dumb not more and not less signature.asc Description: OpenPGP digital signature
Re: [MySQL] innodb_file_per_table / apple workers and logic
Hello all, On 11/30/2011 16:46, Reindl Harald wrote: Am 30.11.2011 19:13, schrieb Karen Abgarian: Hi inline there. On 30.11.2011, at 0:16, Reindl Harald wrote: Most people do not expect a gas tank to shrink once the gas is consumed...right? WHO THE FUCK is comparing computers with a gas tank? Well, I do. I even managed to do it without using foul language. what answer do you expect comparing a database with a gas tank while the gas tank is the hard-drive? if i take some gas out of the tank (hard-drive) i expect that there is space for new one Actually, the gas tank is a good analogy. There is limited volume in a vehicle which must contain the tank. In this analogy, the vehicle must have space for not just fuel but passengers, cargo, engine, transmission, etc. The fact that the tank may grow so large it displaces other items from the vehicle is appropriate to the original situation (no room left on disk). There are a lot of things in this life to be upset about. Empty gas tanks is one thing. But I would not spill all that frustration on the very first person I meet on the net. my frustration is people like you comparing a database with a gas tank while not understand that the gas tank is the underlying hard-disk if you stop make laughable comparison you will not get back frustration I am sorry if you didn't see the larger picture she was trying to present. Taking the logical part of what was said above, there existed a database that possibly was able to save the space by using files_per_table. Does this somehow mean that there are no other databases in the world? have i said this? a default which makes it unable to free no longer used space is dumb not more and not less There are expenses to maintaining separate files per table that you do not have for the larger, more inclusive tablespaces. Individual tablespaces can become so numerous that your system may run out of file handles to operate them all, for example. All of those file names may clog your directory/folder system making it much slower to randomly access any one file, as another example. While it is true that recovering unused space may be useful to restore disk space, it is also true that allocating and deallocating disk space is an expensive action. It is much more efficient in the long run to leave an expanded file in its larger state than it would be to constantly be shrinking it to a minimal size. Also, since the user required that much space at least once before (by their query or usage patterns) it is reasonable to assume that the same working space (or more) will be used again in the future. So this puts the 'design decision' squarely on the side of 'always increase, never decrease' in order to minimize the disk allocation costs associated with operating the InnoDB storage engine. There are other storage options (MyISAM, Archive, CSV, etc) in the event this behavior of InnoDB is more than you want to deal with. The default to NOT use individual tablespaces is related to the need to potentially adjust OS-level limits to handle the additional file volumes. If that is not a problem for you and your admins, more power to you. For desktop users, however, that may not be an option they can use. So the default remains at 0 until the support for it becomes much more common among Linux user accounts. Regards, -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
