mysql - 如何在不挂起 MySQL 进程的情况下使用 innodb_file_per

问题描述

我们有一个重复的过程，我们想要并且需要清理我们的数据库。每个客户或潜在客户都有自己的数据库（有 300 个表，并且每个月都在增加），该数据库在几秒钟内启动，并包含一些基本数据。

几个月后，需要清理数据库。我们简单地调用DROP DATABASE customer_1每个数据库（让 MySQL 服务器在每个语句之间有 10 秒的时间来“休息”），然后DROP USER 'customer_1'@'127.0.0.1').

每隔一段时间，整个数据库就会挂起。SHOW PROCESSLIST给

Id     User       Command    Time    State         Info
[pid]  adm-user   Query      300     System lock   DROP DATABASE `customer_1`

不会完成任何新查询。杀死相关的查询 pid 将导致 Command=Killing，仅此而已。什么都没发生。MySQL 守护进程也无法停止，因为它仍在等待完成查询。

我们已经关闭了整个服务器的电源，重新启动它，并让 MySQL 进行自动崩溃恢复，这工作正常。之后，我们可以再删除 10-30 个数据库，然后这个事件会重复。

我们已经阅读了大量有关该主题的内容，包括但不限于：

似乎共识是，是的，它是 MySQL 在表（空间）上使用全局互斥锁，并结合大缓冲池大小。

我们的 my.cnf：

innodb_file_per_table   = 1
innodb_buffer_pool_size = 9G
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT
table_open_cache        = 200000
table_definition_cache  = 110000
innodb_flush_log_at_trx_commit = 2

有什么方法可以让我们负责任地删除数据库——即，不让服务器因其他潜在客户而停机？

我读过简单地删除所有表文件可以工作，然后删除数据库，其中 MySQL应该简单地删除对数据库的引用。

标签： mysqlinnodbdrop-table

解决方案

您应该做的一件重要的事情是为您的 MySQL 数据目录使用 XFS 文件系统。

在 ext3 文件系统上删除大文件需要太多时间，正如您无疑在您链接到的 Percona 博客中所读到的那样。使用 XFS 可以更快地删除大文件，因此全局互斥锁的保留时间更短。

我还会一次删除一张表，以进一步减少互斥锁的持有时间。然后在删除所有表之后，删除数据库。

MySQL 中的数据库根本不是物理对象。它是 MySQL 数据目录的子目录，以及一个名为的小文件db.opt，用于存储数据库的一些属性，例如其默认字符集（在 MySQL 8.0 中，这甚至不再是一个单独的文件）。在删除所有表之后，删除数据库本身是微不足道的。

另一个建议是先删除客户的 MySQL 用户，然后让 MySQL 运行几个小时，直到该客户表中的数据不再缓存在缓冲池中。当你删除一个大表时，MySQL 必须扫描缓冲池以释放属于该表的页面。缓冲池越大，所需的时间就越长。因此，如果您让该客户表的页面过期并离开缓冲池，您可以最大限度地减少这种影响。这可能需要一些时间，因为它更多是由对其他表的需求驱动的。除了删除表之外，没有什么好的方法可以强制表的页面离开缓冲池。

我已经在某些环境中做到了。将“DROP TABLE”请求放入 RENAME TABLE 以将表移动到另一个用户无权访问的模式中。然后定期运行脚本以真正删除已在该笔中超过 7 天的表格。当来自其他表的数据取代它们时，这为页面逐渐从缓冲池中逐出提供了时间。此外，它还为用户提供了一个宽限期，让他们在决定最终删除他们需要的表时改变主意。

mysql - 如何在不挂起 MySQL 进程的情况下使用 innodb_file_per_table=1 删除 1000 多个数据库？

问题描述

解决方案

推荐阅读