做个简单的企业小网站,wordpress主题加载慢,当地网站建设问卷调查,网站管理系统 免费文章目录创建账号和授权查询没有主键的表统计每个库大小前十张大表清理日志表Prepared statement needs to be re-preparedxtrabackup 问题锁问题处理快速处理查询事务等待和阻塞情况innodb_trxprocesslistdata_locksdata_lock_waitsmetadata_locksevents_statements_current其…
文章目录创建账号和授权查询没有主键的表统计每个库大小前十张大表清理日志表Prepared statement needs to be re-preparedxtrabackup 问题锁问题处理快速处理查询事务等待和阻塞情况innodb_trxprocesslistdata_locksdata_lock_waitsmetadata_locksevents_statements_current其他手动备份单表清理耗时过长的sql创建账号和授权
-- 以 xx_user 为例
CREATE USER xx_user% IDENTIFIED WITH caching_sha2_password BY your_password;-- 全局表授权 select, insert, update, delete
grant select, insert, update, delete on xx_table to xx_user;-- 业务范围内的表授 ALL PRIVILEGES
SELECT CONCAT(GRANT ALL PRIVILEGES ON ,TABLE_SCHEMA,.,TABLE_NAME, TO xx_user%;)
FROM information_schema.TABLES
WHERE TABLE_SCHEMA xxx_db AND TABLE_NAME LIKE test_% ;
FLUSH PRIVILEGES;-- 视图授权 SELECT, SHOW
GRANT SELECT, SHOW VIEW ON xxx_view TO xx_user%;查询没有主键的表
SELECT t.table_name, t.table_type,n.table_schema,n.table_name,n.constraint_name FROM information_schema.tables t
LEFT JOIN information_schema.key_column_usage n
ON t.table_scheman.table_schema AND t.table_name n.table_name AND n.constraint_name PRIMARY
WHERE t.table_schema xxx_db
AND t.table_type BASE TABLE
AND n.table_name is NULL;统计每个库大小
SELECT TABLE_SCHEMA, SUM(DATA_LENGTH)/1024/1024 AS Data_MB, SUM(INDEX_LENGTH)/1024/1024 AS INDEX_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like xxx_db%
GROUP BY table_schema;前十张大表
按行排序TABLE_ROWS 按表空间排序TABLE_ROWS
-- 按行
SELECT TABLE_SCHEMA AS database_name,TABLE_NAME AS table_name,TABLE_ROWS AS table_rows, ENGINE AS table_engine
,ROUND((DATA_LENGTH) /1024.0/1024,2) AS Data_MB ,ROUND((INDEX_LENGTH) /1024.0/1024,2) AS Index_MB
,ROUND((DATA_LENGTH INDEX_LENGTH)/1024.0/1024,2) AS Total_MB,ROUND((DATA_FREE)/1024.0/1024,2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like xxx_db%
ORDER BY TABLE_ROWS
DESC LIMIT 10;-- 按表空间
SELECT TABLE_SCHEMA AS database_name,TABLE_NAME AS table_name,TABLE_ROWS AS table_rows, ENGINE AS table_engine
,ROUND((DATA_LENGTH) /1024.0/1024,2) AS Data_MB ,ROUND((INDEX_LENGTH) /1024.0/1024,2) AS Index_MB
,ROUND((DATA_LENGTH INDEX_LENGTH)/1024.0/1024,2) AS Total_MB,ROUND((DATA_FREE)/1024.0/1024,2) AS Free_MB
FROM information_schema.tables AS T1
WHERE T1.TABLE_SCHEMA like xxx_db%
ORDER BY Total_MB
DESC LIMIT 10;清理日志表
-- 清中间件的日志表
TRUNCATE TABLE xxljob.xxl_job_log;
TRUNCATE TABLE nacos.his_config_info;-- 清业务库的日志表
TRUNCATE TABLE xx_db.xx_table;Prepared statement needs to be re-prepared
问题当时参数 table_open_cache 16384, 但 table_definition_cache 还是默认值 2000table_definition_cache 也需要同时调大这里我设置与 table_open_cache 一样大。
MySQL提示1615: Prepared statement needs to be re-prepared
xtrabackup 问题
问题 LOCK INSTANCE FOR BACKUP xtrabackup 运行过程中会锁mysql实例如果特殊情况下中断或故障时就需要手动解锁
-- sql 执行解锁
UNLOCK INSTANCE;[Xtrabackup] could not find redo log file with LSN 813931920896 [ERROR] [MY-011825] [Xtrabackup] read_logfile() failed. [ERROR] [MY-011825] [Xtrabackup] log copying failed.
-- sql 执行刷盘
FLUSH LOGS;[Xtrabackup] Found tables with row versions due to INSTANT ADD/DROP columns [Xtrabackup] Please run OPTIMIZE TABLE or ALTER TABLE ALGORITHMCOPY on all listed tables to fix this issue.
https://www.modb.pro/db/631294 升级到 8.0.33 或 8.2.0(xtrabackup 同时升到 8.2.0)The input device is not a TTY
在 crontab 定时执行脚本中 docker run -it … 要去掉 -it , 不然就会出现 The input device is not a TTY
锁问题处理
mysql 锁实战分析
官网文档
快速处理
# 查询数据锁
SELECT * FROM performance_schema.data_locks;# 事务ID查询进程ID
SELECT trx.trx_id, trx.trx_mysql_thread_id AS PID, trx.trx_state, trx.trx_query
FROM information_schema.innodb_trx trx;# 释放锁下面语句结果复制出来执行把对应的 pid 给 kill 掉释放锁
SELECT concat(KILL , trx.trx_mysql_thread_id,;) as cmd FROM performance_schema.data_locks lck
INNER JOIN information_schema.innodb_trx trx ON trx.trx_id lck.ENGINE_TRANSACTION_ID;查询事务等待和阻塞情况
# 查询事务等待和阻塞情况
SELECTr.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query,b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_thread,b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id w.requesting_engine_transaction_id;# 直接用 sys.innodb_lock_waits 查询
SELECTwaiting_trx_id,waiting_pid,waiting_query,blocking_trx_id,blocking_pid,blocking_query
FROM sys.innodb_lock_waits;eg.
waiting trx idwaiting threadwaiting queryblocking trx idblocking threadblocking queryA46SELECT b FROM t FOR UPDATEA35SELECT SLEEP(100)A57SELECT c FROM t FOR UPDATEA35SELECT SLEEP(100)A57SELECT c FROM t FOR UPDATEA46SELECT b FROM t FOR UPDATE
在识别阻塞事务时如果发出查询的会话已闲置则阻塞查询会报告一个空值。在这种情况下使用以下步骤确定阻塞查询
确定阻塞事务的进程列表 ID。在 sys.innodb_lock_waits 表中阻塞事务的进程表 ID 就是 blocking_pid 值。使用 blocking_pid查询 MySQL 性能模式线程表以确定阻塞事务的 THREAD_ID。例如如果 blocking_pid 为 6请执行以下查询
SELECT THREAD_ID FROM performance_schema.threads WHERE PROCESSLIST_ID 6;使用 THREAD_ID 查询性能模式 events_statements_current 表以确定线程执行的最后一次查询。例如如果 THREAD_ID 是 28请执行以下查询
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_current WHERE THREAD_ID 28;如果线程执行的最后一次查询信息不足以确定锁被锁定的原因可以查询性能模式events_statements_histor表查看线程执行的最后 10 条语句。
SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history WHERE THREAD_ID 28 ORDER BY EVENT_ID;innodb_trx
查询出事务 id 和 pid( processlist 中的 id)
SELECT trx.trx_id, trx.trx_mysql_thread_id AS PID, trx.trx_state, trx.trx_query FROM information_schema.innodb_trx trx;trx_idpidtrx_statetrix_query292681221590590RUNNING292677969587984RUNNING
processlist
根据 pid 查询进程详情
SELECT * FROM performance_schema.PROCESSLIST WHERE id in (587984,590590);iduserhostdbcommandtimestateinfoexecute_engine587984xxx172.8.8.1:50372xxxSleep6468PRIMARY590590xxx172.8.8.1:1885xxxSleep3095PRIMARY
data_locks
官网文档
MySQL的data_locks表是InnoDB存储引擎用于记录当前事务持有和请求的数据锁信息的。这些数据锁用于控制并发访问确保数据的一致性和完整性。在MySQL中当一个事务访问某个数据时它可以对该数据加锁这样其他事务就无法修改或读取该数据直到锁被释放。
InnoDB存储引擎在做SELECT、INSERT、DELETE、UPDATE操作的时候不会为表加上S锁或者X锁的但是会使用到意向锁这种表级别锁。MyISAM引擎是不支持意向锁的。
意向锁又分为意向共享锁intention shared lock,IS事务有意向对表中的某些行加共享锁S锁意向排他锁intention exclusive lock,IX事务有意向对表中的某些行加排他锁X锁。事务在给一个数据行加共享锁前必须取得该表的IS锁事务在给一个数据行加排他锁前必须取得该表的IX锁。意向锁的引入主要是为了在进行行级锁或页级锁时提供一种机制来表示事务可能会对表中的某些行或页面进行锁定操作的意向从而提高并发控制的效率。
如果没有意向写锁mysql在加行锁之前需要循环扫描表判断表是否有行锁。
有了意向写锁之后mysql在加行写锁时只要判断表上没有意向写锁可以直接加行写锁无需扫描。
查看数据锁的情况查询出来的 THREAD_ID 后面都有关联在使用
SELECT * FROM performance_schema.data_locks WHERE engine_transaction_id in (292681221,292677969);ENGINEENGINE_LOCK_IDENGINE_TRANSACTION_IDTHREAD_IDEVENT_IDOBJECT_SCHEMAOBJECT_NAMEPARTITION_NAMESUBPARTITION_NAMEINDEX_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_MODELOCK_STATUSLOCK_DATAINNODB139813616145968:41767:13981111875760829267796958834896xxxtask_apply139811118757608TABLEIXGRANTEDINNODB139813616145968:32475:13981111875752029267796958834890xxxtask_todo139811118757520TABLEIXGRANTEDINNODB139813616145968:31409:4:19:13981111875460829267796958834890xxxtask_todoPRIMARY139811118754608RECORDX,REC_NOT_GAPGRANTEDINNODB139813616145968:40701:5:6:13981111875495229267796958834896xxxtask_applyPRIMARY139811118754952RECORDX,REC_NOT_GAPGRANTEDINNODB139813616109608:32475:13981111848508829268122159095485xxxtask_todo139811118485088TABLEIXGRANTED
588348 获取 task_todotask_apply 的行写锁X自然也有意向排他锁 IX590954 只获取了 task_todo 意向写锁并没有获得行锁可能是修改的是同一记录但 588348 没有释放写锁。
data_lock_waits
官网文档
select * FROM performance_schema.data_lock_waitsmetadata_locks
官网文档
元数据锁简称MDL锁属于表锁范畴。MDL的作用是保证读写的正确性。比如如果一个查询正在遍历一个表中的数据而执行期间另一个线程对这个表结构做变更增加了一列那么查询线程拿到的结果跟表结构对不上肯定是不行的。 因此当对一个表做 增删改查 操作的时候加MDL读锁当要对表做 结构变更 操作的时候加 MDL写锁。
读锁之间不互斥因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的用来保证变更表结构操作的安全性解决了DML和DDL操作之间的一致性问题。不需要显式使用在访问一个表的时候会被自动加上。
ISIXAUTO-INCSXIS√√√√XIX√√√XXAUTO-INC√√XXXS√XX√XXXXXXX
思考我们在对表做Alter操作的时候是否能立即执行
答案是不一定如果此时还有事务在进行增删改查操作Alter操作会阻塞必须等待所有事务执行完毕才能执行。
需要注意的是我们在对大表做DDL的时候有可能会造成数据库崩溃。所以要求我们尽量在业务不繁忙的时候执行DDL或者是使用第三方工具如 pt-online-schema-change 等来安全的执行表的DDL操作。
SELECT * from performance_schema.metadata_locks ml WHERE ml.owner_thread_id in (588348,590954);OBJECT_TYPEOBJECT_SCHEMAOBJECT_NAMECOLUMN_NAMEOBJECT_INSTANCE_BEGINLOCK_TYPELOCK_DURATIONLOCK_STATUSSOURCEOWNER_THREAD_IDOWNER_EVENT_IDTABLExxxtask_todo139811196356656SHARED_READTRANSACTIONGRANTEDsql_parse.cc:625158834885TABLExxxtask_todo139811197083072SHARED_WRITETRANSACTIONGRANTEDsql_parse.cc:625158834890TABLExxxtask_apply139811195232128SHARED_READTRANSACTIONGRANTEDsql_parse.cc:625158834892TABLExxxtask_apply139811208160848SHARED_WRITETRANSACTIONGRANTEDsql_parse.cc:625158834896TABLExxxtask_todo139811162016464SHARED_READTRANSACTIONGRANTEDsql_parse.cc:625159095480TABLExxxtask_todo139811117870400SHARED_WRITETRANSACTIONGRANTEDsql_parse.cc:625159095485
events_statements_current
SELECT * FROM performance_schema.events_statements_current WHERE THREAD_ID IN (588348,590954)THREAD_IDEVENT_IDEND_EVENT_IDEVENT_NAMESOURCETIMER_STARTTIMER_ENDTIMER_WAITLOCK_TIMESQL_TEXTDIGESTDIGEST_TEXTCURRENT_SCHEMAOBJECT_TYPEOBJECT_SCHEMAOBJECT_NAMEOBJECT_INSTANCE_BEGINMYSQL_ERRNORETURNED_SQLSTATEMESSAGE_TEXTERRORSWARNINGSROWS_AFFECTEDROWS_SENTROWS_EXAMINEDCREATED_TMP_DISK_TABLESCREATED_TMP_TABLESSELECT_FULL_JOINSELECT_FULL_RANGE_JOINSELECT_RANGESELECT_RANGE_CHECKSELECT_SCANSORT_MERGE_PASSESSORT_RANGESORT_ROWSSORT_SCANNO_INDEX_USEDNO_GOOD_INDEX_USEDNESTING_EVENT_IDNESTING_EVENT_TYPENESTING_EVENT_LEVELSTATEMENT_IDCPU_TIMEMAX_CONTROLLED_MEMORYMAX_TOTAL_MEMORYEXECUTION_ENGINE5883489595statement/com/Executeinit_net_server_extension.cc:10212885788008093800012885788119174500011108070004000000xxx000000Rows matched: 1 Changed: 1 Warnings: 000101000000000000085TRANSACTION0682950509084481066609PRIMARY5909548484statement/com/Executeinit_net_server_extension.cc:1021322314220725430001322814278650650005000579252200050005436000000xxx1205HY000Lock wait timeout exceeded; try restarting transaction10000000000000000080TRANSACTION069273860329376350460PRIMARY
其他
select * from performance_schema.events_transactions_current where thread_id in (588348,590954);
select * from performance_schema.threads where thread_id in (588348,590954);SELECT * FROM performance_schema.events_statements_history WHERE THREAD_ID IN (588348,590954);
select * from performance_schema.events_transactions_history where thread_id in (588348,590954) ORDER BY event_id;手动备份单表
mysql集群不支持无主键表, 手动备份单表时务必按下述操作, 否则会导致集群无法启动
# 错误的方式CREATE TABLE xx_table_bakxxx AS SELECT * FROM xx_table;# 这个方法不会备份主键索引注释# 正确的方式CREATE TABLE xx_table_bakxxx LIKE xx_table;INSERT INTO xx_table_bakxxx SELECT * FROM xx_table;清理耗时过长的sql
压测环境的mysql数据已经做了一个清场的存储过程, 如果环境变卡, 监控界面显示cpu过高, 请再压测前调用以便杀掉卡死的sql语句
# 执行SQL CALL xxx.kill_overtime_procs(120);# 验证SQL SELECT * FROM information_schema.processlistWHERE command in (Query, Execute)AND time 0AND user system user;
