| Subject | Re: [firebird-support] Re: Fbserver eating CPU...not taking advantage of memory |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-05-22T17:56:54Z |
Aaron Abend wrote:
InterBase) and other databases.
1) Record location. Many databases cluster records on the primary key
index, either directly storing the data in the index or using the key to
group records. In a well-balanced system clustering on primary keys
makes primary key lookup very quick, though it leads to a wide fanout of
the index when data is stored in leaf nodes, and either sparse storage
or overflows when clustering - or with some value distributions both.
Firebird stores records on data pages, using the most accessible page
with sufficient space. Indexes are stored on index pages an contain a
record locater in the leaf node.
2) Index usage. Most databases systems read an index node, retrieve the
data - quick for clustered primary indexes, slower for secondary indexes
which often contain the primary key as the record locater, turning a
secondary index lookup into two index lookups. This technique also
leads to bouncing between index pages and data, which can be resolved by
proper placement control, assuming that the DBA has the time and skill
to do so. For non-clustered indexes this technique also results in
rereading data pages.
Firebird harvests the record locaters for qualifying records from the
index, builds a bitmap of record locaters, then reads the records in
physical storage order.
3) Index optimization. Because the access strategy binds index access
and record access tightly, most database optimizers must choose one
index per table as the path to data. Because it creates a bitmap of
record locaters, Firebird can use several indexes on a table by anding
and oring the bitmaps. At one point, it got a bit carried away and used
secondary indexes when unique index is available - that got fixed in 1.5.
4) Indexes in lieu of data. Non-versioning databases can resolve some
queries (counts for example) by reading the index without actually
reading the record data. Firebird (and Postgres and other natively
versioning databases) indexes contain entries that are not yet visible
to other transactions and entries that are no longer relevant to some
active transactions. The only way to know is to read the data.
5) Long duplicate chains. Some databases (Firebird 2 for one) are
better than others (Firebird 1.x for one) at removing data from long
(>10000) duplicate chains in indexes.
figure out why your CPU is off the scale.
Regards,
Ann
>There are several differences in index handling between Firebird (and
> We do all our testing on large real-life datasets with about 50,000 records
> after we do development on demo datasets. Having done database application
> development for 25 years, I have thought of most of the obvious errors, but
> I do appreciate your ideas. It is the Firebird-specific stuff that I really
> need help on. My thinking right now is that our indexing, which drew from
> experience with other databases, is probably not optimal for Firebird.
InterBase) and other databases.
1) Record location. Many databases cluster records on the primary key
index, either directly storing the data in the index or using the key to
group records. In a well-balanced system clustering on primary keys
makes primary key lookup very quick, though it leads to a wide fanout of
the index when data is stored in leaf nodes, and either sparse storage
or overflows when clustering - or with some value distributions both.
Firebird stores records on data pages, using the most accessible page
with sufficient space. Indexes are stored on index pages an contain a
record locater in the leaf node.
2) Index usage. Most databases systems read an index node, retrieve the
data - quick for clustered primary indexes, slower for secondary indexes
which often contain the primary key as the record locater, turning a
secondary index lookup into two index lookups. This technique also
leads to bouncing between index pages and data, which can be resolved by
proper placement control, assuming that the DBA has the time and skill
to do so. For non-clustered indexes this technique also results in
rereading data pages.
Firebird harvests the record locaters for qualifying records from the
index, builds a bitmap of record locaters, then reads the records in
physical storage order.
3) Index optimization. Because the access strategy binds index access
and record access tightly, most database optimizers must choose one
index per table as the path to data. Because it creates a bitmap of
record locaters, Firebird can use several indexes on a table by anding
and oring the bitmaps. At one point, it got a bit carried away and used
secondary indexes when unique index is available - that got fixed in 1.5.
4) Indexes in lieu of data. Non-versioning databases can resolve some
queries (counts for example) by reading the index without actually
reading the record data. Firebird (and Postgres and other natively
versioning databases) indexes contain entries that are not yet visible
to other transactions and entries that are no longer relevant to some
active transactions. The only way to know is to read the data.
5) Long duplicate chains. Some databases (Firebird 2 for one) are
better than others (Firebird 1.x for one) at removing data from long
(>10000) duplicate chains in indexes.
>Yes, but lowering the priority is not going to fix anything. Lets
>
> Another thought - is there any way to reduce the priority of the Firebird
> process in the config file? Is there any harm to reducing its priority?
figure out why your CPU is off the scale.
Regards,
Ann