乐趣区

关于mysql:万答3MGR最佳配置参考PFS里的监测指标要全开吗mysqld进程占用内存过高怎么排查

jiezi
  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。

  • 问题 1，有举荐的 MGR 运行最佳配置参考吗

在「3306π」社区广州站 5 月 22 日的分享会上，万里数据库 CTO 娄帅给出了他倡议的配置参考，咱们一起来看下：

group_replication_single_primary_mode=ON
log_error_verbosity=3
group_replication_bootstrap_group=OFF 
group_replication_transaction_size_limit=< 默认值 150MB，但倡议调低在 20MB 以内，不要应用大事务 >
group_replication_communication_max_message_size=10M
group_replication_flow_control_mode=OFF #官网版本的流控机制不太正当，其实能够思考敞开
group_replication_exit_state_action=READ_ONLY
group_replication_member_expel_timeout=5 #如果网络环境不好，能够适当调高

另外，应用 MGR 的其余倡议有：

  • 只是用 InnoDB 表。

    • 每个表都必须要有主键。
  • 节点数采纳奇数。
  • 保障网络可靠性，低提早环境，不要跨城部署（个别倡议网络提早低于 1ms）。
  • 应用单主模式。
  • BINLOG_FORMAT=ROW。

更多对于 MGR 的最佳应用实际，能够关注「3306π」社区公众号（pai3306），获取娄帅老师本次分享内容。

问题 2，MySQL Performance Schema 都倡议开启哪些监控采集指标（除了默认主动开启的指标）

先说我的认识：个别倡议只开启锁 (Lock) 监控相干的监测指标。

# 开启 MDL 监测指标
mysql> CALL sys.ps_setup_enable_instrument('wait/lock/metadata/sql/mdl');

# 开启全副 Lock 相干监测指标
mysql> CALL sys.ps_setup_enable_instrument('%lock%');

其余的监测指标，例如 Memory、Statement、Transaction 等，有必要再长期开启。因为从 MySQL 5.7 开始，PFS 反对在线动静开启和敞开，因而非必要的话，不倡议一口气全开。

一般而言，PFS 里的监测指标全开的话，对性能影响个别 5% 左右，内存耗费 1G 左右，整体还是可控的。

已知的问题是在 Percona 分支版本中，如果同时开启 PFS 和线程池后，很容易产生 OOM。

小结：

  • 需要的话，能够全开。
  • 对性能影响无限。
  • 但还是倡议只开锁监控相干的。

问题 3，mysqld 过程占用内存过高怎么排查

遇到一个比拟极其的案例，innodb_buffer_pool_size 值仅设置为 2GB，然而 mysqld 过程却占用了 25GB 的内存。

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
45305 mysql     20   0   28.4g    25g   8400 S  48.5 81.4  64:46.82 mysqld

前面会有专门的文章介绍详细分析排查过程，这里先间接说可能的起因以及解决方案。

可能的起因

1、session（会话）级内存 buffer 参数设置过高，并且连接数也设置过高，例如

read_buffer_size = 64M
read_rnd_buffer_size = 32M
sort_buffer_size = 64M
join_buffer_size = 64M
tmp_table_size = 1G
max_heap_table_size = 1G
max_connections=2000

当连接数较少时，须要耗费的内存并不多。

然而当遇到突发流量时，可能并发连接数会靠近打满，再加上可能有产生长期表、额定排序的低效率的 SQL 频繁呈现，这就很容易导致内存占用快速增长。

因而倡议调低 session 级 buffer 参数值，并无效管制并发连接数，上面是一个比拟通用的设置值参考：

read_buffer_size = 4M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 32M
max_heap_table_size = 32M
max_connections = 512

2、PFS 中开启过多检测指标，造成内存耗费过大。

在下面也提到过，全副开启 PFS 后，可能须要大概 1GB 内存。不过在高并发并随同频繁低效 SQL 的状况下，可能须要耗费更多内存。

3、可能还用到 MyISAM 引擎，并且 key_buffer_size 设置过大。

不过当初 MyISAM 引擎大家个别用得也比拟少了。

4、程序内存透露危险。

能够用 valgrind 工具测验是否存在这个问题，如果确定的话，能够思考降级 MySQL 版本，或者定期在保护工夫重启 mysqld 实例，或者通过高可用切换形式将有危险的实例重启。

5、glibc 的内存管理器本身缺点导致。

简言之，就是调用 glibc 申请的内存应用结束后，归还给 OS 时没有被失常回收，而变成了碎片，随着碎片的一直增长，就能看到 mysqld 过程占用的内存一直回升。这时候，咱们能够调用函数被动回收开释这些碎片。

PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
45305 mysql     20   0   28.4g    25g   8400 S  48.5 81.4  64:46.82 mysqld

[root@mysql#] gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'

  PID USER      PR  NI    VIRT    RES    SHR  S  %CPU %MEM     TIME+ COMMAND
45305 mysql     20   0   28.4g    5.2g   8288 S  2.7  17.0  64:56.82 mysqld

这就像是在 InnoDB 表中产生太多碎片后，咱们被动执行 OPTIMIZE TABLE 重建表的做法。

Enjoy MySQL :)

本文由博客一文多发平台 OpenWrite 公布！

退出移动版