Hello, I recently read about InnoDB internals but I can't figure out
some questions.

1. When InnoDB update small record (16KB page) all page with header is
rewritten - is there another header whitch need to be updated (after
double write sync)? Something like extent header or segment header -
segment have header but extent have header?

2. How InnoDB engine know in what page in segment write new record?
On 200MB file there will be around 12800 x 16KB pages, so how InnoDB
know where the data need to be inserted? Engine scan disk to find half
filled page with free space id done?

3. How InnoDB know there is need to increment file size with another
pages? Use some fill factor header or scans all segment on start and
store/modify this data in memory?

eteste <piotrek69 (AT) gmail (DOT) com> wrote:
Not sure if I understand you correctly here. InnoDB modifies the page
(the record is overwritten with new values). Then the whole page is
marked dirty and eventually flushed to disk.

If the new record was shorter or equal in size or if there was enough
free space in the page, then only the page has to be touched.

The rows are kept in a tree structure, ordered by PK. There are two
types of pages in this PK tree: leaf nodes (this is where rows are
stored) and non-leaf nodes. The latter contain only index data and
page pointers (building up the tree)

InnoDB builds the PK for the new row and then follows the pointers
in non-leaf pages to find the right leaf page for the new record.
Eventually a new leaf page has to be created (and then also non-leaf
pages need be updated)

Pages are grouped in extents (64 pages=1MB). Extents are allocated
for objects, i.e. the PK structure of a table. Extents are allocated
from table space(s). If a new page is needed and all extents for the
object are full and there is not enough free space in any table space
to allocate another extent - then some tablespace must be grown.

Bookkeeping on database objects, tablespaces, extents and such is
done in invisible InnoDB tables (the data dictionary).


XL