减少在磁盘 MYSQL 上创建的临时表
我们的网站针对用户端的报告功能运行一些相当大的收益查询。较大的帐户在某些页面上加载需要 5 到 20 秒。当这些查询运行时，它们正在使用 MYSQL SUM() 函数计算收入，我相信这会创建大量临时表，而且看起来有些是在磁盘上创建的。我一直在运行 MYSQL Tuner，但它不再对我的设置进行任何变量调整。这是一个示例输出：
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 14m 29s (1M q [98.277 qps], 143K conn, TX: 2B, RX: 212M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.1G global + 3.8M per thread (600 max threads)
[!!] Maximum possible memory usage: 6.3G (89% of installed RAM)
[OK] Slow queries: 0% (7K/1M)
[OK] Highest usage of available connections: 2% (14/600)
[OK] Key buffer size / total MyISAM indexes: 640.0M/1.0G
[OK] Key buffer hit rate: 99.9% (237M cached / 135K reads)
[OK] Query cache efficiency: 62.5% (756K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (429 temp sorts / 134K sorts)
[OK] Temporary tables created on disk: 21% (25K on disk / 120K total)
[OK] Thread cache hit rate: 99% (14 created / 143K connections)
[OK] Table cache hit rate: 70% (247 open / 348 opened)
[OK] Open file limit used: 0% (440/760K)
[OK] Table locks acquired immediately: 99% (577K immediate / 578K locks)
[OK] InnoDB data size / buffer pool: 144.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
另外这是我的 MY.CNF 设置：
[mysqld]
safe-show-database
skip-locking
skip-networking
max_connections = 600
key_buffer = 640M
table_cache = 380000
query_cache_size = 1024M
query_cache_limit = 12M
query_cache_type = 1
local-infile=0
long_query_time=1
myisam_sort_buffer_size = 64M
max_heap_table_size = 2560M
tmp_table_size = 2560M
max_allowed_packet = 8M
thread_cache_size = 70
thread_stack = 256K
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
binlog_cache_size=64K
long_query_time=1
log-slow-queries=/var/log/slow-query.log
low_priority_updates=1
concurrent_insert=2
log-queries-not-using-indexes
join_buffer_size=2M
thread_concurrency = 16
任何帮助将不胜感激。我需要让这些页面加载得更快。该服务器是四核 x2 (8 CPU)，具有 8 GB RAM。这些表已完全索引。谢谢。
**已解决：我能够通过为 WHERE 列创建多列索引来解决此问题。看起来这与磁盘上创建的临时表造成的缓慢无关。 QPS 从 98 跃升至 212。
Our site runs some pretty large earnings queries for our reports feature of the user side. The larger accounts are taking anywhere from 5-20 seconds to load on some pages. When these queries run they are calculating the earnings with the MYSQL SUM() function and I believe this is creating large amounts of temporary tables, and it looks like some are being created on the disk. I've been running MYSQL Tuner but it no longer has any variable adjustments for my settings. Here is a sample output:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 14m 29s (1M q [98.277 qps], 143K conn, TX: 2B, RX: 212M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.1G global + 3.8M per thread (600 max threads)
[!!] Maximum possible memory usage: 6.3G (89% of installed RAM)
[OK] Slow queries: 0% (7K/1M)
[OK] Highest usage of available connections: 2% (14/600)
[OK] Key buffer size / total MyISAM indexes: 640.0M/1.0G
[OK] Key buffer hit rate: 99.9% (237M cached / 135K reads)
[OK] Query cache efficiency: 62.5% (756K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (429 temp sorts / 134K sorts)
[OK] Temporary tables created on disk: 21% (25K on disk / 120K total)
[OK] Thread cache hit rate: 99% (14 created / 143K connections)
[OK] Table cache hit rate: 70% (247 open / 348 opened)
[OK] Open file limit used: 0% (440/760K)
[OK] Table locks acquired immediately: 99% (577K immediate / 578K locks)
[OK] InnoDB data size / buffer pool: 144.0K/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Also here are my MY.CNF settings:
[mysqld]
safe-show-database
skip-locking
skip-networking
max_connections = 600
key_buffer = 640M
table_cache = 380000
query_cache_size = 1024M
query_cache_limit = 12M
query_cache_type = 1
local-infile=0
long_query_time=1
myisam_sort_buffer_size = 64M
max_heap_table_size = 2560M
tmp_table_size = 2560M
max_allowed_packet = 8M
thread_cache_size = 70
thread_stack = 256K
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
binlog_cache_size=64K
long_query_time=1
log-slow-queries=/var/log/slow-query.log
low_priority_updates=1
concurrent_insert=2
log-queries-not-using-indexes
join_buffer_size=2M
thread_concurrency = 16
Any help would be appreciated. I need to get these pages to load faster. The server is a quad core x2 (8 CPU) with 8 GB RAM. The tables are fully indexed. Thannks.
**Solved: I was able to solve this issue by creating a multi-column index for the WHERE columns. Looks like this had nothing to do with slowness created by temporary tables created on disk. QPS jumped from 98 to 212.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您还可以尝试将查询缓存类型设置为 2（按需），然后更改真正有利于 SELECT SQL_CACHE 的查询...等等...这将消除缓存查询的开销，而这些查询实际上不会从缓存中受益
You might also try setting query cache type to 2 (on demand) and then change queries that will really benefit to SELECT SQL_CACHE ...blah... This will eliminate overhead for caching queries that will really have no benefit from caching