|
mysql cpu 점유율이 높거나 db 연결이 안 좋을 때
|
mysql 이 cpu를 꾸준히 점유하고 있으며, 서버의 부하도 많이 발생한다.
물론 log 를 확인하는 방법이 가장 기본적으로 해야할 일이다. 그리고 혹시 slow query 가
얼마나 쌓이는지 확인해 보는것도 좋다.
그리고 mytop 이나 mysqlreport 등의 실시간 모니터링 프로그램을 이용할 수도 있다.
* slow query 에 관해 간단히 알아보면 다음과 같다.
: slow query 란 DBMS 가 client로부터 요청받은 query를 수행할때 일정시간 이상 수행
되지 못한 query를
칭하는 것으로 slow query 가 많다면 당연히 성능저하가 발생된다.
mysql 을 사용할때 mysqld 가 cpu를 유난히 많이 점유한다거나 DB의 접속이 원활하지
않다면
slow query를 이용하여 원인 분석에 도움이 될 수도 있다.
log 를 쌓이게 하기 위해 mysqld 실행시에 다음 옵션을 함께 추가하거나, my.cnf 파일을
직접 수정하여
mysqld 를 실행할 수도 있다.
1) mysqld 작동시 slow query 옵션 추가
#]/usr/local/mysql/bin/mysqd_safe --log-slowqueries=/
usr/local/mysq/data/mysql_slow.log &.....
2) my.cnf 수정
#]vi /etc/my.cnf
log_slow_queries=/usr/local/mysql/data/mysql_slow.log => 로그 저장 경로
long_query_time=1 => 쿼리가 2초이상 지속될때 로그에 기록
* cache size 수정하기
set-variable = query_cache_size = 100M : 100M 로 cache size 수정
cache 수정후 mysql prompt 상에서 다음과 같이 확인 가능
mysql> use mysql;
Database changed
mysql> show global status like '%Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 13 |
| Qcache_free_memory | 33384736 |
| Qcache_hits | 283 |
| Qcache_inserts | 156 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 37 |
| Qcache_total_blocks | 106 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> show global variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
slow query가 쌓이도록 수정후 정상적으로 적용되었는지 확인하기 위해 mysql
command prompt 에서
다음과 같이 실행한다.
#mysql>show global variables WHERE Variable_name LIKE ‘l%’;
+———————————+—————————+
| Variable_name | Value |
+———————————+—————————+
| language | /usr/share/mysql/korean
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 3 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+———————————+—————————+
이제 쌓이는 log를 확인하여 어디에서 문제가 발생되는지 확인하고, 이를 수정해야 할 것
이다.
/usr/local/mysql/data/mysql-bin.00000 슬로우쿼리를 slowquery.log 에 덤프하여 저장
ex)/usr/local/mysql/bin/mysqldumpslow -v /usr/local/mysql/data/mysql-bin.00000
> slowquery.log
* 참고 mysql 명령어
extended-status MySQL : 데이터베이스의 현재 상황을 보여준다.
flust-hosts : MySQL에 캐시된 모든 포스트를 초기화한다.
flust-logs : MySQL의 로그 파일을 새로 작성하며 초기화한다.
flust-status : MySQL의 상태정보를 초기화한다.
flust-tables : MySQL에 캐싱된테이블 정보를 초기화한다.
flust-thread : 쓰레드 캐시에 저장된 쓰레드를 초기화한다.
flust-privileges : 권한정보 테이블을 다시 읽는다.
kill id : 특정 MySQL 프로세스를 죽인다.
Processlist : 현재 MySQL 프로세스 목록은 본다.
Refresh : 현재 캐시되어 있는 모든 테이블을 초기화하고 log 파일은 새로 만든다.
Variables 설정 가능한 모든 변수를 보여줍니다.
SHOW VARIABLES
MySQL은 설정 가능한 값들을 엄청나게 많이 가지고 있으며 SHOW VARIABLE 명령을
통해 현재 설정되어 있는 모든 값을 볼 수 있다. <화면 1>은 SHOW VARIABLES로 통해
살펴본 설정이다.
SHOW VARIABLES로 볼 경우 총 207개 정도의 변수가 표시된다. 오히려 너무 많아서 원
하는 값을 찾기가 힘들 정도이다. 그래서 SHOW VARIABLES 명령 뒤에 LIKE ‘%키워드%
’를 사용하면 원하는 값만을 볼 수 있다.
SHOW STATUS
MySQL은 내부적으로 동작 상황에 대한 실시간 통계 정보를 가지고 있다. SHOW
STATUS는 이러한 통계 정보를 보기 위한 명령이다. 모니터링할 때 가장 기본이 되는 것
이 바로 앞에서 설명한 SHOW VARIABLES의 정보와 SHOW STATUS의 정보이다. 웹
기반의 모니터링 툴을 비롯한 각종 모니터링 툴들이 바로 이 두 명령어를 통해 나온 정보
를 조합해 사용하는 것이다. SHOW STATUS도 SHOW VARIABLES와 마찬가지로 LIKE
‘%키워드%’ 사용해 원하는 값만을 볼 수 있다.
SHOW PROCESSLIST
현재 동작하고 있는 MySQL 데이터베이스 서버의 동작중인 모든 쓰레드와 유저 커넥션 정
보를 보기 위한 명령어이다. 이를 통해 얻어진 정보로 시스템 자원을 지나치게 많이 사용
하거나 잘못된 수행을 하고 있는 프로세스를 죽일 수 있다.
SHOW TABLE/TABLE STATUS/INDEX/INNODB STATUS
SHOW TABLE 명령은 현재 데이터베이스에 존재하는 테이블에 대한 기본적인 정보를 보
여주며 SHOW TABLE STATUS는 각 테이블의 생성 일자, 테이블 크기, 인덱스 크기 등
구체적인 정보를 보여준다. 하지만 이 때 주의할 점이 하나 있는데 바로 SHOW TABLE
STATUS의 경우 테이블의 스토리지 엔진이 MyISAM인 경우에만 정확한 정보를 표시하
며 InnoDB의 경우에는 부정확한 정보를 보여준다는 것이다. InnoDB 스토리지 엔진으로
되어 있는 테이블은 SHOW INNODB STATUS로 구체적인 정보를 확인할 수 있으며
SHOW INDEX를 통해 테이블의 인덱스에 대한 각종 정보를 볼 수 있다
물론 log 를 확인하는 방법이 가장 기본적으로 해야할 일이다. 그리고 혹시 slow query 가
얼마나 쌓이는지 확인해 보는것도 좋다.
그리고 mytop 이나 mysqlreport 등의 실시간 모니터링 프로그램을 이용할 수도 있다.
* slow query 에 관해 간단히 알아보면 다음과 같다.
: slow query 란 DBMS 가 client로부터 요청받은 query를 수행할때 일정시간 이상 수행
되지 못한 query를
칭하는 것으로 slow query 가 많다면 당연히 성능저하가 발생된다.
mysql 을 사용할때 mysqld 가 cpu를 유난히 많이 점유한다거나 DB의 접속이 원활하지
않다면
slow query를 이용하여 원인 분석에 도움이 될 수도 있다.
log 를 쌓이게 하기 위해 mysqld 실행시에 다음 옵션을 함께 추가하거나, my.cnf 파일을
직접 수정하여
mysqld 를 실행할 수도 있다.
1) mysqld 작동시 slow query 옵션 추가
#]/usr/local/mysql/bin/mysqd_safe --log-slowqueries=/
usr/local/mysq/data/mysql_slow.log &.....
2) my.cnf 수정
#]vi /etc/my.cnf
log_slow_queries=/usr/local/mysql/data/mysql_slow.log => 로그 저장 경로
long_query_time=1 => 쿼리가 2초이상 지속될때 로그에 기록
* cache size 수정하기
set-variable = query_cache_size = 100M : 100M 로 cache size 수정
cache 수정후 mysql prompt 상에서 다음과 같이 확인 가능
mysql> use mysql;
Database changed
mysql> show global status like '%Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 13 |
| Qcache_free_memory | 33384736 |
| Qcache_hits | 283 |
| Qcache_inserts | 156 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 6 |
| Qcache_queries_in_cache | 37 |
| Qcache_total_blocks | 106 |
+-------------------------+----------+
8 rows in set (0.00 sec)
mysql> show global variables like '%query_cache%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 33554432 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
6 rows in set (0.00 sec)
slow query가 쌓이도록 수정후 정상적으로 적용되었는지 확인하기 위해 mysql
command prompt 에서
다음과 같이 실행한다.
#mysql>show global variables WHERE Variable_name LIKE ‘l%’;
+———————————+—————————+
| Variable_name | Value |
+———————————+—————————+
| language | /usr/share/mysql/korean
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| license | GPL |
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_slave_updates | OFF |
| log_slow_queries | ON |
| log_warnings | 1 |
| long_query_time | 3 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+———————————+—————————+
이제 쌓이는 log를 확인하여 어디에서 문제가 발생되는지 확인하고, 이를 수정해야 할 것
이다.
/usr/local/mysql/data/mysql-bin.00000 슬로우쿼리를 slowquery.log 에 덤프하여 저장
ex)/usr/local/mysql/bin/mysqldumpslow -v /usr/local/mysql/data/mysql-bin.00000
> slowquery.log
* 참고 mysql 명령어
extended-status MySQL : 데이터베이스의 현재 상황을 보여준다.
flust-hosts : MySQL에 캐시된 모든 포스트를 초기화한다.
flust-logs : MySQL의 로그 파일을 새로 작성하며 초기화한다.
flust-status : MySQL의 상태정보를 초기화한다.
flust-tables : MySQL에 캐싱된테이블 정보를 초기화한다.
flust-thread : 쓰레드 캐시에 저장된 쓰레드를 초기화한다.
flust-privileges : 권한정보 테이블을 다시 읽는다.
kill id : 특정 MySQL 프로세스를 죽인다.
Processlist : 현재 MySQL 프로세스 목록은 본다.
Refresh : 현재 캐시되어 있는 모든 테이블을 초기화하고 log 파일은 새로 만든다.
Variables 설정 가능한 모든 변수를 보여줍니다.
SHOW VARIABLES
MySQL은 설정 가능한 값들을 엄청나게 많이 가지고 있으며 SHOW VARIABLE 명령을
통해 현재 설정되어 있는 모든 값을 볼 수 있다. <화면 1>은 SHOW VARIABLES로 통해
살펴본 설정이다.
SHOW VARIABLES로 볼 경우 총 207개 정도의 변수가 표시된다. 오히려 너무 많아서 원
하는 값을 찾기가 힘들 정도이다. 그래서 SHOW VARIABLES 명령 뒤에 LIKE ‘%키워드%
’를 사용하면 원하는 값만을 볼 수 있다.
SHOW STATUS
MySQL은 내부적으로 동작 상황에 대한 실시간 통계 정보를 가지고 있다. SHOW
STATUS는 이러한 통계 정보를 보기 위한 명령이다. 모니터링할 때 가장 기본이 되는 것
이 바로 앞에서 설명한 SHOW VARIABLES의 정보와 SHOW STATUS의 정보이다. 웹
기반의 모니터링 툴을 비롯한 각종 모니터링 툴들이 바로 이 두 명령어를 통해 나온 정보
를 조합해 사용하는 것이다. SHOW STATUS도 SHOW VARIABLES와 마찬가지로 LIKE
‘%키워드%’ 사용해 원하는 값만을 볼 수 있다.
SHOW PROCESSLIST
현재 동작하고 있는 MySQL 데이터베이스 서버의 동작중인 모든 쓰레드와 유저 커넥션 정
보를 보기 위한 명령어이다. 이를 통해 얻어진 정보로 시스템 자원을 지나치게 많이 사용
하거나 잘못된 수행을 하고 있는 프로세스를 죽일 수 있다.
SHOW TABLE/TABLE STATUS/INDEX/INNODB STATUS
SHOW TABLE 명령은 현재 데이터베이스에 존재하는 테이블에 대한 기본적인 정보를 보
여주며 SHOW TABLE STATUS는 각 테이블의 생성 일자, 테이블 크기, 인덱스 크기 등
구체적인 정보를 보여준다. 하지만 이 때 주의할 점이 하나 있는데 바로 SHOW TABLE
STATUS의 경우 테이블의 스토리지 엔진이 MyISAM인 경우에만 정확한 정보를 표시하
며 InnoDB의 경우에는 부정확한 정보를 보여준다는 것이다. InnoDB 스토리지 엔진으로
되어 있는 테이블은 SHOW INNODB STATUS로 구체적인 정보를 확인할 수 있으며
SHOW INDEX를 통해 테이블의 인덱스에 대한 각종 정보를 볼 수 있다
