运维工作中你遇到过哪些神奇事件？

GreatSQL社区

搜索

[讨论中] MySQL内存持续上升，没有释放

204 1 2024-4-23 09:18
MySQL版本：Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

OS：Ubuntu 22.04.4 LTS
CPU：8H
Memory：16G

my.cnf
  1. [client]
  2. socket = /data/mysql/mysql.sock

  4. [mysql]
  5. prompt = "\u@mysqldb \R:\m:\s [\d]> "
  6. no-auto-rehash

  8. [mysqldump]
  9. single-transaction

  11. [mysqld_safe]
  12. malloc-lib = /usr/lib/x86_64-linux-gnu/libjemalloc.so.2

  14. [mysqld]
  15. #basic settings#
  16. server-id = 100
  17. port = 3306
  18. user = mysql
  19. autocommit = 1
  20. socket = /data/mysql/mysql.sock
  21. character_set_server=utf8mb4
  22. datadir=/data/mysql/
  23. basedir=/usr/local/mysql/
  24. explicit_defaults_for_timestamp = 1
  25. sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  26. transaction_isolation = READ-COMMITTED
  27. max_allowed_packet = 64M
  28. event_scheduler = 1
  29. #connection#
  30. interactive_timeout = 1800
  31. wait_timeout = 1800
  32. lock_wait_timeout = 1800
  33. skip_name_resolve = 1
  34. max_connections = 1024
  35. max_user_connections = 256
  36. max_connect_errors = 1000000
  37. #table cache performance settings#
  38. table_open_cache = 4096
  39. table_definition_cache = 4096
  40. table_open_cache_instances = 64
  41. #session memory settings#
  42. read_buffer_size = 2M
  43. read_rnd_buffer_size = 2M
  44. sort_buffer_size = 2M
  45. tmp_table_size = 2M
  46. join_buffer_size = 2M
  47. thread_cache_size = 64
  48. #log settings#
  49. log_error = error.log
  50. log_bin = binlog
  51. log_error_verbosity = 2
  52. slow_query_log = 1
  53. slow_query_log_file = slow.log
  54. log_queries_not_using_indexes = 1
  55. log_slow_admin_statements = 1
  56. log_slow_replica_statements = 1
  57. log_throttle_queries_not_using_indexes = 10
  58. binlog_expire_logs_seconds = 864000
  59. long_query_time = 2
  60. min_examined_row_limit = 100
  61. log-bin-trust-function-creators = 1
  62. binlog_cache_size  = 2M
  63. ########innodb settings########
  64. innodb_page_size = 16k
  65. innodb_buffer_pool_size = 9G
  66. innodb_buffer_pool_instances = 8
  67. innodb_buffer_pool_load_at_startup = 1
  68. innodb_buffer_pool_dump_at_shutdown = 1
  69. innodb_lru_scan_depth = 4096
  70. innodb_lock_wait_timeout = 5
  71. innodb_io_capacity = 20000
  72. innodb_io_capacity_max = 24000
  73. innodb_flush_method = O_DIRECT
  74. innodb_flush_neighbors = 0
  75. innodb_redo_log_capacity = 4G
  76. innodb_log_buffer_size = 64M
  77. innodb_purge_threads = 4
  78. innodb_thread_concurrency = 0
  79. innodb_print_all_deadlocks = 1
  80. innodb_strict_mode = 1
  81. innodb_sort_buffer_size = 64M
  82. innodb_write_io_threads = 8
  83. innodb_read_io_threads = 4
  84. innodb_file_per_table = 1
  85. innodb_stats_persistent_sample_pages = 64
  86. innodb_autoinc_lock_mode = 2
  87. innodb_online_alter_log_max_size=100M
  88. innodb_open_files=4096
  89. innodb_flush_log_at_trx_commit = 1
  90. #replication settings#
  91. sync_binlog = 1
  92. gtid_mode = on
  93. enforce_gtid_consistency = 1
  94. binlog_format = ROW
  95. binlog_rows_query_log_events = 1
  96. relay_log = relay.log
  97. relay_log_recovery = 1
  98. log_replica_updates = 1
  99. replica_skip_errors = ddl_exist_errors
  100. replica_parallel_workers = 8
  101. replica_preserve_commit_order = 1
  102. replica_transaction_retries = 128
  103. binlog_gtid_simple_recovery = 1
  104. log_timestamps = system
  105. #mgr settings
  106. loose-plugin_load_add = 'mysql_clone.so'
  107. loose-plugin_load_add = 'group_replication.so'
  108. loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
  109. loose-group_replication_local_address = "10.20.12.106:33061"
  110. loose-group_replication_group_seeds = '10.20.12.106:33061,10.20.12.107:33061,10.20.12.108:33061'
  111. loose-group_replication_start_on_boot = OFF
  112. loose-group_replication_bootstrap_group = OFF
  113. loose-group_replication_exit_state_action = READ_ONLY
  114. loose-group_replication_flow_control_mode = "DISABLED"
  115. loose-group_replication_single_primary_mode = ON
  116. loose-group_replication_communication_max_message_size = 10M
  117. loose-group_replication_transaction_size_limit = 1G
  118. loose-group_replication_arbitrator = 0
  119. loose-group_replication_single_primary_fast_mode = 1
  120. loose-group_replication_request_time_threshold = 20000
  121. loose-group_replication_recovery_get_public_key = 1
  122. report-host = "10.20.12.106"
  123. #perforamnce_schema settings#
  124. performance-schema-instrument='memory/%=COUNTED'
  125. performance_schema_digests_size = 40000
  126. performance_schema_max_table_handles = 40000
  127. performance_schema_max_table_instances = 40000
  128. performance_schema_max_sql_text_length = 4096
  129. performance_schema_max_digest_length = 4096
  130. loose_innodb_numa_interleave = 1
  131. innodb_buffer_pool_dump_pct = 40
  132. innodb_page_cleaners = 8
  133. innodb_undo_log_truncate = 1
  134. innodb_max_undo_log_size = 4G
  135. innodb_purge_rseg_truncate_frequency = 128

  137. #innodb monitor settings
  138. innodb_monitor_enable = "module_innodb"
  139. innodb_monitor_enable = "module_server"
  140. innodb_monitor_enable = "module_dml"
  141. innodb_monitor_enable = "module_ddl"
  142. innodb_monitor_enable = "module_trx"
  143. innodb_monitor_enable = "module_os"
  144. innodb_monitor_enable = "module_purge"
  145. innodb_monitor_enable = "module_log"
  146. innodb_monitor_enable = "module_lock"
  147. innodb_monitor_enable = "module_buffer"
  148. innodb_monitor_enable = "module_index"
  149. innodb_monitor_enable = "module_ibuf_system"
  150. innodb_monitor_enable = "module_buffer_page"
  151. innodb_monitor_enable = "module_adaptive_hash"
复制代码


参考叶老师的深入浅出MGR搭建的MGR集群。目前master节点内存使用率大约89%，从启动数据库就一直处于上升趋势。有一些慢查询，但是慢查询应该不会导致内存一直上升，请帮忙看看，谢谢。

gavin.zheng

1

主题

0

博客

3

贡献

新手上路

Rank: 1

积分
4
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-5-5 02:43 , Processed in 0.016801 second(s), 8 queries , Redis On.
快速回复 返回顶部 返回列表