320x100
320x100
쿼리 성능에 영향을 미치는 요소
- 운영체제의 캐시
: MySQL 서버는 운영체제의 파일 시스템 관련 기능 (시스템 콜)을 이용해 데이터 파일을 읽어옴
: 일반적으로 대부분의 운영체제는 운영체제가 관리하는 별도의 캐시 영역에 보관하여 재요청 시 이를 읽음
: InnoDB 스토리지 엔진은 일반적으로 파일 시스템의 캐시나 버퍼를 거치지 않는 Direct I/O를 사용하므로 운영체제의 캐시가 큰 영향을 미치지 않으나,
: MyISAM 스토리지 엔진은 운영체제 캐시에 대한 의존도가 높아 캐시에 따라 성능 차이가 큼
: 따라서 정확히 테스트 하기 위해서는 MySQL 서버를 재시작하거나 캐시를 삭제해줘야 한다
- MySQL 서버의 버퍼 풀 (InnoDB 버퍼 풀과 MyISAM의 키 캐시)
: InnoDB 스토리지 엔진이 관리하는 캐시를 버퍼 풀이라고 하고,
: MyISAM 스토리지 엔진이 관리하는 캐시를 키 캐시하고 함
: 버퍼 풀은 인덱스 페이지는 물론이고 데이터 페이지까지 캐시하며, 쓰기 작업을 위한 버퍼링 작업까지 겸해서 처리
: 키 캐시의 경우 인덱스 데이터에 대해서만 캐싱하며, 주로 읽기를 위한 역할만 수행
: 키 캐시 및 버퍼풀을 초기화 하기 위해서는 MySQL 서버를 재시작 해야한다
- MySQL 쿼리 캐시
: 이전에 실행했던 SQL 문장과 그 결과를 임시로 저장하는 메모리 공간
: RESET QUERY CACHE 명령으로 쿼리 캐시를 제거 후 테스트를 수행함이 좋다
쿼리 성능 테스트
- SQL_NO_CACHE 힌트
: 쿼리 캐시를 남기지 않고 쿼리를 실행
- SQL_CALC_FOUND_ROWS를 LIMIT 0과 함께 사용
: 출력은 하나도 안되지만 끝까지 쿼리를 처리하기 때문에 전체적인 시간 확인 가능
- PAGER
-- 출력 결과를 /dev/null로 리다이렉트 해서 저장
mysql > PAGER /dev/null
mysql > SELECT .. FROM emp
// 테스트 완료 후 쿼리 결과를 화면에 출력되도록
mysql > NOPAGER: GUI SQL 도구를 사용하지 않을 경우에 사용
쿼리 프로파일링
mysql > SHOW VARIABLES LIKE 'profiling';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| profilling | OFF |
+-----------------+---------+
mysql > SET PROFILING=1;
mysql > SHOW VARIABLES LIKE 'profiling';
+-----------------+---------+
| Variable_name | Value |
+-----------------+---------+
| profilling | ON |
+-----------------+---------+: MySQL 5.1 이상에서만 지원
- 각 쿼리에 대한 프로파일 내용 확인
mysql > SELECT * FROM employees WHERE emp_no=10001;
mysql > SELECT COUNT(*) FROM employees WHERE emp_no BETWEEN 10001 AND 12000
GROUP BY first_name;
mysql > SHOW PROFILES;
+------------+-------------+-----------------------------------------------------------------------------------------+
| Query_ID | Duration | Query |
+------------+-------------+-----------------------------------------------------------------------------------------+
| 1 | 0.00026300 | SELECT * FROM employees WHERE emp_no=10001 |
| 2 | 0.00521400 | SELECT COUNT(*) FROM employees WHERE emp_no BETWEEN 10001 AND 12000 GROUP BY first_name |
+------------+-------------------------------------------------------------------------------------------------------+: 기본으로 최근에 실행한 15개의 쿼리에 대해서만 저장하는데, 그 이상 저장하려면 profiling_history_size를 조정하면 된다 (최대 100)
- 각 쿼리별 조회
mysql > SHOW PROFILE FOR QUERY 1
+-----------------------------------+------------+
| Status | Duration |
+-----------------------------------+------------+
| starting | 0.000028 |
| checking query cache for query | 0.000042 |
| Opening tables | 0.000024 |
| System lock | 0.000004 |
| Table lock | 0.000027 |
| init | 0.000025 |
| optimizing | 0.000010 |
| statistics | 0.000042 |
| preparing | 0.000017 |
| executing | 0.000003 |
| Sending data | 0.000015 |
| end | 0.000003 |
| query end | 0.000002 |
| freeing items | 0.000013 |
| storing result in query cache | 0.000005 |
| logging slow query | 0.000001 |
| cleaning up | 0.000002 |
+-----------------------------------+------------+
mysql > SHOW PROFILE;
+-----------------------------------+------------+
| Status | Duration |
+-----------------------------------+------------+
| starting | 0.000028 |
| Opening tables | 0.000024 |
...
+-----------------------------------+------------+
- 메모리 또는 CPU, 디스크 관련하여 확인
mysql > SHOW PROFILE CPU FOR QUERY 2;
+---------------------+------------+------------+-------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------------------+------------+-------------+
| starting | 0.000063 | 0.000000 | 0.000000 |
| Opening tables | 0.000012 | 0.000000 | 0.000000 |
| System lock | 0.000004 | 0.000000 | 0.000000 |
| Table lock | 0.000009 | 0.000000 | 0.000000 |
| init | 0.000018 | 0.000000 | 0.000000 |
| optimizing | 0.000000 | 0.000000 | 0.000000 |
| statistics | 0.000010 | 0.000000 | 0.000000 |
| preparing | 0.000009 | 0.000000 | 0.000000 |
| executing | 0.000040 | 0.000000 | 0.000000 |
| Sending data | 0.700681 | 0.696894 | 0.003999 |
| end | 0.000016 | 0.000000 | 0.000000 |
| removing tmp table | 0.000008 | 0.000000 | 0.000000 |
| end | 0.000003 | 0.000000 | 0.000000 |
| query end | 0.000003 | 0.000000 | 0.000000 |
| freeing items | 0.000432 | 0.000000 | 0.000000 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 |
| cleaning up | 0.000002 | 0.000000 | 0.000000 |
+----------------------------------+------------+-------------+
mysql > SHOW PROFILE SOURCE FOR QUERY 2;
+---------------------+------------+-------------------+----------------+---------------+
| Status | Duration | Source_function | Source_file | Source_line |
+----------------------------------+-------------------+----------------+---------------+
| starting | 0.000063 | NULL | NULL | NULL |
| Opening tables | 0.000012 | unknown function | sql_base.cc | 4515 |
| System lock | 0.000004 | unknown function | lock.cc | 258 |
| Table lock | 0.000009 | unknown function | lock.cc | 269 |
| init | 0.000018 | unknown function | sql_select.cc | 2519 |
| optimizing | 0.000000 | unknown function | sql_select.cc | 828 |
| statistics | 0.000010 | unknown function | sql_select.cc | 1019 |
| preparing | 0.000009 | unknown function | sql_select.cc | 1041 |
| executing | 0.000040 | unknown function | sql_select.cc | 1775 |
| Sending data | 0.700681 | unknown function | sql_select.cc | 2329 |
| end | 0.000016 | unknown function | sql_select.cc | 2565 |
| removing tmp table | 0.000008 | unknown function | sql_select.cc | 10881 |
| end | 0.000003 | unknown function | sql_select.cc | 10906 |
| query end | 0.000003 | unknown function | sql.parse.cc | 5055 |
| freeing items | 0.000432 | unknown function | sql.parse.cc | 6086 |
| logging slow query | 0.000003 | unknown function | sql.parse.cc | 1709 |
| cleaning up | 0.000002 | unknown function | sql.parse.cc | 1677 |
+----------------------------------+-------------------+----------------+---------------+
Reference
쿼리 성능 테스트
작성된 쿼리가 얼마나 효율적이고 더 개선할 부분이 있는지 확인하려면 쿼리를 직접 실행해보는 방법이 가장 일반적일 것이다. 하지만 쿼리를 직접 실행해 보면서 눈으로 성능을 체크할 때는
velog.io
300x250
728x90
'Database > MySQL' 카테고리의 다른 글
| MySQL의 최대 쿼리 문장 길이 (0) | 2023.11.07 |
|---|---|
| MySQL Too many connections 오류 해결 방법 (0) | 2023.11.07 |
| 데이터 모델링 개념 + ERD 및 식별자 관계 총 정리 (0) | 2023.10.10 |
| MySQL JSON_ARRAYAGG와 JSON_ARRAY의 차이점 (1) | 2023.10.10 |
| MySQL 서브 쿼리에 대해서 총 정리 (1) | 2023.10.10 |