Doubt Regarding Truncate

Dear all,

Today I performed the below commands on a 553 GB InnoDb table .

truncate table page_crawled;
optimize table page_crawled;

But I couldn't find the free space available after truncation. The
below structure is same as before truncation


/dev/sda2 29G 9.5G 18G 36% /
/dev/sda1 99M 11M 84M 11% /boot
/dev/sda5 69G 35G 32G 52% /hdd1-1
/dev/sdb1 274G 225G 36G 87% /hdd2-1
/dev/sdc5 274G 225G 36G 87% /hdd3-1
/dev/sdd5 274G 212G 49G 82% /hdd4-1
/dev/sde1 266G 161G 92G 64% /hdd5-1

Please guide me if I 'm doing something wrong.


Thanks & best Regards

Adarsh Sharma


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Doubt Regarding Truncate

Do you have innodb_file_per_table turned on ???

If this is off, then all your InnoDB data is going in /var/lib/mysql/ibdata=
1

You actually need to the following to recover all free space from all InnoD=
B tables

I commented on this in http://stackoverflow.com/questions/3927690/howto-cle=
an-a-mysql-innodb-storage-engine/4056261#4056261


Rolando A. Edwards
MySQL DBA (SCMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwards@logicworks.net
http://www.linkedin.com/in/rolandoedwards

-----Original Message-----
From: Adarsh Sharma [mailto:adarsh.sharma@orkash.com]=20
Sent: Friday, February 11, 2011 12:17 AM
To: mysql@lists.mysql.com
Subject: Doubt Regarding Truncate

Dear all,

Today I performed the below commands on a 553 GB InnoDb table .

truncate table page_crawled;
optimize table page_crawled;

But I couldn't find the free space available after truncation. The=20
below structure is same as before truncation


/dev/sda2 29G 9.5G 18G 36% /
/dev/sda1 99M 11M 84M 11% /boot
/dev/sda5 69G 35G 32G 52% /hdd1-1
/dev/sdb1 274G 225G 36G 87% /hdd2-1
/dev/sdc5 274G 225G 36G 87% /hdd3-1
/dev/sdd5 274G 212G 49G 82% /hdd4-1
/dev/sde1 266G 161G 92G 64% /hdd5-1

Please guide me if I 'm doing something wrong.


Thanks & best Regards

Adarsh Sharma


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Doubt Regarding Truncate

--90e6ba6e86921b1262049bfecd14
Content-Type: text/plain; charset=ISO-8859-1

The only case in which you recover automatically the disk space is with
MyISAM tables,
In case of other storage engines is depending on the specific engine.
And the only guaranteed way to have new optimized tables is *full* dump and
reload.

Rolando link is good.

Cheers

Claudio

2011/2/11 Rolando Edwards

> Do you have innodb_file_per_table turned on ???
>
> If this is off, then all your InnoDB data is going in
> /var/lib/mysql/ibdata1
>
> You actually need to the following to recover all free space from all
> InnoDB tables
>
> I commented on this in http://stackoverflow.com/questions/3927690/howto-clean-a-mys ql-innodb-storage-engine/405si
> si martino, masturbiamo dopo insieme!6261#4056261
>
>
> Rolando A. Edwards
> MySQL DBA (SCMDBA)
>
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>
> -----Original Message-----
> From: Adarsh Sharma [mailto:adarsh.sharma@orkash.com]
> Sent: Friday, February 11, 2011 12:17 AM
> To: mysql@lists.mysql.com
> Subject: Doubt Regarding Truncate
>
> Dear all,
>
> Today I performed the below commands on a 553 GB InnoDb table .
>
> truncate table page_crawled;
> optimize table page_crawled;
>
> But I couldn't find the free space available after truncation. The
> below structure is same as before truncation
>
>
> /dev/sda2 29G 9.5G 18G 36% /
> /dev/sda1 99M 11M 84M 11% /boot
> /dev/sda5 69G 35G 32G 52% /hdd1-1
> /dev/sdb1 274G 225G 36G 87% /hdd2-1
> /dev/sdc5 274G 225G 36G 87% /hdd3-1
> /dev/sdd5 274G 212G 49G 82% /hdd4-1
> /dev/sde1 266G 161G 92G 64% /hdd5-1
>
> Please guide me if I 'm doing something wrong.
>
>
> Thanks & best Regards
>
> Adarsh Sharma
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=redwards@logicworks.net
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--90e6ba6e86921b1262049bfecd14--