-
Notifications
You must be signed in to change notification settings - Fork 48
check
Frédéric Descamps edited this page Oct 21, 2021
·
9 revisions
NAME
check - Check management and utilities.
DESCRIPTION
A collection of Check management tools and utilities
You can find more information on the check plugin on lefred.be:
- https://lefred.be/content/mysql-shell-plugins-audit/
- https://lefred.be/content/mysql-shell-plugins-check-part-2/
- https://lefred.be/content/mysql-shell-plugins-check-part-3/
Prints a summary of the amount of DDL statements performed since server start
JS check.getAmountDDL()
+----------------------------+------------+------------+
| event_name | count_star | sum_errors |
+----------------------------+------------+------------+
| statement/sql/create_table | 13 | 0 |
| statement/sql/create_db | 3 | 0 |
| statement/sql/drop_db | 6 | 3 |
| statement/sql/create_view | 22 | 1 |
| statement/sql/drop_view | 22 | 1 |
+----------------------------+------------+------------+
Prints the list of binary logs available on the server
JS check.getBinlogs()
Binary log file(s) present:
mysql1-bin.000001
mysql1-bin.000002
mysql1-bin.000003
Prints the IO statistics of binary logs files available on the server
JS check.getBinlogsIO()
+-----------------------------+------------+------------+------------+-------------+---------------+------------+------------+-----------+
| file | count_read | total_read | avg_read | count_write | total_written | avg_write | total | write_pct |
+-----------------------------+------------+------------+------------+-------------+---------------+------------+------------+-----------+
| @@datadir/mysql1-bin.000001 | 3 | 5.77 KiB | 1.92 KiB | 0 | 0 bytes | 0 bytes | 5.77 KiB | 0.00 |
| @@datadir/mysql1-bin.000002 | 6 | 3.80 KiB | 649 bytes | 1 | 1 bytes | 1 bytes | 3.80 KiB | 0.03 |
| @@datadir/mysql1-bin.000003 | 14 | 77.42 KiB | 5.53 KiB | 114 | 63.91 KiB | 574 bytes | 141.33 KiB | 45.22 |
| @@datadir/mysql1-bin.index | 4 | 240 bytes | 60 bytes | 0 | 0 bytes | 0 bytes | 240 bytes | 0.00 |
+-----------------------------+------------+------------+------------+-------------+---------------+------------+------------+-----------+
total_latency: 804.53 ms count_read: 35 (91.71 KiB) count_write: 116 (64.06 KiB)
Prints all foreign keys with cascading constraints
JS check.getCascadingFK()
+----------------------+---------------------+---------------------+-------------+-------------+
| foreign key | references | constraint name | UPDATE_RULE | DELETE_RULE |
+----------------------+---------------------+---------------------+-------------+-------------+
| dept_manager.emp_no | employees.emp_no | dept_manager_ibfk_1 | NO ACTION | CASCADE |
| dept_manager.dept_no | departments.dept_no | dept_manager_ibfk_2 | NO ACTION | CASCADE |
| dept_emp.emp_no | employees.emp_no | dept_emp_ibfk_1 | NO ACTION | CASCADE |
| dept_emp.dept_no | departments.dept_no | dept_emp_ibfk_2 | NO ACTION | CASCADE |
| titles.emp_no | employees.emp_no | titles_ibfk_1 | NO ACTION | CASCADE |
| salaries.emp_no | employees.emp_no | salaries_ibfk_1 | NO ACTION | CASCADE |
+----------------------+---------------------+---------------------+-------------+-------------+
Prints the queries performing full table scans
The method can take 4 parameters:
check.getFullTableScanQuery([limit][, select][, schema][,session])
- limit: Integer. The amount of query to return. Default is 1.
- select: Bool. Returns queries only with SELECT.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation
JS check.getFullTableScanQuery()
*************************** 1. row ***************************
schema_name: test
sum_rows_examined: 21
avg_rows_call: 21.0000
tot_lat: 98.20 ms
exec_count: 1
latency_per_call: 98.20 ms
first_seen: 2020-07-31 11:51:16.423903
last_seen: 2020-07-31 11:51:16.423903
query_sample_text: select * from users
Do you want to have EXPLAIN output? (y/N) y
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 18
filtered: 100
Extra: NULL
Do you want to have EXPLAIN in JSON format output? (y/N) y
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.80"
},
"table": {
"table_name": "users",
"access_type": "ALL",
"rows_examined_per_scan": 18,
"rows_produced_per_join": 18,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "1.80",
"prefix_cost": "2.80",
"data_read_per_join": "576"
},
"used_columns": [
"user_id",
"user_name",
"salary"
]
}
}
}
Do you want to have EXPLAIN in TREE format output? (y/N) y
*************************** 1. row ***************************
EXPLAIN: -> Table scan on users (cost=2.80 rows=18)
Do you want to have EXPLAIN ANALYZE output? (y/N) y
*************************** 1. row ***************************
EXPLAIN: -> Table scan on users (cost=2.80 rows=18) (actual time=0.041..0.067 rows=21 loops=1)
Prints all InnoDB tables not having a Primary Key or a non NULL unique key
JS check.getInnoDBTablesWithNoPK()
+--------------+--------------+--------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | SIZE |
+--------------+--------------+--------+------------+------------+
| test | default_test | InnoDB | 0 | 16.00 KiB |
| big | testing | InnoDB | 18086645 | 1.05 GiB |
| big | testing2 | InnoDB | 8658846 | 492.00 MiB |
| test | nodes | InnoDB | 20 | 16.00 KiB |
| test | edges | InnoDB | 40 | 16.00 KiB |
| test | numerics | InnoDB | 2 | 16.00 KiB |
| test | t3 | InnoDB | 2 | 16.00 KiB |
| tvshows | adeol | InnoDB | 12 | 16.00 KiB |
| world | orders | InnoDB | 0 | 16.00 KiB |
| world | sales | InnoDB | 0 | 16.00 KiB |
| test | users | InnoDB | 18 | 16.00 KiB |
| test | zhang | InnoDB | 4 | 16.00 KiB |
| test | t5 | InnoDB | 2 | 16.00 KiB |
| test | t_least | InnoDB | 0 | 16.00 KiB |
| test | sampletest | InnoDB | 0 | 16.00 KiB |
+--------------+--------------+--------+------------+------------+
Prints all tables not using InnoDB Storage Engine
JS check.getNonInnoDBTables()
+--------------+------------+--------+------------+----------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE | TABLE_ROWS | SIZE |
+--------------+------------+--------+------------+----------+
| forums | Some_Table | MyISAM | 3 | 2.10 KiB |
+--------------+------------+--------+------------+----------+
Prints the statements affecting the most rows
check.getQueryMostRowAffected([limit][, schema][, session])
- limit: Integer. The amount of query to return. Default is 1.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
JS check.getQueryMostRowAffected()
*************************** 1. row ***************************
db: test
rows_affected: 11
rows_affected_avg: 11
query_sample_text: update t1 set a=concat(a," fred")
Prints the queries using temporary tables on disk
check.getQueryTempDisk([limit][, schema][, session])
- limit: Integer. The amount of query to return. Default is 1.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
Prints the statements updating mostly the same PK and therefore having to wait more (hotspot)
check.getQueryUpdatingSamePK([limit][, schema][, session])
- limit: Integer. The amount of query to return. Default is 1.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
Prints the statements in one running transaction identified by thread ID
check.getRunningStatements([limit][, session])
- limit: Integer. The amount of running thread to return. Default is 10.
- session: Object. The session to be used on the operation.
JS check.getRunningStatements()
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
| mysql_thread_id | User | Command | trx_duration | latest_statement |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
| 12 | root@localhost | Sleep | 1.50 min | rollback |
| 13 | root@localhost | Sleep | 322.70 us | select *, concat(b.b, ' ',a.b) ... b, ' ',a.b)) > 10 order by b.b |
| 11 | root@localhost | Query | 226.09 us | SELECT thr.processlist_id AS m ... IT DESC LIMIT 10 |
+-----------------+----------------+---------+--------------+-------------------------------------------------------------------+
For which thread_id do you want to see the statements ? (12)
Info: amount of returned statements is limited by performance_schema_events_statements_history_size = 10
update t1 set a='test11' where id=11
rollback
Prints the slowest queries. If the limit is one you can also see all the details about the query
check.getSlowerQuery([limit][, select][, schema][, session])
- limit: Integer. The amount of query to return. Default is 1.
- select: Bool. Returns queries only with SELECT.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
JS check.getSlowerQuery()
*************************** 1. row ***************************
schema_name: test
tot_lat: 28.13 s
exec_count: 1
latency_per_call: 28.13 s
first_seen: 2020-07-31 11:59:18.119404
last_seen: 2020-07-31 11:59:18.119404
query_sample_text: update t1 set b='fred' where id=11
Do you want to have EXPLAIN output? (y/N) y
*************************** 1. row ***************************
id: 1
select_type: UPDATE
table: t1
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100
Extra: Using where
Do you want to have EXPLAIN in JSON format output? (y/N) y
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"update": true,
"table_name": "t1",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"filtered": "100.00",
"attached_condition": "(`test`.`t1`.`id` = 11)"
}
}
}
Prints amount ox transactions committed in a certain interval.
check.getTrxRate([interval][, limit][, session])
- interval: Integer. The optional interval between outputs in seconds (default: 1).
- limit: Integer. How many times this should be printed. 0 means infinite loop (default: 10).
- session: Object. The optional session object used to query the database. If omitted the MySQL Shell's current session will be used.
JS check.getTrxRate()
Amount of transactions committed every 1 seconds:
=================================================
trx/1sec: 0
trx/1sec: 2
trx/1sec: 4
trx/1sec: 4
trx/1sec: 3
trx/1sec: 2
trx/1sec: 0
trx/1sec: 0
trx/1sec: 0
trx/1sec: 0
Prints the transaction with the most amount of statements
check.getTrxWithMostRowsAffected([limit][, schema][,session])
- limit: Integer. The amount of query to return. Default is 1.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
JS check.getTrxWithMostRowsAffected()
Some consumers (events_statements_history_long, events_transactions_history_long) are not enabled, do you want to enabled them now ? (y/N) y
We just made some changes, let the system run for some time to fetch the workload
JS check.getTrxWithMostRowsAffected()
+-----------+----------+-----------------+---------------+--------------------+
| thread_id | event_id | statement_count | rows_affected | # write statements |
+-----------+----------+-----------------+---------------+--------------------+
| 52 | 68 | 3 | 22 | 2 |
+-----------+----------+-----------------+---------------+--------------------+
Do you want to list all statements in that transaction ? (y/N) y
1) update t1 set a=concat(a," fred");
2) update t1 set b=concat(b," fred");
3) commit;
Prints the transaction with the most amount of statements
check.getTrxWithMostStatements([limit][, schema][, session])
- limit: Integer. The amount of query to return. Default is 1.
- schema: String. The name of the schema to look at.
- session: Object. The session to be used on the operation.
JS check.getTrxWithMostStatements()
+-----------+----------+-----------------+---------------+--------------------+
| thread_id | event_id | statement_count | rows_affected | # write statements |
+-----------+----------+-----------------+---------------+--------------------+
| 51 | 61 | 6 | 1 | 1 |
+-----------+----------+-----------------+---------------+--------------------+
Do you want to list all statements in that transaction ? (y/N) y
1) select * from t1;
2) select *, concat(b.b, ' ',a.b) from t1 a join t1 b on b.id=a.id where b.b like 'runn%' order by b.b;
3) update t1 set a='test11' where id=11;
4) select * from t1;
5) select count(*) from t1;
6) commit;
Prints Transactions Size from a binlog
check.showTrxSize([binlog][, session])
- binlog: String. The binlog file to extract transactions from.
- session: Object. The session to be used on the operation
JS check.showTrxSize()
Transactions in binary log binlog.000120:
695 bytes
1 kb
259 bytes
JS check.showTrxSize('binlog.000117')
Transactions in binary log binlog.000117:
325 bytes
Prints Transactions Size from a binlog
check.showTrxSizeSort([limit][, binlog][, session])
- limit: Integer. The maximum transactions to show, default is 10
- binlog: String. The binlog file to extract transactions from.
- session: Object. The session to be used on the operation.
JS check.showTrxSizeSort()
Transactions in binary log binlog.000120 orderer by size (limit 10):
1 kb
695 bytes
259 bytes
Prints the workload ratio between reads and writes.
check.workload([session])
- session: Object. The optional session object used to query the database. If omitted the MySQL Shell's current session will be used.
JS check.workload()
MySQL Workload of the server: 95.83% reads and 4.17% writes
Prints the workload ratio between reads and writes with some information per schema and tables.
check.workloadInfo([schema][, table][, session])
- schema: String. The name of the schema to check (default: None). If none is specified, all schemas are listed.
- table: String. The name of a specific table present in the schema defined (default: None). If none is specified, only the schema is listed. You can use '*' to show them all.
- session: Object. The optional session object used to query the database. If omitted the MySQL Shell's current session will be used
MySQL Workload of the server: 95.83% reads and 4.17% writes
+---------------+--------+--------+----------------------+-----------------------+
| object_schema | reads | writes | ratio to total reads | ratio to total writes |
+---------------+--------+--------+----------------------+-----------------------+
| test | 95.83% | 4.17% | 100.00% | 100.00% |
+---------------+--------+--------+----------------------+-----------------------+