MySQL在磁盘上创建临时表。如何停止？

我们正在运行一个用户当前发现缓慢的网站（Moodle）。我认为我已将问题归结为MySQL在磁盘上创建临时表。我在Mysql Workbench服务器管理中观看了变量created_tmp_disk_tables，该数目以大约50个表/秒的速度增加。使用几天后，created_tmp_disk_tables> 100k。此外，似乎没有释放内存。使用率一直在增加，直到系统变得几乎无法使用为止，我们必须重新启动MySQL。我几乎每天都需要重新启动它，首先要使用约30-35％的可用内存，然后一天要用80％的内存结束。
我在数据库中没有斑点，也无法控制查询，所以我无法尝试优化它们。我还使用了Percona Confirguration向导来生成配置文件，但是my.ini也不能解决我的问题。
问题

我应该怎么做才能停止MySQL？从磁盘上创建临时表？是否需要更改设置？我应该给它更多的内存吗？

如何阻止MySQL耗尽内存？

编辑
我已启用slow_queries日志，发现查询SELECT GET_LOCK() 被记录为慢。快速搜索显示，我已允许PHP配置（mysqli.allow_persistent = ON）中的持久连接。我关闭了这降低了MySQL消耗内存的速度。尽管如此，它仍在创建临时表。
我还检查了key_buffer size是否足够大。
我查看了变量key_writes。应该为零。如果不是，请增加key_buffer_size。我有0个key_reads和0个key_writes，所以我假设key_buffer_size足够大。
我将tmp_table_size和max-heap-table-size增加到1024M，因为created_tmp_disk_tables的增加可能表明表无法容纳在记忆中。这并不能解决问题。
参考：
http://www.mysqlperformanceblog.com/2007/08/16/how-much-overhead-is-caused-by-on-disk-temporary -tables /
编辑2
如果在SHOW GLOBAL STATUS输出中看到每秒许多sort_merge_passes，则可以考虑增加sort_buffer_size的值。我一个小时内有2个sort_merge_passes，所以我认为sort_buffer_size足够大。
参考：sort_buffer_size上的Mysql手册

编辑3
我将sort和join缓冲区修改为由@RolandoMySQLDBA建议。结果显示在下表中，但我认为created_tmp_tables_on_disk仍然很高。更改值后，我重新启动了mysql服务器，并在一天（8h）之后检查了created_tmp_tables_on_disk并计算出平均值。还有其他建议吗？在我看来，有些东西不能放入某种容器中，但我无法弄清楚它到底是什么。

+---------------------+-------------+-------------+--------------------+
| Tmp_table_size,     | Sort_buffer | Join_buffer | No of created      |
| max_heap_table_size |             |             | tmp_tables on disk |
+---------------------+-------------+-------------+--------------------+
| 125M                | 256K        | 256K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 512K        | 512K        |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 1M          | 1M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+
| 125M                | 4M          | 4M          |  100k/h            |
+---------------------+-------------+-------------+--------------------+

这是我的配置：

+-----------------------+-----------------------+
|DATABASE SERVER        |WEB SERVER             |
+-----------------------+-----------------------+
|Windows Server 2008 R2 |Windows Server 2008 R2 |
+-----------------------+-----------------------+
|MySQL 5.1.48           |IIS 7.5                |
+-----------------------+-----------------------+
|4 Core CPU             |4 Core CPU             |
+-----------------------+-----------------------+
|4GB RAM                |8GB RAM                |
+-----------------------+-----------------------+

其他信息

+--------------------+---------+
|PARAM               |VALUE    |
+--------------------+---------+
|Num of tables in Db |361      |
+--------------------+---------+
|Size of database    |2.5G     |
+--------------------+---------+
|Database engine     |InnoDB   |
+--------------------+---------+
|Read/write ratio    |3.5      |
|(Innodb_data_read/  |         |
|innodb_data_written)|         |
+--------------------+---------+
|Avg table size      |15k rows |
+--------------------+---------+
|Max table size      |744k rows|
+--------------------+---------+

这个设置是给我的，所以我对此的控制有限。 Web服务器使用的CPU和RAM很少，因此排除了该机器的瓶颈。大部分MySQL设置都来自配置自动生成工具。
我已经在几天内使用PerfMon监视了系统。由此得出的结论是，不是操作系统正在交换磁盘。

My.ini

[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="D:/DBs/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=125
query_cache_size=350M
table_cache=1520
tmp_table_size=125M
table-definition-cache= 1024
max-heap-table-size= 32M
thread_cache_size=38

MyISAM Specific options
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=125M
key_buffer_size=55M
read_buffer_size=1024K
read_rnd_buffer_size=256K
sort_buffer_size=1024K
join_buffer_size=1024K


INNODB Specific options
innodb_data_home_dir="D:/DBs/"
innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=2G
innodb_log_file_size=407M
innodb_thread_concurrency=8

评论不作进一步讨论；此对话已移至聊天。

#1 楼

看看my.ini，我有两个建议

建议＃1

我会在您的my.ini中增加以下设置

sort_buffer_size=4M
join_buffer_size=4M

这将使一些联接和排序保留在内存中。当然，一旦JOIN或ORDER BY需要的数量超过4M，它将作为MyISAM表分页到磁盘。

如果您不能以root@localhost身份登录，请使用

重新启动mysql。 >

C:\> net stop mysql
C:\> net start mysql

如果您可以以root @ localhost身份登录，则不必重新启动mysql即可使用这些设置。

只需在MySQL客户端中运行此命令：

SET @FourMegs = 1024 * 1024 * 4;
SET GLOBAL sort_buffer_size = @FourMegs;
SET GLOBAL join_buffer_size = @FourMegs;

建议＃2

由于数据位于驱动器D:上，因此驱动器C:上可能具有磁盘I / O。

请运行以下查询：

mysql> show variables like 'tmpdir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tmpdir        | C:\Windows\TEMP |
+---------------+-----------------+
1 row in set (0.00 sec)

由于我在默认情况下在桌面上运行mysql，因此我的临时表已写入驱动器C:。如果驱动器D是比驱动器C:更好的磁盘，也许可以通过在D:中设置tmpdir来将临时表映射到驱动器my.ini，如下所示：

tmpdir="D:/DBs/"

您必须重新启动mysql，因为tmpdir不是动态变量。

尝试一下!!!

UPDATE 2013-11-29 10:09 EST

建议＃3

鉴于MySQL正在Windows中运行并且您无法触摸核心程序包中的查询，我有两个想法必须同时完成。

IDEA ＃1：将数据库移到Linux机器上

您应该能够

设置Linux机器
在Linux机器上安装MySQL
在Windows中启用MySQL的二进制日志记录
mysql将数据库转储到文本SQL文件中
将SQL文件加载到在Linux中运行的MySQL
从MySQL / Windows到MySQL / Linux的设置复制
/>
IDEA＃2：重新配置Moodle以指向Linux机器

Moodle是在第一个Plac中为LAMP设计的e。只需将配置文件更改为指向Linux计算机而不是localhost。

这是旧的Moodle 2.3文档的链接，用于设置MySQL：http://docs.moodle.org/23/en/Installing_Moodle#Create_an_empty_database

我相信最新的文档也可以使用。

将数据库迁移到Linux的意义是什么？

这如何帮助临时表解决？???

然后我建议您将RAM磁盘设置为临时表的目标文件夹

Jan 04, 2013：是否有MySQL引擎或技巧来避免编写太多代码临时表到磁盘吗？

Dec 17, 2012：为什么MySQL产生这么多临时MYD文件？（实际说明）

Nov 30, 2012：同时创建许多mysql临时表是否不好？

仍会创建临时表，但会写入RAM，而不是磁盘。减少磁盘I / O。

更新2013-11-29 11:24 EST

建议＃4

我建议重新考虑建议2使用快速RAID-0磁盘（32+ GB），将其配置为驱动器T ：（对于温度，为T）。安装了这样的磁盘后，将其添加到my.ini中：

[mysqld]
tmpdir="T:\"

需要使用

net stop mysql
net start mysql

MySQL重新启动/>顺便说一句，我故意说RAID-0，以便您可以在RAID-1，RAID-10上获得良好的写入性能。 tmp表磁盘不是我要多余的东西。

如@RaymondNijland所评论的那样，在不优化查询的情况下，您无法以任何方式减少临时表的创建次数。 SUGGESTION #3和SUGGESTION #4提供了加速临时表创建和临时表I / O的唯一方法。

#2 楼

为了完整起见，我在这里回答我自己的问题
我将选择@RolandoMySQLDBA作为首选答案，因为即使实际上并不能解决我的问题，它也给了我最多的提示。
以下是我的调查结果
结论
Windows上的MySQL只是创建了许多临时表，并且通过修改配置文件的内容来调整MySQL并没有帮助。
详细信息
该表详细说明了我已修改的参数在分别执行my.ini和
执行任何查询之前。在每次测试之间都重新启动MySQL。
我将原始问题中找到的my.ini用作模板，然后
根据下表一一更改了参数的值。
我使用JMeter生成了100个并发Web请求（代表了我们的用法），重复了10次十次。每个Test因此总共包含1000个请求。这导致随后的数据库调用。这表明无论我们更改了什么配置参数，MySQL都会创建很多临时表。

+----+------------+-------+---------------+------------+
|Test|Parameter   |Value  |NumOfTempTables|Db Max Conn |
+----+------------+-------+---------------+------------+
| 1  |key_buffer_ | 25M   | 30682         | 29         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 2  |key_buffer_ | 55M   | 30793         | 29         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 3  |key_buffer_ | 100M  | 30666         | 28         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 4  |key_buffer_ | 125M  | 30593         | 24         |
|    |size        |       |               |            | 
+----+------------+-------+---------------+------------+
| 5  |query_cache_| 100M  | 30627         | 32         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 6  |query_cache_| 250M  | 30761         | 26         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 7  |query_cache_| 500M  | 30864         | 83*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 8  |query_cache_| 1G    | 30706         | 75*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 9  |tmp_table_  | 125M  | 30724         | 31         |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 10 |tmp_table_  | 250M  | 30689         | 90*        |
|    |size        |       |               |            |
+----+------------+-------+---------------+------------+
| 11 |tmp_table_  | 500M  | 30792         | 28         |
|    |size        |       |               |            |  
+----+------------+-------+---------------+------------+
| 12 |Sort_buffer&| 256K  | 30754         | 28         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 13 |Sort_buffer&| 512K  | 30788         | 30         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 14 |Sort_buffer&| 1M    | 30788         | 28         |
|    |Join_buffer |       |               |            | 
+----+------------+-------+---------------+------------+
| 15 |Sort_buffer&| 4M    | 30642         | 35         |
|    |Join_buffer |       |               |            |
+----+------------+-------+---------------+------------+
| 16 |innodb-     | 1G    | 30695         | 33         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |
+----+------------+-------+---------------+------------+
| 17 |innodb-     | 2G    | 30791         | 28         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            | 
+----+------------+-------+---------------+------------+
| 18 |innodb-     | 3G    | 30719         | 34         |
|    |buffer-     |       |               |            |
|    |pool-size   |       |               |            |  
+----+------------+-------+---------------+------------+

*三次运行的平均值
下图描述了数据库服务器所需的内存和CPU数量针对不同的配置。黑线表示最小值和最大值，蓝条表示起始值和结束值。如问题所示，最大内存为4096M。

您正在使用哪个存储引擎？MyiSAM？如果您不使用MyISAM表，则依赖key_buffer_size毫无意义。如果您使用的是innodb存储引擎，则innodb_buffer_pool_size的大小是多少。您在使用query_cache吗？

– kasi
15年12月31日在8:28

#3 楼

您还应该检查“内存中临时表大小”是否受“最大堆表大小”变量限制。

将MEMORY存储引擎用于内存中临时表时，MySQL自动转换内存中临时表

如果构建临时表所需的空间超过tmp_table_size或max_heap_table_size，则MySQL在服务器的tmpdir目录中创建基于磁盘的表。

br />内存中临时表的最大大小由tmp_table_size或max_heap_table_size值（以较小者为准）定义。

这个问题出现只有6年了。

– Gerard H. Pille
20 Mar 4 '20 at 16:36

是正确的，但是由于其他社区成员也可能出现类似的问题，因此我希望有人也会发现此答案也有帮助。

– martoncsukas
20 Mar 5 '20在15:07

编程黑洞网

MySQL在磁盘上创建临时表。如何停止？

评论

#1 楼

#2 楼

评论

#3 楼

评论