At 12:12 PM 5/3/2004, Fred Pratt wrote:

> The ability to place heavily used tables on
>separate disks has proven to be a very helpful feature for us - at least
>with Informix. All major database vendors support this feature, so someone
>is finding it helpful out there. :-)

The importance of placement control for database performance depends a
great deal on the design of the database on-disk structure and engine
access strategy as well as the disk access model used by the storage system
- currently the RAID variant used. InterBase/Firebird was designed to
minimize the importance of placement, on the assumption that control of
disk head movement was best handled closer to the disk than any database
management system has any business being.

One frequent goal of placement control is to place related indexes and data
on different devices. In most database systems, record access is done by
alternately reading a node from an index, then the corresponding record,
then the next index node, etc. This can lead to head
contention. InterBase/Firebird use indexes to construct bit-maps of
matching records, so access to the index is not interrupted with data
reads. After the bit-maps have been assembled and combined, data records
are read in storage order.

A second reason for placement control is to provide space for record
clustering. Most database systems store records in some variant of primary
key order. Clustering is reasonably efficient as long as the records
approximately fill the space allocated to the cluster. Too few records
mean more reads per record fetched. Too many records cause overflows which
mean more reads per record fetched. InterBase/Firebird does not cluster
records, but places them on data pages in the order of storage
(approximately, give or take reuse of space held by deleted records and old
version). It then uses the index bit-map access to read the records
efficiently.

As you know, a backup/restore reorganizes the database, placing all data
pages for a particular table together and creating indexes on contiguous
pages. That's all the placement control offered. Most users with large
databases never, or almost never, restore their active databases. Even
without reorganization, performance is adequate. For those applications
that need higher levels of disk throughput, the answer is to throw hardware
at the problem. The inherent simplicity of the InterBase/Firebird file
layout works well with hardware acceleration - something which is not true
of databases with complex placement rules.

Regards,


Ann