如何限制临时表的大小?

问题描述

我在数据库中有比较大的(InnoDB)表；显然，用户能够使用JOIN进行SELECT，从而产生临时的，大的(因而在磁盘上)表.有时，这些磁盘太大，以至于耗尽磁盘空间，从而导致各种奇怪的问题.

是否有一种方法可以限制磁盘上表的临时表最大大小 ，以便该表不会过度占用磁盘? tmp_table_size 仅适用于内存中表，尽管有名字.我没有在文档.

解决方案

在 MariaDB 和 MySQL 中没有这个选项.几个月前，我遇到了与您相同的问题，我进行了很多搜索，最终在NAS上为它们的主要数据集创建了一个特殊的存储区域，从而部分解决了该问题.

在NAS上或内部HDD上的分区上创建一个文件夹，根据定义，它的大小将受到限制，然后将其安装，然后在mysql ini中，将临时存储分配给该驱动器:(选择Windows/Linux)

  tmpdir ="mnt/DBtmp/"tmpdir ="T:\" 

此更改后，应重新启动mysql服务.

使用这种方法，一旦驱动器已满，您在磁盘上的查询仍然会遇到奇怪的问题"，但是其他问题都消失了.

I have largish (InnoDB) tables in a database; apparently the users are capable of making SELECTs with JOINs that result in temporary, large (and thus on-disk) tables. Sometimes, those are so large that they exhaust disk space, leading to all sorts of weird issues.

Is there a way to limit temp table maximum size for an on-disk table, so that the table doesn't overgrow the disk? tmp_table_size only applies to in-memory tables, despite the name. I haven't found anything relevant in the documentation.

解决方案

There's no option for this in MariaDB and MySQL. I ran into the same issue as you some months ago, I searched a lot and I finally partially solved it by creating a special storage area on the NAS for themporary datasets.

Create a folder on your NAS or a partition on an internal HDD, it will be by definition limited in size, then mount it, and in the mysql ini, assign the temporary storage to this drive: (choose either windows/linux)

tmpdir="mnt/DBtmp/"
tmpdir="T:\"

mysql service should be restarted after this change.

With this approach, once the drive is full, you still have "weird issues" with on-disk queries, but the other issues are gone.

这篇关于如何限制临时表的大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！