MySQL unble to lock ./ibdata1 error

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 案例一:   环境说明:mysql 5.6 用户连接不上mysql ,查看error 日志:unble to lock ./ibdata1 error,等待20分钟左右,在启动mysql恢复正常。

案例一:

 

环境说明:mysql 5.6

用户连接不上mysql ,查看error 日志:unble to lock ./ibdata1 error,等待20分钟左右,在启动mysql恢复正常。./ibdate1 文件 40G

 

解决方法:

1、查看slow log

2show procedure status  

#查看存储过程,该实例有很多存储过程

3show create procedure 存储过程名称 \G

 

结论:复杂的存储过程(数据库里的数据也不对),导致sql语句执行的时间很长,7万多秒。这时mysql hug住了 占用了./ibdate1 文件

 

案例二:

有一个线上库的ibdate1文件从1G,一直不断增长到100G,到底怎么回事呢?

 

ibdate1 文件暴增原因:

  1. 大量事务,产生大量来不及purgeundo log
  2. 有旧事务长时间未提交,产生大量旧undo log
  3. file i/o 性能差,purge进度慢
  4. 32bit系统下有bug
  5. 使用共享表空间方式,所有表数据放在一起

 

大量事务,产生大量来不及purgeundo log

ab37d7436654b858f2f0fb91b0496844f27c919f

最新事务ID21520177836

最新purged的事务ID21516167648

二者相差:4010188

等待purgeundo事务列表:41341108

 

有旧事务长时间未提交,产生大量旧undo log

8699b313c012b6511e0dbce8d45cb70fd495bbed

 

解决办法:

调度ibdate1的初始大小

innodb_date_file_path = ibdata1:1G:autoextend

升级到5.6及以上(64-bit),采用独立undo表空间

innodb_undo_directory = path

innodb_undo_logs = 128

innodb_undo_tablespaces = 126

 

启用独立表空间,ibdata1 只存放系统数据

  innodb_file_per_table =  1

增加purge线程数

  innodb_purge_threads = 4

 

提高file i/o能力

 raid 10 & wb & BBU

xfs

deadline/noop

ssd/pice ssd

 

事务及时提交,不要积压

  及时commit/rollback

注意事务自动提交设置

  默认打开autocommit = 1

检查开发框架,确认autocommit =0 的地方,事务结束后都有提交或回滚

 

mysql参数优化:

innodb_buffer_pool_size:约物理内存的50%~~70%

innodb_date_file_path:初始大小至少1G

设置独立数据表空间以及独立undo表空间(5.6+)

innodb_log_file_size ,5.5及以上1G以上,5.5以下建议不超过512M

innodb_flush_log_at_trx_commit,0 => 最快数据最不安全,1 =>最慢最安全,2=>折中

innodb_max_dirty_pages_pct: 25%~~50% 为宜

innodb_io_capacity :普通机械盘=>1000左右,ssd=>10000左右,PCle SSD=>20000以上

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
5月前
|
关系型数据库 MySQL
Mysql 主键冲突(ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY')
Mysql 主键冲突(ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY')
221 0
|
4月前
|
关系型数据库 MySQL Linux
【Linux环境】centos安装mysql5.7.26报 ./mysqld: error while loading shared libraries: libaio.so.1: cannot op
【Linux环境】centos安装mysql5.7.26报 ./mysqld: error while loading shared libraries: libaio.so.1: cannot op
96 0
|
4月前
|
SQL 关系型数据库 MySQL
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
错误提示: Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; che
81 0
|
1月前
|
SQL 数据采集 关系型数据库
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
如何解决MySQL报错 You have an error in your SQL syntax; check the manual that corresponds to your MySQL?
|
7月前
|
关系型数据库 MySQL
|
3月前
|
关系型数据库 MySQL
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such
|
4月前
|
关系型数据库 MySQL Shell
MySQL报错:Got error 28 from storage engine
MySQL报错:Got error 28 from storage engine
|
4月前
|
缓存 关系型数据库 MySQL
Mysql备份时报错:mysqldump: Got error: 1016: Can't open file: '**.fr..
Mysql备份时报错:mysqldump: Got error: 1016: Can't open file: '**.fr..
|
4月前
|
SQL 关系型数据库 MySQL
【SQL异常】启动MySQL报错:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)
【SQL异常】启动MySQL报错:ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)
62 1
|
5月前
|
关系型数据库 MySQL
`navicat`外网链接`mysql`报`...error 10061`
`navicat`外网链接`mysql`报`...error 10061`
`navicat`外网链接`mysql`报`...error 10061`