issue1. mariaDB 속도 저하

1. 발단

• Maria DB에서 데이터 조회 속도가 느려짐을 감지
• Jennifer에서 데이터 조회 속도로 인해 서비스 실행 시간문제 발생 (java.net.SocketTimeoutException : Read timed out)
• 서비스 평균 처리 시간 1~2초 → 서비스 평균 처리 시간 200초 초과

2. 장애 회고

2-1. 원인 분석

• 동일한 쿼리 문장도 사용자가 많은 시간에는 속도가 느리고, 사용자가 없는 시간에는 속도가 느리지 않는 현상 발견
• 테이블 사이즈 및 인덱스 용량이 크지만 여유 공간은 있음.
• 단순한 DML 문장도 시간대에 따라 처리 속도가 다름(이 경우 캐시 제거)

2-2. 장애 분석

1) 테이블 사이즈, 인덱스 용량 확인

#-- 데이터베이스에 해당하는 모든 정보 확인
show table status from 테이블스키마명;

#-- 테이블, 인덱스 사이즈 확인
SELECT round(sum(data_length + index_length)/1024/1024/1024, 2) "USED_GB",
	   round(sum(index_length)/1024/1024/1024, 2) "INDEX_GB"
       round(sum(data_free)/1024/1024/1024, 2) "FREE_GB"
  FROM information_schema.TABLES
 WHERE table_schema = '테이블스키마명'
 
 #--인덱스를 사용한 테이블에 대한 전체 인덱스의 백분율
 select database_name,
 		   table_name,
           index_name,
           round((stat_value*@@innodb_page_size)/1024/1024, 2) SizeMB,
           round((100/(select INDEX_LENGTH
           				 from information_schema.TABLES) t
                        where t.TABLE_NAME==iis.table_name
                          and t.TABLE_SCHEM==iis.database_name))
                              *(stat_value*@@innodb_page_size)),2) 'Percentage'
  from mysql.innodb_index_stats iis
 where stat_name='size'
   and table_schema='테이블스키마명';

• 분석 : 테이블 및 인덱스 여유 용량 있음. 용량 문제는 아니라고 판단했지만 필요 없는 테이블을 우선적으로 삭제
• 결과 : 필요 없는 테이블 삭제 후, 테이블 용량 줄어듬

2) 세션 확인

#-- query
show status
where variable_name in 
('max_used_connections',
 'aborted_clients',
 'aborted_connects',
 'threads_connected',
 'connections'
);

• 분석 : max_used_connections 수가 늘어난 것을 확인.

  Variable Name	Comment
  Max_used_connections	동시 최대 접속자 수
  Aborted_clients	연결된 상태에서 강제로 연결 해제 된 연결 수
  Aborted_connects	연결 과정 중 fail된 연결 수
  Threads_connected	현재 오픈된 연결 수
  connections	연결 시도된 총 수

• 결과 : 앞에서 처리하지 못한 트랜잭션이 늘어나면서 max_used_connection 수가 늘어난 것으로 추정. DB lock 테이블이 있는지 확인

3) DB Lock 테이블 확인

#-- 현재 Lock 걸려 대기중인 정보 확인
select *
  from information_schema.INNODB_LOCK_WAITS;
  
#--Lock 걸고 있는 프로세스 정보
select *
  from information_schema.INNODB_TRX;

• 결과 : 락 걸린 프로세스 없음.

4) 쿼리 실행계획 확인

• 분석 :
  1) 데이터 로드하는 쿼리문에 explain을 앞에 붙여서 실행계획 확인
  2) 사용자가 많을 때의 실행계획과 사용자가 적을 때의 실행계획 동일한지 비교

• 결과 :
  1) 인덱스를 통해 로드하는 것을 확인(쿼리 문제X)
  2) 시간대에 따른 실행계획 동일한 것을 확인

5) 버퍼 풀 및 버퍼 사이즈 확인

show status 
like '%innodb_buffer_pool%';

• 결과 : 버퍼 용량 증설했지만 속도 이슈 변화 X

6) 메모리 사용률 확인

• 분석 : free 명령어를 사용한 실질 메모리 사용률 계산
• 결과 : 메모리 사용률 48%
  

7) CPU 사용률 확인

• 분석 : 사용자가 많지 않은 시간에도 높은 cpu 사용률을 보임
• 결과 : 사용하지 않는 프로세스 kill, db 서버 껐다 킴.

3. 마무리

• 결론 : db 서버 및 os 자체를 OFF ON

• reference : https://clarkshim.tistory.com/177 참고