☰

改进 mysqltuner 建议

tag-icon • tag-icon mysql mariadb
改进 mysqltuner 建议

在具有 16 个内核、32GB RAM 的 CPU 优化 droplet 中，Ubuntu 20.04.0 下的 mysql 出现问题。

我在这个服务器上安装了 25 个网站。每天的页面浏览量约为 10 万次，高峰期与 mysql 的连接数约为 300 次。当某个拥有大量用户的网站上传新帖子时（需要注册才能访问），mysql 崩溃了，所有网站都瘫痪了。

这是 mysqltuner 分析，我不知道如何改进它。

https://pastebin.com/Zfu9fScy

关于如何具体解决这些建议，您有什么想法吗？

*** MySQL 的最大内存使用量非常高 *** *** 在增加 MySQL 缓冲区变量之前添加 RAM *** join_buffer_size（> 256.0K，或始终使用带有 JOIN 的索引）

答案1

Analysis of GLOBAL STATUS and VARIABLES:

观察结果：

  • 版本：10.5.17-MariaDB-1:10.5.17+maria~ubu2004-log
  • 32 GB 内存
  • 正常运行时间 = 21:41:20；一些 GLOBAL STATUS 值可能尚无意义。
  • 每秒 1.14e+3

更重要的问题：

  • 建议更改设置：

      table_open_cache = 10000
  innodb_io_capacity = 10000
  innodb_io_capacity_max = 10000
  innodb_open_files = 10000
  max_heap_table_size = 512M
  table_definition_cache = 5000

  • 一般情况下，不要盲目增加设置。在某些情况下，这会导致额外的 RAM 分配，从而导致交换，这对性能非常不利。

  • innodb_buffer_pool_size似乎大于数据集大小所需的值。稍后，如果数据集增长，请考虑提高该值。（在达到 70% 之前停止可用的内存。

  • 升级到 10.6 之前请将其删除innodb_log_files_in_group。（它的存在可能会导致错误。）

  • 如果您要使用西欧以外地区的文本，请考虑切换到 utf8mb4。（超出了本讨论范围。）

  • 似乎有一个每秒运行一次的监控过程。（参见 Com_show_variables、Com_show_generic、Com_show_status，尤其是 Com_show_tables（19 /秒！））考虑它是否会使系统速度减慢到超出其应有的程度。

  • OPTIMIZE TABLE几乎毫无用处，而且具有侵袭性。这种情况似乎（平均）每分钟发生两次。

详细信息和其他观察结果：

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 9728M / 0.70)) / 32768M = 44.4%-- 大多数可用 RAM 应用于缓存。--http://mysql.rjweb.org/doc.php/memory

( table_open_cache ) = 247,485-- 要缓存的表描述符的数量 -- 通常几百个就够了。

( Open_tables / table_open_cache ) = 5,205 / 247485 = 2.1%-- 缓存使用情况（打开表 + 临时表）-- 降低 table_open_cache（现在为 247485）

( innodb_buffer_pool_size ) = 9,728 / 32768M = 29.7%-- 用于 InnoDB 缓冲池的 RAM 百分比 -- 设置为可用 RAM 的 70% 左右。（太低效率较低；太高有交换风险。）

( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 9728M / 1 = 9728MB-- 每个缓冲池实例的大小。-- 一个实例至少应为 1GB。在非常大的 RAM 中，有 16 个实例。（innodb_buffer_pool_instances在 10.5.1 中被忽略。）

( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,536 * 1 = 1,536-- 页面清理器每秒的工作量。-- “InnoDB：page_cleaner：预期循环耗时 1000ms ...” 可通过降低 lru_scan_depth 来解决：考虑 1000 / innodb_page_cleaners（现在为 1）。还请检查交换。

( innodb_lru_scan_depth ) = 1,536-- innodb_lru_scan_depth 是一个命名很差的变量。更好的名称应该是 innodb_free_page_target_per_buffer_pool。它是 InnoDB 尝试在每个缓冲池实例中保留的页面数，以加快读取和页面创建操作。--“InnoDB：page_cleaner：预期循环花费 1000ms ...”可以通过降低 lru_scan_depth 来修复

( Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size ) = 465,304 * 16384 / 9728M = 74.7%-- 缓冲池可用 -- buffer_pool_size 大于工作集；可以减小它

( innodb_io_capacity ) = 500,000-- 刷新时，使用这么多 IOP。-- 读取可能会很慢或不稳定。如果使用 SSD 驱动器，请使用 2000。

( innodb_io_capacity_max ) = 1,000,000 = 1.0e+6-- 紧急刷新时，使用这么多的 IOP。-- 读取可能会缓慢或尖锐。

( Innodb_buffer_pool_pages_free / Innodb_buffer_pool_pages_total ) = 465,304 / 612940 = 75.9%-- 缓冲池的百分比当前未使用 -- innodb_buffer_pool_size（现在为 10200547328）是否大于必要的？

( innodb_change_buffering ) = innodb_change_buffering = none -- 在 5.6.11 / 5.5.31 之前，有一个错误使 ="changes" 成为更安全的选项。MariaDB 10.xy 正在转向“无”并删除设置和/或功能？

( Innodb_buffer_pool_bytes_data / innodb_buffer_pool_size ) = 2,418,868,224 / 9728M = 23.7%-- 数据占用缓冲池的百分比 -- 很小的百分比可能表明 buffer_pool 不必要地大。

( innodb_log_buffer_size ) = 2408M-- 建议 2MB-64MB，至少与事务中设置的最大 blob 一样大。-- 调整 innodb_log_buffer_size（现在为 2524971008）。

( innodb_log_buffer_size ) = 2,408 / 32768M = 7.3%-- 用于缓冲 InnoDB 日志写入的 RAM 百分比。-- 需要至少为最大 blob 大小的 10 倍，但除此之外，不应因值过大而浪费 RAM。

( innodb_log_buffer_size / innodb_log_file_size ) = 2408M / 2048M = 117.6%-- 缓冲区在 RAM 中；文件在磁盘上。-- buffer_size 应该更小和/或 file_size 应该更大。

( innodb_log_files_in_group ) = 1-- InnoDB 日志文件的数量 -- 在 10.6 版中已从配置文件中删除。

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 78,080 / 60 * 2048M / 5138268672 = 543-- InnoDB 日志轮换间隔的分钟数 从 5.6.8 开始，innodb_log_file_size 可以动态更改；我不知道 MariaDB 的情况。一定要更改 my.cnf --（建议轮换间隔 60 分钟，这有点武断。）调整 innodb_log_file_size（现在为 2147483648）。（在 AWS 中无法更改。）

( default_tmp_storage_engine ) = default_tmp_storage_engine =

( innodb_flush_neighbors ) = innodb_flush_neighbors = 1-- 将块写入磁盘时进行小幅优化。-- 对于 SSD 驱动器使用 0；对于 HDD 使用 1。

( innodb_flush_log_at_trx_commit ) = 1-- 1 = 安全；2 = 更快 -- （您决定）使用 1，以及 sync_binlog（现在为 0）=1，以获得最高级别的容错能力。0 最适合速度。2 是 0 和 1 之间的折衷。

( sync_binlog ) = 0-- 使用 1 来增加安全性，但会花费一些 I/O 成本 =1 可能会导致大量“查询结束”；=0 可能会导致“binlog 处于不可能的位置”并在崩溃时丢失事务，但速度更快。对于 Galera 来说 0 是可以的。

( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF-- 是否记录所有死锁。-- 如果您受到死锁的困扰，请启用此功能。注意：如果您有大量死锁，这可能会将大量数据写入磁盘。

( max_connections ) = 5,000-- 最大连接数（线程）。影响各种分配。-- 如果 max_connections（现在为 5000）太高，并且各种内存设置都很高，则可能会耗尽 RAM。

( min( tmp_table_size, max_heap_table_size ) ) = (min( 512M, 1024M )) / 32768M = 1.6%-- 需要 MEMORY 表（每个表）或 SELECT 中的临时表（每个临时表每个某些 SELECT）时分配的 RAM 百分比。太高可能会导致交换。-- 将 tmp_table_size（现在为 536870912）和 max_heap_table_size（现在为 1073741824）减少到 RAM 的 1%。

( 176000 * max_connections ) = (176000 * 5000) / 32768M = 2.6%-- 根据 max_connections 的大小估计内存使用量。-- max_connections（现在为 5000）有点高

( character_set_client ) = character_set_client = latin1--

( character_set_connection ) = character_set_connection = latin1--

( character_set_results ) = character_set_results = latin1--

( (Com_show_create_table + Com_show_fields) / Questions ) = (2110 + 1257054) / 89202925 = 1.4%-- 不合理的框架 -- 花费大量精力重新发现模式。-- 向第三方供应商投诉。

( local_infile ) = local_infile = ON -- local_infile（现在为 ON）= ON 是一个潜在的安全问题

( Created_tmp_tables ) = (6701709) / 78080 = 86 /sec——作为复杂 SELECT 的一部分创建“临时”表的频率。

( Created_tmp_disk_tables ) = (2611985) / 78080 = 33 /sec-- 创建频率磁盘“临时”表作为复杂 SELECT 的一部分 - 增加 tmp_table_size（现在为 536870912）和 max_heap_table_size（现在为 1073741824）。检查使用 MEMORY 而不是 MyISAM 时的临时表规则。也许较小的架构或查询更改可以避免使用 MyISAM。更好的索引和查询的重新表述更有可能有所帮助。

( Created_tmp_disk_tables / Created_tmp_tables ) = 2,611,985 / 6701709 = 39.0%-- 溢出到磁盘的临时表的百分比 -- 可能增加 tmp_table_size（现在为 536870912）和 max_heap_table_size（现在为 1073741824）；改进索引；避免 blob 等。

( tmp_table_size ) = 512M-- 大小限制记忆用于支持 SELECT 的临时表 - 减少 tmp_table_size（现在为 536870912）以避免 RAM 耗尽。可能不超过 64M。

( Com_show_variables ) = (78795) / 78080 = 1 /sec-- 显示变量...-- 为什么您如此频繁地请求变量？

( Com_optimize ) = (220384 + 109361 + 50 + 0 + 571666 + 1626) / 78080 = 0.037 /sec-- OPTIMIZE TABLE 执行的频率。-- OPTIMIZE TABLE 很少有用，当然频率也不高。

( long_query_time ) = 10-- 定义“慢速”查询的截止时间（秒）。-- 建议 2

( Max_used_connections / max_connections ) = 79 / 5000 = 1.6%-- 连接的峰值百分比 -- 由于几个内存因素可以基于 max_connections（现在为 5000）扩大，因此最好不要将该设置得太高。

( Com_change_db ) = (220384 + 109361 + 50 + 0 + 571666 + 1626) / 78080 = 7.4 /sec-- 可能来自 USE 语句。-- 考虑连接 DB、使用 db.tbl 语法、消除虚假的 USE 语句等。

( Connections ) = (220384 + 109361 + 50 + 0 + 571666 + 1626) / 78080 = 7.4 /sec-- 连接 -- 增加 wait_timeout（现在为 60）；使用池？

( thread_cache_size / Max_used_connections ) = 128 / 79 = 162.0% -- 线程缓存大于可能的连接数没有任何好处。浪费空间才是缺点。

( thread_pool_max_threads ) = 65,536-- MariaDB 线程池的众多设置之一 -- 降低值。

异常小：

Innodb_buffer_pool_pages_misc = 0
innodb_adaptive_max_sleep_delay = 0
innodb_lru_scan_depth / innodb_io_capacity = 0.00307
query_cache_limit = 131,072
wait_timeout = 60

异常大：

(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace) = 91.5
Access_denied_errors = 78,124
Aria_pagecache_reads = 31 /sec
Com_check = 0.037 /sec
Com_show_fields = 16 /sec
Com_show_generic = 1 /sec
Com_show_keys = 97 /HR
Com_show_status = 1 /sec
Com_show_tables = 19 /sec
Com_stmt_send_long_data = 30 /HR
Feature_json = 0.74 /sec
Feature_timezone = 2.9 /sec
Handler_discover = 3.7 /HR
Handler_read_last = 5.9 /sec
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 122
Innodb_log_writes / Innodb_log_write_requests = 576.1%
Innodb_num_open_files = 2,887
Open_streams = 4
Open_table_definitions = 2,992
Open_tables = 5,205
Performance_schema_file_instances_lost = 2,885
Sort_priority_queue_sorts = 15 /sec
Sort_range = 83 /sec
Sort_scan = 48 /sec
host_cache_size = 853
innodb_open_files = 500,000
max_heap_table_size = 1024MB
min(max_heap_table_size, tmp_table_size) = 512MB
performance_schema_max_statement_classes = 222
table_definition_cache = 10,000
table_open_cache / max_connections = 49.5

异常字符串：

Slave_heartbeat_period = 0
Slave_received_heartbeats = 0
aria_recover_options = BACKUP,QUICK
concurrent_insert = ALWAYS
disconnect_on_expired_password = OFF
innodb_fast_shutdown = 1
log_slow_verbosity = query_plan
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
sql_slave_skip_counter = 0
tx_isolation = READ-COMMITTED

答案2

每秒速率 = RPS

对 my.cnf [mysqld] 部分的建议

read_rnd_buffer_size=32K  # from 1M to reduce RAM reqd and handler_read_rnd_next RPS of 101,933
read_buffer_size=512K  # from 2M to reduce RAM reqd 
tmp_table_size=1G  # from 1/2G to match max_heap_table_size and reduce created_tmp_disk_tables RPS of 33 
innodb_adaptive_max_sleep_delay=10000  # from 0 for improved multi processing
log_slow_verbosity=query_plan,explain  # from query_plan for more useful Slow Query Log

观察：当前 select_scan RPS 为 100 是由于缺少所需的索引。

请查看个人资料以获取联系信息。还有更多调整机会。

相关内容