DB2/Linux/Slow Drop Table Execution

by bitznbitez

Running a large DB2 instance on a Redhat linux I recently encountered extremely slow execution of DROP TABLE statements.    On AIX this has always been an almost instant operation but on linux when dropping a 500 million record table in the 100 GB range it would take on the order of 30+ seconds.

This became a problem for us due to certain lock contention in the catalog tables impacting other ongoing processing.   So I did a bit of research.

The root cause seems to be our filesystem type.   Our system admin built the standard ext3 filesystems for us.   This filesystem type is extent based and can be very slow especially on the type of operations like I am describing.    To resolve this we will convert to ext4.    First we will attempt the inplace conversion by remounting the filesystem as ext4.   This is not as complete a solution as wiping the partition and starting over.    However we completely reload our tables from scratch every two weeks so we should get some of the benefit.   If not we will then proceed to rebuild the partitions from scratch.

Advertisements