카테고리 없음

[240328/데이터 추출]slow.log 로그데이터 정제

onlyun 2024. 3. 28. 04:20

 
 
slow.log 파일 내 데이터를
DB에 insert 하고,
다시 결과항목별로 파싱하여 데이터 추출
 
| db
- 텍스트 insert 하는 테이블 생성

CREATE TABLE temp (
	seq INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	cont VARCHAR(6000) NULL
);

- 결과 테이블 생성

CREATE TABLE `tmp_result` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`rDbTb` VARCHAR(255) NOT NULL DEFAULT '0' COMMENT '쿼리 테이블' COLLATE 'utf8mb3_general_ci',
	`rTime` DATETIME NULL DEFAULT NULL COMMENT '쿼리 로깅된 시간',
	`rUser` VARCHAR(255) NULL DEFAULT NULL COMMENT '쿼리를 실행한 사용자와 호스트' COLLATE 'utf8mb3_general_ci',
	`rThread_id` INT(11) NULL DEFAULT NULL COMMENT '쿼리를 실행하는 스레드의 ID',
	`rSchema` VARCHAR(255) NULL DEFAULT NULL COMMENT '쿼리가 실행되는 데이터베이스 스키마' COLLATE 'utf8mb3_general_ci',
	`rQCHit` VARCHAR(255) NULL DEFAULT NULL COMMENT '쿼리 캐시 히트 여부(no, 캐시에서 해당 쿼리가 찾아지지 않았음)' COLLATE 'utf8mb3_general_ci',
	`queryTime` FLOAT NULL DEFAULT NULL COMMENT '쿼리 실행에 소요된 시간(sec)',
	`toMin` FLOAT NULL DEFAULT NULL COMMENT 'convert to min',
	`toHour` FLOAT NULL DEFAULT NULL COMMENT 'convert to hour',
	`lockTime` FLOAT NULL DEFAULT NULL COMMENT '쿼리 실행 동안 사용된 락(lock) 시간',
	`rowSent` INT(11) NULL DEFAULT NULL COMMENT '쿼리에 의해 반환된 행의 수',
	`rowExamined` BIGINT(20) NULL DEFAULT NULL COMMENT '쿼리가 조사한 행의 수',
	`rowAffected` INT(11) NULL DEFAULT NULL COMMENT '쿼리가 영향을 미친 행의 수',
	`bytesSent` BIGINT(20) NULL DEFAULT NULL COMMENT '쿼리에 의해 반환된 바이트 수',
	`rTimestamp` BIGINT(20) NULL DEFAULT NULL,
	`rQuery` VARCHAR(2000) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
	`originalTxt` VARCHAR(6000) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=141
;

 
 
| log 파일 정제(by 에디터플러스)
- #Time을 기준으로 "줄바꿈" 넣어줌
- #Time 부터 ~ 쿼리 까지 한덩어리로 database insert 하는 쿼리문 생성

-- 에디더플러스 [ctrl] + [H]
-- 정규식 체크
변경전 -> #Time:
변경후 -> \n#Time:

-- insert 쿼리문 만들기
변경전 -> #Time:
변경후 -> "); \n INSERT INTO temp (cont) value("

-- 마무리 작업
    -- 맨 처음 "); 삭제
    -- 맨 마지막 "); 추가

 
 
| 데이터 파싱
데이터를 항목별 파싱, 결과 테이블 INSERT

INSERT INTO tmp_result 
SELECT
	ROW_NUMBER() OVER () AS num,
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(LOWER(s.cont),'from',-1),'where',1),'\n',1),
	TRIM(STR_TO_DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Time:',-1), '# User@Host:', 1), '%y%m%d %H:%i:%s')) AS res1,
	TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# User@Host:',-1), '# Thread_id:', 1)) AS res2,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Thread_id:',-1), 'Schema:', 1)) AS INT) AS res3,
	TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'Schema:',-1), ' QC_hit:', 1)) AS res4,
	TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'QC_hit:',-1), '# Query_time:', 1)) AS res5,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Query_time:',-1), 'Lock_time:', 1)) AS FLOAT) AS res6,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Query_time:',-1), 'Lock_time:', 1)) AS FLOAT)/60 AS tomin,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Query_time:',-1), 'Lock_time:', 1)) AS FLOAT)/3600 AS tohour,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'Lock_time:',-1), 'Rows_sent:', 1)) AS DOUBLE) AS res7,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'Rows_sent:',-1), 'Rows_examined:', 1)) AS INT) AS res8,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'Rows_examined:',-1), '# Rows_affected:', 1)) AS INT) AS res9,
	CAST(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, '# Rows_affected:',-1), 'Bytes_sent:', 1)) AS INT) AS res10,
	CAST(TRIM(REPLACE(REGEXP_SUBSTR(s.cont,'Bytes_sent:(\\s*)[0-9$]+'),'Bytes_sent:','')) AS INT) AS res11,
	CAST(TRIM(REPLACE(REGEXP_SUBSTR(s.cont,'SET timestamp=(\\s*)[0-9$]+'),'SET timestamp=','')) AS INT) AS res12,
	TRIM(SUBSTRING_INDEX(REGEXP_REPLACE(s.cont, 'SET timestamp=(\\s*)[0-9$]+;', '!SPLIT!'),'!SPLIT!',-1)) AS tmpQuery,
--	REGEXP_REPLACE(SUBSTRING_INDEX(s.cont, 'Bytes_sent:',-1),'^(\\s*)[0-9$]+','') AS res12,
	s.cont AS  originaltxt
--	SUBSTRING_INDEX(SUBSTRING_INDEX(s.cont, 'Bytes_sent:',-1), '# Thread_id:', 1) AS res11
FROM temp s

 
 
위 결과로 추출된 결과