MySQL 과 MariaDB 는 아주 널리 사용되는 RDBMS 의 종류중 하나이다. 기본 설정만으로도 아주 놀라운 성능을 보여준다. 하지만 모든 상황에서 최적의 성능을 발휘하기는 어렵다. 특히 데이터베이스의 규모가 커지고, 복잡한 쿼리가 자주 실행되기도 하며, 동시에 많은 사용자가 접근하는 경우, 성능 저하와 자원 낭비가 발생할 수 있다. 이는 비용의 낭비와 서비스의 질적 저하로 이어질 수 있다.
위와 같은 문제를 해결하고 서버의 효율을 극대화 하기 위해서 서버의 상태를 지속적으로 모니터링하고, 그 결과에 따른 최적화된 설정을 적용하는 것이 필수적이다. 이때 사용할 수 있는 도구를 하나 소개한다. 숙련된 DBA 라면 필요하지 않겠지만 간편하게 성능을 분석하고 개선할 수 있는 팁을 제공해준다.
MySQLTuner-perl
링크 : https://github.com/major/MySQLTuner-perl
지원데이터베이스
- MySQL 8.0, 8.2, 8.3 (full support)
Percona Server 8.0, 8.2, 8.3 (full support)
MariaDB 10.4, 10.5, 10.6, 10.11, 11.0, 11.1, 11.2 (full support)
Galera replication (full support)
Percona XtraDB cluster (full support)
Mysql Replications (partial support, no test environment)
MySQL 8.1 (not supported, deprecated version)
Percona Server 5.7 (not supported, deprecated version)
MySQL 5.7 (not supported, deprecated version)
MySQL 5.6 and earlier (not supported, deprecated version)
Percona Server 5.6 (not supported, deprecated version)
MariaDB 10.7, 10.8, 10.9, 10.10 (not supported, deprecated version)
MariaDB 10.3 and earlier (not supported, deprecated version)
MariaDB 5.5 (not supported, deprecated version)
주의!!!
제작자도 언급하고 있지만 테스트 결과에 따른 변경사항등을 완벽하게 이해하고 설정을 적용해야 한다. 어려운 부분이 있다면, 또는 이해가 되지 않는 설정이 있다면 숙련된 DBA에게 문의 후 해당 설정을 적용하기 바란다. 먼저 변경사항을 테스트하고 프로덕션에 적용하는 것을 권장한다. 또한 정확한 결과를 위해서 시스템의 가동시간을 먼저 충분히 확보하여 데이터가 축적된 후에 테스트할 필요가 있다. 새롭게 구축된 서버에서 사용하는 것은 무의미하다.
필수 요구사항
- perl 이 설치된 서버여야 하며
- 리눅스 기반의 서버에서 사용할 수 있다.
perl 이 설치되지 않은 경우에는 아래의 설명을 따라 perl 을 설치한다.
#rhel/rocky/centos
dnf install perl
#debian/ubuntu
sudo apt-get install perl
다운로드
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv실행방법
먼저 DB서버의 관리자 권한을 가진 계정정보가 필요하다
./mysqltuner.pl --user [username] --pass [password]만약 로컬서버가 아닌 원격의 서버를 분셕하려면 --host 옵션을 사용할 수 있다.
주요옵션
--host [hostname]: 원격 MySQL 서버의 호스트네임 또는 IP 주소를 지정합니다.
--port [port]: MySQL 서버가 사용하는 포트를 지정합니다(기본값: 3306).
--socket [socket]: MySQL 서버의 소켓 파일을 지정합니다.
--user [username]: MySQL 서버의 사용자명을 지정합니다.
--pass [password]: MySQL 서버의 비밀번호를 지정합니다.
--forcemem: 서버 메모리 사용량을 강제로 평가합니다(메모리 부족 상황에서 사용).
--skipsize: 테이블 및 데이터베이스 크기를 건너뜁니다.
--cvefile: CVE(공개된 취약점 데이터베이스)를 사용해 MySQL 버전의 보안 문제를 점검합니다.
테스트 예시
아래의 결과는 테스트 서버에서 실행해본 결과입니다.
# perl mysqltuner.pl --user root
>> MySQLTuner 2.6.0
* Jean-Marie Renouard
* Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.pl/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials passed on the command line
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 12.4G (Tables: 102)
[OK] Total fragmented tables: 0
[OK] Currently running supported MySQL version 10.5.18-MariaDB
-------- Log file Recommendations ------------------------------------------------------------------
[!!] Log file doesn't exist
-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.
-------- Views Metrics -----------------------------------------------------------------------------
-------- Triggers Metrics --------------------------------------------------------------------------
-------- Routines Metrics --------------------------------------------------------------------------
-------- Security Recommendations ------------------------------------------------------------------
[OK] All database users have passwords assigned
[--] There are 620 basic passwords in the list.
-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION
-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 570d 7h 56m 35s (138M q [2.818 qps], 13M conn, TX: 277G, RX: 32G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory : 15.5G
[--] Max MySQL memory : 13.2G
[--] Other process memory: 0B
[--] Total buffers: 1.3G global + 302.7M per thread (40 max threads)
[--] Performance_schema Max memory usage: 104M
[--] Galera GCache Max memory usage: 0B
[!!] Maximum reached memory usage: 13.5G (87.10% of installed RAM)
[!!] Maximum possible memory usage: 13.2G (85.20% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (8/138M)
[!!] Highest connection usage: 100% (41/40)
[!!] Aborted connections: 5.79% (806657/13926964)
[!!] Name resolution is active: a reverse name resolution is made for each new connection which can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 20M sorts)
[!!] Joins performed without indexes: 11462734
[OK] Temporary tables created on disk: 0% (621K on disk / 62M total)
[OK] Thread cache hit rate: 99% (5K created / 13M connections)
[OK] Table cache hit rate: 94% (150M hits / 158M requests)
[OK] table_definition_cache (400) is greater than number of tables (386)
[OK] Open file limit used: 0% (56/32K)
[OK] Table locks acquired immediately: 99% (2K immediate / 2K locks)
-------- Performance schema ------------------------------------------------------------------------
[--] Performance_schema is activated.
[--] Memory used by Performance_schema: 105.0M
[--] Sys schema is installed.
-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.
-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] General MyIsam metrics:
[--] +-- Total MyISAM Tables : 0
[--] +-- Total MyISAM indexes : 0B
[--] +-- KB Size :128.0M
[--] +-- KB Used Size :23.3M
[--] +-- KB used :18.2%
[--] +-- Read KB hit rate: 0% (0 cached / 0 reads)
[--] +-- Write KB hit rate: 0% (0 cached / 0 writes)
[--] No MyISAM table(s) detected ....
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB Buffer Pool size ( 1.0G ) under limit for 64 bits architecture: (17179869184.0G )
[!!] InnoDB buffer pool / data size: 1.0G / 12.4G
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 250.0M * 1/1.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk: 8 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.98% (48329381442 hits / 48338130671 total)
[!!] InnoDB Write Log efficiency: 126.69% (1281059 hits / 1011187 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2292246 writes)
-------- Aria Metrics ------------------------------------------------------------------------------
[--] Aria Storage Engine is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/336.0K
[OK] Aria pagecache hit rate: 100.0% (3B cached / 598K reads)
-------- 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: MIXED
[--] XA support enabled: ON
[--] Semi synchronous replication Master: OFF
[--] Semi synchronous replication Slave: OFF
[--] This is a standalone server
-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Remove Anonymous User accounts: there are 2 anonymous accounts.
Reduce your overall MySQL memory footprint for system stability
Reduce or eliminate persistent connections to reduce connection usage
Reduce or eliminate unclosed connections and network issues
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
max_connections (> 40)
wait_timeout (< 28800)
interactive_timeout (< 28800)
skip-name-resolve=ON
join_buffer_size (> 300.0M, or always use indexes with JOINs)
innodb_buffer_pool_size (>= 12.4G) if possible.
innodb_log_buffer_size (> 16M)위와 같은 결과를 참고 후 서버의 설정을 변경하여 튜닝할 수 있습니다.