Sort Order & DB Size

SUMMARY

When you create a checkpoint on one platform and restore on another, the database size is different from the database size when creating and restoring a checkpoint on the same platform. Why does this happen?

DETAILS

The data in a checkpoint is written in database key order, sorted according to the case handling behavior of the Perforce Server operating system. When restoring this checkpoint on the same platform, the case handling remains the same, resulting in ordered inserts.

When restoring from this checkpoint on a platform where the case handling differs, you are effectively performing out-of-order inserts. This type of restore can cause the B-trees, the structures used to store data in the Perforce database, to become imbalanced, resulting in unused space in the database. This, in turn, increases the size of the database files compared with those on the original platform.

This imbalance in the B-tree data structure does not need to be corrected, although you are able to do so by taking a checkpoint on the new platform - thus using the 'new' sort order - moving the database files (db.*), then restoring from this checkpoint. The data is now inserted in order, limiting or removing any imbalance.

Notes

Since the 2005.1 release, the server performs a passive reorganization when updates are made to the database tables. From the release notes:

Btree passive reorganization - #68043 **

As updates are made to the db.* tables, the server now reorganizes portions of the table as necessary to maintain sorted order across contiguous leaf pages. These passive reorganizations will result in better performance when scanning portions of the table since physical movements of the disk heads will be reduced and readahead will be more effective. This feature should reduce the need to periodically recreate the db.* tables from a checkpoint to improve performance, but may increase the size of some of the db.* tables due to the allocation of new contiguous pages.