mysqltuner를 사용하면 SQL이 감당할 수 있는 메모리의 최대치가 조금 걱정됩니다. SQL을 더 잘 실행하고 직접 수행하는 방법을 이해하고 싶습니다.
내 mysqltuner 결과는 다음과 같습니다.
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.32
[OK] Operating on 64-bit architecture
-------- Log file Recommendations ------------------------------------------------------------------
[OK] Log file /var/log/mysqld.log exists
[--] Log file: /var/log/mysqld.log(141K)
[OK] Log file /var/log/mysqld.log is readable.
[OK] Log file /var/log/mysqld.log is not empty
[OK] Log file /var/log/mysqld.log is smaller than 32 Mb
[!!] /var/log/mysqld.log contains 246 warning(s).
[!!] /var/log/mysqld.log contains 46 error(s).
[--] 16 start(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:30:59.930120Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2021-01-14T11:08:52.530228Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2021-01-14T11:06:44.798420Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2021-01-14T10:58:56.636812Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2021-01-14T10:58:49.998564Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2021-01-14T10:23:15.649280Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2021-01-14T09:35:59.598495Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2021-01-14T09:35:24.591837Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2021-01-14T09:26:15.821136Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2021-01-14T08:23:56.658016Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7 shutdown(s) detected in /var/log/mysqld.log
[--] 1) 2021-01-14T11:27:51.895559Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2021-01-14T11:08:49.145150Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2021-01-14T11:06:24.969477Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2021-01-14T10:58:53.419431Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2021-01-14T09:35:54.472640Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2021-01-08T08:38:07.365178Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2021-01-07T20:25:04.834240Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 525.3M (Tables: 446)
[OK] Total fragmented tables: 0
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- 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: 13m 25s (63K q [79.404 qps], 358 conn, TX: 381M, RX: 20M)
[--] Reads / Writes: 91% / 9%
[--] Binary logging is disabled
[--] Physical Memory : 2.8G
[--] Max MySQL memory : 38.0G
[--] Other process memory: 0B
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
[--] P_S Max memory usage: 72B
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[OK] Slow queries: 0% (0/63K)
[OK] Highest usage of available connections: 6% (10/151)
[OK] Aborted connections: 0.00% (0/358)
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 56K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 7K sorts)
[!!] Joins performed without indexes: 11
[!!] Temporary tables created on disk: 57% (3K on disk / 5K total)
[OK] Thread cache hit rate: 97% (10 created / 358 connections)
[!!] Table cache hit rate: 16% (863 open / 5K opened)
[OK] table_definition_cache(1400) is upper than number of tables(725)
[OK] Open file limit used: 0% (11/40K)
[OK] Table locks acquired immediately: 100% (160 immediate / 160 locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 72B
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 18.3% (1M used / 8M cache)
[OK] Key buffer size / total MyISAM indexes: 8.0M/43.0K
[!!] Read Key buffer hit rate: 90.7% (118 cached / 11 reads)
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 40.0M/525.3M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (240 %): 48.0M * 2/40.0M should be equal to 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.74% (7453089 hits/ 7472246 total)
[!!] InnoDB Write Log efficiency: 72.13% (18568 hits/ 25743 total)
[OK] InnoDB log waits: 0.00% (0 waits / 7175 writes)
-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.
-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.
-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.
-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.
-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: ROW
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries which have no LIMIT clause
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64
Read this before increasing for MariaDB
This is MyISAM only table_cache scalability problem, InnoDB not affected.
This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
Beware that open_files_limit (40000) variable
should be greater than table_open_cache (2000)
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (=0)
query_cache_type (=0)
query_cache_limit (> 1M, or use smaller result sets)
join_buffer_size (> 256.0K, or always use indexes with JOINs)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
table_open_cache (> 2000)
innodb_buffer_pool_size (>= 525.3M) if possible.
innodb_log_file_size should be (=5M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
My.cnf 파일은 제가 모든 다른 변수를 이해하지 못하기 때문에 기본입니다.
performance-schema=0
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=41943040
max_allowed_packet=268435456
open_files_limit=40000
innodb_file_per_table=1
bind-address=127.0.0.1
skip-name-resolve=1
performance_schema=ON
어떤 아이디어나 조언이 있나요?
답변1
왜 그렇게 높은 최대 메모리 사용량을 가지고 있습니까?
여기에서 세부 사항을 살펴보겠습니다.
귀하의 MySQL은 기본 번호인 max_connections150을 사용하고 있습니다(이는 실제로 데이터베이스 관리자가 연결할 수 있도록 해당 번호에 1을 더한 것을 허용합니다. 이것이 바로 표시된 번호가 151인 이유입니다).
[--] Total buffers: 81.0M global + 257.1M per thread (151 max threads)
이는 모든 연결이 동시에 사용되는 경우 사용할 버퍼 공간의 양을 알려줍니다. 계산기가 없다면 총 38,903.1M(38.0G)이 됩니다.
[!!] Maximum possible memory usage: 38.0G (1372.84% of installed RAM)
역사적으로 동시 연결 수는 최대 10개이므로 max_connections상당히 줄어들 수 있습니다. 시작점으로 20으로 설정하고 필요한 경우에만 거기에서 올립니다.
[OK] Highest usage of available connections: 6% (10/151)
하지만 더 우려스러운 점은 실제로 사용된 메모리의 양입니다.
[!!] Maximum reached memory usage: 2.6G (93.59% of installed RAM)
대규모 바이너리 blob을 데이터베이스 안팎으로 이동할 필요가 없는 한(그렇게 한다면 애플리케이션 설계를 다시 고려하십시오) max_allowed_packet. 기본값은 64M이며 메모리 사용량(실제 및 잠재적 모두)의 거의 3/4를 줄이도록 설정할 수 있습니다. "패킷이 너무 큼" 오류가 발생하면 오류를 발생시킬 수 있지만 열려 있는 연결마다 RAM이 필요합니다.
답변2
성능 문제가 있는 경우 가능한 해결책은 쿼리 인덱싱 및/또는 재구성과 관련됩니다. 가장 느린 쿼리를 찾아 제안하고 조언해 주세요.
한편, '소형' 2.8GB RAM에서 조정할 수 있는 두 가지 설정은 다음과 같습니다.
innodb_buffer_pool_size500M 정도 되어야 합니다.max_allowed_packet = 30M