How to reclaim disk space?

14 posts / 0 new
Last post
imran
imran's picture
Offline
Last seen: 1 year 7 months ago
Joined: Aug 14 2010
Junior Boarder

Posts: 14

Imran Javaid
How to reclaim disk space?

Once I create a table, add data to it and then drop it, how do I reclaim the disk space that the table used?

I did try running ddlcleanup but it gave the following error:
Found file /usr/local/Calpont/dbrm/DDLLog_myschema.mytable_3089
Error in removing extents from extent map.
Error in removing files.

davidhill
davidhill's picture
Offline
Last seen: 4 weeks 1 day ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
Re:How to reclaim disk space?

First off, thanks for downloading and checking out InfiniDB.

The 'drop table' command does reclaim the disk space that was used.

Are you seeing something different with your testing?

Also the script 'ddlcleanup' is used for a different reason, so that is why you got the pervious errors.

imran
imran's picture
Offline
Last seen: 1 year 7 months ago
Joined: Aug 14 2010
Junior Boarder

Posts: 14

Imran Javaid
Re:How to reclaim disk space?

When dropping tables I do not see a decrease in disk usage.

davidhill
davidhill's picture
Offline
Last seen: 4 weeks 1 day ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
Re:How to reclaim disk space?

So which disk database are you referring to, the one in /usr/local/Calpont/data1/000dir?
This is the InfiniDB database location.

And you are creating the table with the engine=infinidb?

I performed a create table using this statement and run the below command before during and after and saw the disk space being reclaimed.

create table lineitem (
-> l_orderkey int,
-> l_partkey int,
-> l_suppkey int,
-> l_linenumber bigint,
-> l_quantity decimal(12,2),
-> l_extendedprice decimal(12,2),
-> l_discount decimal(12,2),
-> l_tax decimal(12,2),
-> l_returnflag char (1),
-> l_linestatus char (1),
-> l_shipdate date,
-> l_commitdate date,
-> l_receiptdate date,
-> l_shipinstruct char (25),
-> l_shipmode char (10),
-> l_comment varchar (44)
-> ) engine=infinidb
-> ;

[root@srvswint2 data1]# du --max-depth=1 000.dir/
39869172 000.dir/000.dir
39869176 000.dir/
[root@srvswint2 data1]# du --max-depth=1 000.dir/
39898584 000.dir/000.dir
39898588 000.dir/
[root@srvswint2 data1]# du --max-depth=1 000.dir/
39869172 000.dir/000.dir
39869176 000.dir/
[root@srvswint2 data1]#

Double check what I did versus what you did and see where we might be different please.

imran
imran's picture
Offline
Last seen: 1 year 7 months ago
Joined: Aug 14 2010
Junior Boarder

Posts: 14

Imran Javaid
Re:How to reclaim disk space?

When I follow your steps, disk space is reclaimed.
My own scenario is different in that after creating the table, I am using cpimport to load 5 million records and then dropping the table.

In one case, when I drop the table using the MySQL Query Browser (v. 1.2.17), I get the error: Error occured during file deletion. Restart DDLProc or use command tool ddlcleanup to clean up. When I use ddlcleanup, I get the error mentioned earlier.

When I drop the table using idbmysql, I do not get this error, but I did not see the space reclaimed. I should actually say that a little bit of space was reclaimed, but not all of it.

who is supposed to own the files in the data1 directory: root or mysql?

davidhill
davidhill's picture
Offline
Last seen: 4 weeks 1 day ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
Re:How to reclaim disk space?

We need to look into the MySQL Query Browser error a little closer here.

On your question, data1 directory and files are root user:

drwxrwxrwt 4 root root 4096 Sep 24 2009 data1

drwxrwxrwt 3 root root 4096 Sep 24 2009 000.dir

And I tried your scenerio and reclaimed the database, strange..
I ran any commands via the idb console.

STARTED:

[root@srvswint2 data1]# du --max-depth=1 000.dir/
39869172 000.dir/000.dir
39869176 000.dir/

CREATED TABLE:

[root@srvswint2 data1]# du --max-depth=1 000.dir/
39898584 000.dir/000.dir
39898588 000.dir/

CPIMPORTED 6001215 rows

[root@srvswint2 data1]# du --max-depth=1 000.dir/
40808296 000.dir/000.dir
40808300 000.dir/

DROPPED TABLE:

[root@srvswint2 data1]# du --max-depth=1 000.dir/
39869172 000.dir/000.dir
39869176 000.dir/

imran
imran's picture
Offline
Last seen: 1 year 7 months ago
Joined: Aug 14 2010
Junior Boarder

Posts: 14

Imran Javaid
Re:How to reclaim disk space?

I think my problem my have to do with me changing the ownership of the files under /usr/local/Calpont/data1. I change all ownership to a user other than root so that I can use cmimport with that user. Is there a way for a user other than root to use cmimport (apart from using sudo)?

davidhill
davidhill's picture
Offline
Last seen: 4 weeks 1 day ago
Joined: Oct 27 2009
Administrator

Posts: 595

david hill
Re:How to reclaim disk space?

Glad to hear you figured it ot.

Sorry, currently we only support installing and running as a root user.

chetandr
chetandr's picture
Offline
Last seen: 10 months 4 weeks ago
Joined: Jun 3 2011
Junior Boarder

Posts: 1

Chetan Rane
Re: How to reclaim disk space?

Hi,

I have a different Situation.

I import around 1 million records at a time using cpimport. to update another 500000 records I actualy perform a delete and then perform another import using cpimport as this is faster then running an update Query for each individual records.

how can I reclaim the Disk space in this case.

radams
radams's picture
Offline
Last seen: 17 hours 5 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: How to reclaim disk space?

Hi chetandr,

Dropping the table is how you would reclaim disk space on the community version of InfiniDB. Once you drop the old table, you can create and import the data into the new table without the rows that have been deleted.

Thank you,

Robert
Calpont

zc2yhy
zc2yhy's picture
Offline
Last seen: 8 months 3 weeks ago
Joined: Sep 2 2011
Junior Boarder

Posts: 1

chao zhang
Re: How to reclaim disk space?

Is there a new way to reclaim disk space on the lastest, community version 2.2 of Infinidb?

radams
radams's picture
Offline
Last seen: 17 hours 5 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Re: How to reclaim disk space?

Hi,

The process is the same as noted in the previous post.

Dropping the table is how you would reclaim disk space on the community version of InfiniDB. Once you drop the old table, you can create and import the data into the new table without the rows that have been deleted.

Thanks,

Robert
Calpont

tsogtoo
tsogtoo's picture
Offline
Last seen: 1 month 1 week ago
Joined: Feb 20 2014
Junior Boarder

Posts: 3

tsogtoo tsogtoo
Erased /usr/local/Calpont file but has data1 folder

Hi,

I am here with serious problem.

I accidentally did "rm -rf /usr/local/Calpont/" folder but fortunately the data1 folder which contains all the data was in another directory.

I also has the table scripts.

Is there a way of recovering database from /data1 folder? What I am asking is that can we restore the database by reinstalling infinidb and connecting it into /data1?

As I see from /data1, there are subdirectories named 000.dir, etc. 

What are those *.dir telling?

 

Thanks,

 

February 28, 2014 - 10:23am
radams
radams's picture
Offline
Last seen: 17 hours 5 min ago
Joined: Jan 3 2011
Administrator

Posts: 492

Robert Adams
Erased /usr/local/Calpont file but has data1 folder

 

Hi,

 

You can reinstall InfiniDB, run the scripts to recreate the tables ( assuming they will all match the existing data files).

After the tables are created shutdown the system. Back up the new data1 directory and then add back the original data1.

(this assumes you have a complete data1 directory with all the system files intact.) Start the system back up and check

the data.

 

We have nver tried to recover a system using this method but you can give it a try to save having to reload the data.

 

Best regards,

 

Robert