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!

11 comments

  1. Hi ,

    I am new to mysql db. As you mentioned above I have created two tables (innodb_moniter and innodb_tablespace_moniter), but giving select statement to those table I am not getting any result as u mentioned above. Can you please guide me here.

  2. Hey Prakash,

    Unfortunately they do not currently fill the tables, they just cause InnoDB to dump all of the information to MySQL’s error log:

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

    However, this will be changing very soon – in the new 5.5.5-m3 release, there are a number of new tables available within the INFORMATION_SCHEMA database:

    mysql> show tables like ‘innodb_sys%';
    +——————————————–+
    | Tables_in_information_schema (innodb_sys%) |
    +——————————————–+
    | INNODB_SYS_FIELDS |
    | INNODB_SYS_INDEXES |
    | INNODB_SYS_TABLESTATS |
    | INNODB_SYS_COLUMNS |
    | INNODB_SYS_FOREIGN_COLS |
    | INNODB_SYS_FOREIGN |
    | INNODB_SYS_TABLES |
    +——————————————–+
    7 rows in set (0.00 sec)

    mysql> select * from innodb_sys_tables;
    +———-+——————+——+——–+——-+
    | TABLE_ID | NAME | FLAG | N_COLS | SPACE |
    +———-+——————+——+——–+——-+
    | 11 | SYS_FOREIGN | 0 | 7 | 0 |
    | 12 | SYS_FOREIGN_COLS | 0 | 7 | 0 |
    | 13 | test/t1 | 1 | 4 | 1 |
    +———-+——————+——+——–+——-+
    3 rows in set (0.00 sec)

    mysql> select * from innodb_sys_indexes;
    +———-+—————–+———-+——+———-+———+——-+
    | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
    +———-+—————–+———-+——+———-+———+——-+
    | 11 | ID_IND | 11 | 3 | 1 | 302 | 0 |
    | 12 | FOR_IND | 11 | 0 | 1 | 303 | 0 |
    | 13 | REF_IND | 11 | 0 | 1 | 304 | 0 |
    | 14 | ID_IND | 12 | 3 | 2 | 305 | 0 |
    | 15 | GEN_CLUST_INDEX | 13 | 1 | 0 | 3 | 1 |
    +———-+—————–+———-+——+———-+———+——-+
    5 rows in set (0.00 sec)

    etc.

  3. Currently I just want to know, How I will calculate the used and free space for the InnoDB tablespace (Both global and per_table table spaces).

    rgds
    Prakash

  4. Hi Mark, I’m using innodb_file_per_table. I created the innodb_table_monitor table and then look at error log and compared with file system files:

    TABLE: name my_schema/innodb_table_monitor, id 0 266, flags 1, columns 4, indexes 1, appr.rows 0
    COLUMNS: a: DATA_INT DATA_BINARY_TYPE len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;
    INDEX: name GEN_CLUST_INDEX, id 0 406, fields 0/4, uniq 1, type 1
    root page 3, appr.key vals 0, leaf pages 1, size pages 1
    FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR a

    -rw-rw—- 1 mysql mysql 8554 Oct 21 23:03 innodb_table_monitor.frm
    -rw-rw—- 1 mysql mysql 98304 Oct 21 23:03 innodb_table_monitor.ibd

    How can I estimate a value of 98304 bytes?
    All what I see is:
    column a = 4 bytes
    DB_ROW_ID = 6 bytes
    DB_TRX_ID = 6 bytes
    DB_ROLL_PTR = 7 bytes

    I know space is allocated on 16K pages chunks (Correct me here if I’m wrong). This table seems to be 6 * 16K. How can I figure out how estimate a file table size for n number of rows?
    THANKS!

  5. Hey Rick,

    Yes, space is in 16K pages – as I say above:

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

    As for the space differences you see above, this is just due to the initial overhead when creating each table, we have at least one header page, and one reserved page, before the actual index structure starts. You can see this with the “root page” of GEN_CLUST_INDEX being 3.

    When the tables are initially created they are created with 4 pages – this is a hard coded size:

    ./include/fil0fil.h:44:#define FIL_IBD_FILE_INITIAL_SIZE 4

    Next, when we create the primary key, we also reserve two pages in the file space for growth, which immediately extends the per tablespace file by another 2 pages.

    This is actually unaccounted for in the innodb_table_monitor output, but is accounted for in the innodb_tablespace_monitor output, with the “res” count. The corresponding “used” from the tablespace monitor is what is shown for “size pages” in the table monitor. Unfortunately, the innodb_tablespace_monitor does not show data for the ibd files.

    With larger tables, with data in them, it is much easier to account for the space, but still not a totally exact science – you may see a few pages drift.

    Hopefully we can make this accounting better with the new metadata tables.

  6. Hi Mark,

    Your post in Aug 2010 indicated there would be some INFORMATION_SCHEMA.INNODB_SYS_XXX tables, yet in 5.5.13 I don’t see these. I guess those tables didn’t make it into the final GA 5.5 version? Perhaps it’s handy to clarify that here is this page is referenced in the MySQL manual and people may get confused. Or do we need to do something special to make these tables “appear” ?

  7. Hi Mark,

    Thanks for the detailed information. Its very helpful.

    From the documentation regarding enabling Innodb monitors, I can see “output generation does result in some performance decrement”
    http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html

    Do you think there can be the same performance decrement if we don’t enable the Innodb monitors (by not creating the monitor tables), and just get the output of SHOW ENGINE INNODB STATUS into a file at regular schedules.
    The purpose is to have regular snapshot information which can be used for analysis and comparison later when required. Just like we have in oracle with snapshot and AWR reports.

    Thanks

    • Hey Bobby,

      If you want the output of SHOW ENGINE INNODB STATUS written to a file regularly, then that is exactly what the “innodb_status_file” variable does.. There is very very little overhead to this, and you can then process it regularly with your own scripts/tools. From the page you mentioned:

      “InnoDB sends diagnostic output to stderr or to files rather than to stdout or fixed-size memory buffers, to avoid potential buffer overflows. As a side effect, the output of SHOW ENGINE INNODB STATUS is written to a status file in the MySQL data directory every fifteen seconds. The name of the file is innodb_status.pid, where pid is the server process ID. InnoDB removes the file for a normal shutdown. If abnormal shutdowns have occurred, instances of these status files may be present and must be removed manually. Before removing them, you might want to examine them to see whether they contain useful information about the cause of abnormal shutdowns. The innodb_status.pid file is created only if the configuration option innodb-status-file=1 is set.”

      The warning on overhead is more from things like the lock monitor printing, which can result in far more data being captured. Also, there is no worry with error log sizes when using the status file, as the file is just overwritten every 15 seconds, rather than printing a status snapshot regularly to the error log, as is the case when you create the innodb_monitor table.

Got something to say?