MySql 최대 메모리 용량이 위험할 정도로 높아도 증대가 필요함

MySql 최대 메모리 용량이 위험할 정도로 높아도 증대가 필요함

4개의 코어가 있는 VM에 Wordpress 사이트가 매우 적은 트래픽을 가지고 있다/8GB Ram. 사이트에 대한 쿼리가 매우 느리게 실행되므로 mysqltuner.pl 스크립트를 실행하여 조사할 수 있습니다.결과는 다음과 같습니다.

[OK] Currently running supported MySQL version 10.1.22-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: ./master_error.log(1M)
[OK] Log file ./master_error.log exists
[OK] Log file ./master_error.log is readable.
[OK] Log file ./master_error.log is not empty
[OK] Log file ./master_error.log is smaller than 32 Mb
[!!] ./master_error.log contains 89 warning(s).
[!!] ./master_error.log contains 24 error(s).
[--] 4 start(s) detected in ./master_error.log
[--] 1) 2017-03-24  3:43:22 140281079671040 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-03-24  3:41:30 140390495832320 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-03-24  3:37:36 139677438822656 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-03-03 11:45:32 139685722654976 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3 shutdown(s) detected in ./master_error.log
[--] 1) 2017-03-24  3:42:18 140390132259584 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-03-24  3:40:01 139677360564992 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-03-24  3:37:32 139685358557952 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE 
[--] Data in InnoDB tables: 277M (Tables: 677)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 13d 11h 25m 55s (3M q [2.764 qps], 43K conn, TX: 20G, RX: 451M)
[--] Reads / Writes: 97% / 3%
[--] Binary logging is disabled
[--] Physical Memory     : 7.6G
[--] Max MySQL memory    : 24.3G
[--] Other process memory: 2.3G
[--] Total buffers: 22.5G global + 12.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 22.7G (296.63% of installed RAM)
[!!] Maximum possible memory usage: 24.3G (318.26% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (11K/3M)
[OK] Highest usage of available connections: 10% (16/151)
[OK] Aborted connections: 0.00%  (2/43596)
[--] Skipped name resolution test due to skip_networking=ON in system variables.
[!!] Query cache may be disabled by default due to mutex contention.
[OK] Query cache efficiency: 40.2% (1M cached / 4M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 71K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 51% (44K on disk / 86K total)
[OK] Thread cache hit rate: 99% (23 created / 43K connections)
[OK] Table cache hit rate: 22% (854 open / 3K opened)
[OK] Open file limit used: 0% (60/16K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 4 thread(s).
[--] Using default value is good enough for your version (10.1.22-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.9% (2B used / 11B cache)
[OK] Key buffer size / total MyISAM indexes: 11.0G/123.0K
[OK] Read Key buffer hit rate: 99.3% (1K cached / 9 reads)
[OK] Write Key buffer hit rate: 100.0% (60 cached / 60 writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 11.0G/278.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (1.77556818181818 %): 100.0M * 2/11.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (111655571 hits/ 111658256 total)
[OK] InnoDB Write log efficiency: 97.88% (1070445 hits/ 1093608 total)
[OK] InnoDB log waits: 0.00% (0 waits / 23163 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.3% (1M cached / 35K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into ./master_error.log file
Control error line(s) into ./master_error.log file
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_type (=0)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    performance_schema = ON enable PFS
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=5G) if possible.
    innodb_buffer_pool_instances(=11)

...여기 server.conf가 있습니다.

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
# Connection and Thread variables
socket = /var/lib/mysql/mysql.sock
pid-file=/var/lib/mysql/maria.pid
tmpdir = /run/mariadb
optimizer_search_depth=0
bind-address    = 127.0.0.1

skip-external-locking
key_buffer_size = 11G
max_allowed_packet = 1M
table_open_cache = 4000
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 256M
query_cache_limit = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 2

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 11G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64


# Security variables
local_infile                   = 0 
secure_auth                    = 1 
sql_mode                       =    TRADITIONAL,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip_name_resolve              = 1
symbolic-links=0
skip-networking

### LOGS ###
# MySQL error log
log_error                      = master_error.log
log_warnings                   = 2
# innodb_print_all_deadlocks     = 1

# Slow Query Log
slow_query_log_file            = master_slow.log
slow_query_log                 = 1
log_queries_not_using_indexes  = 1
long_query_time                = 0.5
min_examined_row_limit         = 100

# General Query Log
general_log_file               = master_general.log
general_log                    = 0

[mysqldump]
quick
max_allowed_packet = 16M

4000의 table_open_cache 등 몇 가지 문제가 있는 것 같습니다만, 설정을 만지작거리기 전에 전문가의 의견을 듣고 싶었습니다.또, 메모리 사용율이 어떻게 이렇게 높은지 혼란스러워하고 있습니다만, 값을 한층 더 높이는 것이 권장되고 있습니다.

mysqltuner는 완벽하지 않습니다.하지만 8GB의 RAM에 비해 너무 높은 설정이 있습니다.

기본적으로 MyISAM 테이블이 없는 8GB RAM? key_buffer_sizemysqltuner 사람들이 듣고 있다면 그들은 그 테스트를 고쳐야 한다.

또한.innodb_buffer_pool_size5G에 불과해야 RAM이 손실되는 것을 방지할 수 있습니다.innodb_buffer_pool_instances5까지)

query_cache_size약 5천만개를 넘으면 비효율적이 됩니다.

log_queries_not_using_indexes불필요하게 슬로로그를 흐트러뜨립니다.오프해 주세요.

아리아--는1B1바이트인가 10억바이트인가?(mysqltuner의 다른 버그).

RAM을 MySQL에 오버할당했기 때문에 아마도 스왑하고 있을 것입니다.MySQL의 모든 버퍼가 RAM에 상주한다고 가정하기 때문에 스와핑은 매우 비효율적입니다.

언급URL : https://stackoverflow.com/questions/43259441/mysql-maximum-memory-dangerously-high-still-asking-for-increases