우수한 소프트웨어와 실용적인 튜토리얼
CyberPanel의 MySQL 쓰기 성능 최적화 가이드
CyberPanel을 사용할 때 MySQL 쓰기 속도가 느려질 수 있습니다. 일반적으로 데이터베이스 쓰기에는 문제가 없지만, 웹사이트 트래픽이 증가하면 데이터베이스 속도가 매우 느려집니다.
이때 CPU와 메모리는 여전히 유휴 상태이고, 30% 미만이 사용 중이며, 대역폭도 여유가 있습니다. 데이터베이스 쓰기 속도가 느려지는 이유는 무엇일까요?
기본 설치된 데이터베이스는 최적화되어 있지 않습니다. MySQL 데이터베이스를 최적화하는 방법을 살펴보겠습니다.
MySQL의 쓰기 성능 최적화에는 하드웨어, 구성, 쿼리 설계 및 모니터링 조정이 포함됩니다. 실제 워크로드(예: 높은 동시 쓰기)에 따라 최적화를 통해 성능을 2~10배 향상시킬 수 있습니다.
CyberPanel에서 MySQL을 최적화하는 방법은 다음과 같습니다.
먼저 모니터링 도구를 설치합니다: sudo apt install mysql-tuner-perl, 그리고 perl mysql-tuner.pl을 실행하여 innodb_buffer_pool_size를 늘리는 것과 같은 예비 제안을 받습니다.
데이터베이스 구성 파일을 엽니다
vi /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] bind-address = 127.0.0.1 # 원격 남용을 방지하기 위해 로컬에 바인딩 default_storage_engine = InnoDB # 기본 InnoDB(트랜잭션 지원) character-set-server = utf8mb4 # 이모티콘 지원 collation-server = utf8mb4_unicode_ci
설정 파일에 없으면 [mysqld]에 추가하세요.
InnoDB 버퍼 풀을 최적화하세요. 쓰기 병목 현상은 InnoDB 로그, 버퍼, 잠금에서 자주 발생합니다. 점진적으로 조정하고 변경 사항을 모니터링하세요.
innodb_buffer_pool_size = 70% 시스템 RAM을 설정합니다.
innodb_buffer_pool_size = 44G innodb_buffer_pool_instances = 8 # 여러 인스턴스는 잠금 경합을 줄입니다.
고성능 웹 애플리케이션에서 데이터베이스 엔진인 MariaDB(MySQL 호환)는 기본 구성으로는 서버의 잠재력을 충분히 활용하지 못하는 경우가 많습니다. 예를 들어, 64GB RAM에서 기본 InnoDB 버퍼 풀은 128MB에 불과하며, 이는 권장 RAM 용량인 70%(약 45GB)보다 훨씬 적습니다. 이로 인해 디스크 I/O가 잦아지고 쿼리 지연 시간이 발생하며 쓰기 병목 현상이 발생할 수 있습니다. MariaDB 공식 벤치마크와 2025년 Percona 보고서에 따르면, 최적화되지 않은 구성의 TPS(초당 트랜잭션 수)는 최대 30%에 불과합니다. 그러나 최적화를 통해 이 수치는 4~8배까지 증가할 수 있으며, 특히 전자상거래나 CMS와 같이 동시성이 높은 환경에서는 더욱 그렇습니다.
CyberPanel에서 MySQL을 최적화하는 것은 일회성 작업이 아니라 반복적인 프로세스입니다. 기본 구성부터 시작하여 대시보드 도구 및 모니터링과 결합하면 성능을 크게 향상시키고 일반적인 병목 현상을 방지할 수 있습니다. 최적화 후에는 JMeter 시뮬레이션 등을 통해 부하를 테스트하고 MySQL 튜너를 정기적으로 실행하세요. 2025년까지 AI 기반 데이터베이스 도구의 등장으로 CyberPanel 통합은 더욱 스마트해질 것이지만, 수동 튜닝은 여전히 기본이 될 것입니다. 최적화가 완료되면 웹사이트의 효율성이 향상되고 비즈니스 성장에 도움이 될 것입니다.
MySQL 임시 테이블 디스크 생성 최적화: 임계값을 46%에서 25%로 줄이는 실용 가이드
MySQL 성능 튜닝에서 임시 테이블이 생성되는 위치는 쿼리 효율성과 리소스 소비에 직접적인 영향을 미칩니다. 현재 상황은 Created_tmp_disk_tables / (Created_tmp_tables + Created_tmp_disk_tables) * 100 = 46%로 권장 임계값인 25%를 크게 초과합니다. 이는 이러한 임시 테이블의 거의 절반이 메모리가 아닌 디스크에 저장되어 I/O 오버헤드가 급증하고 쿼리 대기 시간이 두 배로 늘어나며 높은 동시성에서 병목 현상이 발생한다는 것을 의미합니다. 수식(값 > 25)에 따르면 이는 mysql-tuner와 같은 도구에서 경고를 트리거합니다. 임시 테이블은 GROUP BY, ORDER BY 또는 DISTINCT 작업에서 자주 사용됩니다. 메모리가 부족하면 MySQL은 자동으로 디스크(MyISAM 엔진)로 전환합니다. 이는 64GB RAM이 있는 서버에서는 발생하지 않지만 최적이 아닌 구성이나 쿼리 설계 문제를 나타낼 수 있습니다.
먼저, my.cnf 파일의 [mysqld] 섹션에 다음 줄을 추가하여 매개변수를 동시에 조정합니다. max_heap_table_size = tmp_table_size = 1024MB(RAM 1.6% 점유)로 설정하고 MariaDB를 재시작한 후 테스트합니다. SHOW GLOBAL STATUS LIKE 'Created_tmp%'; 를 모니터링하여 25% 미만으로 줄이는 것을 목표로 합니다. 그래도 문제가 해결되지 않으면 쿼리를 다시 작성합니다. EXPLAIN을 사용하여 느린 SQL을 분석하거나, BLOB 필드를 외부 저장소로 분할하거나, 쿼리 캐싱을 활성화합니다. Percona Toolkit의 pt-query-digest와 함께 사용하여 로그를 분석하고 최적화를 반복합니다. 궁극적으로 임시 테이블 메모리 적중률이 80%를 초과할 뿐만 아니라 전체 성능이 2~4배 향상되어 간신히 버티던 데이터베이스가 응답성이 매우 뛰어난 데이터베이스로 변모합니다.
tmp_table_size = 1024M 최대_힙_테이블_크기 = 1024M
MySQL long_query_time 설정 최적화: 10초를 1~5초로 줄이는 방법
MySQL 성능 모니터링에서 long_query_time 매개변수는 슬로우 쿼리 로그 임계값을 정의합니다. 이 값을 초과하는 쿼리는 후속 분석 및 최적화를 위해 "슬로우 쿼리"로 기록됩니다. 현재 설정(10초 이상)은 매우 느린 쿼리(복잡한 조인이나 대규모 테이블 스캔 등)만 캡처되므로 잠재적인 병목 현상을 놓치고 성능 문제가 드러나지 않습니다. MySQL 설명서와 Percona 모범 사례에 따르면, 기본 10초 임계값은 부하가 낮은 테스트 환경에 적합하지만, 운영 서버(일일 쿼리 볼륨이 10만 개를 초과하는 경우)에서는 슬로우 쿼리 로그가 너무 희소해져 인덱스 누락이나 잠금 경합과 같은 문제를 적시에 식별하지 못합니다. 결과적으로 데이터베이스 응답 시간이 약간 증가하고 사용자 경험이 저하되며 TPS(초당 트랜잭션 수)가 20~50% 감소합니다.
현재 분석: long_query_time 임계값 10은 수식(값 >= 10)을 기반으로 직접 검증되며, mysql-tuner와 같은 도구에서 경고를 발생시킵니다. 10초 임계값은 1~5초의 "중간-느린 쿼리"를 무시하는데, 이러한 쿼리는 종종 높은 수익률의 최적화 기회(예: EXPLAIN을 사용한 분석)를 제공합니다. 트래픽이 많은 시나리오에서는 임계값을 1~5초로 조정하는 것이 좋습니다. 1초는 실시간 애플리케이션(예: 전자상거래), 2~3초는 일반적인 용도, 5초는 보고 워크로드에 적합합니다. 임계값을 줄이면 로그가 더 풍부해지지만, 디스크 부하를 방지하기 위해 로그 크기(/var/log/mysql/mariadb-slow.log)를 모니터링해야 합니다.
long_query_time = 5 # 부하 테스트를 기준으로 1~5초를 권장합니다 slow_query_log = 1 # 느린 쿼리 로그를 활성화합니다(활성화되지 않은 경우) slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbosity = query_plan,explain # 실행 계획을 기록합니다 log_queries_not_using_indexes = 1 # 인덱스를 사용하지 않는 쿼리를 기록합니다 log_slow_admin_statements = 1 # 관리 명령문을 기록합니다
튜닝 후, 슬로우 쿼리 로그는 더 자세한 정보를 캡처하여 문제를 신속하게 파악하고 전체 쿼리 속도를 30% 이상 향상시킵니다. 하지만 로그 파일 크기는 증가합니다(하루에 10~100MB). logrotate /var/log/mysql/mariadb-slow.log를 사용하여 정기적으로 로그 파일을 순환시키세요. 부하가 높을 때는 EXPLAIN과 인덱스 최적화를 병행하여 과도한 로깅을 방지하세요. mysql-tuner를 정기적으로 실행하여 임계값을 확인하여 실제 요구 사항과 일치하는지 확인하세요(예: 2초에서 3초로 조정).
이러한 조정을 통해 MySQL 데이터베이스의 민첩성이 향상되어 웹사이트 운영 효율성이 향상됩니다. 최적화는 지속적인 프로세스이며, Grafana와 같은 모니터링 도구와 함께 사용하면 더욱 나은 결과를 얻을 수 있습니다.
다음은 프로덕션 환경에서 사용할 수 있는 완전한 MySQL 구성입니다.
# # 이 그룹은 MariaDB 서버에서 읽습니다. # 서버(클라이언트는 아님)에서만 볼 수 있는 옵션에 사용합니다. # 이것은 독립 실행형 데몬과 내장형 서버에서 읽습니다. [server] # 이것은 mysqld 독립 실행형 데몬에만 해당합니다. [mysqld] # # * 기본 설정 # #user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr #datadir = /var/lib/mysql #tmpdir = /tmp # 끊어진 역방향 DNS는 연결 속도를 상당히 늦추고 이름 확인은 # "도메인 이름으로 호스트" 액세스 권한이 없는 경우 건너뛰어도 안전합니다. #skip-name-resolve # skip-networking 대신 기본값은 이제 localhost에서만 수신하는 것인데, 이는 호환성이 더 좋고 보안성이 떨어지지 않습니다. bind-address = 127.0.0.1 # # * 미세 조정 # key_buffer_size = 512M max_allowed_packet = 2G thread_stack = 192K thread_cache_size = 1024 tmp_table_size = 1024M max_heap_table_size = 1024M open_files_limit = 0 #key_buffer_size = 128M #max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 # 이것은 시작 스크립트를 대체하고 필요한 경우 MyISAM 테이블을 확인합니다 # 처음으로 만질 때 #myisam_recover_options = BACKUP #max_connections = 100 #table_cache = 64 myisam_recover_options = BACKUP max_connections = 9999 table_open_cache = 40000 # # * 로깅 및 복제 # # 참고: 구성된 로그 파일이나 해당 디렉토리는 # 생성되어야 하며 mysql 사용자가 쓸 수 있어야 합니다. 예: # $ sudo mkdir -m 2750 /var/log/mysql # $ sudo chown mysql /var/log/mysql # 두 위치 모두 cronjob에 의해 회전됩니다. # 이 로그 유형은 성능을 저하시킨다는 점에 유의하세요. # 필요한 경우 짧은 테스트 기간 동안 런타임에만 이 설정을 변경하는 것이 좋습니다! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # systemd에서 실행할 때 오류 로깅은 stdout/stderr을 통해 journald로 전송되고 # 레거시 init을 실행할 때 오류 로깅은 # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf로 인해 syslog로 전송됩니다. # 별도의 파일에 오류 로깅을 저장하려면 이 옵션을 활성화합니다. #log_error = /var/log/mysql/error.log # 특히 지속 시간이 긴 쿼리를 보려면 느린 쿼리 로그를 활성화합니다. #log_slow_query_file = /var/log/mysql/mariadb-slow.log #log_slow_query_time = 10 #log_slow_verbosity = query_plan,explain #log-queries-not-using-indexes #log_slow_min_examined_row_limit = 1000 long_query_time =5 # 부하 테스트를 기준으로 1~5초를 권장합니다 slow_query_log = 1 # 느린 쿼리 로그를 활성화합니다(활성화하지 않은 경우) slow_query_log_file = /var/log/mysql/mariadb-slow.log log_slow_verbosity = query_plan,explain # 실행 계획을 기록합니다 log_queries_not_using_indexes = 1 # 인덱스를 사용하지 않는 쿼리를 기록합니다 log_slow_admin_statements = 1 # 관리 명령문을 기록합니다 # 다음은 백업 로그를 쉽게 재생하거나 복제하는 데 사용할 수 있습니다. # 참고: 복제본을 설정하는 경우 변경해야 할 수 있는 다른 설정에 대해서는 README.Debian을 참조하세요. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_size = 100M # # * SSL/TLS # # 설명서는 # https://mariadb.com/kb/en/securing-connections-for-client-and-server/를 읽어보세요 #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem #require-secure-transport = on # # * 문자 집합 # # MySQL/MariaDB 기본값은 Latin1이지만 Debian에서는 전체 # utf8 4바이트 문자 집합을 기본값으로 사용합니다. client.cnf의 character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci도 참조하세요. # # * InnoDB # # InnoDB는 기본적으로 /var/lib/mysql/에 10MB 데이터 파일을 사용하여 활성화됩니다. # InnoDB 관련 옵션은 매뉴얼을 참조하세요. 옵션이 매우 많습니다! # 가장 중요한 것은 버퍼 사용을 위해 InnoDB에 시스템 RAM의 80 %를 제공하는 것입니다. # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8G innodb_buffer_pool_size = 48G innodb_buffer_pool_instances = 16 innodb_log_file_size = 12G innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 2000 innodb_read_io_threads = 8 innodb_write_io_threads = 8 # 이것은 임베디드 서버에만 해당됩니다. [임베디드] # 이 그룹은 MySQL이 아닌 MariaDB 서버에서만 읽습니다. # MySQL과 MariaDB에 동일한 .cnf 파일을 사용하는 경우 # MariaDB 전용 옵션을 여기에 넣을 수 있습니다. [mariadb] # 이 그룹은 MariaDB-10.11 서버에서만 읽을 수 있습니다. # 다른 버전의 MariaDB에 동일한 .cnf 파일을 사용하는 경우 # 이전 서버에서 인식하지 못하는 옵션에 이 그룹을 사용합니다. [mariadb-10.11]
