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 10000 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: http://lists.mysql.com/mysql
