| Subject | Re: [IB-Architect] Faster DB Backups, WAS Super-transactions and Incremental Backup |
|---|---|
| Author | Charlie Caro |
| Post date | 2000-06-21T16:11:59Z |
Jim Starkey wrote:
of last resort when there was corruption in the record version chains.
Today, most people use that switch to achieve a "fast[er]" backup instead
of for its original intention.
I think you are referring to the -u[se all space] switch to prevent
reserving space on page for record back versions. This was pre-V3
behavior. Some customers complained back then that their queries were
taking 20-30% longer after V3 was released. Of course, these queries
were performing sequential table scans and felt the extra distance due
to the reserved space. [BTW, updates and deletes to most tables improved
by a factor of 2 or 3 times (200-300%).]
In V6, I believe InterBase automatically asserts the -u switch when
restoring a read-only database.
In hindsight, I think the -u switch has very limited applicability to
an entire database. It would be more sensible to be able to apply that
functionality at the table level where a table is declared as having
an "archival" property.
For an archival table, the life cycle of a row is:
1) INSERTed once;
2) SELECTed thousands of times;
3) UPDATEd hardly ever;
4) DELETEd once;
The frequency of modification to an archival table just doesn't justify
reserving space for delete stubs and update back versions. Sequential
scans are morely likely for aggregate queries. Archival tables also tend
to be the largest tables in a database.
Archival tables would speed up database backups/restores because gbak
performs full table scans to pickup every row.
Regards,
Charlie
>Ann added a switch to gbak to avoid garbage collection to create a backup
>
> Gbak is a non-privileged logical backup utility using the published
> API. On backup, gbak first reads system tables, writes an abstract
> to the backup file, then loops through each table fetching all fields,
> writing data to backup file. If the -t (transportable) switch is
> specified, gbak translates all datatypes to a canonical format.
> A gbak restore is, in fact, an ordinary database creation followed
> by meta-data restore (all indexes, constraints, and triggers are
> turned off), data restore, and activation of indexes, constraints,
> and triggers. Absolutely no magic at all (well, almost none.
> Charlie added an option to defer garbage collection or something
> like that).
>
of last resort when there was corruption in the record version chains.
Today, most people use that switch to achieve a "fast[er]" backup instead
of for its original intention.
I think you are referring to the -u[se all space] switch to prevent
reserving space on page for record back versions. This was pre-V3
behavior. Some customers complained back then that their queries were
taking 20-30% longer after V3 was released. Of course, these queries
were performing sequential table scans and felt the extra distance due
to the reserved space. [BTW, updates and deletes to most tables improved
by a factor of 2 or 3 times (200-300%).]
In V6, I believe InterBase automatically asserts the -u switch when
restoring a read-only database.
In hindsight, I think the -u switch has very limited applicability to
an entire database. It would be more sensible to be able to apply that
functionality at the table level where a table is declared as having
an "archival" property.
For an archival table, the life cycle of a row is:
1) INSERTed once;
2) SELECTed thousands of times;
3) UPDATEd hardly ever;
4) DELETEd once;
The frequency of modification to an archival table just doesn't justify
reserving space for delete stubs and update back versions. Sequential
scans are morely likely for aggregate queries. Archival tables also tend
to be the largest tables in a database.
Archival tables would speed up database backups/restores because gbak
performs full table scans to pickup every row.
Regards,
Charlie