mysql服务记录一次操作千万级数据归档错误

8次阅读

共计 1053 个字符，预计需要花费 3 分钟才能阅读完成。

业务上有一张回传状态记录数据，随着业务及时间的发展，这张表的数据量达 1400W 条数; 应需求做归档操作；应数据量大使用 delete 操作效率低，不现实；方案操作记录如下：

## 锁表 ----> 备份新数据 ----> 重命名表；---- 锁表
LOCK TABLES node_flow read local; 将当前表设置为只读，不能进行插入或更新操作。UNLOCK TABLES; 锁住表了，使用 UNLOCK 进行释放。---- 备份数据
INSERT INTO node_flow_copy SELECT * FROM node_flow WHERE create_time > '2020-05-01'
---- 重命名表
ALTER TABLE node_flow RENAME TO node_flow_20200603;
RENAME TABLE node_flow_copy TO node_flow;

因表数据比较大，在执行 INSERT 语句时出现报错；
“ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction” 的问题。
### 解决；
1）在执行 insert 操作时, 运行的进程被卡住，分析原因；

---- 查看数据库进程列表；Show processlist；kill id;
  操作发现 command 显示 killed，sql 被阻塞了；---- 查看数据库锁及锁等待；select * from information_schema.innodb_locks;
select * from information_schema.innodb_lock_waits;
---- 查看数据进程 及锁、触发器
show processlist;
show engine innodb status\G;
Show triggers from DB_NAME;
----- 查看系异常统进程；Iotop
perf top -p `pidof mysqld`

2）确定调整 innodb 内存使用大小解决卡住问题

 ---- 查看数据库内存大小的问题
show variables like 'innodb_buffer_pool%';
发现 innodb_buffer_pool_size 只有 5M，感觉原因就是因为 buffer pool 过小，增加 buffer\_pool 的大小到 20G
 select 20\*1024\*1024\*1024;
 set global innodb\_buffer\_pool\_size=21474836480;
 再使用 show processlist; 等待一下 killd 状态不在了；

正文完