乐趣区

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

问题

业务上有一张回传状态记录数据,随着业务及时间的发展,这张表的数据量达 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 状态不在了;
退出移动版