Ann - thanks for the great info in your reply. You might be pleased to know
that we have done extensive data load/access tests in the 15GB range for our
data warehouse and Firebird has been considerably faster than Oracle 9i in
almost all cases. We can, however, close the gap with careful attention to
data placement and fragmentation with Oracle. The primary method of doing
this is with tablespaces - a collection of related tables and indexes within
an Oracle database that can be managed separately from the rest of the
database. When table data is partitioned among several tablespaces, the
engine can do large table scans in parallel (large table scans are
practically unavoidable with the ad hoc nature of data warehouse queries).
You can also backup/restore at the tablespace level. This is not a
commercial for Oracle - DB2 and Informix have the same capabilities. I just
think that this ability to divide and conquer huge data sets (dozens of
tables with hundreds of millions of rows) is a feature that most data
warehouse admins feel a need for. Incremental backups would also go on this
list.

I love Interbase/Firebird's elegance and economy. It is far simpler to
install and manage than any commercial database and just seems to "respect"
computer resources more.

I have followed this group pretty closely and have not seen a reference to a
Firebird database larger than 15GB (and that was my own). Are there larger
ones out there?

Thanks again,
Fred Pratt


-----Original Message-----
From: Ann W. Harrison [mailto:aharrison@...]
Sent: Monday, May 03, 2004 11:58 AM
To: Firebird-general@yahoogroups.com; Firebird-general@yahoogroups.com
Subject: RE: [Firebird-general] big time firebird


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








Community email addresses:
Post message: Firebird-general@yahoogroups.com
Subscribe: Firebird-general-subscribe@yahoogroups.com
Unsubscribe: Firebird-general-unsubscribe@yahoogroups.com
List owner: Firebird-general-owner@yahoogroups.com

Shortcut URL to this page:
http://www.yahoogroups.com/community/Firebird-general
Yahoo! Groups Links