Skip to content
Frédéric Descamps edited this page Mar 10, 2021 · 4 revisions

innodb

NAME
      innodb - InnoDB management and utilities.

DESCRIPTION
      A collection of InnoDB management tools and related utilities that work
      on InnoDB Engine

More info on https://lefred.be/content/mysql-shell-plugins-innodb/

methods

getAlterProgress

Prints InnoDB Alter progress info

innodb.getAlterProgress([format][, session])
  • format: String. Output format (default is table)
  • session: Object. The session to be used on the operation.
 JS  innodb.getAlterProgress()
The consumer for 'events_stages_current' is not enabled, 
do you want to enabled it now ? (y/N) y

 JS  innodb.getAlterProgress()
+-----------+----------------------------------------------+------------------------------------------------------+----------------+----------------+--------------+------------+-----------+
| THREAD_ID | SQL_TEXT                                     | State                                                | WORK_COMPLETED | WORK_ESTIMATED | CompletedPct | StartedAgo | Memory    |
+-----------+----------------------------------------------+------------------------------------------------------+----------------+----------------+--------------+------------+-----------+
|        51 | alter table pktest add index name_idx2(name) | stage/innodb/alter table (read PK and internal sort) |           3980 |          58639 |        6.79% |     2.42 s | 20.05 MiB |
+-----------+----------------------------------------------+------------------------------------------------------+----------------+----------------+--------------+------------+-----------+

 JS  innodb.getAlterProgress('vertical')
*************************** 1. row ***************************
     THREAD_ID: 51
      SQL_TEXT: alter table pktest add index name_idx2(name)
         State: stage/innodb/alter table (read PK and internal sort)
WORK_COMPLETED: 22874
WORK_ESTIMATED: 58639
  CompletedPct:       39.01%
    StartedAgo:    10.27 s
        Memory: 22.10 MiB

getAutoincFill

Prints information about auto_increment fill up

innodb.getAutoincFill([percentage][, schema][, session])
  • percentage: Integer. Only shows the tables where auto increments values are filled to at least % (default is 50)
  • schema: String. Perform the check only in that specific schema
  • session: Object. The session to be used on the operation.

More info at https://lefred.be/content/mysql-keep-an-eye-on-your-auto_increment-values/

 JS  innodb.getAutoincFill()
Warning: information_schema_stats_expiry is set to 86400.
Do you want to change it ? (y/N) y
+--------------+------------+-------------+-----------+-------------+-------------+-----------+----------------+----------------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | COLUMN_TYPE | IS_UNSIGNED | MAX_VALUE | AUTO_INCREMENT | AUTO_INCREMENT_RATIO |
+--------------+------------+-------------+-----------+-------------+-------------+-----------+----------------+----------------------+
| imdb         | fillme     | id          | tinyint   | tinyint     |           0 |       127 |            127 | 100%                 |
+--------------+------------+-------------+-----------+-------------+-------------+-----------+----------------+----------------------+

getCheckpointAge

Returns the InnoDB Checkpoint Age

innodb.getCheckpointAge([session])
  • session: Object. The session to be used on the operation.
 JS  innodb.getCheckpointAge()
InnoDB is using 2 files of 48.00 MiB
InnoDB Checkpoint Age:    56.44 MiB / 96.00 MiB (62.61%)

getFragmentedTables

Prints InnoDB fragmented tables

innodb.getFragmentedTables([percent][, session])
  • percent: Integer. Amount of free space to be considered as fragmented
  • session: Object. The session to be used on the operation.
 JS  innodb.getFragmentedTables()
Warning: information_schema_stats_expiry is set to 86400.
Do you want to change it ? (y/N) y
+------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
| TABLE      | ENGINE | ROWS  | DATA  | IDX   | TOTAL SIZE | IDXFRAC | data_free | data_free_pct |
+------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
| big.pktest | InnoDB | 0.00M | 0.22G | 0.23G | 0.45G      |    1.03 | 171.00MB  | (37.52%)      |
+------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
Don't forget to run 'ANALYZE TABLE ...' for a more accurate result.

getFragmentedTablesDisk

Prints InnoDB fragmented tables with disk info

innodb.getFragmentedTablesDisk([percent][, session])
  • percent: Integer. Amount of free space to be considered as fragmented
  • session: Object. The session to be used on the operation.
 JS  innodb.getFragmentedTablesDisk()
Warning: information_schema_stats_expiry is set to 86400.
Do you want to change it ? (y/N) y
+-----------------------+----------+-------------+------------+------------+------------+------------+-------------+--------+
| NAME                  | ROWS     | DATA_SIZE   | INDEX_SIZE | TOTAL_SIZE | DATA_FREE  | FILE_SIZE  | WASTED_SIZE | FREE   |
+-----------------------+----------+-------------+------------+------------+------------+------------+-------------+--------+
| imdb/movie_info       |  8611299 | 1006.98 MiB | 188.73 MiB | 1.17 GiB   |    0 bytes | 1.33 GiB   | 164.28 MiB  | 12.08% |
| imdb/cast_info        | 19974383 | 942.00 MiB  |    0 bytes | 942.00 MiB |    0 bytes | 2.23 GiB   | 1.31 GiB    | 58.76% |
| imdb/person_info      |  2226438 | 291.78 MiB  |    0 bytes | 291.78 MiB |    0 bytes | 352.00 MiB | 60.22 MiB   | 17.11% |
| imdb/name             |  2748686 | 153.67 MiB  |    0 bytes | 153.67 MiB |    0 bytes | 344.00 MiB | 190.33 MiB  | 55.33% |
| imdb/char_name        |  2217027 | 111.62 MiB  |    0 bytes | 111.62 MiB |    0 bytes | 192.00 MiB | 80.38 MiB   | 41.86% |
| imdb/movie_companies  |  1877204 | 96.62 MiB   |    0 bytes | 96.62 MiB  |    0 bytes | 112.00 MiB | 15.38 MiB   | 13.73% |
| imdb/title            |  1416503 | 93.62 MiB   |    0 bytes | 93.62 MiB  |    0 bytes | 132.00 MiB | 38.38 MiB   | 29.07% |
| imdb/movie_keyword    |  2661313 | 83.59 MiB   |    0 bytes | 83.59 MiB  |    0 bytes | 96.00 MiB  | 12.41 MiB   | 12.92% |
| imdb/movie_info_idx   |   933179 | 36.56 MiB   |    0 bytes | 36.56 MiB  |    0 bytes | 44.00 MiB  | 7.44 MiB    | 16.90% |
| imdb/movie_link       |   920920 | 33.56 MiB   |    0 bytes | 33.56 MiB  |    0 bytes | 44.00 MiB  | 10.44 MiB   | 23.72% |
| employees/titles      |   442486 | 19.56 MiB   |    0 bytes | 19.56 MiB  |    0 bytes | 27.00 MiB  | 7.44 MiB    | 27.55% |
| docstore/restaurants  |    24353 | 17.56 MiB   |    0 bytes | 17.56 MiB  |    0 bytes | 25.00 MiB  | 7.44 MiB    | 29.75% |
| employees/dept_emp    |   331143 | 11.52 MiB   | 5.52 MiB   | 17.03 MiB  |    0 bytes | 25.00 MiB  | 7.97 MiB    | 31.88% |
| imdb/company_name     |   240036 | 15.52 MiB   |    0 bytes | 15.52 MiB  |    0 bytes | 23.00 MiB  | 7.48 MiB    | 32.54% |
| employees/employees   |   299335 | 14.52 MiB   |    0 bytes | 14.52 MiB  |    0 bytes | 22.00 MiB  | 7.48 MiB    | 34.02% |
| big/pktest_seq        |    81880 | 6.52 MiB    |    0 bytes | 6.52 MiB   |    0 bytes | 14.00 MiB  | 7.48 MiB    | 53.46% |
| imdb/complete_cast    |    97356 | 4.52 MiB    |    0 bytes | 4.52 MiB   |    0 bytes | 12.00 MiB  | 7.48 MiB    | 62.37% |
| imdb/keyword          |    88061 | 4.52 MiB    |    0 bytes | 4.52 MiB   |    0 bytes | 12.00 MiB  | 7.48 MiB    | 62.37% |
| sbtest/sbtest1        |     9936 | 2.52 MiB    | 1.09 MiB   | 3.61 MiB   |    0 bytes | 12.00 MiB  | 8.39 MiB    | 69.92% |
| world/city            |     4188 | 400.00 KiB  | 128.00 KiB | 528.00 KiB |    0 bytes | 848.00 KiB | 320.00 KiB  | 37.74% |
| library/books         |       39 | 384.00 KiB  | 16.00 KiB  | 400.00 KiB |    0 bytes | 464.00 KiB | 64.00 KiB   | 13.79% |
| world/countrylanguage |      984 | 96.00 KiB   | 64.00 KiB  | 160.00 KiB |    0 bytes | 240.00 KiB | 80.00 KiB   | 33.33% |
| tvshows/shows_wd      |      131 | 144.00 KiB  | 16.00 KiB  | 160.00 KiB |    0 bytes | 272.00 KiB | 112.00 KiB  | 41.18% |
| clusterdemo/demo      |     1608 | 80.00 KiB   | 48.00 KiB  | 128.00 KiB |    0 bytes | 240.00 KiB | 112.00 KiB  | 46.67% |
| world/country         |      239 | 96.00 KiB   |    0 bytes | 96.00 KiB  |    0 bytes | 208.00 KiB | 112.00 KiB  | 53.85% |
+-----------------------+----------+-------------+------------+------------+------------+------------+-------------+--------+
Don't forget to run 'ANALYZE TABLE ...' for a more accurate result.

getTablesInBP

Prints Tables in BP with some statistics

 JS  innodb.getTablesInBP()
Processing, this can take a while (don't forget to run ANALYZE TABLE for accurate results)...
InnoDB Buffer Pool Size = 128.00 MiB (1 instance)
+----------------+-------+------------------+------------------+--------+
| Table Name     | Pages | Total Data in BP | Total Table Size | in BP  |
+----------------+-------+------------------+------------------+--------+
| `big`.`pktest` |  2327 | 36.36 MiB        | 455.78 MiB       |  7.98% |
+----------------+-------+------------------+------------------+--------+
Clone this wiki locally