-
Notifications
You must be signed in to change notification settings - Fork 49
innodb
Frédéric Descamps edited this page Mar 10, 2021
·
4 revisions
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/
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
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% |
+--------------+------------+-------------+-----------+-------------+-------------+-----------+----------------+----------------------+
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%)
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.
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.
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% |
+----------------+-------+------------------+------------------+--------+