MySQL 메모리 사용량 최대 100% 증가

MySQL 메모리 사용량 최대 100% 증가

256GB RAM을 탑재한 머신에 MariaDB(10.0.20)를 인스톨 하고 있습니다.메모리를 재기동할 때까지, 메모리 사용량은 하루 종일 증가해, 최대 100%가 됩니다.몇 번 재부팅하면 메모리 사용량이 70%로 증가하지 않게 되지만 몇 시간 후에 다시 증가하기 시작합니다.

서버 또는 구성에 어떤 문제가 있습니까?

빨간색 선 - 메모리.0%로 저하 - 재부팅

mysqltuner 정보:

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 4h 50m 27s (28M q [1K qps], 305K conn, TX: 157G, RX: 29G)
[--] Reads / Writes: 14% / 86%
[--] Binary logging is disabled
[--] Physical Memory     : 251.6G
[--] Max MySQL memory    : 197.1G
[--] Other process memory: 1.5G
[--] Total buffers: 182.9G global + 48.5M per thread (300 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 187.3G (74.44% of installed RAM)
[OK] Maximum possible memory usage: 197.1G (78.34% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (3/28M)
[OK] Highest usage of available connections: 31% (93/300)
[OK] Aborted connections: 0.00%  (1/305591)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 339K sorts)
[!!] Joins performed without indexes: 187
[!!] Temporary tables created on disk: 61% (170K on disk / 278K total)
[OK] Thread cache hit rate: 99% (93 created / 305K connections)
[OK] Table cache hit rate: 110% (693 open / 630 opened)
[OK] Open file limit used: 0% (63/8K)
[OK] Table locks acquired immediately: 100% (26M immediate / 26M locks)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 180.0G/304.6G
[OK] InnoDB buffer pool instances: 64
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.98% (55901943340 hits/ 55911688878 total)
[OK] InnoDB Write log efficiency: 92.03% (161171609 hits/ 175129277 total)
[OK] InnoDB log waits: 0.00% (0 waits / 13957668 writes)

my.cnf:

[mysqld]
#pid-file = /var/run/mysqld/mysqld.pid
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

ft_min_word_len         = 3

## Cache
thread-cache-size               = 128
table-open-cache                = 1024
table-definition-cache          = 1024
query-cache-size                = 768M 
query-cache-limit               = 2048M
query-cache-type          = 0

## Per-thread Buffers
sort-buffer-size                = 16M
read-buffer-size                = 256K
read-rnd-buffer-size            = 16M
join-buffer-size                = 16M

## Temp Tables
tmp-table-size                  = 2G 
max-heap-table-size             = 2G 

## Networking
back-log                        = 300
open-files-limit        = 8192
open-files          = 1024
max-connections                 = 300
max-connect-errors              = 100
concurrent-insert       = 2
max-allowed-packet              = 512M
interactive-timeout             = 30
wait-timeout                    = 30

### Storage Engines
#default-storage-engine         = InnoDB
innodb                          = FORCE

## MyISAM
key-buffer-size                 = 10M
key_buffer_size                 = 10M
myisam-sort-buffer-size         = 128M
myisam-max-sort-file-size   = 256M

## InnoDB
innodb-buffer-pool-size         = 160G
innodb_buffer_pool_size         = 160G
innodb_buffer_pool_instances            = 150
innodb-buffer-pool-instances            = 150
innodb-log-buffer-size          = 32M
innodb-log-file-size            = 1512M
innodb-file-per-table           = 1
#innodb-open-files              = 300
innodb-flush-method     = O_DIRECT
innodb-autoinc-lock-mode    = 0
#innodb_max_dirty_pages_pct     = 0
#innodb-max-dirty-pages-pct     = 0
#innodb_io_capacity = 2000
#innodb-io-capacity = 2000
#innodb_read_io_threads = 64
#innodb_write_io_threads = 64
#innodb-read-io-threads = 64
#innodb-write-io-threads = 64


#server_audit_logging=ON
#server_audit_events=connect

## Logging
log-output                      = FILE
slow-query-log                  = 1
slow-query-log-file             = /var/log/mariadb/slow-log.log
long-query-time                 = 200
max-binlog-size         = 256M

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb2.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

다음과 같은 이중 참조가 있습니다.

innodb-buffer-pool-size         = 160G

innodb_buffer_pool_size         = 160G

'-'만 유효한 것 같아요.

또, 다음의 주소도 체크해 주세요.wait_timeout파라멘터:

mysql> show variables like 'innodb_lock_wait_timeout';

thread-cache-size               = 128  -- drop to 30
query-cache-size                = 768M  -- bad for performance, drop to 50M
query-cache-limit               = 2048M  -- make less than the query_cache_size
query-cache-type         = 0  -- good
tmp-table-size           = 2G   -- drop to 1G
max-heap-table-size      = 2G   -- drop to 1G
max-connections          = 300   -- drop to 150
innodb_buffer_pool_size  = 160G  -- good
innodb_buffer_pool_instances  = 150  -- lower to 32
slow-query-log          = 1  -- good
long-query-time         = 200  -- slow log is useless unless this is low enough, say =2

slowlog(변경 후)long_query_time)를 사용하면, 「!」를 찾을 수 있습니다.디스크에 작성된 임시 테이블: 61%(디스크에 17,000/총 278,000)가 중요할 수 있습니다.

Linux의 경우 설정swappiness(1%와 같이) 1로 합니다.

언급URL : https://stackoverflow.com/questions/39389759/mysql-memory-usage-growing-up-to-100