InnoDB Table and Tablespace Monitors

InnoDB allows you to dump information on its internal data dictionary out to stderr, by creating specific InnoDB monitor tables. Specifically the innodb_table_monitor and innodb_tablespace_monitor tables. This is covered to a limited extent in the MySQL Reference Manual at http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html, however not to any great detail.

In the following I’ll use output created via these tables based upon the sample “sakila” database, and give details on how to attempt to correlate the information between the different outputs.

The output of SHOW INNODB STATUS is the output of the InnoDB Monitor, which is different than both the InnoDB Tablespace Monitor and the Innodb Table Monitor. You can also cause this information to be output to the MySQL error log every 15 seconds by creating an InnoDB table named innodb_monitor, like this:

CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;

In order to invoke InnoDB to output it’s data dictionary information on the tablespace, you must create an InnoDB table, like this:

CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE = InnoDB;

InnoDB will then dump the tablespace information to either the error log, or the console (if started without log-error).

Note: This only works fully when not using the innodb_file_per_table option, as it only dumps information on the shared tablespace, and not individual “tablespaces”.

================================================
060403 14:20:31 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 1152, free limit 832, free extents 3
not full frag extents 4: used pages 202, full frag extents 2
first seg id not used 0 114
SEGMENT id 0 86 space 0; page 209; res 28 used 28; full ext 0
fragm pages 28; free extents 0; not full extents 0: pages 0
SEGMENT id 0 87 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 88 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 89 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 90 space 0; page 209; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 91 space 0; page 209; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
–snip–
SEGMENT id 0 81 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 82 space 0; page 2; res 16 used 16; full ext 0
fragm pages 16; free extents 0; not full extents 0: pages 0
SEGMENT id 0 83 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 84 space 0; page 2; res 96 used 53; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 21
SEGMENT id 0 85 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 108
Validating tablespace
Validation ok
—————————————
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================

Now lets break down what this means for each segment. Right near the end we have SEGMENT id 0 84:

SEGMENT id 0 84 space 0; page 2; res 96 used 53; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 21

Space 0, page 2 means that the inode of the segment is on page 2 of space 0. The system tablespace (ibdata files) always have the id 0.

res means how many pages we have allocated for the segment.

used means how many of these pages are actually used by the segment at the moment.

So for this segment we have 96 pages allocated, but only 53 are being used currently, leaving 43 pages unused.

full ext means that 0 extents are completely used.

fragm pages means that additionally we have used all of the first 32 pages that InnoDB first allocates for the segment.

free extents means the number of those extents that are allocated for the segment, but where none of the pages is used.

not full extents tells you the number of those extents that are allocated for the segment, where some of the pages are used but not all.

pages tells you how many pages are used within those extents.

An extent in InnoDB is always 64 pages at a size of 16kb – 1 MB.

The tablespace is divided into these 64 page extents. When a segment grows it starts at a single page, and InnoDB allocates the first 32 pages for it individually. After that, InnoDB allocates complete 64 page extents.

This is shown with the number of pages reserved – 32 initial pages + 64 pages for the next extent.

InnoDB bases it’s internal tablespace management as a filesystem. The analogy between an OS file system and an InnoDB tablespace is:

File system              <-> InnoDB
----------------------------------------------
disk partition           <-> tablespace
file                     <-> segment
inode                    <-> fsp0fsp.c 'inode'
fs space allocation unit <-> extent
disk block               <-> page (16 kB)

So currently, the table in question (SEGMENT id 0 84) is 96 pages in size, or 1.5Mb, and has 43 pages free, or 688Kb.

Now that we have this information, how do we try to find out which table this is? Let’s move on to the innodb_table_monitor output.

To direct the output of the InnoDB Table Monitor to the erro log create the following table:

create table innodb_table_monitor (a int) engine = innodb;

This produces the following output for the Sakila sample database:

===========================================
060403 14:29:35 INNODB TABLE MONITOR OUTPUT
===========================================
————————————–
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 22
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; FOR_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name ID_IND, id 0 11, fields 1/6, type 3
root page 46, appr.key vals 22, leaf pages 1, size pages 1
FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
root page 47, appr.key vals 11, leaf pages 1, size pages 1
FIELDS: FOR_NAME ID
INDEX: name REF_IND, id 0 13, fields 1/2, type 0
root page 48, appr.key vals 12, leaf pages 1, size pages 1
FIELDS: REF_NAME ID
————————————–
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 22
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name ID_IND, id 0 14, fields 2/6, type 3
root page 49, appr.key vals 22, leaf pages 1, size pages 1
FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
————————————–
TABLE: name sakila/actor, id 0 14, columns 8, indexes 2, appr.rows 200
COLUMNS: actor_id: DATA_INT len 2 prec 0; first_name: type 12 len 135 prec 0; last_name: type 12 len 135 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 16, fields 1/6, type 3
root page 51, appr.key vals 200, leaf pages 1, size pages 1
FIELDS: actor_id DB_TRX_ID DB_ROLL_PTR first_name last_name last_update
INDEX: name idx_actor_last_name, id 0 17, fields 1/2, type 0
root page 52, appr.key vals 121, leaf pages 1, size pages 1
FIELDS: last_name actor_id
FOREIGN KEY CONSTRAINT sakila/fk_film_actor_actor: sakila/film_actor ( actor_id )
REFERENCES sakila/actor ( actor_id )
————————————–
–snip–
————————————–
TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
root page 207, appr.key vals 16305, leaf pages 53, size pages 97
FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
INDEX: name rental_date, id 0 45, fields 3/4, type 2
root page 208, appr.key vals 17655, leaf pages 28, size pages 29
FIELDS: rental_date inventory_id customer_id rental_id
INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
root page 210, appr.key vals 4467, leaf pages 16, size pages 17
FIELDS: inventory_id rental_id
INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
root page 211, appr.key vals 589, leaf pages 16, size pages 17
FIELDS: customer_id rental_id
INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
root page 212, appr.key vals 1, leaf pages 13, size pages 14
FIELDS: staff_id rental_id
FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id )
REFERENCES sakila/inventory ( inventory_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id )
REFERENCES sakila/customer ( customer_id )
FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id )
REFERENCES sakila/rental ( rental_id )
————————————–
TABLE: name sakila/staff, id 0 27, columns 15, indexes 3, appr.rows 1
COLUMNS: staff_id: DATA_INT len 1 prec 0; first_name: type 12 len 135 prec 0; last_name: type 12 len 135 prec 0; address_id: DATA_INT len 2 prec 0; picture: type 5 len 10 prec 0; email: type 12 len 150 prec 0; store_id: DATA_INT len 1 prec 0; active: DATA_INT len 1 prec 0; username: type 12 len 48 prec 0; password: type 12 len 120 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 49, fields 1/13, type 3
root page 213, appr.key vals 1, leaf pages 3, size pages 4
FIELDS: staff_id DB_TRX_ID DB_ROLL_PTR first_name last_name address_id picture email store_id active username password last_update
INDEX: name idx_fk_store_id, id 0 50, fields 1/2, type 0
root page 214, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: store_id staff_id
INDEX: name idx_fk_address_id, id 0 51, fields 1/2, type 0
root page 215, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: address_id staff_id
FOREIGN KEY CONSTRAINT sakila/fk_staff_store: sakila/staff ( store_id )
REFERENCES sakila/store ( store_id )
FOREIGN KEY CONSTRAINT sakila/fk_staff_address: sakila/staff ( address_id )
REFERENCES sakila/address ( address_id )
FOREIGN KEY CONSTRAINT sakila/fk_payment_staff: sakila/payment ( staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_store_staff: sakila/store ( manager_staff_id )
REFERENCES sakila/staff ( staff_id )
————————————–
TABLE: name sakila/store, id 0 28, columns 8, indexes 3, appr.rows 2
COLUMNS: store_id: DATA_INT len 1 prec 0; manager_staff_id: DATA_INT len 1 prec 0; address_id: DATA_INT len 2 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 52, fields 1/6, type 3
root page 216, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: store_id DB_TRX_ID DB_ROLL_PTR manager_staff_id address_id last_update
INDEX: name idx_unique_manager, id 0 53, fields 1/2, type 2
root page 217, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: manager_staff_id store_id
INDEX: name idx_fk_address_id, id 0 54, fields 1/2, type 0
root page 218, appr.key vals 2, leaf pages 1, size pages 1
FIELDS: address_id store_id
FOREIGN KEY CONSTRAINT sakila/fk_store_staff: sakila/store ( manager_staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_store_address: sakila/store ( address_id )
REFERENCES sakila/address ( address_id )
FOREIGN KEY CONSTRAINT sakila/fk_customer_store: sakila/customer ( store_id )
REFERENCES sakila/store ( store_id )
FOREIGN KEY CONSTRAINT sakila/fk_inventory_store: sakila/inventory ( store_id )
REFERENCES sakila/store ( store_id )
FOREIGN KEY CONSTRAINT sakila/fk_staff_store: sakila/staff ( store_id )
REFERENCES sakila/store ( store_id )
————————————–
TABLE: name test/t3, id 0 13, columns 6, indexes 1, appr.rows 1
COLUMNS: i: DATA_INT len 4 prec 0; j: DATA_VARCHAR prtype 1 len 4000 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name GEN_CLUST_INDEX, id 0 15, fields 0/5, type 1
root page 50, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i j
———————————–
END OF INNODB TABLE MONITOR OUTPUT
==================================

InnoDB uses clustered indexes, so the actual row data is stored along with the PRIMARY KEY (be this user, or synthetically generated if no PRIMARY KEY was specified). We are looking for a PRIMARY KEY in the above output that is around 96 pages long, and that has 53 pages used.

First, here is the actual table that we are looking for, sakila.rental:

TABLE: name sakila/rental, id 0 26, columns 11, indexes 5, appr.rows 16305
COLUMNS: rental_id: DATA_INT len 4 prec 0; rental_date: DATA_INT len 8 prec 0; inventory_id: DATA_INT len 3 prec 0; customer_id: DATA_INT len 2 prec 0; return_date: DATA_INT len 8 prec 0; staff_id: DATA_INT len 1 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
root page 207, appr.key vals 16305, leaf pages 53, size pages 97
FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update
INDEX: name rental_date, id 0 45, fields 3/4, type 2
root page 208, appr.key vals 17655, leaf pages 28, size pages 29
FIELDS: rental_date inventory_id customer_id rental_id
INDEX: name idx_fk_inventory_id, id 0 46, fields 1/2, type 0
root page 210, appr.key vals 4467, leaf pages 16, size pages 17
FIELDS: inventory_id rental_id
INDEX: name idx_fk_customer_id, id 0 47, fields 1/2, type 0
root page 211, appr.key vals 589, leaf pages 16, size pages 17
FIELDS: customer_id rental_id
INDEX: name idx_fk_staff_id, id 0 48, fields 1/2, type 0
root page 212, appr.key vals 1, leaf pages 13, size pages 14
FIELDS: staff_id rental_id
FOREIGN KEY CONSTRAINT sakila/fk_rental_staff: sakila/rental ( staff_id )
REFERENCES sakila/staff ( staff_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_inventory: sakila/rental ( inventory_id )
REFERENCES sakila/inventory ( inventory_id )
FOREIGN KEY CONSTRAINT sakila/fk_rental_customer: sakila/rental ( customer_id )
REFERENCES sakila/customer ( customer_id )
FOREIGN KEY CONSTRAINT sakila/fk_payment_rental: sakila/payment ( rental_id )
REFERENCES sakila/rental ( rental_id )

Among the wealth of obvious information here, for INDEX: name PRIMARY you can see:

INDEX: name PRIMARY, id 0 44, fields 1/9, type 3
root page 207, appr.key vals 16305, leaf pages 53, size pages 97
FIELDS: rental_id DB_TRX_ID DB_ROLL_PTR rental_date inventory_id customer_id return_date staff_id last_update

leaf pages is the number of used pages used by the clustered index

size pages is the overall number of pages allocated to the clustered index

Note: It is more difficult to correlate the relations between small tables (1 page allocated each for example). It would be nice if InnoDB were to expose unique IDs between the table and tablespace monitors.

This output also gives information on the internal data dictionary tables used by InnoDB, which are not exposed anywhere else:

————————————–
TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 22
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; FOR_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name ID_IND, id 0 11, fields 1/6, type 3
root page 46, appr.key vals 22, leaf pages 1, size pages 1
FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
INDEX: name FOR_IND, id 0 12, fields 1/2, type 0
root page 47, appr.key vals 11, leaf pages 1, size pages 1
FIELDS: FOR_NAME ID
INDEX: name REF_IND, id 0 13, fields 1/2, type 0
root page 48, appr.key vals 12, leaf pages 1, size pages 1
FIELDS: REF_NAME ID
————————————–
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 8, indexes 1, appr.rows 22
COLUMNS: ID: DATA_VARCHAR prtype 1 len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; REF_COL_NAME: DATA_VARCHAR prtype 1 len 0 prec 0; DB_ROW_ID: DATA_SYS prtype 8 len 6 prec 0; DB_TRX_ID: DATA_SYS prtype 8 len 6 prec 0; DB_ROLL_PTR: DATA_SYS prtype 8 len 7 prec 0;
INDEX: name ID_IND, id 0 14, fields 2/6, type 3
root page 49, appr.key vals 22, leaf pages 1, size pages 1
FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
————————————–

These are the tables that InnoDB uses to track FOREIGN KEY constraints internally.

Wow, with that lengthy post, I hope this gives people a little more insight in to how to find out whether your tables and tablespace are fragmented within InnoDB!