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
> 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
> 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.