Organizing Databank Files
There are a number of factors involved in the organization of physical databank files that are important to database security and the overall performance of the Mimer SQL system.
Allocating Disk Space
Whenever possible, pre-allocate file space for databanks early in the lifetime of the databank file system.
The databank creation facilities allow the initial size of a new databank file to be specified in terms of the number of Mimer SQL pages. The size of a Mimer SQL page is 2 kilobytes.
The size of the databank file will be extended automatically by the database server during the lifetime of the databank as more space is required for data storage.
UNIX: Under Unix, the environment variable
MIMER_EXTENDcan be set to the number of Mimer SQL pages by which all databank files will be extended. The default setting is 128.
VMS: By default, under OpenVMS, databank files will be extended by 1000 OpenVMS blocks at a time. The extend size for a databank file can be altered by using the following DCL command:$ SET FILE/EXTENSION=extensionsize file.DBFThe databank file must not be in use by the database server or accessed in single user mode when this command is used.
Win: Under Windows, the number of Mimer SQL pages by which all databank files will be extended is determined by the Mimer SQL system and is not configurable.
An attempt to extend a file will fail if the disk is full, the databank attribute
MAXSIZEis reached, or any imposed disk quota is exceeded.
Having a small file extension size may cause disk fragmentation leading to reduced I/O performance. In addition, if the databank is growing rapidly, the frequently occurring file extension operations may have a negative effect on performance.
A databank file which is created with the size it will actually need in production will be accessed more efficiently than one created with a small initial size and then incrementally extended.
The SQL statement
ALTER DATABANK SET FILESIZEcan be used to change the size of a databank file to a specified size.
ALTER DATABANK DROP FILESIZEis used to shrink the database file as much as possible. The attributes
GOALSIZEcan also be used to manage the databank file size. Refer to the Mimer SQL Reference Manual, ALTER DATABANK, for details.
Mimer SQL databank files are organized internally into 2, 16 and 64 kilobyte databank blocks.
Accessing an internal databank block which is physically split over two or more distinct areas of allocated disk will require two disk read operations.
To avoid the risk of fragmenting the internal databank blocks, ensure that the number of disk blocks allocated for databank file extensions maps onto a whole number of 64 kilobyte databank blocks.
This will optimize disk I/O efficiency.
VMS: Disk blocks under OpenVMS are 512 bytes in size, therefore a disk cluster size which is a multiple of 4 will avoid fragmenting the 2 kilobyte databank blocks. The cluster size is set when formatting a disk.Use the following command to check the cluster size of a disk that is already formatted:$ SHOW DEVICE/FULL
Win: On Windows machines, disk clustering effects are hardware dependent and are not configurable.Disks are typically configured in terms of an even number of 512 byte or 1024 byte disk blocks and will therefore always work efficiently with Mimer SQL databank files.Use of disk defragmentation utilities may improve performance for large block
Protecting Data Against Loss
For data security reasons, in case of a disk failure, it is strongly recommended that
LOGDBis located on a disk unit that is physically separate from that on which the other databanks are located. See Background Information for more information.
LOGDBshould always be located on different physical disks which are served by separate disk controllers and no other databank files should be located on either disk.
The ordinary maintenance procedures for any computer system must involve backup and restore. A strategy, structure and procedure must be set up to include the Mimer SQL databases in the system backup routines. See Backing-up and Restoring Data for a detailed discussion of backup and restore.
Note: A system without a complete and valid backup and restore procedure runs the risk of losing valuable data.
If several physical disk units are available, the various databanks should be distributed across the available disk units in order to balance the system I/O load.
To optimize the distribution of I/O across disks, place databanks on physical disks in such a way that databanks which are likely to be accessed at the same time are on different disk units.
It is generally the case that
TRANSDBwill be accessed at the same time as other databanks during a transaction.
The structure of the databank file system and procedures such as backup and restore are generally simplified if databank files are placed in directories reserved solely for that purpose. The system administrator should create and maintain a directory structure that best suits the local system.
It is very common practice to reserve entire disks for databanks to allow for the ultimate size of the files.
Other Performance Issues
The placement of databanks on physical disk units will depend on exactly how they will be used when the database system is in operation.
The following issues generally have a more significant effect on database performance than the disk I/O factors relating specifically to physical layout of the Mimer SQL database:
- the amount of virtual memory paging
- the speed of the disk
- the involvement of unnecessary network communication.
For example, to enhance performance, frequently accessed databanks such as TRANSDB may be placed on separate, high performance disks and sufficient memory should be allocated to avoid paging.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40