V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
cmai
V2EX  ›  数据库

mysql 内存持续增长,可以从哪里着手排查呢?

  •  
  •   cmai · 2023-03-27 14:25:41 +08:00 · 1544 次点击
    这是一个创建于 367 天前的主题,其中的信息可能已经有所发展或是发生改变。

    my.conf 如下 [mysqld]

    character_set_server            = utf8mb4
    
    datadir                         = /var/lib/mysql
    
    expire_logs_days                = 1
    
    explicit_defaults_for_timestamp = 1
    
    general_log                     = ON
    
    general_log_file                = /var/log/mysql/general_log_file.log
    
    innodb_buffer_pool_size         = 1G
    
    innodb_flush_log_at_trx_commit  = 2
    
    innodb_flush_method             = O_DIRECT
    
    innodb_flush_neighbors          = 0
    
    innodb_io_capacity              = 1000
    
    innodb_io_capacity_max          = 2000
    
    innodb_large_prefix             = 1
    
    innodb_lock_wait_timeout        = 30
    
    innodb_print_all_deadlocks      = 1
    
    innodb_thread_concurrency       = 4
    
    join_buffer_size                = 1M
    
    log-error                       = /var/log/mysql/error.log
    
    log_queries_not_using_indexes   = 0
    
    log_slow_admin_statements       = 1
    
    log_slow_slave_statements       = 1
    
    log_timestamps                  = system
    
    long_query_time                 = 10
    
    max_connect_errors              = 10
    
    pid-file                        = /var/run/mysqld/mysqld.pid
    
    read_rnd_buffer_size            = 8388608
    
    slow_query_log                  = 1
    
    slow_query_log_file             = /var/log/mysql/slow_query_log_file.log
    
    socket                          = /var/run/mysqld/mysqld.sock
    
    sort_buffer_size                = 4194304
    
    tmp_table_size                  = 67108864
    
    wait_timeout                    = 600
    
    binlog-ignore-db                = mysql
    
    enforce-gtid-consistency        = ON
    
    gtid-mode                       = ON
    
    log-bin                         = mysql-bin
    
    log-slave-updates               = ON
    
    innodb_log_file_size            = 256M
    
    lower_case_table_names          = 1
    
    max_connections                 = 512
    
    server-id                       = 1
    
    sql-mode                        =
    STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    第 1 条附言  ·  342 天前
    现在该数据库的 RSS 基本在 20G 左右平稳了, 但是之前这个数据库在虚拟机上的表现是 10G 左右,我想知道他们可能有什么差异点

    看了下她的 smaps ,里面有大量的 65536KB 的内存快,有大概 170 个左右,尝试过 gdb 导出内存,会抛出访问不了某个内存区域的异常,所以问下大家有什么看法吗?
    7f0208000000-7f020c000000 rw-p 00000000 00:00 0
    Size: 65536 kB
    KernelPageSize: 4 kB
    MMUPageSize: 4 kB
    Rss: 65536 kB
    Pss: 65536 kB
    Shared_Clean: 0 kB
    Shared_Dirty: 0 kB
    Private_Clean: 0 kB
    Private_Dirty: 65536 kB
    Referenced: 65536 kB
    Anonymous: 65536 kB
    LazyFree: 0 kB
    AnonHugePages: 65536 kB
    ShmemPmdMapped: 0 kB
    FilePmdMapped: 0 kB
    Shared_Hugetlb: 0 kB
    Private_Hugetlb: 0 kB
    Swap: 0 kB
    SwapPss: 0 kB
    Locked: 0 kB
    THPeligible: 1
    VmFlags: rd wr mr mw me nr
    13 条回复    2023-06-21 16:22:04 +08:00
    cmai
        1
    cmai  
    OP
       2023-03-27 14:27:31 +08:00
    error log 里有频繁的这个信息

    2023-03-27T14:25:34.279971+08:00 41869 [Note] Got an error reading communication packets
    2023-03-27T14:25:54.280650+08:00 41876 [Note] Got an error reading communication packets
    2023-03-27T14:25:54.280710+08:00 41877 [Note] Got an error reading communication packets
    2023-03-27T14:26:14.280804+08:00 41881 [Note] Got an error reading communication packets
    2023-03-27T14:26:14.280832+08:00 41882 [Note] Got an error reading communication packets
    2023-03-27T14:26:34.280017+08:00 41885 [Note] Got an error reading communication packets
    2023-03-27T14:26:34.280043+08:00 41886 [Note] Got an error reading communication packets
    2023-03-27T14:26:54.280510+08:00 41891 [Note] Got an error reading communication packets
    2023-03-27T14:26:54.280538+08:00 41890 [Note] Got an error reading communication packets
    cmai
        2
    cmai  
    OP
       2023-03-27 14:28:21 +08:00
    使用 k8s 部署的, 内存增长到 limit 上限就重启了
    cmai
        3
    cmai  
    OP
       2023-03-27 14:29:26 +08:00
    @cmai 以及有大量的 2023-03-27T14:28:59.264149+08:00 41757 [Note] Aborted connection 41757
    jeffrey921
        4
    jeffrey921  
       2023-03-27 16:57:22 +08:00
    有可能是查询数据后没释放掉
    cmai
        5
    cmai  
    OP
       2023-03-27 17:18:42 +08:00
    @jeffrey921 有办法验证吗
    sunjiayao
        6
    sunjiayao  
       2023-03-27 17:31:19 +08:00
    先 show processlist 看看
    liprais
        7
    liprais  
       2023-03-27 17:37:46 +08:00
    日志挂出来了?
    CharAct3
        8
    CharAct3  
       2023-03-27 18:00:58 +08:00
    可以设置一下 maxLifetime ,定期重建连接,触发内存回收,可能对你们的 case 会有帮助
    Cornstalk8256
        9
    Cornstalk8256  
       2023-03-27 18:04:12 +08:00
    多大的业务啊,MySQL 居然部署在容器里面
    cmai
        10
    cmai  
    OP
       2023-03-27 18:57:44 +08:00
    @sunjiayao 额,这个的目的是什么, 只有 74 个连接,大部分在 sleep 状态
    cmai
        11
    cmai  
    OP
       2023-03-27 19:01:23 +08:00
    统一回复,大家不用纠结容器的问题,我们做的是 devops 平台,目的在于一键部署一个 mysql ,并且有统一的指标监控、告警、备份策略以及配置等, 在于开发提效和分离开发人员的关注度
    cmai
        12
    cmai  
    OP
       2023-03-27 19:01:47 +08:00
    @CharAct3 ok ,我先了解下
    a7851578
        13
    a7851578  
       281 天前
    找个实例挂上 valgrind 看看,固定大小不能访问内存,可能是内存泄漏了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5521 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 09:03 · PVG 17:03 · LAX 02:03 · JFK 05:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.