카테고리 없음
[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
위 결과로 추출된 결과