InnoDB Table and Tablespace Monitors

InnoDB allows you to dump information on it’s 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 extent.

In the following we will 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.

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!

Monitoring OS statistics with INFORMATION_SCHEMA plugins

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

With all the talk lately of the new INFORMATION_SCHEMA plugin API, I thought I’d have a go at making a couple. I’ve now made three different pluggable INFORMATION_SCHEMA tables – one that works from df -k – which will only work on UNIX like systems (other than AIX or HPUX) – and two which integrate the SIGAR library available from Hyperic.

I’ll post the df -k plugin in a couple of days, however I wanted to get the two I have created based on the SIGAR library ‘out to the world’ for some feedback on them! They are currently a very rough prototype (they need a little more work on return checking etc.!) – however they are currently functional.

There are two INFORMATION_SCHEMA tables within the plugin library – INFORMATION_SCHEMA.OS_STATUS and INFORMATION_SCHEMA.OS_VARIABLES – much like MySQL’s current (well, within 5.1) INFORMATION_SCHEMA.GLOBAL|SESSION_STATUS and INFORMATION_SCHEMA.GLOBAL|SESSION_VARIABLES tables.

Here’s some sample output:

mysql> install plugin os_status soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> install plugin os_variables soname 'libos_stats_info_schema.so';
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from os_variables;
+---------------------------+-------------------+----------------------------+---------------+
| VARIABLE_NAME             | VARIABLE_INSTANCE | VARIABLE_VALUE             | VARIABLE_TYPE |
+---------------------------+-------------------+----------------------------+---------------+
| Cpu_vendor                | cpu0              | Intel                      | VARCHAR       |
| Cpu_model                 | cpu0              | Xeon                       | VARCHAR       |
| Cpu_mhz                   | cpu0              | 3401                       | INTEGER       |
| Cpu_cache_size            | cpu0              | 1024                       | INTEGER       |
| Fs_name                   | /                 | /dev/md0                   | VARCHAR       |
| Fs_location               | /                 | local                      | VARCHAR       |
| Fs_type                   | /                 | ext3                       | VARCHAR       |
| Fs_name                   | /boot             | /dev/cciss/c0d0p1          | VARCHAR       |
| Fs_location               | /boot             | local                      | VARCHAR       |
| Fs_type                   | /boot             | ext3                       | VARCHAR       |
| Fs_name                   | /data0            | /dev/md10                  | VARCHAR       |
| Fs_location               | /data0            | local                      | VARCHAR       |
| Fs_type                   | /data0            | ext3                       | VARCHAR       |
| Fs_name                   | /users            | production:/usersnfs       | VARCHAR       |
| Fs_location               | /users            | remote                     | VARCHAR       |
| Fs_type                   | /users            | nfs                        | VARCHAR       |
| Fs_name                   | /nfstmp1          | nfssrva:/nfstmp1           | VARCHAR       |
| Fs_location               | /nfstmp1          | remote                     | VARCHAR       |
| Fs_type                   | /nfstmp1          | nfs                        | VARCHAR       |
| Rlimit_cpu_cur            |                   | 4294967295                 | INTEGER       |
| Rlimit_cpu_max            |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_cur      |                   | 4294967295                 | INTEGER       |
| Rlimit_file_size_max      |                   | 4294967295                 | INTEGER       |
| Rlimit_pipe_size_cur      |                   | 8                          | INTEGER       |
| Rlimit_pipe_size_max      |                   | 8                          | INTEGER       |
| Rlimit_data_cur           |                   | 4294967295                 | INTEGER       |
| Rlimit_data_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_stack_cur          |                   | 10240                      | INTEGER       |
| Rlimit_stack_max          |                   | 4294967295                 | INTEGER       |
| Rlimit_core_cur           |                   | 0                          | INTEGER       |
| Rlimit_core_max           |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_cur         |                   | 4294967295                 | INTEGER       |
| Rlimit_memory_max         |                   | 4294967295                 | INTEGER       |
| Rlimit_processes_cur      |                   | 16383                      | INTEGER       |
| Rlimit_processes_max      |                   | 16383                      | INTEGER       |
| Rlimit_open_files_cur     |                   | 1024                       | INTEGER       |
| Rlimit_open_files_max     |                   | 1024                       | INTEGER       |
| Rlimit_virtual_memory_cur |                   | 4294967295                 | INTEGER       |
| Rlimit_virtual_memory_max |                   | 4294967295                 | INTEGER       |
| Os_name                   |                   | Linux                      | VARCHAR       |
| Os_version                |                   | 2.6.9-22.ELsmp             | VARCHAR       |
| Os_architecture           |                   | i686                       | VARCHAR       |
| Os_machine                |                   | i686                       | VARCHAR       |
| Os_description            |                   | Red Hat Enterprise Linux 4 | VARCHAR       |
| Os_patch_level            |                   | unknown                    | VARCHAR       |
| Os_vendor                 |                   | Red Hat                    | VARCHAR       |
| Os_vendor_version         |                   | Enterprise Linux 4         | VARCHAR       |
| Os_vendor_name            |                   | Linux                      | VARCHAR       |
| Os_vendor_code_name       |                   | Nahant Update 2            | VARCHAR       |
+---------------------------+-------------------+----------------------------+---------------+
49 rows in set (0.01 sec)

mysql> select * from os_status;
+-------------------------+-------------------+----------------------+---------------+
| VARIABLE_NAME           | VARIABLE_INSTANCE | VARIABLE_VALUE       | VARIABLE_TYPE |
+-------------------------+-------------------+----------------------+---------------+
| Memory_total            |                   | 1059471360           | INTEGER       |
| Memory_used             |                   | 682172416            | INTEGER       |
| Memory_free             |                   | 377298944            | INTEGER       |
| Swap_total              |                   | 1002676224           | INTEGER       |
| Swap_used               |                   | 121491456            | INTEGER       |
| Swap_free               |                   | 881184768            | INTEGER       |
| Cpu_user_total          |                   | 695644430            | INTEGER       |
| Cpu_sys_total           |                   | 273173250            | INTEGER       |
| Cpu_nice_total          |                   | 14360                | INTEGER       |
| Cpu_idle_total          |                   | 46569287330          | INTEGER       |
| Cpu_wait_total          |                   | 809380330            | INTEGER       |
| Cpu_total_total         |                   | 48347499700          | INTEGER       |
| Cpu_user                | cpu0              | 695644430            | INTEGER       |
| Cpu_sys                 | cpu0              | 273173250            | INTEGER       |
| Cpu_nice                | cpu0              | 14350                | INTEGER       |
| Cpu_idle                | cpu0              | 46569287330          | INTEGER       |
| Cpu_wait                | cpu0              | 809380330            | INTEGER       |
| Cpu_total               | cpu0              | 48347499690          | INTEGER       |
| Load_average            | 0                 | 0.05                 | DECIMAL       |
| Load_average            | 1                 | 0.04                 | DECIMAL       |
| Load_average            | 2                 | 0.01                 | DECIMAL       |
| Fs_size                 | /                 | 9618148              | INTEGER       |
| Fs_free                 | /                 | 5002764              | INTEGER       |
| Fs_used                 | /                 | 4615384              | INTEGER       |
| Fs_avail                | /                 | 4514188              | INTEGER       |
| Fs_files                | /                 | 1221600              | INTEGER       |
| Fs_disk_reads           | /                 | 20785383             | INTEGER       |
| Fs_disk_read_bytes      | /                 | 176143946752         | INTEGER       |
| Fs_disk_writes          | /                 | 625659222            | INTEGER       |
| Fs_disk_write_bytes     | /                 | 363676917760         | INTEGER       |
| Fs_disk_queue           | /                 | 0                    | INTEGER       |
| Fs_size                 | /boot             | 98747                | INTEGER       |
| Fs_free                 | /boot             | 87464                | INTEGER       |
| Fs_used                 | /boot             | 11283                | INTEGER       |
| Fs_avail                | /boot             | 82365                | INTEGER       |
| Fs_files                | /boot             | 25584                | INTEGER       |
| Fs_disk_reads           | /boot             | 2053                 | INTEGER       |
| Fs_disk_read_bytes      | /boot             | 6922240              | INTEGER       |
| Fs_disk_writes          | /boot             | 350                  | INTEGER       |
| Fs_disk_write_bytes     | /boot             | 361472               | INTEGER       |
| Fs_disk_queue           | /boot             | 18446744073709551615 | INTEGER       |
| Fs_size                 | /data0            | 19228180             | INTEGER       |
| Fs_free                 | /data0            | 18804340             | INTEGER       |
| Fs_used                 | /data0            | 423840               | INTEGER       |
| Fs_avail                | /data0            | 17827596             | INTEGER       |
| Fs_files                | /data0            | 2443200              | INTEGER       |
| Fs_disk_reads           | /data0            | 13058321             | INTEGER       |
| Fs_disk_read_bytes      | /data0            | 485011117056         | INTEGER       |
| Fs_disk_writes          | /data0            | 68639632             | INTEGER       |
| Fs_disk_write_bytes     | /data0            | 281147932672         | INTEGER       |
| Fs_disk_queue           | /data0            | 0                    | INTEGER       |
| Fs_size                 | /users            | 373057344            | INTEGER       |
| Fs_free                 | /users            | 43121216             | INTEGER       |
| Fs_used                 | /users            | 329936128            | INTEGER       |
| Fs_avail                | /users            | 24170944             | INTEGER       |
| Fs_files                | /users            | 47382528             | INTEGER       |
| Fs_size                 | /nfstmp1          | 307583488            | INTEGER       |
| Fs_free                 | /nfstmp1          | 179642368            | INTEGER       |
| Fs_used                 | /nfstmp1          | 127941120            | INTEGER       |
| Fs_avail                | /nfstmp1          | 164017952            | INTEGER       |
| Fs_files                | /nfstmp1          | 39075840             | INTEGER       |
| Mysqld_threads          | mysqld            | 10                   | INTEGER       |
| Mysqld_mem_size         | mysqld            | 128045056            | INTEGER       |
| Mysqld_mem_resident     | mysqld            | 26636288             | INTEGER       |
| Mysqld_mem_share        | mysqld            | 5382144              | INTEGER       |
| Mysqld_mem_minor_faults | mysqld            | 8926                 | INTEGER       |
| Mysqld_mem_major_faults | mysqld            | 7                    | INTEGER       |
| Mysqld_mem_page_faults  | mysqld            | 8933                 | INTEGER       |
| Mysqld_cpu_user         | mysqld            | 440                  | INTEGER       |
| Mysqld_cpu_sys          | mysqld            | 180                  | INTEGER       |
| Mysqld_cpu_total        | mysqld            | 620                  | INTEGER       |
| Uptime                  |                   | 24173401.250000      | DECIMAL       |
+-------------------------+-------------------+----------------------+---------------+
72 rows in set (0.01 sec)

If you have multiple CPUs then you will have multiple returns for variables such as ‘Cpu_user’, ‘Cpu_nice’ – labelled cpu0, cpu1 etc. – just like there is with the ‘Fs*’ variables – specific to each CPU.

You can download a copy of the plugin code from here. Build and installation instructions are within the INSTALL file.

All feedback welcome!

MySQL 5.0 – INFORMATION_SCHEMA

Arjen Lentz (“Community Relations Manager” for MySQL AB) posted “The MySQL 5.0 Beta Challenge” on his blog today – so in the spirit of community I decided to give it a crack, by putting together some posts on the new features of MySQL 5.0 up here to my humble blog. Who knows – I may win a mug! Then I can have a list of ALL the people to bug over at MySQL AB!

I’m a DBA at heart, so whilst I’ll take a look at the new features that are available for developers, I’m going to start from the DBA tack, and take a look at INFORMATION_SCHEMA.

So what is the INFORMATION_SCHEMA? Those that have used other RDBMS’ in the past will recognize it as MySQL’s “Data Dictionary”. An RDBMS Data Dictionary is a collection of tables or views (that appear as tables), that utilize the database metadata to give easy access to information about the objects (tables, indexes, procedures, functions, views etc.) within the database, as well as information on things such security/privileges, performance statistics, auditing trails etc.

On installing MySQL 5.0, and issuing “SHOW DATABASES;” you will immediately see “information_schema” listed. The INFORMATION_SCHEMA is implemented to look like a database in it’s own right. However, it doesn’t conform with the conventional database privilege system. You can “USE” it, but you can’t run any type of DML against it. GRANT does seem to work against it, but attempting to run a DELETE results in an error.

mysql> use information_schema;
Database changed
mysql> show grants;
+------------------------------
| Grants for mark@localhost                                            |
+------------------------------
| GRANT USAGE ON *.* TO 'mark'@'localhost'                             |
| GRANT SELECT, DELETE ON `information_schema`.* TO 'mark'@'localhost' |
+------------------------------
2 rows in set (0.00 sec)

mysql> delete from information_schema.tables;
ERROR 1288 (HY000): The target table tables of the DELETE is not updatable

“What good are they to me?”

Well, one question I’ve seen a lot around IRC is “Can I use the output of a SHOW command in a statement, such as a subquery?”. This mostly comes down to something to do with SHOW TABLES – getting databases sizes, table sizes etc. SHOW TABLES still works in 5.0, as do all other SHOW commands, however, INFORMATION_SCHEMA.TABLES now also parses the exact same output. Think of it of as view (more on these later) against the SHOW TABLES command. The great thing about this is we can now use conventional SQL to use these values – we no longer have to write external scripts to do what SUM() can do, for example. So here’s one for those guys on IRC that want to find out how big their databases are from SQL:

SELECT s.schema_name,
       CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),'Mb') total_size,
       CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),'Mb') data_used,
       CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),'Mb') data_free,
       IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used,
       COUNT(table_name) total_tables
  FROM information_schema.schemata s
LEFT JOIN information_schema.tables t ON s.schema_name = t.table_schema
 WHERE s.schema_name != 'information_schema'
GROUP BY s.schema_name
ORDER BY pct_used DESCG

*****************1. row **************
 schema_name: test
  total_size: 0.06Mb
   data_used: 0.06Mb
   data_free: 0.00Mb
    pct_used: 100.00
total_tables: 2
*****************2. row **************
 schema_name: flightstats
  total_size: 2950.14Mb
   data_used: 2949.71Mb
   data_free: 0.43Mb
    pct_used: 99.99
total_tables: 81
...

So now we can get the information we want, in the format we want. We can also JOIN the INFORMATION_SCHEMA tables to create better reports – such as above, if there are no actual tables in a database, they won’t be shown within INFORMATION_SCHEMA.TABLES, so to get a full report on all databases, we LEFT JOIN from INFORMATION_SCHEMA.SCHEMATA.

This also leads us in to getting helpful information on any object optimizations easily. Perhaps you have performed an archive of a lot of data, and want to reclaim freespace. You want to find all tables with > 40% of the datafile free, so that you can perform an OPTIMIZE TABLE against them:

SELECT table_name,
       ROUND(((t.data_length+t.index_length)-t.data_free)/(t.data_length+t.index_length)*100) pct_used
  FROM information_schema.tables t
 WHERE table_schema = 'flightstats'
HAVING pct_used < 60
ORDER BY table_name;</blockquote>

Let’s take this one step further – now we can actually use MySQL to generate the scripts that we’ve had to use an external scripting language to achieve. Let’s focus on another question often asked on IRC – “How do I drop all my tables, without dropping my database?” – previous to MySQL 5.0 the answer is “get table_name from the output of SHOW TABLES, and loop through this with your scripting language of choice, running a DROP TABLE command for each one”.

Now let’s use the INFORMATION_SCHEMA, and SQL to do the job for us:

SELECT CONCAT('DROP TABLE ',table_name,';')
  INTO OUTFILE '/sql/drop_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'test';

Now all we have to do is “SOURCE /sql/drop_tables.sql;” and all of the tables in the test database will be gone, and we didn’t have to leave the mysql command line interface! Don’t try this on your production schemas kids!

Or to go back to our OPTIMIZE TABLE:

SELECT CONCAT('OPTIMIZE TABLE ',table_name,';')
  INTO OUTFILE '/sql/optimize_tables.sql'
  FROM information_schema.tables
 WHERE table_schema = 'flightstats'
AND ROUND(((data_length+index_length)-data_free)/(data_length+index_length)*100) < 60;<

This makes life a lot easier! There are a number of other INFORMATION_SCHEMA tables, but I won’t go in to all of those now. We can use these to get information on grants, build grant scripts, get information about indexes, columns, foreign keys etc. For instance – it’s probably possible now to perform “fuzzy” foreign key matches – searching for columns that are named the same within different tables, that may not necessarily be the InnoDB engine. As I put together more scripts in readiness for 5.0, I’ll post them here, and to the other site that I run – SQLDump.co.uk (which I haven’t really started filling yet!! But it’s wiki! ;))

It should be noted that all users have access to the INFORMATION_SCHEMA. They will however only be able to see the objects for which they have some privileges on.