注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

早衰男的巢

大音希声 道隐无名

 
 
 

日志

 
 

MySQL 减肥笔记  

2013-05-15 17:00:07|  分类: others note. |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

背景:

公司的一个应用系统,近段时间较为频繁出现负载高、DB检索响应慢的情况——即使并发的查询数量并不多,需执行的sql语句也并不复杂或低效。查DB服务器见库文件 ibdata1 文件已经增长到88G的巨型尺寸!

再检查

mysql> show status like “table%”;

+-----------------------+----------+
| Variable_name???????? | Value??? |
+-----------------------+----------+
| Table_locks_immediate | 13463538 |
| Table_locks_waited??? | 546?????? |
+-----------------------+----------+
2 rows in set (0.01 sec)

出现大量的死锁,重启mysqld几小时后问题重现,再检查数还存有部分最早可追溯2007年的历史数据。于是决定大幅度清理,只保留2012年至今的记录。

*最初并未能确认问题是在于数据库,但尝试对DB减肥后得到比较理想的效果。

待解决的问题、注意事项、备忘:

1. 要清理的若干个表存在外键关联,而且外键没有 ON DELETE 的规则,需要外链的倒方向逐层顺序清理。否则删除时可能会触发外键约束错误

53282 :Cannot delete or update a parent row: a foreign key constraint fails (`main_tab`, CONSTRAINT `FK3E3D45E818A4A933` FOREIGN KEY (`id`) REFERENCES `sub_tab` (`mid`))

//eg. 2表 main_tab , sub_tab? 。sub_tab.mid= main_tab.id

//删除时应先清理 sub_tab
DELETE sub_tab

FROM sub_tab,main_tab
WHERE sub_tab.mid= main_tab.id

AND {其他 条件}……;

2. 进行optimize table 会锁表,期间无法正常存取数据,反之如执行时相关表或行已锁也会导致optimize失败。

3. DELETE的条件注意不要选及没有做索引的字段,会导致删除性能大幅下降。

4. 使用脚本进行批处理。

mysql> source? /路径/脚本.sql;

5. 对于所有共享一个ibdata库文件的innodb表,删除、optimize甚至drop都不会释放磁盘空间,文件依然会被ibdata1占用。网上资料建议释放磁盘空间的做法是完整mysqldump后删除库、索引、日志文件,再重新导入,但这个操作有风险,不建议做。

6. mysql 参数 innodb_buffer_pool_size? 缺省为8M。执行大表的optimize或大批量数据带锁操作时会触发以下错误

ERROR 1206 (HY000): The total number of locks exceeds the lock table size

参考:http://major.io/2010/02/16/mysql-the-total-number-of-locks-exceeds-the-lock-table-size-2/

解决办法是编辑 my.cnf 在[mysqld] 中设定 innodb_buffer_pool_size = 1G。 大小视乎服务器的可用内存(<=50%),保存后重启mysqld,确认方法查看。

mysql>

show variables like "innodb%";
+---------------------------------+------------------------+
| Variable_name?????????????????? | Value????????????????? |
+---------------------------------+------------------------+
| innodb_additional_mem_pool_size | 1048576??????????????? |
| innodb_autoextend_increment???? | 8????????????????????? |
| innodb_buffer_pool_awe_mem_mb?? | 0????????????????????? |
| innodb_buffer_pool_size???????? | 1073741824???????????? |

……

7. 当遇到锁冲突操作失败时查看锁定的详细情况,找相关的线索

mysql> show innodb status \G;

?

?

操作顺序:

1. 关闭应用服务器或断开所有DB的链接以防在操作过程中锁定导致压缩失败

2. 重启mysqld, mysqldump 备份整个库(未删除清理的版本)

3. DELETE \ TRUNCATE 相关需要清理的表

4. OPTIMIZE TABLE 相关表

5.* 不需要的表可以在optimize后进行DROP TABLE操作

6. 再mysqldump一个清理和重建索引后的完整备份(如果需要做后续的释放磁盘空间操作)

7. 启动应用服务。

(其中2-6步可以使用sql脚本批处理)

* mysqldump 和分析日志还有把单个ibdata库文件修改为每个innodb表独立库文件的方法不在本文赘述。

  评论这张
 
阅读(903)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018