MySQL 구성 및 튜닝

소개

이 랩에서는 MySQL 서버 구성 및 성능 튜닝의 기본 사항을 배우게 됩니다. 현재 서버 설정을 검사하는 것으로 시작하여, 주요 성능 매개변수인 innodb_buffer_pool_size를 수정하여 변경 사항이 어떻게 적용되는지 확인합니다.

이 랩에서는 MySQL 구성 파일을 편집하고, 변경 사항을 적용하기 위해 서버를 다시 시작하고, 새로운 설정이 활성화되었는지 확인하는 과정을 안내합니다. 마지막으로 MySQL 의 내장 프로파일러를 사용하여 쿼리 성능을 분석하는 기본적인 방법을 배우게 됩니다. 이를 통해 다양한 워크로드에 맞게 데이터베이스를 최적화하는 기초를 다질 수 있습니다.

현재 MySQL 구성 확인

변경하기 전에 MySQL 서버의 현재 구성을 이해하는 것이 중요합니다. 이 단계에서는 MySQL 에 연결하여 중요한 성능 변수인 innodb_buffer_pool_size의 값을 검사합니다.

먼저 데스크톱에서 터미널을 엽니다.

root 사용자로 MySQL 서버에 연결합니다. 이 랩 환경에서는 비밀번호 없이 연결하기 위해 sudo를 사용할 수 있습니다.

sudo mysql -u root

연결되면 MySQL 프롬프트 (mysql>) 가 표시됩니다.

시스템 변수는 MySQL 서버의 동작을 제어합니다. innodb_buffer_pool_size 변수는 InnoDB 테이블의 데이터 및 인덱스를 캐시하기 위해 할당된 메모리 양을 결정합니다. 적절한 크기의 버퍼 풀은 좋은 성능에 필수적입니다.

LIKE 절과 함께 SHOW VARIABLES 명령을 사용하여 이 변수의 현재 값을 찾습니다.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

바이트 단위의 기본값을 보여주는 다음과 유사한 출력이 표시됩니다. 다음 단계에서 이 값을 변경할 것이므로 이 값을 기록해 두십시오.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+--------------------------+-----------+
1 row in set (0.01 sec)

이제 현재 구성을 확인했으므로 MySQL 셸을 종료할 수 있습니다.

exit

MySQL 구성 파일 수정

MySQL 설정은 현재 세션에 대해 임시로 변경하거나 구성 파일에서 영구적으로 변경할 수 있습니다. 서버 재시작 후에도 변경 사항을 유지하려면 구성 파일을 편집해야 합니다. 이 단계에서는 /etc/mysql/my.cnf를 수정하여 innodb_buffer_pool_size를 늘립니다.

sudo 권한으로 nano 텍스트 편집기를 사용하여 MySQL 구성 파일을 엽니다.

sudo nano /etc/mysql/my.cnf

아래로 스크롤하여 [mysqld] 섹션을 찾습니다. 이 섹션에는 MySQL 서버 데몬에 특정한 설정이 포함되어 있습니다. 버퍼 풀 크기를 256 메가바이트로 설정하려면 [mysqld] 헤더 아래에 다음 줄을 추가합니다.

innodb_buffer_pool_size=256M

이제 [mysqld] 섹션은 다음과 유사하게 보일 것입니다.

[mysqld]
innodb_strict_mode=OFF
init_connect='SET NAMES utf8'
character-set-server = utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size=256M

이제 파일을 저장하고 nano를 종료합니다. Ctrl+X를 누르고 변경 사항을 확인하기 위해 Y를 입력한 다음 파일을 쓰기 위해 Enter를 누릅니다.

구성 파일의 변경 사항은 MySQL 서버가 다시 시작된 후에만 적용됩니다. service 명령을 사용하여 다시 시작합니다.

sudo service mysql restart

이제 구성을 영구적으로 업데이트했습니다. 다음 단계에서는 변경 사항이 활성화되었는지 확인할 것입니다.

구성 변경 확인

구성 파일을 수정하고 서버를 다시 시작한 후 새 설정이 올바르게 적용되었는지 확인해야 합니다. 이 단계에서는 MySQL 에 다시 연결하여 innodb_buffer_pool_size 변수를 다시 확인합니다.

MySQL 서버에 연결합니다.

sudo mysql -u root

이제 SHOW VARIABLES 명령을 다시 실행하여 새 값을 확인합니다.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

출력에는 이제 바이트 단위의 새 값이 표시되어야 합니다. MySQL 은 256M(256 메가바이트) 을 자동으로 268435456 바이트로 변환합니다.

+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+--------------------------+-----------+
1 row in set (0.00 sec)

이 값을 1 단계에서 기록한 값과 비교하면 구성 변경이 성공했으며 현재 활성화되었음을 확인할 수 있습니다.

이제 MySQL 셸을 종료할 수 있습니다.

exit

쿼리 성능 분석

쿼리 성능 향상을 위해 서버 변수를 튜닝합니다. 심층 분석은 복잡하지만, MySQL 의 내장 쿼리 프로파일러를 사용하여 쿼리 실행 시간에 대한 기본적인 측정을 할 수 있습니다. 이 단계에서는 테스트 테이블을 생성하고, 데이터를 삽입하고, 간단한 쿼리를 분석합니다.

먼저 MySQL 서버에 연결합니다.

sudo mysql -u root

testdb라는 새 데이터베이스를 생성하고 해당 데이터베이스로 전환합니다.

CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;

다음으로 샘플 데이터를 저장할 employees라는 테이블을 생성합니다.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary INT
);

employees 테이블에 몇 개의 레코드를 삽입합니다.

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Sales', 60000),
('Bob', 'Engineering', 80000),
('Charlie', 'Sales', 65000),
('David', 'Marketing', 55000);

이제 세션에 대한 쿼리 프로파일러를 활성화합니다. 이렇게 하면 이후 쿼리에 대한 성능 데이터가 기록됩니다.

SET profiling = 1;

분석하려는 쿼리를 실행합니다. 예를 들어, 'Sales' 부서의 모든 직원을 찾아보겠습니다.

SELECT * FROM employees WHERE department = 'Sales';

성능 결과를 보려면 SHOW PROFILES 명령을 사용합니다. 이 명령은 프로파일링을 활성화한 이후 실행한 쿼리와 해당 실행 시간을 나열합니다.

SHOW PROFILES;

출력은 다음과 유사하게 표시되며, 각 쿼리의 실행 시간을 초 단위로 보여줍니다.

+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00038500 | SELECT * FROM employees WHERE department = 'Sales'    |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

이 Duration 열은 쿼리 성능의 기준선을 제공합니다. 실제 시나리오에서는 이 도구를 사용하여 훨씬 더 큰 데이터 세트에서 구성 변경 전후의 쿼리 속도를 비교할 수 있습니다.

프로파일러를 사용하여 쿼리를 성공적으로 분석했습니다. 이제 MySQL 셸을 종료할 수 있습니다.

exit;

요약

이 실습에서는 MySQL 서버를 구성하고 튜닝하는 기본적인 과정을 배웠습니다. innodb_buffer_pool_size와 같은 시스템 변수를 검사하여 현재 서버 구성을 확인하는 방법을 실습했습니다.

MySQL 구성 파일 (my.cnf) 을 수정하여 영구적인 변경을 적용하고, 서버를 다시 시작하여 적용하며, 새 설정이 활성화되었는지 확인하는 실습 경험을 쌓았습니다. 마지막으로 MySQL 의 내장 쿼리 프로파일러를 사용하여 쿼리 실행 시간을 측정하는 기본적인 성능 분석 기법을 소개받았습니다.

이러한 기본적인 기술은 건강하고 성능이 뛰어난 MySQL 데이터베이스를 유지 관리하는 모든 개발자 또는 관리자에게 필수적입니다.