List:General Discussion« Previous MessageNext Message »
From:Suresh Kuna Date:August 3 2010 4:31pm
Subject:Re: InnoDB Tablespace
View as plain text  
Hi Johnny,

Sorry about that - i just overlooked and the simple way to calculate the
sizes is to query the information_schema table called "tables" for data and
index sizes.

On Tue, Aug 3, 2010 at 8:55 PM, Johnny Withers <johnny@stripped> wrote:

> ----About the above - it is saying 6144 KB so it is 6.1 GB.
>
> Are you sure? I would think 6144KB = 6.144 MB, or 6144 * 1000 = 6,144,000
> bytes.
>
> I think since InnoDB, by default, extends the table space by 8MB
> increments, this is reporting the free space in this increment. How can I
> tell total remaining space so I can adjust and/or add new table space before
> it runs out of space next time?
>
> I have another server with a different config line, however, the last
> innodb file specified is also max 16G and when i run show table status on
> it, it reports 3983360 kB free, which i would assume is 3.9 GB? Could this
> be because it's filling up space in one of the files before the last
> auto-extending file, which these files are fixed sizes?
>
> Both servers are 5.0.77-log. One server is Cent OS, the other is RHEL.
>
> I'm very confused here.
>
> JW
>
> On Mon, Aug 2, 2010 at 1:53 PM, Suresh Kuna <sureshkumarilu@stripped>wrote:
>
>> Hey john,
>>
>> Yes you can add it but safe to keep auto-extend at the end and monitor the
>> disk space as well.
>>
>>
>> "Now when i run the same show table status command, the comment field
>> says:
>> InnoDB free: 6144 kB
>>
>> Is that telling me that I only have 6MB of storage left even though I
>> increased the table space by 8GB?"
>>
>> About the above - it is saying 6144 KB so it is 6.1 GB.
>>
>>
>>
>> On Tue, Aug 3, 2010 at 12:05 AM, Johnny Withers
> <johnny@stripped>wrote:
>>
>>> I recently ran out of table space on a production server that had the
>>> following configuration line:
>>>
>>>
>>>
> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:8G
>>>
>>> Before I changed this line and restarted the server, I ran SHOW TABLE
>>> STATUS
>>> LIKE 'table' on one of the databases and the comment filed said:
>>> InnoDB Free: 3NNN kB (I don't remember the exact number, but know it
>>> started
>>> with 3 and had 4 digits.
>>>
>>> I modified the configuration line above to:
>>>
>>>
>>>
> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:2G:autoextend:max:16G
>>>
>>> Now when i run the same show table status command, the comment field
>>> says:
>>> InnoDB free: 6144 kB
>>>
>>> Is that telling me that I only have 6MB of storage left even though I
>>> increased the table space by 8GB?
>>>
>>> Also, If I wanted to add another file to this file_path variable, can I
>>> just
>>> add it to the end like so:
>>>
>>>
>>>
> innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G;ibdata4:1G;ibdata5:1G;ibdata6:16G;
>>> *ibdata7:16G*
>>>
>>> Or will that cause MySQL to complain the file size isn't correct the next
>>> time it starts?
>>>
>>>
>>> Thanks for any help!
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> johnny@stripped
>>>
>>
>>
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>



-- 
Thanks
Suresh Kuna
MySQL DBA

Thread
InnoDB TablespaceJohnny Withers2 Aug
  • Re: InnoDB TablespaceSuresh Kuna2 Aug
    • Re: InnoDB TablespaceJohnny Withers3 Aug
      • Re: InnoDB TablespaceSuresh Kuna3 Aug
  • Re: InnoDB TablespaceJohan De Meersman5 Aug