gpt4 book ai didi

mysqltuner 输出,设置建议?

转载 作者:行者123 更新时间:2023-11-29 18:01:51 26 4
gpt4 key购买 nike

我没有 250 GB 的 RAM(超出了我们的预算),我目前有 70GB,使用一小时后就填满了 57GB(mysql 缓冲区)。
我主要不确定:
临时表缓冲区查询缓存修剪join_buffer 和 query_cache 大小

innodb 缓冲区可能不太小,这 240GB 中很大一部分很少被访问。我认为大部分访问内容都被缓冲了。

其中一些值已经处于边缘,我读到它没有好处(例如 128MB tmp_table 或 1MB join_buffer)

[--] Up for: 9h 51m 30s (78M q [2K qps], 921K conn, TX: 110B, RX: 26B)
[--] Reads / Writes: 49% / 51%
[--] Total buffers: 46.6G global + 1.9M per thread (1500 max threads)
[OK] Maximum possible memory usage: 49.3G (82% of installed RAM)
[OK] Slow queries: 0% (1K/78M)
[OK] Highest usage of available connections: 28% (428/1500)
[OK] Key buffer size / total MyISAM indexes: 256.0M/1.6G
[OK] Key buffer hit rate: 99.9% (105M cached / 55K reads)
[OK] Query cache efficiency: 26.9% (11M cached / 44M selects)
[!!] Query cache prunes per day: 4482061
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 10M sorts)
[!!] Joins performed without indexes: 603
[!!] Temporary tables created on disk: 49% (1M on disk / 2M total)
[OK] Thread cache hit rate: 99% (6K created / 921K connections)
[OK] Table cache hit rate: 83% (1K open / 2K opened)
[OK] Open file limit used: 12% (633/5K)
[OK] Table locks acquired immediately: 99% (9M immediate / 9M locks)
[!!] InnoDB buffer pool / data size: 46.0G/240.6G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increasing the query_cache size over 128M may reduce performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_size (> 180M) [see warning above]
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
innodb_buffer_pool_size (>= 240G)

配置:

max_allowed_packet      = 64M
thread_stack = 256K
thread_cache_size = 16

max_connections = 1500
max_user_connections = 850
query_cache_limit = 3M
query_cache_size = 180M
query_cache_type = 1
table_open_cache = 2500
key_buffer_size = 256M # index in memory for myisam
innodb_buffer_pool_size = 46G
innodb_log_file_size = 256M
tmp_table_size = 128M
max_heap_table_size = 128M
join_buffer_size = 1M

最佳答案

让我们尝试在 70GB RAM 内工作。 “InnoDB 缓冲池/数据大小:46.0​​G/240.6G”没问题,如果你的抖动不是太严重。通常改进索引和/或查询会有所帮助。

我所看到的查询缓存规范还不错。但它有多大呢? query_cache_size 的值是多少?如果超过50M,就太大了,因为prune的成本很高。

不要更改 mysqltuner 推荐的其他设置。

每秒大约有 50 个 query_cache_prunes,这太高了。

所以,虽然命中率很高,但我怀疑QC没有调好。建议:

发现哪些查询可能完全重复,哪些不会。将常用的更改为SELECT SQL_CACHE ...,将不常用的更改为SELECT SQL_NO_CACHE ...。并更改为 query_cache_type = DEMAND

要深入挖掘并修复一些缓慢的查询,请遵循建议 here .

关于mysqltuner 输出,设置建议?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48257302/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com