MySQL插入大批量数据时报错“The total number of locks exceeds the lock table size”的解决办法
事情的原因是:我执行了一个load into
语句的SQL将一个很大的文件导入到我的MySQL数据库中,执行了一段时间后报错“The total number of locks exceeds the lock table size”。
首先使用命令 show variables like '%storage_engine%'
查看MySQL的存储引擎:
mysql> show variables like '%storage_engine%';
+----------------------------------+--------+
| Variable_name | Value |
+----------------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.00 sec)
可以看到InnoDB是MySQL的默认引擎。
报错“The total number of locks exceeds the lock table size”说明MySQL的默认配置已经无法满足你的需求了,
InnoDB表执行大批量数据的更新,插入,删除操作时会出现这个问题,
需要调整InnoDB全局的innodb_buffer_pool_size的值来解决这个问题,并且重启mysql服务。
首先我们通过命令 show variables like "%_buffer_pool_size%"
查看MySQL缓存池的大小:
mysql> show variables like "%_buffer_pool_size%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| innodb_buffer_pool_size | 8388608 |
+-------------------------+---------+
1 row in set, 1 warning (0.00 sec)
可以看到,默认的缓存池大小是 8388608 = 8 * 1024 * 1024 = 8 MB。我们需要把它改大一点。
那么到底是多少呢,就是说你剩多少内存,用多少内存咯,我估计我有个3个G的内存可以用,
那么我可以将innodb_buffer_pool_size的值设成310241024*1024=3221225472。
然后我们配置一下``文件(MySQL Installer安装的话,这个是配置文件的默认位置),将
innodb_buffer_pool_size=8M
修改为:
innodb_buffer_pool_size=3G
对于这个值的配置,其实在配置文件中也给了说明:
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
然后重启mysqld服务。(可通过命令行执行services.msc进入服务窗口)
然后在命令行执行命令查看此时的缓存池大小:
mysql> show variables like "%_buffer_pool_size%";
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 3221225472 |
+-------------------------+------------+
1 row in set, 1 warning (0.00 sec)
可以看到这个值已经修改成了我们想要的大小 —— 3GB。
再次运行我的导入文件的SQL,发现可以了,而且还很快呢。
但是内存也是有些吃紧的。
最新文章
- Visualize The Workshop
- ArcGIS 最短路径计算
- 使用EntityFramework6连接MySql数据库
- python:UnicodeDecodeError: ‘ascii’ codec can’t decode byte 0xef in position xxx: ordinal not in range(128)
- SEO学习之路
- uva 10779 Collectors Problem 网络流
- SGU 201 Non Absorbing DFA (DP)
- Flash中用AS3做的游戏,导出apk安装到手机上滤镜效果出不来为什么?
- WPF弹性模拟动画
- java字节数组格式化为十六进制字符串
- XJOI1657&;Codevs1255搭积木【树状动规】
- windows 下 Django 搭架子 - 从建立project到建立app
- new 经典基础模板总结
- linux dd指令
- Linux/Unix 新手和专家教程
- fiddler 抓取iphone发出的http和https包
- ClientScript.GetCallbackEventReference实现局部刷新
- pyDay5
- thinkphp5使用redis
- 【BZOJ5008】方师傅的房子 三角剖分