MariaDB社区

标题: MySQL碎片管理疑问 [打印本页]

作者: win1027 时间: 2013-4-11 08:43
标题: MySQL碎片管理疑问
我有一张不断增长的innodb表，已经做了哈希分区，目前两亿多数据，对该表只有delete、insert操作，会产生部分碎片，目前需要规划下碎片怎么管理清理，请问各位大侠对于大数据量的数据碎片一般是怎么处理？

另网上查资料，大部分都如 http://pengbotao.cn/mysql-suipian-youhua.html 这样说，但我试验下对于6千万数据
ALTER TABLE table_name ENGINE = Innodb; 语句耗时1个多小时，所以对于两亿多的数据来说，现网操作感觉有点不切实际

作者: kider 时间: 2013-4-11 10:26
当然两亿条数据在现网操作实在是太不切实际了。

那么探讨一下：
经过一段时间的运行，大量的增删会给表产生碎片。

如何判断：正如文章中所提示，我们不仅仅只看DATA_FREE，更多看看实际数据库总体大小情况，然后跟实际数据库文件在磁盘的大小来对比。设置一个百分比，如果超过我们的设置，认为碎片过多，需要优化。

数据库大小：
SELECT count(*) TABLES,table_schema,
concat(round(sum(table_rows)/1000000,2),'M') rows,
concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
round(sum(index_length)/sum(data_length),2) idxfrac
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY sum(data_length+index_length) DESC LIMIT 10;

复制代码

当然也可以正对某个表大小做对比，单独dump出某个表，然后和数据库中的统计做对比：

某数据库中表大小统计:
SELECT TABLE_NAME,
CONCAT(ROUND(table_rows/1000000,2),'M') ROWS,
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') DATA,
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') idx,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') total_size,
ROUND(index_length/data_length,2) idxfrac
FROM information_schema.TABLES
WHERE table_schema LIKE 'YOUDB'
ORDER BY data_length+index_length DESC LIMIT 5;

复制代码

优化碎片：
好了，如果对比数据差异较大，那么就碎片量也相对较大，就需要优化了。
只说说对单个大表吧：
用dump出来，重建新表，再导入或load进数据。这个要比直接“ALTER TABLE foo ENGINE=InnoDB;”快很多。
当然此过程中需要暂停服务。
那么如何可以不暂停服务呢？做个Replication，主备机模式，先停同步，如上方法操作备机，然后打开同步，直到同步追上，切换主备。

作者: win1027 时间: 2013-4-11 10:58
<高性能mysql>中的8.4.3 主动-被动模式下的主-主复制，可能适用于这种耗时长的操作，其他有实践操作的可以说下经验处理

欢迎光临 MariaDB社区 (http://123.56.88.72/)