Performance Monitoring Tools

-- Connect MySQL Number of servers
SHOW GLOBAL STATUS LIKE 'Connections';
-- MySQL Server online time
SHOW GLOBAL STATUS LIKE 'Uptime';
-- Number of slow queries
SHOW GLOBAL STATUS LIKE 'Slow_queries';
--  Select Number of rows returned by the query
SHOW GLOBAL STATUS LIKE 'Innodb_rows_read';
-- implement INSERT Number of rows inserted by operation
SHOW GLOBAL STATUS LIKE 'Innodb_rows_inserted';
-- implement UPDATE Number of rows updated by operation
SHOW GLOBAL STATUS LIKE 'Innodb_rows_updated';
-- implement DELETE Number of rows deleted by operation
SHOW GLOBAL STATUS LIKE 'Innodb_rows_deleted';
-- Number of query operations
SHOW GLOBAL STATUS LIKE 'Com_select';
-- Number of insert operations for bulk inserts INSERT Operation, cumulative only once
SHOW GLOBAL STATUS LIKE 'Com_insert';
-- Number of update operations
SHOW GLOBAL STATUS LIKE 'Com_update';
-- Number of delete operations
SHOW GLOBAL STATUS LIKE 'Com_delete';

Statistics sql execution scan pages

-- Case 1
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id = 900001;
-- View cost of last record query, (Number of pages queried)
SHOW STATUS LIKE 'last_query_cost';
-- +-----------------+----------+
-- | Variable_name | Value | 
-- +-----------------+----------+
-- | Last_query_cost | 1.000000 | 
-- +-----------------+----------+
-- You can see the above sql Statement queries only one page of data, So the cost is lower

-- Case 2
SELECT student_id, class_id, NAME, create_time FROM student_info
WHERE id BETWEEN 900001 AND 900100;
-- Query above sql Cost of implementation
SHOW STATUS LIKE 'last_query_cost';
-- +-----------------+-----------+
-- | Variable_name | Value | 
-- +-----------------+-----------+
-- | Last_query_cost | 21.134453 | 
-- +-----------------+-----------+
-- You can see this sql Need to load 22 pages of data

Slow Query Log

-- Check if slow queries are open
show GLOBAL variables like 'slow_query_log%'
-- Open Slow Query Log
set global slow_query_log='ON';

-- See how many seconds a slow query takes to record
show variables like '%long_query_time%';
-- Set slow query seconds
set global long_query_time = 1

-- See how many full queries the current system has sql
SHOW GLOBAL STATUS LIKE '%Slow_queries%';


-- Permanent Settings Modification my.cnf
[mysqld]
slow_query_log=NO
slow_query_log_file=/var/mysql/slow.log
long_query_time=3
log_output=FILE


-- Once you have a slow query log, you can open it for viewing or use it mysqldumpslow Tool Query Slow sql

View sql execution costs

--  Query whether to turn on costing
show variables like 'profiling';
--  Open Costing 
set profiling = 'ON';
-- Query Execution sql
show profiles;
-- Query for an item sql Execution Cost 57
show profile  for query 57;
-- The execution cost of the last execution of the query
show profile

EXPLAIN, execution plan of sql, explanation of tool parameters

Other Tools

• Cost and details of executing the plan can be queried
  EXPLAIN FORMAT=JSON SELECT * from table;
• You can use Workbench to query sql in a graphical interface
• Show sql statements after query optimizer optimization
  SHOW WARNINGS\G
  Executing the above statement after using EXPLAIN analysis allows you to view the sql statement that executes the optimizer optimization
• Analysis Optimizer Execution Plan: trace

-- open trace Track
SET optimizer_trace="enabled=on",end_markers_in_json=on; 
set optimizer_trace_max_mem_size=1000000;
-- Execute Statement 
select * from student where id < 10;
-- View Tracking Results
select * from information_schema.optimizer_trace\G

• Sys schema view usage scenario

-- Index correlation
#1. Query Redundant Index 
select * from sys.schema_redundant_indexes; 
#2. Query unused indexes 
select * from sys.schema_unused_indexes;
#3. Use of query index 
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;

-- Table correlation
# 1. Access to Query Tables 
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from sys.schema_table_statistics group by table_schema,table_name order by io desc; 
# 2. Queries take up more bufferpool s for tables 
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10; 
# 3. View the full table scan of the table 
select * from sys.statements_with_full_table_scans where db='dbname';

-- Statement correlation
#1. Monitor the frequency of SQL execution 
select db,exec_count,query from sys.statement_analysis order by exec_count desc; 
#2. Monitor the use of ordered SQL
 select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1; 
#3. Monitoring uses SQL for temporary tables or disk temporary tables 
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc;

-- IO Relevant
#1. View files that consume disk IO 
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;

-- InnoDb Relevant
#1. Row lock blocking 
select * from sys.innodb_lock_waits;