我在localhost中使用MySQL作为在R中执行统计的“查询工具”,也就是说,每次我运行R脚本时,我都会创建一个新的数据库(a),创建一个新的表(B),将数据导入到B中,提交一个查询以获得我需要的数据,然后我删除B和删除a。
这对我来说很好,但我意识到ibdata文件的大小正在迅速增加,我在MySQL中没有存储任何东西,但ibdata1文件已经超过100 MB。
我正在使用或多或少的默认MySQL设置进行设置,是否有一种方法可以让我在一段固定的时间后自动收缩/清除ibdata1文件?
我在localhost中使用MySQL作为在R中执行统计的“查询工具”,也就是说,每次我运行R脚本时,我都会创建一个新的数据库(a),创建一个新的表(B),将数据导入到B中,提交一个查询以获得我需要的数据,然后我删除B和删除a。
这对我来说很好,但我意识到ibdata文件的大小正在迅速增加,我在MySQL中没有存储任何东西,但ibdata1文件已经超过100 MB。
我正在使用或多或少的默认MySQL设置进行设置,是否有一种方法可以让我在一段固定的时间后自动收缩/清除ibdata1文件?
当前回答
在新版的mysql服务器菜谱上面会粉碎“mysql”数据库。 在旧版本中,它是有效的。在new中,一些表切换到INNODB类型,这样做会损坏它们。 最简单的方法是:
转储所有数据库 卸载mysql服务器, 添加remain my.cnf:
[mysqld]
innodb_file_per_table=1
删除/var/lib/mysql中的所有文件 安装mysql服务器 恢复用户和数据库
其他回答
如前所述,您不能收缩ibdata1(为此需要转储和重新构建),但通常也没有真正的必要。
使用autoextend(可能是最常见的大小设置),ibdata1预分配存储空间,在存储空间接近满时增长。这使得写入速度更快,因为已经分配了空间。
当你删除数据时,它不会缩小,但文件内部的空间被标记为未使用。现在,当您插入新数据时,它将在进一步增长文件之前重用文件中的空白空间。
所以只有当你真的需要这些数据时,它才会继续增长。除非您确实需要为另一个应用程序使用空间,否则可能没有理由缩小它。
当你删除innodb表时,MySQL不会释放ibdata文件中的空间,这就是为什么它一直在增长。这些文件几乎从不缩小。
如何缩小现有的ibdata文件:
https://dev.mysql.com/doc/refman/5.6/en/innodb-system-tablespace.html#innodb-resize-system-tablespace
您可以编写脚本,并安排脚本在固定的时间后运行,但对于上面描述的设置,多个表空间似乎是更简单的解决方案。
如果使用配置选项innodb_file_per_table,则会创建多个表空间。也就是说,MySQL为每个表创建单独的文件,而不是一个共享文件。这些单独的文件存储在数据库的目录中,当您删除该数据库时,它们将被删除。在您的情况下,这样就不需要收缩/清除ibdata文件了。
关于多个表空间的更多信息:
https://dev.mysql.com/doc/refman/5.6/en/innodb-file-per-table-tablespaces.html
在新版的mysql服务器菜谱上面会粉碎“mysql”数据库。 在旧版本中,它是有效的。在new中,一些表切换到INNODB类型,这样做会损坏它们。 最简单的方法是:
转储所有数据库 卸载mysql服务器, 添加remain my.cnf:
[mysqld]
innodb_file_per_table=1
删除/var/lib/mysql中的所有文件 安装mysql服务器 恢复用户和数据库
似乎没有人提到innodb_undo_log_truncate设置可能产生的影响。
在阅读了Percona关于这个主题的博客文章后,我在MariaDB 10.6中启用了截断UNDO LOG条目的功能,它填充了ibdata1的95%,在完全删除和恢复之后,从那时起,ibdata1就不再增长了。
在默认innodb_undo_log_truncate = 0的情况下,我的ibdata1很容易就达到了数据库空间占用的10%,也就是几十gb。
innodb_undo_log_truncate = 1时,ibdata1的大小为76 Mb。
如果您的目标是监视MySQL的空闲空间,而您又不能停止MySQL来缩小ibdata文件,那么可以通过表状态命令来获取它。例子:
MySQL > 5.1.24:
mysqlshow --status myInnodbDatabase myTable | awk '{print $20}'
MySQL < 5.1.24:
mysqlshow --status myInnodbDatabase myTable | awk '{print $35}'
然后将这个值与ibdata文件进行比较:
du -b ibdata1
来源:http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html