Quantcast

Understanding Catalog File Size

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Understanding Catalog File Size

Jeremy Lemaire
I have two identical 2.7T machines running Lucidb, server 1 and server 2.  Server 1 is being updated nightly by an incremental ETL process while server 2 was recently restored from a backup of server1.  The odd thing is that server 1 is at 47% disk capacity while server 2 is only at 26% capacity.  

Server 1
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             2.7T  1.2T  1.4T  47% /

Server 2
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             2.7T  654G  2.0T  26% / 


A large percentage of this difference can be attributed to difference in the db.dat and temp.dat files:

Server 1
total 805G
-rw-r--r-- 1 lucid lucid 565G 2010-06-28 15:38 db.dat
-rw------- 1 lucid lucid 243G 2010-06-28 14:43 temp.dat
-rw------- 1 lucid lucid 625M 2010-06-28 15:38 txnlog.dat
-rw-r--r-- 1 lucid lucid  71M 2010-06-28 15:29 FarragoCatalog.log
-rw------- 1 lucid lucid  63M 2010-06-28 15:36 shadowlog.dat
-rw-r--r-- 1 lucid lucid  43M 2010-06-22 15:48 FarragoCatalog.script
drwxr-xr-x 2 lucid lucid 4.0K 2009-10-23 03:49 fennel
-rw-r--r-- 1 lucid lucid 2.6K 2009-10-23 03:49 ReposStorage.properties
-rw-r--r-- 1 lucid lucid  411 2010-06-22 15:50 FarragoCatalog.properties
-rw-r--r-- 1 lucid lucid   16 2010-06-28 17:04 FarragoCatalog.lck
-rw-r--r-- 1 lucid lucid    0 2010-02-10 15:37 ReposStorage.properties.lck

Server 2
total 379G
-rw-r--r-- 1 lucid lucid 565G 2010-06-28 16:12 db.dat
-rw------- 1 lucid lucid 384M 2010-06-28 15:39 temp.dat
-rw-r--r-- 1 lucid lucid  78M 2010-06-28 15:39 FarragoCatalog.script
-rw------- 1 lucid lucid  63M 2010-06-28 16:12 shadowlog.dat
-rw------- 1 lucid lucid  63M 2010-06-28 16:12 txnlog.dat
drwxr-xr-x 2 lucid lucid 4.0K 2009-10-23 03:49 fennel
-rw-r--r-- 1 lucid lucid 2.6K 2009-10-23 03:49 ReposStorage.properties
-rw-r--r-- 1 lucid lucid  411 2010-06-28 16:12 FarragoCatalog.properties
-rw-r--r-- 1 lucid lucid  341 2010-06-28 16:12 FarragoCatalog.log
-rw-r--r-- 1 lucid lucid   16 2010-06-28 17:22 FarragoCatalog.lck
-rw-r--r-- 1 lucid lucid    0 2010-06-17 09:19 ReposStorage.properties.lck

Here are a few questions I was hoping someone could answer:

  1. temp.dat - Although the db.dat files are the same size on both server1 and server 2 the temp.dat file size varies greatly.  What causes this variation and is there a way to reclaim the temp.dat space?  Since server 1 is where 99% of the work is being done I am assuming the larger temp.dat file size is related to things like query optimization (i.e. caching etc)  and I probably do not want to reclaim it.  Is this true? Note that "ALTER SYSTEM DEALLOCATE OLD" is run daily and seems to have no effect on the server 1 temp.dat file size.
  2. db.dat - When comparing the catalog directories between server 1 and server 2 the directory totals differ greatly between the two.  Also the sum of the file sizes within the catalog directory on server 2 differ from it's directory totals.  For example on server 2 the db.dat file alone is 565G but the total for the entire directory is only 379G.  Is there a LucidDb explanation for this or is it just a Linux nuance that I am unaware of, or both maybe?  I am aware that the db.dat will never shrink so maybe the larger directory total on server 1 is the result of creating and dropping tables, the 565G is the allocated space and the 379G is the actual space used?  If this is the case does this mean a FULL restore on server 1 will reclaim more space than an ALTER SYSTEM DEALLOCATE OLD, approximately 183G in this case?     
  3. Generally speaking is there a way to reclaim the space on server 1 so it has the same available disk space as server 2? 



  

------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Understanding Catalog File Size

John Sichi
Administrator
Jeremy Lemaire wrote:
>    1. */temp.dat/* - Although the db.dat files are the same size on both
>       server1 and server 2 the temp.dat file size varies greatly.  What
>       causes this variation and is there a way to reclaim the temp.dat
>       space?  Since server 1 is where 99% of the work is being done I am
>       assuming the larger temp.dat file size is related to things like
>       query optimization (i.e. caching etc)  and I probably do not want
>       to reclaim it.  Is this true? Note that "ALTER SYSTEM DEALLOCATE
>       OLD" is run daily and seems to have no effect on the server 1
>       temp.dat file size.

temp.dat stores intermediate results during complex query processing
over large data (e.g. partitions for hash join, and partially sorted
runs for sort/merge join; we don't currently use it to cache any results
across queries, although we might in the future).  Like db.dat, it never
shrinks by itself.  However, unlike db.dat, it is possible to reclaim it
by shutting down the server and then deleting the file.  LucidDB will
recreate the file back at a small size and then regrow it from there.

Growing the file is somewhat expensive on most filesystems (xfs is an
exception), so usually it's better to just leave it alone so that its
space can be reused later.  However, if you performed some one-time
maintenance operation which caused it to grow excessively, then
shrinking it could be useful.  See notes below on lucidDbAllocFile for
recreating it back to a larger size.

>    2. */db.dat/* - When comparing the catalog directories between server
>       1 and server 2 the directory totals differ greatly between the
>       two.  Also the sum of the file sizes within the catalog directory
>       on server 2 differ from it's directory totals.  For example on
>       server 2 the db.dat file alone is 565G but the total for the
>       entire directory is only 379G.  Is there a LucidDb explanation for
>       this or is it just a Linux nuance that I am unaware of, or both
>       maybe?  I am aware that the db.dat will never shrink so maybe the
>       larger directory total on server 1 is the result of creating and
>       dropping tables, the 565G is the allocated space and the 379G is
>       the actual space used?  If this is the case does this mean a FULL
>       restore on server 1 will reclaim more space than an ALTER SYSTEM
>       DEALLOCATE OLD, approximately 183G in this case?    

Linux filesystems such as ext3 support "holes", which accounts for what
you are seeing.  When we do the restore, we recreate the file at full
size, but only write to data blocks that were actually occupied in the
original backup; the unused blocks remain as holes, with no associated
storage.

Please read this section of the wiki:

http://pub.eigenbase.org/wiki/LucidDbPerformanceTuning#Pre-allocating_File_Space_.28Linux_Only.29

The lucidDbAllocFile is supplied to allow you to create a file with no
holes (for speeding up bulk load into a new database, e.g. during
benchmarking) since the expensive part of growing a file is actually
filling in the holes.  As described, it can be used against temp.dat too.

The performance counters noted in that doc can also be used to
understand and monitor what's going on here.

>    3. Generally speaking is there a way to reclaim the space on server 1
>       so it has the same available disk space as server 2?* *

There's currently no "shrink-it" utility.  You would have to recreate
the database and schema on a new machine, copy all the data over
logically into the new tables (using LucidDB->LucidDB SQL/MED: INSERT
INTO new_luciddb_table SELECT FROM remote_lucidb_table...), and then swap.

However, it turns out restore can actually do the job for you at the
disk usage level (even though the file still claims to be large due to
the holes); I hadn't thought of that before.

JVS

------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
luciddb-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/luciddb-users
Loading...