背景
用于问题诊断
写在前面
(推荐阅读)原文链接为https://www.modb.pro/db/40447
link
操作
读者需要根据实际参数,以下参数5.7和8.0存在不同,修改以下模板。kill 的是PROCESSLIST_ID
1.连接相关
查看某用户连接的会话级别参数设置及状态变量,用于观测其它会话连接行为,辅助定位连接类问题
例:查看用户连接 ID 为 18 的字符集设置,也可不指定 PROCESSLIST_ID 条件,查看所有用户连接
SELECT T1.VARIABLE_NAME,
T1.VARIABLE_VALUE,
T2.PROCESSLIST_ID,
concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST),
T2.PROCESSLIST_DB,
T2.PROCESSLIST_COMMAND
FROM performance_schema.variables_by_thread T1,
performance_schema.threads T2
WHERE T2.thread_id = T2.thread_id
AND T1.variable_name LIKE 'character%'
AND PROCESSLIST_ID ='18';
发现用户 ID 为 * 的 sql_log_bin 设置
SELECT T1.VARIABLE_NAME,
T1.VARIABLE_VALUE,
T2.PROCESSLIST_ID,
concat(T2.PROCESSLIST_USER,"@",T2.PROCESSLIST_HOST) AS 'User@Host',
T2.PROCESSLIST_DB,
T2.PROCESSLIST_COMMAND
FROM performance_schema.variables_by_thread T1,
performance_schema.threads T2
WHERE T1.thread_id = T2.thread_id
AND T1.variable_name LIKE 'sql_log_bin';
2.长事务
事务开启后,超过 5s 未提交的用户连接
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx.trx_mysql_thread_id != connection_id()
AND TO_SECONDS(now())-TO_SECONDS(trx.trx_started) >= 5;
3.元数据锁
MySQL 5.7 开启元数据锁追踪,以便追踪定位元数据锁相关的阻塞问题
// 临时开启,动态生效
select * from performance_schema.setup_consumers WHERE NAME ='global_instrumentation';
select * from performance_schema.setup_instruments WHERE NAME ='wait/lock/metadata/sql/mdl';
UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME ='global_instrumentation';
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';
UPDATE performance_schema.setup_instruments
SET TIMED = 'YES'
WHERE NAME ='wait/lock/metadata/sql/mdl';
#配置文件中添加,重启生效
performance-schema-instrument = wait/lock/metadata/sql/mdl=ON
场景 1:杀掉持有 MDL 锁的会话,使 DDL 语句顺利执行。
DDL 语句被阻塞通常因为存在获取资源后未及时提交释放的长事务。因此,查找 kill 掉事务运行时间大于 DDL 运行时间的会话即可使 DDL 语句顺利下发,SQL 语句如下:
// 查找事务运行时间 >= DDL等待时间的线程
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
AND TO_SECONDS(now())-TO_SECONDS(trx_started) >=
(SELECT MAX(Time)
FROM INFORMATION_SCHEMA.processlist
WHERE STATE='Waiting for table metadata lock'
AND INFO LIKE 'alter%table%' OR INFO LIKE 'truncate%table%') ;
// kill掉长事务,释放持有的MDL资源
kill 253;
注:因 MySQL 元数据信息记录有限,此处可能误杀无辜长事务,且误杀无法完全避免。
当 kill 掉阻塞源后,可能存在 DDL 语句与被阻塞的 SQL 语句同时加锁的情况,此时会出现事务开始时间等于 DDL 开始时间连接,此类事务也需 kill。
//查找事务开始时间 = DDL语句事务开始时间的线程
SELECT trx_mysql_thread_id AS PROCESSLIST_ID,
NOW(),
TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,
USER,
HOST,
DB,
TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()
AND trx_started =
(SELECT MIN(trx_started)
FROM INFORMATION_SCHEMA.INNODB_TRX
GROUP BY trx_started HAVING count(trx_started)>=2)
AND TRX_QUERY NOT LIKE 'alter%table%'
OR TRX_QUERY IS NULL;
//杀掉阻塞源
kill 255;
场景 2:kill 掉下发 DDL 语句的用户连接,取消 DDL 语句下发,保障业务不被阻塞。
// 查找DDL语句所在用户连接
SELECT *
FROM INFORMATION_SCHEMA.PROCESSLIST
WHERE INFO LIKE 'ALTER%TABLE%';
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 254 | root | localhost | NULL | Query | 730 | Waiting for table metadata lock | alter table db.t1 add index (id) |
+-----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
1 row in set (0.00 sec)
// 杀掉DDL语句所在用户连接
kill 254;
4.锁等待
查看锁等待相关的阻塞线程、被阻塞线程信息及相关用户、IP、PORT
SELECT locked_table,
locked_index,
locked_type,
blocking_pid,
concat(T2.USER,'@',T2.HOST) AS "blocking(user@ip:port)",
blocking_lock_mode,
blocking_trx_rows_modified,
waiting_pid,
concat(T3.USER,'@',T3.HOST) AS "waiting(user@ip:port)",
waiting_lock_mode,
waiting_trx_rows_modified,
wait_age_secs,
waiting_query
FROM sys.x$innodb_lock_waits T1
LEFT JOIN INFORMATION_SCHEMA.processlist T2 ON T1.blocking_pid=T2.ID
LEFT JOIN INFORMATION_SCHEMA.processlist T3 ON T3.ID=T1.waiting_pid;
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| locked_table | locked_index | locked_type | blocking_pid | blocking(user@ip:port) | blocking_lock_mode | blocking_trx_rows_modified | waiting_pid | waiting(user@ip:port) | waiting_lock_mode | waiting_trx_rows_modified | wait_age_secs | waiting_query |
+--------------+--------------+-------------+--------------+------------------------+--------------------+----------------------------+-------------+-----------------------+-------------------+---------------------------+---------------+---------------------------------+
| `db`.`t1` | PRIMARY | RECORD | 228 | dks@127.0.0.1:56724 | X | 1 | 231 | root@127.0.0.1:50852 | S | 0 | 1 | insert into db.t1(id) values(2) |
+--------------+--------------+-------------+--------------+------------------------+--------------------+------------------
若不关心阻塞相关的用户、IP、PORT,可直接查看 innodb_lock_waits 表信息。
select * from sys.x$innodb_lock_waits\G
*************************** 1. row ***************************
wait_started: 2020-02-23 02:14:22
wait_age: 00:00:32
wait_age_secs: 32
locked_table: `db`.`t1`
locked_index: PRIMARY
locked_type: RECORD
waiting_trx_id: 7204404
waiting_trx_started: 2020-02-23 02:14:18
waiting_trx_age: 00:00:36
waiting_trx_rows_locked: 1
waiting_trx_rows_modified: 0
waiting_pid: 213
waiting_query: delete from db.t1 where id=200
waiting_lock_id: 7204404:1994:3:4
waiting_lock_mode: X
blocking_trx_id: 7204394
blocking_pid: 207
blocking_query: select * from sys.x$innodb_lock_waits
blocking_lock_id: 7204394:1994:3:4
blocking_lock_mode: X
blocking_trx_started: 2020-02-23 02:10:06
blocking_trx_age: 00:04:48
blocking_trx_rows_locked: 1
blocking_trx_rows_modified: 1
sql_kill_blocking_query: KILL QUERY 207
sql_kill_blocking_connection: KILL 207
1 row in set, 3 warnings (0.00 sec)
影响锁等待超时的参数
5.全局读锁
PERFORMANCE_SCHEMA.METADATA_LOCKS 表 LOCK_DURATION 列为 EXPLICIT 状态表示 FTWRL 语句添加,OBJECT_TYPE 出现 COMMIT 状态表示已经加锁成功
场景 1:杀掉添加 FTWRL 的会话,恢复业务运行
SELECT processlist_id,
mdl.OBJECT_TYPE,
OBJECT_SCHEMA,
OBJECT_NAME,
LOCK_TYPE,
LOCK_DURATION,
LOCK_STATUS
FROM performance_schema.metadata_locks mdl
INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id
AND processlist_id <> connection_id()
AND LOCK_DURATION='EXPLICIT';
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
| processlist_id | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
| 231 | GLOBAL | NULL | NULL | SHARED | EXPLICIT | GRANTED |
| 231 | COMMIT | NULL | NULL | SHARED | EXPLICIT | GRANTED |
+----------------+-------------+---------------+-------------+-----------+---------------+-------------+
2 rows in set (0.00 sec)
// 杀掉添加FTWRL的用户连接
kill 231;
场景 2:杀掉语句执行时间大于 FTWRL 执行时间的线程,确保 FTWRL 下发成功
SELECT T2.THREAD_ID,
T1.ID AS PROCESSLIST_ID,
T1.User,
T1.Host,
T1.db,
T1.Time,
T1.State,
T1.Info,
T3.TRX_STARTED,
TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME
FROM information_schema.processlist T1
LEFT JOIN performance_schema.threads T2 ON T1.ID=T2.PROCESSLIST_ID
LEFT JOIN information_schema.innodb_trx T3 ON T1.id=T3.trx_mysql_thread_id
WHERE T1.TIME >=
(SELECT MAX(Time)
FROM information_schema.processlist
WHERE INFO LIKE 'flush%table%with%read%lock')
AND Info IS NOT NULL;
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
| THREAD_ID | PROCESSLIST_ID | User | Host | db | Time | State | Info | TRX_STARTED | TRX_LAST_TIME |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
| 284 | 246 | root | localhost | NULL | 364 | User sleep | select * from db.t1 where sleep(1000000000) | 2020-02-23 14:57:23 | 364 |
| 286 | 248 | root | 10.211.55.2:55435 | NULL | 232 | Waiting for table flush | flush table with read lock | NULL | NULL |
+-----------+----------------+------+-------------------+------+------+-------------------------+---------------------------------------------+---------------------+---------------+
内存使用监控
默认只对 performance_schema 库进行内存统计,对全局内存统计需要手工开启
//动态开启,开启后开始统计
update performance_schema.setup_instruments set
enabled = 'yes' where name like 'memory%';
select count(*) from performance_schema.setup_instruments;
select count(*) from performance_schema.setup_instruments where name like 'memory%';
//配置文件中添加,重启生效
performance-schema-instrument=‘memory/%=COUNTED’
查看实例内存消耗分布,sys 库下有多张 memory 相关视图用于协助用户定位分析内存溢出类问题
mysql8.0可以直接查:
SELECT event_name,
current_alloc
FROM sys.memory_global_by_current_bytes
WHERE event_name LIKE 'memory%innodb%';
+-------------------------------------------+---------------+
| event_name | current_alloc |
+-------------------------------------------+---------------+
| memory/innodb/buf_buf_pool | 134.31 MiB |
| memory/innodb/log0log | 32.01 MiB |
| memory/innodb/mem0mem | 15.71 MiB |
| memory/innodb/lock0lock | 12.21 MiB |
| memory/innodb/os0event | 8.37 MiB |
| memory/innodb/hash0hash | 4.74 MiB |
...
7.分区表
查看实例中的分区表相关信息
SELECT TABLE_SCHEMA,
TABLE_NAME,
count(PARTITION_NAME) AS PARTITION_COUNT,
sum(TABLE_ROWS) AS TABLE_TOTAL_ROWS,
CONCAT(ROUND(SUM(DATA_LENGTH) / (1024 * 1024), 2),'M') DATA_LENGTH,
CONCAT(ROUND(SUM(INDEX_LENGTH) / (1024 * 1024), 2),'M') INDEX_LENGTH,
CONCAT(ROUND(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME NOT IN ('sys',
'mysql',
'INFORMATION_SCHEMA',
'performance_schema')
AND PARTITION_NAME IS NOT NULL
GROUP BY TABLE_SCHEMA,
TABLE_NAME
ORDER BY sum(DATA_LENGTH + INDEX_LENGTH) DESC ;
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_COUNT | TABLE_TOTAL_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
| db | t1 | 365 | 0 | 5.70M | 17.11M | 22.81M |
| db | t2 | 391 | 0 | 6.11M | 0.00M | 6.11M |
| db | t3 | 4 | 32556 | 2.28M | 0.69M | 2.97M |
| db | t4 | 26 | 0 | 0.41M | 2.44M | 2.84M |
| db | t5 | 4 | 0 | 0.06M | 0.00M | 0.06M |
| db | t6 | 4 | 0 | 0.06M | 0.00M | 0.06M |
+--------------+------------------+-----------------+------------------+-------------+--------------+------------+
查看某分区表具体信息,此处以库名为 db、表名为 e 的分区表为例
SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_EXPRESSION,
PARTITION_METHOD,
PARTITION_DESCRIPTION,
TABLE_ROWS,
CONCAT(ROUND(DATA_LENGTH / (1024 * 1024), 2),'M') DATA_LENGTH,
CONCAT(ROUND(INDEX_LENGTH / (1024 * 1024), 2),'M') INDEX_LENGTH,
CONCAT(ROUND(ROUND(DATA_LENGTH + INDEX_LENGTH) / (1024 * 1024),2),'M') TOTAL_SIZE
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA NOT IN ('sys',
'mysql',
'INFORMATION_SCHEMA',
'performance_schema')
AND PARTITION_NAME IS NOT NULL
AND TABLE_SCHEMA='db'
AND TABLE_NAME='e';
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | PARTITION_EXPRESSION | PARTITION_METHOD | PARTITION_DESCRIPTION | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | TOTAL_SIZE |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
| db | e | p0 | id | RANGE | 50 | 4096 | 0.20M | 0.09M | 0.30M |
| db | e | p1 | id | RANGE | 100 | 6144 | 0.28M | 0.13M | 0.41M |
| db | e | p2 | id | RANGE | 150 | 6144 | 0.28M | 0.13M | 0.41M |
| db | e | p3 | id | RANGE | MAXVALUE | 16172 | 1.52M | 0.34M | 1.86M |
+--------------+------------+----------------+----------------------+------------------+-----------------------+------------+-------------+--------------+------------+
4 rows in set (0.00 sec)
8.长时间未更新的表(可以先use 到库中,这样显示的就是当前库的表信息,否则是全部表)
UPDATE_TIME 为 NULL 表示实例启动后一直未更新过:
SELECT TABLE_SCHEMA,
TABLE_NAME,
UPDATE_TIME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('SYS',
'MYSQL',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND TABLE_TYPE='BASE TABLE'
ORDER BY UPDATE_TIME ;
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME |
+--------------+------------+---------------------+
| ceshi100 | book | NULL |
| ceshi100 | tmp_table | NULL |
| ceshi100 | test | NULL |
| ceshi100 | t1_copy3 | NULL |
| ceshi100 | t1_copy2 | NULL |
| ceshi100 | t1 | 2021-03-02 14:04:10 |
+--------------+------------+---------------------+
验证:
insert into book values(12,'test01','test02',5.11);
+--------------+------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME | UPDATE_TIME |
+--------------+------------+---------------------+
| ceshi100 | t1_copy2 | NULL |
| ceshi100 | tmp_table | NULL |
| ceshi100 | test | NULL |
| ceshi100 | t1_copy3 | NULL |
| ceshi100 | t1 | 2021-03-02 14:04:10 |
| ceshi100 | book | 2021-03-02 17:15:52 |
+--------------+------------+---------------------+
9.数据库信息
先use 库,显示的信息少
统计实例中各数据库大小
SELECT TABLE_SCHEMA,
round(SUM(data_length+index_length)/1024/1024,2) AS TOTAL_MB,
round(SUM(data_length)/1024/1024,2) AS DATA_MB,
round(SUM(index_length)/1024/1024,2) AS INDEX_MB,
COUNT(*) AS TABLES
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('sys',
'mysql',
'INFORMATION_SCHEMA',
'performance_schema')
GROUP BY TABLE_SCHEMA
ORDER BY 2 DESC;
+--------------+----------+---------+----------+--------+
| TABLE_SCHEMA | TOTAL_MB | DATA_MB | INDEX_MB | TABLES |
+--------------+----------+---------+----------+--------+
| ceshi100 | 28.70 | 28.64 | 0.06 | 6 |
+--------------+----------+---------+----------+--------+
统计某库下各表大小,修改TABLE_SCHEMA='ceshi100'
SELECT TABLE_SCHEMA,
TABLE_NAME TABLE_NAME,
CONCAT(ROUND(data_length / (1024 * 1024), 2),'M') data_length,
CONCAT(ROUND(index_length / (1024 * 1024), 2),'M') index_length,
CONCAT(ROUND(ROUND(data_length + index_length) / (1024 * 1024),2),'M') total_size,
engine
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'performance_schema',
'sys',
'mysql')
AND TABLE_SCHEMA='ceshi100'
ORDER BY (data_length + index_length) DESC LIMIT 10;
+--------------+------------+-------------+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | data_length | index_length | total_size | engine |
+--------------+------------+-------------+--------------+------------+--------+
| ceshi100 | tmp_table | 28.56M | 0.00M | 28.56M | InnoDB |
| ceshi100 | test | 0.02M | 0.02M | 0.03M | InnoDB |
| ceshi100 | t1_copy3 | 0.02M | 0.02M | 0.03M | InnoDB |
| ceshi100 | t1_copy2 | 0.02M | 0.02M | 0.03M | InnoDB |
| ceshi100 | t1 | 0.02M | 0.02M | 0.03M | InnoDB |
| ceshi100 | book | 0.02M | 0.00M | 0.02M | InnoDB |
+--------------+------------+-------------+--------------+------------+--------+
查看某库下表的基本信息(注意表行数不是准确值)
SELECT TABLE_SCHEMA,
TABLE_NAME,
table_collation,
engine,
table_rows
FROM INFORMATION_SCHEMA.tables
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'sys',
'mysql',
'performance_schema')
AND TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA='ceshi100'
ORDER BY table_rows DESC ;
+--------------+------------+-----------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME | table_collation | engine | table_rows |
+--------------+------------+-----------------+--------+------------+
| ceshi100 | tmp_table | utf8_general_ci | InnoDB | 998568 |
| ceshi100 | book | utf8_general_ci | InnoDB | 11 |
| ceshi100 | t1_copy3 | utf8_general_ci | InnoDB | 4 |
| ceshi100 | test | utf8_general_ci | InnoDB | 3 |
| ceshi100 | t1_copy2 | utf8_general_ci | InnoDB | 0 |
| ceshi100 | t1 | utf8_general_ci | InnoDB | 0 |
+--------------+------------+-----------------+--------+------------+
6 rows in set (0.00 sec)
[root@localhost][ceshi100]> select count(*) from tmp_table;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
存储引擎分布
SELECT TABLE_SCHEMA,
ENGINE,
COUNT(*)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA' ,
'PERFORMANCE_SCHEMA',
'SYS',
'MYSQL')
AND TABLE_TYPE='BASE TABLE'
GROUP BY TABLE_SCHEMA,
ENGINE;
+--------------------+--------------------+----------+
| TABLE_SCHEMA | ENGINE | COUNT(*) |
+--------------------+--------------------+----------+
| mysql | InnoDB | 32 |
| performance_schema | PERFORMANCE_SCHEMA | 108 |
| ceshi100 | InnoDB | 6 |
| mysql | CSV | 2 |
| sys | InnoDB | 1 |
+--------------------+--------------------+----------+
11.主键、索引
无主键、唯一键及二级索引基表
MySQL Innodb 存储引擎为索引组织表,因此设置合适的主键字段对性能至关重要
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
'MYSQL',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND T2.TABLE_TYPE='BASE TABLE'
AND T1.TABLE_SCHEMA='db'
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';
无主键、唯一键,仅有二级索引表
该类型表因无高效索引,因此从库回放时容易导致复制延迟
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
'MYSQL',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND T2.TABLE_TYPE='BASE TABLE'
AND T1.COLUMN_KEY != ''
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';
仅有主键、唯一键表
该类型表结构因无二级索引,可能导致应用 SQL 语句上线后频繁全表扫描出现性能抖动
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
'MYSQL',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND T2.TABLE_TYPE='BASE TABLE'
AND T1.COLUMN_KEY != ''
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';
无主键、唯一键表
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1 JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS',
'MYSQL',
'INFORMATION_SCHEMA',
'PERFORMANCE_SCHEMA')
AND T2.TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING group_concat(COLUMN_KEY) NOT REGEXP 'PRI|UNI';
12.shell实时负载
新建一个1.sh
chmod +x 1.sh
./1.sh
脚本内容为:
mysqladmin extended-status -uroot -p123456 -i1|awk 'BEGIN{local_switch=0}
$2 ~ /Queries$/ {q=$4-lq;lq=$4;}
$2 ~ /com_commit$/ {c=$4-lc;lc=$4;}
$2 ~ /Com_rollback$/ {r=$4-lr;lr=$4;}
$2 ~ /Com_select$/ {s=$4-ls;ls=$4;}
$2 ~ /Com_update$/ {u=$4-lu;lu=$4;}
$2 ~ /Com_insert$/ {i=$4-li;li=$4;}
$2 ~ /Com_delete$/ {d=$4-ld;ld=$4;}
$2 ~ /Innodb_rows_read$/ {irr=$4-lirr;lirr=$4;}
$2 ~ /Innodb_rows_deleted$/ {ird=$4-lird;lird=$4;}
$2 ~ /Innodb_rows_inserted$/ {iri=$4-liri;liri=$4;}
$2 ~ /Innodb_rows_updated$/ {iru=$4-liru;liru=$4;}
$2 ~ /Innodb_buffer_pool_read_requests$/ {ibprr=$4-libprr;libprr=$4;}
$2 ~ /Innodb_buffer_pool_reads$/ {ibpr=$4-libpr;libpr=$4;}
$2 ~ /Threads_connected$/ {tc=$4;}
$2 ~ /Threads_running$/ {tr=$4;
if(local_switch==0)
{local_switch=1; count=16}
else {
if(count>15) {
count=0;
print "----------------------------------------------------------------------------
-------------------------------------------------------- ";
print "Time-----| QPS | Commit Rollback TPS | select insert update delete | read
inserted updated deleted | logical physical | Tcon Trun";
print "----------------------------------------------------------------------------
-------------------------------------------------------- ";
}else{
count+=1;
printf "%s | %-5d| \n ",strftime("%H:%M:%S") , lq
printf "%s | %-5d| %-6d %-7d %-5d| %-7d %-7d %-5d %-6d| %-7d %-7d %-7d %-7d| %-6d
%-9d| %-4d %-2d \n", strftime("%H:%M:%S"),q,c,r,c+r,s,u,i,d,irr,ird,iri,iru,ibprr,ibpr,tc,tr;
}
}
}'
13.binlog统计DDL&DML
统计DML:
mysqlbinlog --no-defaults --base64-output=decode-rows -v -v mysql-bin.000006 | awk '/###/{if($0~/UPDATE|INSERT|DELETE/)count[$2" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3nr
DELETE `ceshi100`.`book` 3
INSERT `ceshi100`.`book` 3
DELETE `ceshi100`.`t1` 2
统计DDl:
mysqlbinlog mysql-bin.000006 | awk 'BEGIN{IGNORECASE=1} {if($0~/alter/)count[$1" " $2" " $3" "$NF]++}END{for(i in count)print i,"\t",count[i]}'|column -t|sort -k3n
alter table t1 null 2
14.binlog并行复制统计(不好用)
mysqlbinlog mysql-bin.000001 --start-position=1000 | grep -o 'last_committed.*' | sed 's/=/ /g' | awk '{print $2"\t"$4}' | awk '{count++;print $0;} END{print "total count is ",count}'
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 69271552, event_type: 10
total count is
15.not utf8mb4 table
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
from information_schema.TABLES
WHERE TABLE_COLLATION not like 'utf8mb4'
and table_schema not in ('information_schema' ,'mysql','performance_schema', 'sys');
+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| ceshi100 | book | utf8_general_ci |
| ceshi100 | t1 | utf8_general_ci |
| ceshi100 | t1_copy2 | utf8_general_ci |
| ceshi100 | t1_copy3 | utf8_general_ci |
| ceshi100 | test | utf8_general_ci |
| ceshi100 | tmp_table | utf8_general_ci |
+--------------+------------+-----------------+
或者查看not utf8 table
SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_COLLATION
from information_schema.TABLES
WHERE TABLE_COLLATION not like 'utf8%'
and table_schema not in ('information_schema' ,'mysql','performance_schema', 'sys');
16.字符集验证
1.参看系统字符集:
show global variables like 'collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
2.跟系统字符集不一样的数据库:Server characterset: utf8mb4
SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME
from information_schema.SCHEMATA b
WHERE b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema', 'sys')
and b.DEFAULT_COLLATION_NAME<>@@collation_server ;
+-------------+----------------------------+------------------------+
| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+-------------+----------------------------+------------------------+
| ceshi100 | utf8 | utf8_general_ci |
| ceshi555 | utf8 | utf8_general_ci |
| ceshi55555 | utf8 | utf8_general_ci |
| ceshi666 | utf8 | utf8_general_ci |
| ceshi66666 | utf8 | utf8_general_ci |
| ceshi888 | utf8 | utf8_general_ci |
| ceshi88888 | utf8 | utf8_general_ci |
+-------------+----------------------------+------------------------+
3.跟系统字符集不一样的表和字段:
select distinct tschema,tname,tcoll
from
(
select a.TABLE_SCHEMA as tschema , a.TABLE_NAME as tname,a.TABLE_COLLATION as tcoll
from information_schema.TABLES a
WHERE a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys')
and a.TABLE_COLLATION<>@@collation_server
union
select a.TABLE_SCHEMA as tschema, TABLE_NAME as tname, a.COLLATION_NAME as tcoll
from information_schema.COLUMNS a
WHERE a.TABLE_SCHEMA not in ('information_schema' ,'mysql','performance_schema', 'sys')
and a.COLLATION_NAME<>@@collation_server ) as aa ;
+----------+-----------+-----------------+
| tschema | tname | tcoll |
+----------+-----------+-----------------+
| ceshi100 | book | utf8_general_ci |
| ceshi100 | t1 | utf8_general_ci |
| ceshi100 | t1_copy2 | utf8_general_ci |
| ceshi100 | t1_copy3 | utf8_general_ci |
| ceshi100 | test | utf8_general_ci |
| ceshi100 | tmp_table | utf8_general_ci |
+----------+-----------+-----------------+
常用
17.CPU使用率过高
查看CPU飙高的mysql线程,top -H -p <mysqld进程id>
top -H -p 104406
根据具体PID,定位问题SQL
SELECT a.THREAD_OS_ID,b.id,b.user,b.host,b.db,b.command,b.time,b.state,b.info
FROM performance_schema.threads a,information_schema.processlist b
WHERE b.id = a.processlist_id and a.THREAD_OS_ID=<具体pid>;
18.kill应用会话
mysql>select concat('KILL ',id,';') from information_schema.processlist where time>10 and db is not null and command!='sleep' into outfile '/tmp/a.txt';
OK, rows affected (0.00 sec)
mysqQu2eryl>source /tmp/a.txt;
Query OK, 0 rows affected (0.00 sec)
本文说明,主要技术内容来自互联网技术大佬的分享,还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问,请留言,将确认之后,执行侵权必删