跳过复制到磁盘mysql上的tmp表 [英] skip copying to tmp table on disk mysql

问题描述

我对大型mysql查询有疑问.是否可以跳过mysql用于大型查询的复制到磁盘上的tmp表步骤，还是可以使其运行得更快?因为此步骤花费的时间太长，无法取回我的查询结果.我在MySQL页面上读到mysql执行此操作来节省内存，但是我不在乎节省内存，我只是想快速获取查询结果，我的机器上有足够的内存.另外，我的表已正确索引，所以这不是查询缓慢的原因.

I have a question for large mysql queries. Is it possible to skip the copying to tmp table on disk step that mysql takes for large queries or is it possible to make it go faster? because this step is taking way too long to get the results of my queries back. I read on the MySQL page that mysql performs this to save memory, but I don't care about saving memory I just want to get the results of my queries back FAST, I have enough memory on my machine. Also, my tables are properly indexed so that's not the reason why my queries are slow.

有帮助吗?

谢谢

推荐答案

您可以做两件事来减轻这种影响

There are two things you can do to lessen the impact by this

选项1:增加变量 max_heap_table_size

OPTION #1 : Increase the variables tmp_table_size and/or max_heap_table_size

这些选项将控制内存临时表在被认为过大之前可以有多大，然后将页面作为临时MyISAM表存入磁盘.这些值越大，复制到磁盘上的tmp表"的可能性就越小.请确保您的服务器具有足够的RAM和 <如果单个DB连接需要大量RAM用于其临时表，则对strong> max_connections 进行适当配置.

These options will govern how large an in-memory temp table can be before it is deemed too large and then pages to disk as a temporary MyISAM table. The larger these values are, the less likely you will get 'copying to tmp table on disk'. Please, make sure your server has enough RAM and max_connections is moderately configured should a single DB connection need a lot of RAM for its own temp tables.

选项2:将RAM磁盘用于tmp表

您应该能够在Linux中配置RAM磁盘，然后设置在mysql中，="=" noreferrer> tmpdir 是已安装RAM磁盘的文件夹.

You should be able to configure a RAM disk in Linux and then set the tmpdir in mysql to be the folder that has the RAM disk mounted.

对于初学者，请在操作系统中配置RAM磁盘

For starters, configure a RAM disk in the OS

在Linux中创建一个名为/var/tmpfs的文件夹

Create a folder in the Linux called /var/tmpfs

mkdir /var/tmpfs

下一步，将此行添加到/etc/fstab中(例如，如果您想要16GB RAM磁盘)

Next, add this line to /etc/fstab (for example, if you want a 16GB RAM disk)

none                    /var/tmpfs              tmpfs   defaults,size=16g        1 2

然后重新启动服务器.

注意:可以在不重新引导的情况下制作RAM磁盘.只需记住，在服务器重新启动后，仍将上述行添加到/etc/fstab中即可获得RAM磁盘.

Note : It is possible to make a RAM disk without rebooting. Just remember to still add the aforementioned line to /etc/fstab to have the RAM disk after a server reboot.

现在适用于MySQL:

Now for MySQL:

将此行添加到/etc/my.cnf

Add this line in /etc/my.cnf

[mysqld]
tmpdir=/var/tmpfs

然后重新启动mysql.

and restart mysql.

选项3:尽快将tmp表放入RAM磁盘(假设您先应用选项2)

您可能希望将tmp表尽快移入RAM磁盘，以使MySQL不会轻易将大型内存tmp表迁移到RAM磁盘.只需将其添加到/etc/my.cnf:

You may want to force tmp tables into the RAM disk as quickly as possible so that MySQL does not spin its wheels migrating large in-memory tmp tables into a RAM disk. Just add this to /etc/my.cnf:

[mysqld]
tmpdir=/var/tmpfs
tmp_table_size=2K

并重新启动mysql.这将导致即使最小的临时表也直接在RAM磁盘中存在.您可以定期运行ls -l /var/tmpfs来查看临时表.

and restart mysql. This will cause even the tiniest temp table to be brought into existence right in the RAM disk. You could periodically run ls -l /var/tmpfs to watch temp tables come and go.

尝试一下！

CAVEAT

如果在/var/tmpfs 24/7中看不到临时表，则可能会影响操作系统的功能/性能.为确保/var/tmpfs不会过度填充，请考虑调整查询.完成后，应该会在/var/tmpfs中看到更少的tmp表.

If you see nothing but temp tables in /var/tmpfs 24/7, this could impact OS functionality/performance. To make sure /var/tmpfs does not get overpopulated, look into tuning your queries. Once you do, you should see less tmp tables materializing in /var/tmpfs.

这篇关于跳过复制到磁盘mysql上的tmp表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！