List:General Discussion« Previous MessageNext Message »
From:Shawn Green (MySQL) Date:November 30 2011 11:01pm
Subject:Re: [MySQL] innodb_file_per_table / apple workers and logic
View as plain text  
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


Thread
help needed restoring crashed mysqlLuis Pugoy29 Nov
  • Re: help needed restoring crashed mysqlReindl Harald29 Nov
    • Re: help needed restoring crashed mysqlKaren Abgarian29 Nov
      • Re: help needed restoring crashed mysqlClaudio Nanni29 Nov
      • Re: help needed restoring crashed mysqlReindl Harald29 Nov
        • Re: help needed restoring crashed mysqlKaren Abgarian30 Nov
          • Re: help needed restoring crashed mysqlReindl Harald30 Nov
            • Re: help needed restoring crashed mysqlKaren Abgarian30 Nov
              • [MySQL] innodb_file_per_table / apple workers and logicReindl Harald30 Nov
                • Re: [MySQL] innodb_file_per_table / apple workers and logicJohan De Meersman30 Nov
                • Re: [MySQL] innodb_file_per_table / apple workers and logicKaren Abgarian30 Nov
                  • Re: [MySQL] innodb_file_per_table / apple workers and logicReindl Harald30 Nov
                    • Re: [MySQL] innodb_file_per_table / apple workers and logicMySQL)1 Dec
                      • Re: [MySQL] innodb_file_per_table / apple workers and logicKaren Abgarian1 Dec
              • Re: help needed restoring crashed mysqlReindl Harald30 Nov
        • Re: help needed restoring crashed mysqlhsv30 Nov
          • Re: help needed restoring crashed mysqlReindl Harald30 Nov
Re: help needed restoring crashed mysqlKaren Abgarian30 Nov