mysql tmp_table_size优化之设置多大合适
115 浏览量
2020-09-10
02:42:51
上传
评论 1
收藏 75KB PDF 举报
mysql tmp_table_size优化之设置多大合适优化之设置多大合适
主要介绍了mysql tmp_table_size优化问题，很多朋友都会问tmp_table_size设置多大合适，其实既然你都搜索
到这篇文章了，一般大于64M比较好，当然你也可以可以根据自己的机器内容配置增加，一般64位的系统能充
分利用大内存
通过设置tmp_table_size选项来增加一张临时表的大小，例如做高级GROUP BY操作生成的临时表。如果调高该值，MySQL
同时将增加heap表的大小，可达到提高联接查询速度的效果，建议尽量优化查询，要确保查询过程中生成的临时表在内存
中，避免临时表过大导致生成基于硬盘的MyISAM表。
mysql> show global status like ‘created_tmp%‘;
+——————————–+———+
| Variable_name | Value |
+———————————-+———+
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1771587 |
+——————————–+———–+
每次创建临时表，Created_tmp_tables增加，如果临时表大小超过tmp_table_size，则是在磁盘上创建临时
表，Created_tmp_disk_tables也增加,Created_tmp_files表示MySQL服务创建的临时文件文件数，比较理想的配置是：
Created_tmp_disk_tables / Created_tmp_tables * 100% <= 25%比如上面的服务器Created_tmp_disk_tables /
Created_tmp_tables * 100% ＝1.20%，应该相当好了
默认为16M，可调到64-256最佳，线程独占，太大可能内存不够I/O堵塞
如果动态页面要调大点，100M以上，如果网站大部分都是静态内容，一般64M足够。
tmp_table_size优化优化
数据库连接突然增多到1000的问题
查看了一下，未有LOCK操作语句。
但是明显有好多copy to tmp table的SQL语句，这条语读的时间比较长，且这个表会被加读锁，相关表的update语句会被排进
队列。如果多执行几次这样的copyt to tmp table 语句，会造成更多的语句被阻塞。
连接太多造成mysql处理慢。
copy to tmp talbe 语句产生的原因是查询需要Order By 或者Group By等需要用到结果集时，参数中设置的临时表的大小小于
结果集的大小时，就会将该表放在磁盘上，这个时候在硬盘上的IO要比内销差很多。所耗费的时间也多很多。另外Mysql的另
外一个参数max_heap_table_size比tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内存临时表的上
限，大于这个时，改写硬盘。
我们的mysql这两个参数为：
tmp_table_size 33554432 (33.5M)
max_heap_table_size 16777216 (16.7M)
比较小。
建议增加到上百M。我们的内存应该够吧。
另外join_buffer_size（影响 表之间join性能的缓存）为131072 (131K)较小，可以增加一点。
[root@mail ~]# vi /etc/my.cnf
[mysqld]
tmp_table_size=200M
mysql> show processlist;
mysql> show columns from wp_posts;
SQL 语句的第一个 LEFT JOIN ON 子句中： LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid _mydata 的 userid 被参与了
条件比较运算。为 _mydata 表根据字段 userid 建立了一个索引： mysql> ALTER TABLE `_mydata` ADD INDEX ( `userid` )
增加 tmp_table_size 值。
mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table
资源评论
