我有MariaDB(10.0.20)安装在256 GB RAM的机器上。内存使用量整天增长高达100%,直到重新启动它。在一些重新启动后,内存使用量停止增长70% -但在几个小时内又开始增长。
我的服务器或配置有什么问题?
Red line - memory. Falling to 0% - reboots
mysqltuner info:
-------- 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
型
2条答案
按热度按时间kgsdhlau1#
你有一些双重引用,比如:
字符串
我认为只有“-”的才有效。
还可以使用以下命令检查
wait_timeout
参数:型
w8f9ii692#
字符串
slowlog(在您更改
long_query_time
之后)将帮助您找到“[!!] Temporary tables created on disk:61%(170 K on disk /278 K total)",这可能很重要。在Linux中,将
swappiness
设置为1(如1%)。