| Subject | Re: [firebird-support] Table is fragmented? |
|---|---|
| Author | Ann Harrison |
| Post date | 2019-11-22T17:49:15Z |
On Fri, Nov 22, 2019 at 11:47 AM rudi.feijo@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
Recently I ran a "Database statistics" with ibexpert on one of our dbs, and there were a lot of tables marked in yellow, with the tooltip saying "Table xxx Is fragmented"
I couldn't quite find an explanation for it online, so I have a few doubts about it:
• Is this the same as index fragmentation?
Sort of. Index fragmentation occurs when new entries are added to the index that
require splitting an index page. Suppose you have an index in which one page
holds all the entries from "England" to "Japan". Someone adds "Ireland" which
doesn't fit. As a result the page must be split into two pages - "England" to "Georgia"
and "Germany" to "Japan". Firebird allocates a new page for the "Germany" to
"Japan" part of the index. That page will tend to be at the end of the database file.
If you had your index settled into pages in index order - which would happen after
a backup and restore - each index page would sit in the file between the page
logically before it and the page logically after it*. After the page split, the "Germany"
to "Japan" page would be at the end of the file, rather than physically next to
"England" to "Georgia". Back in the early 1990's with slowly rotating magnetic
disks, that made a difference, I guess.
Table fragmentation occurs when new data is added to a table and the new data
page is not adjacent to other data pages for the same table. In an unfragmented
table, a sequential scan of all records reads a series of physically adjacent pages.
Or did, in the early history of rotating disks. Striping, redundant storage, SSDs -
the world has moved on to the point where a database ought to worry about
physical storage locations.
• Is this something to be worried about?
No. Stable storage technology is way beyond the point were being able to stream
pages off a disk is relevant at all.
• Does gfix or backup/restore fix the problem?
Since there's no problem ... fixing it is hard.
A restore will unfragment indexes and data. The backup gets all the data for one
table then moves on to the next, so the restore creates all data pages on logically
adjacent pages. Currently gbak restores indexes one at a time, so they too are
on logically adjacent pages. Building indexes in parallel is a reasonable optimization,
in which case the pages for several indexes would be interspersed. But as above,
it doesn't matter at all.
• How can I query the metadata to figure out if there are fragmented tables in my db?
You can't. The physical file page numbers for data pages are stored in the database on
special pages that ibexpert knows how to find and read. However, it doesn't matter at
all, since storage is so very different than it was thirty years ago.
Good luck,
Ann
* At least Firebird would think that the pages were adjacent. In fact, the file system could
put them anywhere or in several places.