InnoDB Tablespace

--001636499165303cbf048cdb7671
Content-Type: text/plain; charset=ISO-8859-1

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;ibdat a4: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;ibdat a4: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;ibdat a4: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@pixelated.net

--001636499165303cbf048cdb7671--

Re: InnoDB Tablespace

--0016364d242fc1a2dd048cdbb570
Content-Type: text/plain; charset=ISO-8859-1

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 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;ibdat a4: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;ibdat a4: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;ibdat a4: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@pixelated.net
>



--
Thanks
Suresh Kuna
MySQL DBA

--0016364d242fc1a2dd048cdbb570--

Re: InnoDB Tablespace

--0016e65a0d46f99ab9048ceced95
Content-Type: text/plain; charset=ISO-8859-1

----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 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 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;ibdat a4: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;ibdat a4: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;ibdat a4: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@pixelated.net
>>
>
>
>
> --
> Thanks
> Suresh Kuna
> MySQL DBA
>



--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e65a0d46f99ab9048ceced95--

Re: InnoDB Tablespace

--000e0ce0d87a1ce1fb048ceddbec
Content-Type: text/plain; charset=ISO-8859-1

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 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 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 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;ibdat a4: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;ibdat a4: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;ibdat a4: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@pixelated.net
>>>
>>
>>
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>



--
Thanks
Suresh Kuna
MySQL DBA

--000e0ce0d87a1ce1fb048ceddbec--

Re: InnoDB Tablespace

--000325579d926f4674048d188301
Content-Type: text/plain; charset=ISO-8859-1

On Mon, Aug 2, 2010 at 8:35 PM, Johnny Withers wrote:

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


I seem to recall - but am on holiday, and thus way too lazy to actually
check - that the "free" refers to the amount of free space in the files (so
should go up when you delete records, and so on), not amount of total disk
space you are still allowed to fill.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000325579d926f4674048d188301--