MySQL 데이터베이스를 사용하면서 공통적으로 대량의 데이터를 처리할 때 발생하는 입출력 속도에 문제가 발생된다. JOIN이나 GROUP BY 같은 복잡한 쿼리를 실행할 때 생성되는 임시 파일을 RAM이나 디스크에 운영하는 임시 파일 시스템 MySQL tmpfs를 이용해 성능 최적화를 해볼 수 있다.
[목차]
1. MySQL tmpfs의 이해
tmpfs는 주기억장치인 메모리 (RAM)에서 동작하는 임시 파일 시스템이다. 메모리는 데이터를 저장하고 읽어오는 속도가 디스크에 비해 월등히 빠르기 때문에, 이를 활용한 tmpfs를 운영한다면 매우 빠른 입출력 성능을 낼 수 있다. 특히 MySQL에서 대용량의 임시 테이블을 처리할 때 tmpfs를 활용하면 상당한 효과가 나타난다.
그중 한 가지 개선 방법 중 하나로 RAM에서 운영되는 임시 파일 시스템인 tmpfs 활용 방법이다. 다만 메모리가 충분치 않다면 tmpfs 마운트는 오히려 성능 저하를 발생시킨다.
반대의 경우도 있다. 시스템 메모리 리소스가 부족한 상황으로 swap 메모리 사용율이 증가하는 상황에서는 메모리에 임시 파일을 쓰기보다는 tmpfs를 이용해 물리적인 디스크에 임시 테이블을 생성하여 메모리 사용율을 효율적으로 낮추는 방법도 있다. 이경우의 문제는 Disk I/O 성능이 떨어질 수 있으므로, 디스크 성능에 따라 설정할지 여부는 고려되어야 한다.
2. mysql tmpfs 설정 방법
메모리에 임시파일 쓰는 방법
/etc/fstab 파일에 tmpfs 추가
tmpfs /mnt/mysql_tmp tmpfs rw,size=1G 0 0
my.cnf 파일에 tmp 테이블 저장 경로 설정
[mysqld]
tmpdir=/mnt/mysql_tmp
설정 후에 는 mysql을 재시작
# service mysql restart
디스크에 임시파일 쓰는 방법
MySQL의 임시 파일이 저장될 디렉터리를 만든다.
# mkdir /home/mysql_tmp
tmpfs를 위해 생성한 디렉터리에 마운트 한다. tmpfs의 크기를 지정할 수 있다. 생성되는 tmp 테이블에 크기를 고려하여 생성해야 하며 마운트된 용량이 임시 테이블 생성 용량 보다 부족할 경우 mysql 프로세스가 무한 대기 상태로 빠지거나 테이블이 손상될 수도 있다.
# mount -t tmpfs -o size=1G tmpfs /home/mysql_tmp
# df -h
my.cnf 또는 my.ini 에서 tmpdir 설정을 새로 마운트 한 디렉터리로 변경한다.
[mysqld]
tmpdir=/home/mysql_tmp
mysql 재시작한다.
# service mysql restart
위 설정은 재부팅 시 사라지기 때문에 재부팅될 때마다 tmpfs으로 마운트 하게 하려면
/etc/fstab 파일에 아래와 같이 추가한다.
tmpfs /home/mysql_tmp tmpfs size=1G,mode=0755 0 0
3. tmpfs 적용 후 프로파일링 성능 측정 방법
예를 들어, users와 orders라는 두 개의 테이블을 JOIN 하는 복잡한 쿼리를 실행한다고 가정하였다.
SET profiling = 1;SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.name;SHOW PROFILES;
디스크 기반 임시 테이블 생성 횟수를 확인하려면 아래와 쿼리를 실행한다.
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
설정 전후 이 값이 크게 감소했다면, tmpfs의 활용이 성공적이라고 볼 수 있다.
4. tmpfs 설정 시 주의할 점
tmpfs는 데이터를 메모리에 저장하므로, 시스템이 재부팅되거나 정전 등으로 서버의 전원이 꺼지게 되면 메모리에 저장된 모든 데이터가 사라지게 된다. 따라서 tmpfs를 사용할 때는 이러한 데이터 손실 위험성을 항상 고려해야 한다. 특히 중요한 데이터를 tmpfs에 저장할 경우, 정기적인 백업을 고려해야 한다. 백업은 데이터 손실을 예방하는 가장 기본적인 방법이며, 문제 발생 시 데이터 복구 가능성을 높여준다. 또한 여건이 된다면 전원이 꺼졌을 때 데이터를 복구할 수 있도록 UPS(Uninterruptible Power Supply) 등의 장치를 사용하는 것도 고려해 볼 수 있겠다.
또한, tmpfs는 메모리로 설정할 경우 시스템의 메모리를 사용하기 때문에, 메모리 사용량을 적절하게 관리해야 한다. 메모리가 부족해지면 swap space 사용하게 되어, 성능 저하의 원인이 될 수 있다. 따라서, tmpfs의 크기를 적절히 설정하고, 메모리 사용량을 주기적으로 모니터링해야 디스크에 쓸 경우 디스크 I/O 성능과 수치를 고려하여 성능 저하를 모니터링해야 한다.
tmpfs는 백업 정책 등이 잘 고려되어야 하며 상황별로 써야 하는 방법을 판단하는 게 중요한 포인트다 적절하게 활용되면 시스템 성능향상에 아주 큰 효과가 있다.