'RDB'에 해당되는 글 62건

2020.04.13 [튜닝] 니 형이 뭐야?
2020.04.11 slave online hot backup 구축
2020.04.08 [튜닝] mysql function 성능 이슈
2020.04.08 mysql 컬럼 타입 datetime vs timestamp 차이
2020.04.02 [error] mysqldump: Got errno 28 on write
2020.03.31 SSD 파티션 튜닝 2
2020.03.26 SSD 파티션 튜닝 1
2020.03.26 [에러] mysql DB disk 폴더 이동후 재시작

[튜닝] 니 형이 뭐야?

RDB/mysql 2020. 4. 13. 02:49

사업부서의 요청으로 OLAP 관련 쿼리를 짜던 중에 뜻하지 않던 슬로우쿼리를 만났다.

select 절은 복잡했지만 from 이하절은 테이블 2개의 양쪽에 pk를 join해서 값을 가져오는 단순한 쿼리였다.

limit 200으로 끊어서 1차 데이터 이상없음을 검증하고

전체 쿼리를 수행하려는데 근데 이게 느려도 너무 느렸다...

into outfile로 수행해도 느렸다.. 뭔가 문제가 있어보였지만 query 상 문제는 아닌 것으로 보였다.

//나머지 제거하고 문제되는 부분만 본다.
select
T1.*
from TMP_LOAD_20200313_01 T1
LEFT OUTER JOIN TMP_LOAD_20200313_02 T2
ON T1.SEQ_NO = T2.SEQ_NO;

1) plan 확인

뭐가 문제일까 일단 쿼리를 단순화 시켜 plan을 떠봤다.

id	select type	table	type	possible_key	key	key len	ref	rows	extra
1	SIMPLE	T1	ALL					559741
1	SIMPLE	T2	ALL	PRIMARY				66728	Range checked for each record (index map: 0x1)

흠..일단 pk인데 T2 절에 인덱스스캔이 안된 것이 이상했고

Range checked for each record (index map: 0x1) 이게 뭔지 궁금해서 찾아보았다.

# range checked for each record (index map:N)

Mysql은 사용하기에 좋은 인덱스를 찾지 못했으나, 이전 테이블에서 컬럼 값을 찾고 난 후에는 사용할 수도 있을 법한 인덱스는 알아냈다. 이전 테이블에 있는 각 열 조합에 대해서는, Mysql은 그 조합이 열을 추출하기 위해서 range 또는 index_merge 접근방식을 사용할 수 있을지를 검사한다. 이방법은 그리 빠른 방법은 아니지만, 인덱스를 전혀 사용하지 않는 것보다는 빠르게 진행한다.

--> 인덱스를 찾지 못했다?.. pk이라고... 잴빠른 b-tree 인덱스인데..왜 못찾을까;;

인덱스를 못찾았는데 어떻게든 옵티마이져가 인덱스 비슷하게 만들어서 scan 했다는 얘기같은데.. 이게 정확히 뭘 의미하는지 몰랐다.

# 테이블 스키마를 확인했다.

TMP_LOAD_20200313_01

SEQ_NO bigint (15) unsigned not null

TMP_LOAD_20200313_02

SEQ_NO varchar (20) not null

--> 같은 컬럼 네이밍에 데이터 타입이 다르다니; 솔직히 생각지 못했다.

seq_no는테이블 200 여개에서 같은 컬럼 네이밍을 쓰고 있는데 데이터형이 다를 줄이야...

결국 range checked for each record 의미는 컬럼의 데이터형이 맞지 않는데 옵티마이져가 강제 형변환해서 scan을 한 것이었다.

2) 형변환후 plan 확인

id	select type	table	type	possible_key	key	key len	ref	rows	extra
1	SIMPLE	T1	ALL					559741
1	SIMPLE	T2	eq_ref	PRIMARY	PRIMARY	8	xxx.T1.SEQ_NO	1

전체 데이터 추출시간 2초이내로 끝났고, 인덱스 스캔이 (eq_ref ) 제대로 되는 것을 확인할 수 있다.

mysql의 옵티마이져는 생각보다 똑똑했다..;

--> 최근에 개발된 테이블 스키마가 문제였고, 데이터 형변환을 적용하려 개발쪽과 협의해서 추가반영을 하였다.

저작자표시

'RDB > mysql' 카테고리의 다른 글

[테스트] stored function cache 설정 (0)	2020.04.28
쿼리를 실행한다는 것 1 (0)	2020.04.21
slave online hot backup 구축 (0)	2020.04.11
[튜닝] mysql function 성능 이슈 (0)	2020.04.08
mysql 컬럼 타입 datetime vs timestamp 차이 (0)	2020.04.08

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

slave online hot backup 구축

RDB/mysql 2020. 4. 11. 01:35

고객이 늘면서 1000만 row가 넘어가는 테이블이 속속들이 생기고 있다.

mysql 은 100만 row만 넘어도 대용량 테이블로 보고 관련테이블을 join으로 쓰는 쿼리는 최소한의 function 사용, procedure 사용지양하여

open table cache를 최소한으로 쓸 수있는 방법과 index 튜닝을 해왔는데..

이제는 그것만으로 버티긴 힘들다. 그래서 디스크를 SAS 에서 SSD로 교체작업을 진행중이고

Master 교체후 이제 Slave를 교체하는 작업을 진행하였다.

예전에는 서비스 중단후 slave 교체작업을 했었지만 이제는 xtrabackup의 도움을 받아 한번 도전해보고 싶다.

결론은 상용 서버에서 100% 무중단으로 slave 구축에 성공했고, 작업 전에 테스트기에서 했던 내용을 남긴다.

mysql replication은 참 편리한 기능이다.
하지만 replication 관련 에러가 났을 때 그것을 해결할 만한 레퍼런스는 많지 않다.
3년 전에 그렇게 한 5일을 고생하고 .. 결국 찾은 원인은 federated table 때문이었지만..
그 이후에는 그냥 점검할 떄 웬만하면 서비스 중단 후 작업을 진행했다.

이번에 온라인으로 상용기 slave를 구축하는 목적은 추후 로드밸런싱이나 모니터링 툴 도입시 slave를 추가로

구축할 수도 있어서 미리 해보기로 했다.

참조사이트는 성동찬님의 xtrabackup 부분을 참조했다.

# 테스트기 환경

1) 백업전 준비

실시간 트렌젝션은 지속된다.(상용 서버에 작업한다는 전제하에 최대한 동일한 환경을 구성)

Master는 계속해서 insert update가 일어나고 binlog에 데이터는 쌓인다.

--> xtrabackup 전에 임시테이블을 생성하고 insert into로 10만건을 밀어넣도록 했다. 백업 끝날때까지 안끝나도록 하는 것이 목적이었다. binlog 와 pos가 변경되고 xtrabackup 후에 apply log가 변경지점까지 잘따라갔는지 확인하기 위해서다.

2) 백업

Master DB<------------Slave DB

Slave 에서 Master로 가서 xtrabackup으로 데이터를 끌어온다.

ssh -o StrictHostKeyChecking=no mysql@192.168.10.101 \
 "innobackupex \
 --host="127.0.0.1" \
 --port=3306 \
 --user=root \
 --password="DB비번" \
 --no-lock \
 --stream=xbstream \
 /backup/temp | pv --rate-limit 50000000" 2> innobackupex.log \
 | xbstream -x 2> xbstream.log

흠.. 뭐 바로 될거라고 생각안했지만 일단 xtrabackup shell이 제대로 동작을 안한다.

구글링해보니 xtrabackup 2.4.12 ver이상부터 된다는 얘기가 있다.

음... 지금보니 xtarbackup 2.4.9다. version 패치한다.

[root@mysqldb_tmp]# rpm -Uvh percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-2.4.19-1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
준비 중...               ########################################### [100%]
   1:percona-xtrabackup-24  ########################################### [100%]
[root@mysqldb_tmp]# rpm -Uvh percona-xtrabackup-24-debuginfo-2.4.19-1.el6.x86_64.rpm
경고: percona-xtrabackup-24-debuginfo-2.4.19-1.el6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 8507efa5: NOKEY
준비 중...               ########################################### [100%]
   1:percona-xtrabackup-24-d########################################### [100%]

xtarbackup 2.4.19로 올리니 잘된다. version 문제였다.

백업이 잘되었는지 여부는

마지막에 xbstream.log에 아무것도 없고, innodbackupex.log에 completed 를 확인하면된다.

# shell 분석

# xtrabackup 옵션

--stream=name
이 옵션에 명시된 포맷으로 백업 데이터를 stream한다. 현재는 xbstream과 tar를 지원한다.
스트리밍 기능을 사용하려면 --stream 옵션을 사용하여 스트림 형식은 (tar 또는 xbstream)을 사용하고
임시 파일을 저장할 위치를 제공해야 합니다.

--> stream의 임시저장 경로(/backup/temp) 은 어디에 만들어야할까? Master다. Slave에서는 데이터 끌어올 위치에서 실행하면 그 위치에 데이터가 쌓이는 것이지 실제 쉘에서 쓰는 /backup/temp는 마스터에 있어야할 폴더이고 이게 없으면 innodbackup.log에서 에러를 뱉어내고 실행 안된다.

--no-lock
이 옵션을 사용하게 되면 FLUSH TABLES WITH READ LOCK을 실행하지 않는다. 이옵션을 사용하지 않아도
일관성에 문제가 없다고 판단하는 경우에만 사용하도록 한다. 만약 백업 도중에 DDL이 발생할 수 있다면
사용하지 않는 것이 좋다. 이 옵션을 백업을 위해 락을 잡는데 문제가 된다고 생각하는 경우에만 사용하도록 한다.
--safe-slave-backup 을 사용하여 잠시라도 리플리케이션 슬레이브 스레드를 중지하게 되면 성공적으로 백업을
진행할 수 있게 도움을 줄 수 있다. 하지만, 이 옵션에 의존적일 필요는 없다.
xtrabackup_binlog_info 파일은 이 옵션을 사용하면 생성되지 않는다. 대신에 xtrabackup_binlog_pos_innodb 파일을
사용할 수 있다.

--> xtrabackup을 하게되면 xtrabackup_binlog_info, xtrabackup_binlog_pos_innodb 둘다 binlog 파일이름과 pos가 생긴다. 둘중에 뭘 써야할 지 몰랐는데 이번에 shell분석하면서 확실히 알게되었다. no-lock 는 xtrabackup_binlog_pos_innodb 다.

# pv 옵션

- linux 에서 대용량 파일 복사시 진행 상황 표시 및 중단시 이어서 복사 (cp 명령어 대체)
pv 는 콘솔 프로그램으로 다음과 같은 특징이 있습니다.
•pipe 기반으로 동작하여 속도가 빠름
•파일의 복사/이동 진행 상태를 확인할 수 있음
•총 전송량과 예상 시간을 표시
•중단후 다시 시작할 경우 이후부터 시작

--rate-limit 옵션
이 옵션을 사용하여 처리 속도를 제한할 수 있다. 이 옵션의 인수로는 하나의 숫자와 하나의 접미어(예: MB/초를 나타내는 m)가 있다

--> 온라인 hot 백업시 가장 큰 고민이었다. 분명 ssh로 끌어온다면 Master의 네트웍 부하를 최소화해야하는데 방법을 고민했는데, pv 라는 좋은 것을 알게 되었다.

50M로 끊어서 전송한다.

3) 재생성

3-1) apply log 복구

xtrabackup은 백업 도중에 Master에서 발생한 트랜젝션을 xtrabackup_logfile에 쌓아놓고 xtrabackup이 끝날 때까지 쌓는다.

apply log를 실행하게되면 xtrabackup_logfile 파일을 읽어 xtrabackup 동안 추가된 트랜젝션 데이터를 /data/영역에 merge하고 마지막 binlog 파일과 pos를 xtrabackup_binlog_pos_innodb에 남긴다.

--> xtrabackup_logfile 을 한번 보고 싶어서 mysqlbinlog로 열어봤는데 안열린다. 다른 파일포맷인가보다...

일단 임시테이블 생성하고 발생한 트랜젝션이 apply log 실행으로 제대로 merge 되는지 확인했다.

백업시작시 0건에서 시작한 임시테이블 count(*)가 xtrabackup이 끝났을 떄 54689건이 되었다.

xtrabackup 도중에 발생한 트랜젝션이 제대로 쌓였음을 확인했다.

[root@backup]# /usr/bin/innobackupex --defaults-file=/etc/my.cnf --datadir=/data/mysqldb_data  --apply-log /backup2/temp/temp

xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend --innodb_log_files_in_group=3 --innodb_log_file_size=268435456 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=1 --redo-log-version=1 --datadir=/data/mysqldb_data 
xtrabackup: recognized client arguments: 
200403 15:37:35 innobackupex: Starting the apply-log operation

IMPORTANT: Please check that the apply-log run completes successfully.
           At the end of a successful apply-log run innobackupex
           prints "completed OK!".

/usr/bin/innobackupex version 2.4.19 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c2d69da)
xtrabackup: cd to /backup2/temp/temp/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=682229760, start_lsn=(232065897958)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 682229760
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 682229760
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 232065897958
InnoDB: Doing recovery: scanned up to log sequence number 232071140352 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 232076383232 (1%)
InnoDB: Doing recovery: scanned up to log sequence number 232081626112 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232086868992 (3%)
InnoDB: Doing recovery: scanned up to log sequence number 232092111872 (4%)
InnoDB: Doing recovery: scanned up to log sequence number 232097354752 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 232102597632 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232107840512 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232113083392 (7%)
InnoDB: Doing recovery: scanned up to log sequence number 232118326272 (8%)
InnoDB: Doing recovery: scanned up to log sequence number 232123569152 (9%)
InnoDB: Doing recovery: scanned up to log sequence number 232128812032 (10%)
InnoDB: Doing recovery: scanned up to log sequence number 232134054912 (11%)
InnoDB: Doing recovery: scanned up to log sequence number 232139297792 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232144540672 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232149783552 (13%)
InnoDB: Doing recovery: scanned up to log sequence number 232155026432 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232160269312 (15%)
InnoDB: Doing recovery: scanned up to log sequence number 232165512192 (16%)
InnoDB: Doing recovery: scanned up to log sequence number 232170755072 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 232175997952 (18%)
InnoDB: Doing recovery: scanned up to log sequence number 232181240832 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232186483712 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232191726592 (20%)
InnoDB: Doing recovery: scanned up to log sequence number 232196969472 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232202212352 (22%)
InnoDB: Doing recovery: scanned up to log sequence number 232207455232 (23%)
InnoDB: Doing recovery: scanned up to log sequence number 232212698112 (24%)
InnoDB: Doing recovery: scanned up to log sequence number 232217940992 (25%)
InnoDB: Doing recovery: scanned up to log sequence number 232223183872 (25%)
InnoDB: Doing recovery: scanned up to log sequence number 232228426752 (26%)
InnoDB: Doing recovery: scanned up to log sequence number 232233669632 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232238912512 (28%)
InnoDB: Doing recovery: scanned up to log sequence number 232244155392 (29%)
InnoDB: Doing recovery: scanned up to log sequence number 232249398272 (30%)
InnoDB: Doing recovery: scanned up to log sequence number 232254641152 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232259884032 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232265126912 (32%)
InnoDB: Doing recovery: scanned up to log sequence number 232270369792 (33%)
InnoDB: Doing recovery: scanned up to log sequence number 232275612672 (34%)
InnoDB: Doing recovery: scanned up to log sequence number 232280855552 (35%)
InnoDB: Doing recovery: scanned up to log sequence number 232286098432 (36%)
InnoDB: Doing recovery: scanned up to log sequence number 232291341312 (37%)
InnoDB: Doing recovery: scanned up to log sequence number 232296584192 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232301827072 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232307069952 (39%)
InnoDB: Doing recovery: scanned up to log sequence number 232312312832 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232317555712 (41%)
InnoDB: Doing recovery: scanned up to log sequence number 232322798592 (42%)
InnoDB: Doing recovery: scanned up to log sequence number 232328041472 (43%)
InnoDB: Doing recovery: scanned up to log sequence number 232333284352 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232338527232 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232343770112 (45%)
InnoDB: Doing recovery: scanned up to log sequence number 232349012992 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232354255872 (47%)
InnoDB: Doing recovery: scanned up to log sequence number 232359498752 (48%)
InnoDB: Doing recovery: scanned up to log sequence number 232364741632 (49%)
InnoDB: Doing recovery: scanned up to log sequence number 232369984512 (50%)
InnoDB: Doing recovery: scanned up to log sequence number 232375227392 (51%)
InnoDB: Doing recovery: scanned up to log sequence number 232380470272 (51%)
InnoDB: Doing recovery: scanned up to log sequence number 232385713152 (52%)
InnoDB: Doing recovery: scanned up to log sequence number 232390956032 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232396198912 (54%)
InnoDB: Doing recovery: scanned up to log sequence number 232401441792 (55%)
InnoDB: Doing recovery: scanned up to log sequence number 232406684672 (56%)
InnoDB: Doing recovery: scanned up to log sequence number 232411927552 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232417170432 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232422413312 (58%)
InnoDB: Doing recovery: scanned up to log sequence number 232427656192 (59%)
InnoDB: Doing recovery: scanned up to log sequence number 232432899072 (60%)
InnoDB: Doing recovery: scanned up to log sequence number 232438141952 (61%)
InnoDB: Doing recovery: scanned up to log sequence number 232443384832 (62%)
InnoDB: Doing recovery: scanned up to log sequence number 232448627712 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232453870592 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232459113472 (64%)
InnoDB: Doing recovery: scanned up to log sequence number 232464356352 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232469599232 (66%)
InnoDB: Doing recovery: scanned up to log sequence number 232474842112 (67%)
InnoDB: Doing recovery: scanned up to log sequence number 232480084992 (68%)
InnoDB: Doing recovery: scanned up to log sequence number 232485327872 (69%)
InnoDB: Doing recovery: scanned up to log sequence number 232490570752 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232495813632 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232501056512 (71%)
InnoDB: Doing recovery: scanned up to log sequence number 232506299392 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232511542272 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 232516785152 (74%)
InnoDB: Doing recovery: scanned up to log sequence number 232522028032 (75%)
InnoDB: Doing recovery: scanned up to log sequence number 232527270912 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232532513792 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232537756672 (77%)
InnoDB: Doing recovery: scanned up to log sequence number 232542999552 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232548242432 (79%)
InnoDB: Doing recovery: scanned up to log sequence number 232553485312 (80%)
InnoDB: Doing recovery: scanned up to log sequence number 232558728192 (81%)
InnoDB: Doing recovery: scanned up to log sequence number 232563971072 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232569213952 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232574456832 (83%)
InnoDB: Doing recovery: scanned up to log sequence number 232579699712 (84%)
InnoDB: Doing recovery: scanned up to log sequence number 232584942592 (85%)
InnoDB: Doing recovery: scanned up to log sequence number 232590185472 (86%)
InnoDB: Doing recovery: scanned up to log sequence number 232595428352 (87%)
InnoDB: Doing recovery: scanned up to log sequence number 232600671232 (88%)
InnoDB: Doing recovery: scanned up to log sequence number 232605914112 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232611156992 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232616399872 (90%)
InnoDB: Doing recovery: scanned up to log sequence number 232621642752 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232626885632 (92%)
InnoDB: Doing recovery: scanned up to log sequence number 232632128512 (93%)
InnoDB: Doing recovery: scanned up to log sequence number 232637371392 (94%)
InnoDB: Doing recovery: scanned up to log sequence number 232642614272 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232647857152 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232653100032 (96%)
InnoDB: Doing recovery: scanned up to log sequence number 232658342912 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232663585792 (98%)
InnoDB: Doing recovery: scanned up to log sequence number 232668828672 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 232672354749 (100%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: Doing recovery: scanned up to log sequence number 232067601408 (0%)
InnoDB: Doing recovery: scanned up to log sequence number 232072844288 (1%)
InnoDB: Doing recovery: scanned up to log sequence number 232078087168 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232083330048 (2%)
InnoDB: Doing recovery: scanned up to log sequence number 232088572928 (3%)
InnoDB: Doing recovery: scanned up to log sequence number 232093815808 (4%)
InnoDB: Doing recovery: scanned up to log sequence number 232099058688 (5%)
InnoDB: Doing recovery: scanned up to log sequence number 232104301568 (6%)
InnoDB: Doing recovery: scanned up to log sequence number 232109544448 (7%)
InnoDB: Doing recovery: scanned up to log sequence number 232114787328 (8%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232120030208 (8%)
InnoDB: Doing recovery: scanned up to log sequence number 232125273088 (9%)
InnoDB: Doing recovery: scanned up to log sequence number 232130515968 (10%)
InnoDB: Doing recovery: scanned up to log sequence number 232135758848 (11%)
InnoDB: Doing recovery: scanned up to log sequence number 232141001728 (12%)
InnoDB: Doing recovery: scanned up to log sequence number 232146244608 (13%)
InnoDB: Doing recovery: scanned up to log sequence number 232151487488 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232156730368 (14%)
InnoDB: Doing recovery: scanned up to log sequence number 232161973248 (15%)
InnoDB: Doing recovery: scanned up to log sequence number 232167216128 (16%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232172459008 (17%)
InnoDB: Doing recovery: scanned up to log sequence number 232177701888 (18%)
InnoDB: Doing recovery: scanned up to log sequence number 232182944768 (19%)
InnoDB: Doing recovery: scanned up to log sequence number 232188187648 (20%)
InnoDB: Doing recovery: scanned up to log sequence number 232193430528 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232198673408 (21%)
InnoDB: Doing recovery: scanned up to log sequence number 232203916288 (22%)
InnoDB: Doing recovery: scanned up to log sequence number 232209159168 (23%)
InnoDB: Doing recovery: scanned up to log sequence number 232214402048 (24%)
InnoDB: Doing recovery: scanned up to log sequence number 232219644928 (25%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232224887808 (26%)
InnoDB: Doing recovery: scanned up to log sequence number 232230130688 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232235373568 (27%)
InnoDB: Doing recovery: scanned up to log sequence number 232240616448 (28%)
InnoDB: Doing recovery: scanned up to log sequence number 232245859328 (29%)
InnoDB: Doing recovery: scanned up to log sequence number 232251102208 (30%)
InnoDB: Doing recovery: scanned up to log sequence number 232256345088 (31%)
InnoDB: Doing recovery: scanned up to log sequence number 232261587968 (32%)
InnoDB: Doing recovery: scanned up to log sequence number 232266830848 (33%)
InnoDB: Doing recovery: scanned up to log sequence number 232272073728 (33%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232277316608 (34%)
InnoDB: Doing recovery: scanned up to log sequence number 232282559488 (35%)
InnoDB: Doing recovery: scanned up to log sequence number 232287802368 (36%)
InnoDB: Doing recovery: scanned up to log sequence number 232293045248 (37%)
InnoDB: Doing recovery: scanned up to log sequence number 232298288128 (38%)
InnoDB: Doing recovery: scanned up to log sequence number 232303531008 (39%)
InnoDB: Doing recovery: scanned up to log sequence number 232308773888 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232314016768 (40%)
InnoDB: Doing recovery: scanned up to log sequence number 232319259648 (41%)
InnoDB: Doing recovery: scanned up to log sequence number 232324502528 (42%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232329745408 (43%)
InnoDB: Doing recovery: scanned up to log sequence number 232334988288 (44%)
InnoDB: Doing recovery: scanned up to log sequence number 232340231168 (45%)
InnoDB: Doing recovery: scanned up to log sequence number 232345474048 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232350716928 (46%)
InnoDB: Doing recovery: scanned up to log sequence number 232355959808 (47%)
InnoDB: Doing recovery: scanned up to log sequence number 232361202688 (48%)
InnoDB: Doing recovery: scanned up to log sequence number 232366445568 (49%)
InnoDB: Doing recovery: scanned up to log sequence number 232371688448 (50%)
InnoDB: Doing recovery: scanned up to log sequence number 232376931328 (51%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232382174208 (52%)
InnoDB: Doing recovery: scanned up to log sequence number 232387417088 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232392659968 (53%)
InnoDB: Doing recovery: scanned up to log sequence number 232397902848 (54%)
InnoDB: Doing recovery: scanned up to log sequence number 232403145728 (55%)
InnoDB: Doing recovery: scanned up to log sequence number 232408388608 (56%)
InnoDB: Doing recovery: scanned up to log sequence number 232413631488 (57%)
InnoDB: Doing recovery: scanned up to log sequence number 232418874368 (58%)
InnoDB: Doing recovery: scanned up to log sequence number 232424117248 (59%)
InnoDB: Doing recovery: scanned up to log sequence number 232429360128 (59%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232434603008 (60%)
InnoDB: Doing recovery: scanned up to log sequence number 232439845888 (61%)
InnoDB: Doing recovery: scanned up to log sequence number 232445088768 (62%)
InnoDB: Doing recovery: scanned up to log sequence number 232450331648 (63%)
InnoDB: Doing recovery: scanned up to log sequence number 232455574528 (64%)
InnoDB: Doing recovery: scanned up to log sequence number 232460817408 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232466060288 (65%)
InnoDB: Doing recovery: scanned up to log sequence number 232471303168 (66%)
InnoDB: Doing recovery: scanned up to log sequence number 232476546048 (67%)
InnoDB: Doing recovery: scanned up to log sequence number 232481788928 (68%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232487031808 (69%)
InnoDB: Doing recovery: scanned up to log sequence number 232492274688 (70%)
InnoDB: Doing recovery: scanned up to log sequence number 232497517568 (71%)
InnoDB: Doing recovery: scanned up to log sequence number 232502760448 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232508003328 (72%)
InnoDB: Doing recovery: scanned up to log sequence number 232513246208 (73%)
InnoDB: Doing recovery: scanned up to log sequence number 232518489088 (74%)
InnoDB: Doing recovery: scanned up to log sequence number 232523731968 (75%)
InnoDB: Doing recovery: scanned up to log sequence number 232528974848 (76%)
InnoDB: Doing recovery: scanned up to log sequence number 232534217728 (77%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232539460608 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232544703488 (78%)
InnoDB: Doing recovery: scanned up to log sequence number 232549946368 (79%)
InnoDB: Doing recovery: scanned up to log sequence number 232555189248 (80%)
InnoDB: Doing recovery: scanned up to log sequence number 232560432128 (81%)
InnoDB: Doing recovery: scanned up to log sequence number 232565675008 (82%)
InnoDB: Doing recovery: scanned up to log sequence number 232570917888 (83%)
InnoDB: Doing recovery: scanned up to log sequence number 232576160768 (84%)
InnoDB: Doing recovery: scanned up to log sequence number 232581403648 (85%)
InnoDB: Doing recovery: scanned up to log sequence number 232586646528 (85%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232591889408 (86%)
InnoDB: Doing recovery: scanned up to log sequence number 232597132288 (87%)
InnoDB: Doing recovery: scanned up to log sequence number 232602375168 (88%)
InnoDB: Doing recovery: scanned up to log sequence number 232607618048 (89%)
InnoDB: Doing recovery: scanned up to log sequence number 232612860928 (90%)
InnoDB: Doing recovery: scanned up to log sequence number 232618103808 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232623346688 (91%)
InnoDB: Doing recovery: scanned up to log sequence number 232628589568 (92%)
InnoDB: Doing recovery: scanned up to log sequence number 232633832448 (93%)
InnoDB: Doing recovery: scanned up to log sequence number 232639075328 (94%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Doing recovery: scanned up to log sequence number 232644318208 (95%)
InnoDB: Doing recovery: scanned up to log sequence number 232649561088 (96%)
InnoDB: Doing recovery: scanned up to log sequence number 232654803968 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232660046848 (97%)
InnoDB: Doing recovery: scanned up to log sequence number 232665289728 (98%)
InnoDB: Doing recovery: scanned up to log sequence number 232670532608 (99%)
InnoDB: Doing recovery: scanned up to log sequence number 232672354749 (100%)
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 76339ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.26 started; log sequence number 232672354749
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 232672356123
InnoDB: Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:100M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 3
xtrabackup:   innodb_log_file_size = 268435456
InnoDB: PUNCH HOLE support available
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Uses event mutexes
InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
InnoDB: Compressed tables use zlib 1.2.3
InnoDB: Number of pools: 1
InnoDB: Not using CPU crc32 instructions
InnoDB: Initializing buffer pool, total size = 100M, instances = 1, chunk size = 100M
InnoDB: Completed initialization of buffer pool
InnoDB: page_cleaner coordinator priority: -20
InnoDB: Setting log file ./ib_logfile101 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Setting log file ./ib_logfile1 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Setting log file ./ib_logfile2 size to 256 MB
InnoDB: Progress in MB:
 100 200
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=232672356123
InnoDB: Highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 232672356364
InnoDB: Doing recovery: scanned up to log sequence number 232672356373 (0%)
InnoDB: Database was not shutdown normally!
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 33415270, file name mysql-bin.000012
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: page_cleaner: 1000ms intended loop took 12821ms. The settings might not be optimal. (flushed=0 and evicted=0, during the time.)
InnoDB: 5.7.26 started; log sequence number 232672356373
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 232672356392
200403 15:39:10 completed OK!

3-2) 데이터 복구

copy-back 이나 move-back를 하면된다. 하기전에 /data/mysqldb_data는 비우고 새로 생성해야한다.

/usr/bin/innobackupex --defaults-file=/etc/my.cnf --datadir=/data/mysqldb_data --copy-back /backup2/temp/temp

4) slave replication 연결

--> 현재는 Master는 10만건의 데이터가 전부 임시테이블에 들어가 있고 slave는 DB올리고 보니 54689이 쌓여있음을 확인했다. 데이터유실 없는 replication이 되려면 임시테이블의 데이터가 replication 후 10만건이 되면 된다.

vi xtrabackup_binlog_info
mysql-bin.000012        61288234

vi xtrabackup_binlog_pos_innodb
mysql-bin.000012        61287127

둘 중에 아래꺼를 slave 복구 때 적용하면 된다.

change master to
master_host='192.168.10.101',
master_user='repl',
master_password='repl',
master_port=3306,
master_log_file='mysql-bin.000012',
master_log_pos=61287127;

slave start;

--> show slave status\G; 로 동기화완료후 확인해보니 slave 임시테이블의 데이터가 10만건임을 확인했다.

replication 동기화 끝!! 데이터 유실없이 online slave 구축이 가능함을 확인했다.

참조사이트

https://gywn.net/tag/xtrabackup/

'RDB > mysql' 카테고리의 다른 글

쿼리를 실행한다는 것 1 (0)	2020.04.21
[튜닝] 니 형이 뭐야? (0)	2020.04.13
[튜닝] mysql function 성능 이슈 (0)	2020.04.08
mysql 컬럼 타입 datetime vs timestamp 차이 (0)	2020.04.08
[error] mysqldump: Got errno 28 on write (0)	2020.04.02

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

[튜닝] mysql function 성능 이슈

RDB/mysql 2020. 4. 8. 16:50

where 절에 function 쓰면 성능이슈가 있다고 한다.

실제로 본적은 많지 않은데... 오늘 장애를 만나면서 그 끝판왕을 보았다.

이슈를 기록하고 튜닝한 내용을 정리한다.

CREATE FUNCTION `xxx`.`FN_DATE_xxx`(varDate datetime) RETURNS datetime
BEGIN


declare varxxx datetime;
declare varDt varchar(8);

set varDt:= FN_YMD_xxx(varDate,'');

SELECT  STR_TO_DATE(concat(varDt,'000000'), '%Y%m%d%H%i%s') INTO varxxx
FROM    DUAL
;

RETURN varxxx;
END

fucntion에서 다시 function을 호출한다.

fucntion을 분석해보니 현재시간에서 일정기간을 뺀 range를 검색하는 목적으로 만든 듯했다.

개발당시 어떤 이슈가 있었는지는 모르지만 mysql function 레퍼런스를 확인할 시간적 여유가 없었나보다...

이게 쌓이고 쌓여 3년만에 대용량 테이블과 만나서 장애를 일으켰다...

튜닝)

// 수많은 join으로 되어있지만 대용량테이블 하나만 가지고 분석한다. 나머지 테이블 다붙여도 1초 내외이다.
//튜닝전
select * FROM xxxx.xxxxxxx AP
where 1=1 
AND AP.REG_DATE BETWEEN xxx.FN_DATE_xxx (xxx.FN_xxx(NOW(),-14)) AND NOW()  

//튜닝후
select * FROM xxxx.xxxxxxx AP
where 1=1 
AND AP.REG_DATE BETWEEN DATE_FORMAT(DATE_SUB(NOW(), interval 14 day),'%Y-%m-%d 00:00:00') and NOW()

튜닝전)

1) 플랜 : 1 SIMPLE AP ALL REG_DATE 422277 11.11 Using where

2) 수행시간 : 51초

튜닝후)

1) 플랜 : 1 SIMPLE AP range REG_DATE REG_DATE 5 23128 100 Using index condition

2) 수행시간 : 0.83초

where 조건절에는 웬만하면 function 쓰면 안되고 어쩔 수 없이 쓰더라도

mysql에서 기본으로 제공하는 function을 사용하는 것이 좋다.

'RDB > mysql' 카테고리의 다른 글

[튜닝] 니 형이 뭐야? (0)	2020.04.13
slave online hot backup 구축 (0)	2020.04.11
mysql 컬럼 타입 datetime vs timestamp 차이 (0)	2020.04.08
[error] mysqldump: Got errno 28 on write (0)	2020.04.02
SSD 파티션 튜닝 2 (0)	2020.03.31

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

mysql 컬럼 타입 datetime vs timestamp 차이

RDB/mysql 2020. 4. 8. 11:41

개발에서 반영요청한 테이블 스키마를 보던 중에 datetime과 timestamp를 각각 선언해서 쓴 경우가 있어서 둘의 차이를 분석해보고 반영여부를 검토했다.

일단 구글링..

1. 날짜 및 시간 타입

1) DATETIME
이 타입은 매우 큰 범위의 값, 즉 1001년부터 9999년까지의 값을 1초 단위로 저장할 수 있다. DATETIME 은 날짜와 시각을 YYYYMMDDHHMMSS` 포맷의 정수 값으로 묶는데, 시간대에는 영향을 받지 않는다. DATETIME 은 8 바이트의 저장 공간을 사용한다.

2) TIMESTAMP
이름이 암시하듯, TIMESTAMP 타입은 1970년 1월 1일 자정(그리니치 평균시)을 기준으로 몇초가 지났는지를 저장하며, Unix 타임스탬프와 동일하다. TIMESTAMP 는 저장 공간을 4 바이트만 사용하므로 값의 범위가 DATETIME 보다 훨씬 작고, 1970년부터 2038년 사이의 값만을 저장할 수 있다.

MySQL 에서는 FROM_UNIXTIME(), UNIX_TIMESTAMP()` 같은 함수를 제공하는데 이 함수를 이용하면 Unix 타임스탬프를 날짜로 바꾸거나 그 날짜를 Unix 타임스탬프로 바꿀 수 있다.

TIMESTAMP 에 출력되는 값은 시간대에 따라 다르다. MySQL 서버, 운영체제, 클라이언트 연결은 모두 시간대 설정이 있다.
또한 TIMESTAMP 에는 DATETIME 에는 없는 특별한 특성들이 있다. 기본적으로 TIMESTAMP 컬럼에 값을 지정하지 않은 채로 행을 추가하면 첫 번째 TIMESTAMP 컬럼에 현재 시각을 넣는다.
또, UPDATE 실행 시 TIMESTAMP 값을 명시적으로 할당하지 않고 실행하면 TIMESTAMP 컬럼의 값이 자동으로 업데이트 된다. (즉, TIMESTAMP 는 기본적으로 NOT NULL)

2. 표로 정리

	timestamp	datetime
날짜 범위	1970-01-01 09:00:00 ~ 2037-12-31 23:59:59	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
저장형태	숫자형	문자형
파일사이즈	4byte	8byte
자동입력?	O (defalut 값을 주면 굳이now() 컬럼 값 주지 않아도 자동입력됨)	x

3. 테스트

컬럼 스키마 선언

`REG_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '등록일시'

위처럼 선언하면 insert into 할 때 now() 안써도 된다는데 확인해본다.

음.. 잘된다.

보통은 datetime 이라면 insert into 테이블 ( SEQ, REG_DATE) values ( SEQ, now() );

이렇게 썼겠지만

타임스템프 default 옵션을 쓰면 insert into 테이블 ( SEQ, REG_DATE) values ( SEQ );

만 쓰면 자동으로 현재시간을 테이블에 넣는다.

4. timestamp 쓸것인가 말것인가

개발쪽에서 알고 기존 테이블 복붙해서 쓴 것이고 제대로 알고 쓴 것이 아닌 것으로 확인했고,

소스도 이미 now()를 넣는 것으로 컴파일해서 올렸다고 한다..

그래서 소스재배포 없이 그냥 DB 스키마를 datetime not null로 변경해서 반영했다.

DB 서버의 UTC 값을 가져와서 해외로 이전할 때는 문제 될 거 같긴 하지만

성능으로 보자면 파일사이즈(4byte)가 datetime 의 절반이라

2038년까지는 timestamp를 쓰는 게 좋을 거 같긴하다..^^

'RDB > mysql' 카테고리의 다른 글

slave online hot backup 구축 (0)	2020.04.11
[튜닝] mysql function 성능 이슈 (0)	2020.04.08
[error] mysqldump: Got errno 28 on write (0)	2020.04.02
SSD 파티션 튜닝 2 (0)	2020.03.31
SSD 파티션 튜닝 1 (0)	2020.03.26

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

[error] mysqldump: Got errno 28 on write

RDB/mysql 2020. 4. 2. 04:44

mysqldump 뜨던중에 갑자기 이런 에러가 났다

뭐지? 급당황... 찾아보니 생각보다 간단한 에러였다.

용량이 다 찼다는 에러다 ㅋㅋ

비우고 다시 dump 뜨면 된다.

[admin@backup]$ mysqldump -uroot -p DB스키마 테이블이름  > 62_TALBES.sql

Filesystem Type Size Used Avail Use% Mounted on
/dev/sda2 ext4 20G 19G 16M 100% /
tmpfs tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 ext4 477M 111M 341M 25% /boot
/dev/sda5 ext4 20G 9.5G 9.1G 52% /usr
/dev/sda7 ext4 3.9G 645M 3.0G 18% /var
/dev/sda6 ext4 3.9G 8.2M 3.7G 1% /tmp
/dev/sdb1 ext4 1.1T 264G 780G 26% /data
/dev/sda8 ext4 212G 98G 103G 49% /backup
xxx.xxx.xxx.xxx:/data nfs 13T 3.6T 8.1T 31% /data2

'RDB > mysql' 카테고리의 다른 글

[튜닝] mysql function 성능 이슈 (0)	2020.04.08
mysql 컬럼 타입 datetime vs timestamp 차이 (0)	2020.04.08
SSD 파티션 튜닝 2 (0)	2020.03.31
SSD 파티션 튜닝 1 (0)	2020.03.26
[에러] mysql DB disk 폴더 이동후 재시작 (0)	2020.03.26

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

SSD 파티션 튜닝 2

RDB/mysql 2020. 3. 31. 18:07

1) SSD / 4k 테스트

ssd / innodb_page_size = 4k 를 위한 성능테스트를 다시하려고

대용량 조회쿼리부분 DB와 table만 마이그레이션을 하던 중에 아래와 같은 에러를 만났다.

ERROR 1118 (42000) at line 25: Row size too large (> 1982). Changing some columns to TEXT or BLOB  may help. In current row format, BLOB prefix 0 bytes is stored inline.

에러내용을 보면 innodb table에서 한 row에 넣을 수 있는 size max가 있는데 그것을 넘어서 table 생성을 못한다는 에러이다 생성하려는 테이블의 컬럼은 varchar, text, int, char 등..뭐 다양하게 있는 테이블인데 정확하게 113개 넣고 114개째.. 에러가 났다.

테이블 자체가 정규화가 안되서 컬럼이 무식하게 많은 테이블이긴 하지만, 만약 page size가 16k 였다면

아래와 같은 에러를 만났을 것이다.

ERROR 1118 (42000) at line 25: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

2) 테스트 중단

테스트 의미가 없어서 하지 않기로 했다.

테이블 스키마 변경까지 해서 테스트를 하는 것은 기존 쿼리와 동일비교가 되지않아 할 이유가 없어졌다.

3) 결론

SSD / 4k 써야할까?

처음부터 my.cnf에 innodb_page_size = 4096으로 설정하고 데이터를 쌓았다면 모를까..;;

그렇지 않았다면 쓰지 않는 것이 나을 거 같다.

쓰지 않는 이유 중 또 한가지는 ERROR 1118 (42000) 을 만났을 때 page_size 4k는 다른 선택지가 없다.

하지만 16k는 테이블 압축이라는 옵션이 있어서 Barracuda 엔진이 = on 인 상태라면(mysql 5.7 이상 기본 on) ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED 으로 설정하면 압축을 통해 varchar 사이즈가 한계를 넘는 경우라도 index까지 생성할 수 있다.

innodb_page_size 테스트는 여기서 종료한다.

'RDB > mysql' 카테고리의 다른 글

mysql 컬럼 타입 datetime vs timestamp 차이 (0)	2020.04.08
[error] mysqldump: Got errno 28 on write (0)	2020.04.02
SSD 파티션 튜닝 1 (0)	2020.03.26
[에러] mysql DB disk 폴더 이동후 재시작 (0)	2020.03.26
mysqldump Table wildcard % 백업하기 (0)	2020.03.16

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

SSD 파티션 튜닝 1

RDB/mysql 2020. 3. 26. 18:11

mysql의 SSD 디스크 파티션의 최적 튜닝가이드 따라하기

참조1)

50-Tips-for-Boosting-MySQL-Performance-CON2655.pdf

1.86MB

가이드에서 최고의 perfomance라고 하는 디스크 파티션 옵션과 mysqldb page size를 수정해서

최적의 성능을 찾아본다.

참조2) https://blog.naver.com/theswice

THESWICE : 네이버 블로그

DBMS Blog

blog.naver.com

#Mount options

◦ext4 (rw,noatime, nodiration, nobarrier, data=ordered)
◦xfs (rw, noatime, nodiratime, nobarrier, logbufs=8, logbsize=32k)
◦SSD specific ◾trim
◾innodb_page_size = 4k
◾innodb_flush_neighbors = 0

#SSD --> innodb_flush_method = O_DIRECT 란?
I/O 처리량에 영향을 줄 수 있는 InnoDB 데이터 파일 및 로그 파일로 데이터를 플러시하는데 사용되는 방법을 정의합니다.

#innodb_flush_method 옵션 종류
fsync : InnoDB는 fsync() 시스템 호출을사용하여 데이터와 로그 파일을 플러시합니다. fsync가 기본 설정입니다.
O_DSYNC : InnoDB는 O_SYNC를 사용하여 로그 파일을 열고 플러시하고 fsync()를 사용하여 데이터 파일을플러시합니다. InnoDB는 O_DSYNC를 직접 사용하지 않습니다. 왜냐하면 많은 종류의 유닉스에서 문제가 발생했기때문입니다.
O_DIRECT : InnoDB는 O_DIRECT (또는 Solaris의 경우 directio())를 사용하여 데이터 파일을 열고 fsync()를 사용하여 데이터와 로그 파일을 모두 플러시합니다.
O_DIRECT_NO_FSYNC : InnoDB는 I/O를 플러시하는 동안 O_DIRECT를 사용하지만 나중에 fsync() 시스템호출을 건너 뜁니다. 이 설정은 일부 유형의 파일 시스템에는 적합하지만 다른 유형에는 적합하지 않습니다.
예를 들어 XFS에는 적합하지 않습니다. 사용하는 파일 시스템에 fsync()가 필요한지 여부가 확실하지 않은 경우 (예 : 모든 파일 메타 데이터를 유지하는 경우) 대신 O_DIRECT를 사용하십시오.

# 현재 페이지 사이즈 확인

mysql> show global variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

MySQL 인스턴스에서 모든 InnoDB 테이블 스페이스의 페이지 크기를 지정한다. 64k, 32k, 16k (기본값), 8k 또는 4k 값을 사용하여 페이지 크기를 지정할 수 있습니다.
또는 페이지 크기 (바이트) (65536, 32768, 16384, 8192, 4096)를 지정할 수 있습니다.
innodb_page_size는 MySQL 인스턴스를 초기화하기 전에만 구성 할 수 있으며 나중에 변경할 수 없습니다.
값을 지정하지 않으면 인스턴스는 기본 페이지 크기를 사용하여 초기화됩니다.
MySQL 5.7에서는 32k 및 64k 페이지 크기에 대한 지원이 추가되었습니다. 32k 및 64k 페이지 크기의 경우 최대 행 길이는 약 16000 바이트입니다.
innodb_page_size가 32KB 또는 64KB로 설정된 경우 ROW_FORMAT = COMPRESSED는 지원되지 않습니다. innodb_page_size = 32k의 경우, Extent 크기는 2MB입니다.
innodb_page_size = 64k의 경우, Extent 크기는 4MB입니다. innodb_log_buffer_size는 32k 또는 64k 페이지 크기를 사용할 때 최소 16M (기본값)으로 설정해야 합니다.

기본 16KB 페이지 크기 이상은 광범위한 워크로드, 특히 대량 스캔을 포함하는 테이블 스캔 및 DML 조작과 관련된 조회에 적합합니다.
단일 페이지에 많은 행이 포함될 때 경합이 문제가 될 수 있는 많은 작은 쓰기를 포함하는 OLTP 작업 부하에 대해 더 작은 페이지 크기가 더 효율적일 수 있습니다.
더 작은 페이지는 일반적으로 작은 블록 크기를 사용하는 SSD 저장 장치에서도 효율적일 수 있습니다.
InnoDB 페이지 크기를 저장 장치 블록 크기에 가깝게 유지하면 디스크에 다시 쓰여지는 변경되지 않은 데이터의 양이 최소화됩니다.

첫 번째 시스템 테이블 공간 데이터 파일 (ibdata1)의 최소 파일 크기는 innodb_page_size 값에 따라 다릅니다.

# innodb_flush_neighbors 옵션 설명

buffer pool에 있는 내용을 flush 할때 같은 extent에 있는 다른 dirty page 들까지도 같이 flush 할지 설정할 수 있다.
팁 : HDD를 사용하는 경우에 인접하는 dirty 까지 모두 flush 하면 IO부하를 줄일 수 있다. 반면 SSD를 사용할 경우에는 seektime 이 더이상 중요한 팩트가 아니므로 설정을 꺼놓는 것이 좋다
SSD를 사용 중이면 플러시는 중복 조작입니다 (SDD는 순차적이지 않습니다).

mysql> set global innodb_flush_neighbors = 0;

1) SSD 파티션 생성

SSD 마운트 옵션 : xfs / rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k

mysql cnf 튜닝옵션 : innodb_page_size = 4k (4096)

mysql vers : 5.7.19

OS : CentOS 6.10이고 xfs 지원범위를 확인하고 SSD 파티션 생성한다.

[root@localhost ~]# mkfs.xfs -f /dev/sdb1
-bash: mkfs.xfs: command not found

CentOS xfs 지원범위
1) kernel-2.6.18-194.el5.x86_64 이상의
2) 최대크기 100TB 까지 지원
3) OS 설치 후 생성가능
4) root와 boot 파티션에는 지원불가

yum install kmod-xfs xfsdump xfsprogs dmapi

[root@localhost ~]# yum install kmod-xfs xfsdump xfsprogs dmapi
Loaded plugins: fastestmirror, security
Setting up Install Process
base                                                                                                                          | 3.7 kB     00:00     
base/primary_db                                                                                                               | 4.7 MB     00:37     
extras                                                                                                                        | 3.4 kB     00:00     
extras/primary_db                                                                                                             |  29 kB     00:00     
updates                                                                                                                       | 3.4 kB     00:00     
updates/primary_db                                                                                                            | 8.9 MB     00:02     
No package kmod-xfs available.
No package dmapi available.
Resolving Dependencies
--> Running transaction check
---> Package xfsdump.x86_64 0:3.0.4-4.el6_6.1 will be installed
---> Package xfsprogs.x86_64 0:3.1.1-20.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================================================================================================
 Package                            Arch                             Version                                    Repository                      Size
=====================================================================================================================================================
Installing:
 xfsdump                            x86_64                           3.0.4-4.el6_6.1                            base                           252 k
 xfsprogs                           x86_64                           3.1.1-20.el6                               base                           725 k

Transaction Summary
=====================================================================================================================================================
Install       2 Package(s)

Total download size: 977 k
Installed size: 3.9 M
Is this ok [y/N]: y
Downloading Packages:
(1/2): xfsdump-3.0.4-4.el6_6.1.x86_64.rpm                                                                                     | 252 kB     00:01     
(2/2): xfsprogs-3.1.1-20.el6.x86_64.rpm                                                                                       | 725 kB     00:05     
-----------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                128 kB/s | 977 kB     00:07     
경고: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <centos-6-key@centos.org>
 Package: centos-release-6-10.el6.centos.12.3.x86_64 (@anaconda-CentOS-201806291108.x86_64/6.10)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
Is this ok [y/N]: y
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : xfsprogs-3.1.1-20.el6.x86_64                                                                                                      1/2 
  Installing : xfsdump-3.0.4-4.el6_6.1.x86_64                                                                                                    2/2 
  Verifying  : xfsprogs-3.1.1-20.el6.x86_64                                                                                                      1/2 
  Verifying  : xfsdump-3.0.4-4.el6_6.1.x86_64                                                                                                    2/2 

Installed:
  xfsdump.x86_64 0:3.0.4-4.el6_6.1                                           xfsprogs.x86_64 0:3.1.1-20.el6                                          

Complete!


[root@localhost ~]# mkfs.xfs -f /dev/sdb1
meta-data=/dev/sdb1              isize=256    agcount=4, agsize=5242711 blks
         =                       sectsz=512   attr=2, projid32bit=0
data     =                       bsize=4096   blocks=20970841, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0
log      =internal log           bsize=4096   blocks=10239, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0


[root@localhost ~]# fdisk -l

Disk /dev/sda: 107.4 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x000834d3

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          64      512000   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              64        1370    10485760   83  Linux
/dev/sda3            1370        2415     8392704   82  Linux swap / Solaris
/dev/sda4            2415       13055    85466112    5  Extended
/dev/sda5            2415        2937     4194304   83  Linux
/dev/sda6            2937        3459     4194304   83  Linux
/dev/sda7            3459        3590     1048576   83  Linux
/dev/sda8            3590        3721     1048576   83  Linux
/dev/sda9            3721       13055    74975232   83  Linux

Disk /dev/sdb: 85.9 GB, 85899345920 bytes
255 heads, 63 sectors/track, 10443 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x6f525b46

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1       10443    83883366   83  Linux

[root@localhost /]# mount /dev/sdb1 /svc2
[root@localhost /]# df -Th
Filesystem     Type   Size  Used Avail Use% Mounted on
/dev/sda2      ext4   9.8G  310M  9.0G   4% /
tmpfs          tmpfs  3.9G     0  3.9G   0% /dev/shm
/dev/sda1      ext4   477M   39M  413M   9% /boot
/dev/sda7      ext4   976M  1.3M  924M   1% /home
/dev/sda9      ext4    71G   52M   67G   1% /svc
/dev/sda8      ext4   976M  1.3M  924M   1% /tmp
/dev/sda5      ext4   3.9G  1.6G  2.2G  42% /usr
/dev/sda6      ext4   3.9G  139M  3.5G   4% /var
/dev/sdb1      xfs     80G   33M   80G   1% /svc2

// /etc/fstab에 등록
//xfs rw,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k

2) my.cnf 페이지 사이즈 수정

default는 16k이다.

innodb_page_size = 4096 수정 후 재시작

mysql> show global variables like '%page%';
+--------------------------------------+-----------+
| Variable_name                        | Value     |
+--------------------------------------+-----------+
| innodb_log_compressed_pages          | ON        |
| innodb_max_dirty_pages_pct           | 90.000000 |
| innodb_max_dirty_pages_pct_lwm       | 0.000000  |
| innodb_page_cleaners                 | 4         |
| innodb_page_size                     | 16384     |
| innodb_stats_persistent_sample_pages | 20        |
| innodb_stats_sample_pages            | 8         |
| innodb_stats_transient_sample_pages  | 8         |
| large_page_size                      | 0         |
| large_pages                          | OFF       |
+--------------------------------------+-----------+
10 rows in set (0.00 sec)

DB가 안올라온다...;;;

[mysql@localhost error]$ service mysqld start
Starting MySQL...The server quit without updating PID file [실패]/mysqldb_data/localhost.localdomain.pid).

error log)
2020-03-26T10:12:13.476719+09:00 0 [ERROR] InnoDB: Data file './ibdata1' uses page size 16384, but the innodb_page_size start-up parameter is 4096
2020-03-26T10:12:13.476792+09:00 0 [ERROR] InnoDB: Corrupted page [page id: space=0, page number=0] of datafile './ibdata1' could not be found in the doublewrite buffer.
2020-03-26T10:12:13.476813+09:00 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-03-26T10:12:14.082225+09:00 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-03-26T10:12:14.082252+09:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-03-26T10:12:14.082257+09:00 0 [ERROR] Failed to initialize plugins.
2020-03-26T10:12:14.082260+09:00 0 [ERROR] Aborting

//에러로그를 확인하여 로그버퍼관련 파일 백업하고 재시작한다.
[mysql@localhost mysqldb_data]$ cd backup/
[mysql@localhost backup]$ ls
db.opt  ib_buffer_pool  ib_logfile0  ib_logfile1  ib_logfile2  ibdata1  ibdata2  ibdata3  ibdata4  ibtmp1

[mysql@localhost mysqldb_data]$ service mysqld start
Starting MySQL...........................                  [  OK  ]

//뜨긴하는데 이런 에러가 난다..
2020-03-26T12:01:55.070009+09:00 0 [ERROR] Can't open and lock privilege tables: Table 'mysql.servers' doesn't exist
2020-03-26T12:01:55.070315+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070397+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070451+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070460+09:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2020-03-26T12:01:55.070521+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070575+09:00 0 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2020-03-26T12:01:55.070583+09:00 0 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.

// 접속해서 스키마별 테이블을 확인해보니 테이블에 데이터 매핑이 안된다. 제대로 열리는 테이블이 없다.;;;

3) 구글링

innodb_page_size는 dynamic으로 변경할 수 없고 재시작하더라도 tablespace를 비우고 다시 데이터를 import (mysqldump) 해야한다는 것을 알게되었다. 어찌 생각해보면 당연했다. 16k -> 4k로 줄이는데 기존 데이터와 index가 문제가 될 것 같긴 했다;

You must, however, set the page size before the InnoDB tablespace is initialized.
All tablespaces (including per-table tablespaces, general tablespaces, undo tablespaces, temp tablespaces, etc.) must use the same page size.
You set the page size to 8KB by putting this line in your /etc/my.cnf file, in the [mysqld] section:
innodb_page_size=8K
You need to do this before the InnoDB tablespaces are initialized. If you want to change the page size later:

If you want to create a MySQL Instance with a new size for innodb_page_size
1.you must setup a new datadir with no data (but do not start mysqld)
2.set the innodb_page_size to 32768 in the my.ini for that new instance
3.start mysqld against that new my.ini and new datadir
4.mysqldump the data out of the old instance
5.load the mysqldump into the new instance

Give it a try and let us know !!!

//기존 16k 페이지인 DB에서 mysqldump로 전체 백업
[mysql@localhost svc]$ mysqldump -uroot -p@12345678 --routines --events --all-databases > local_ALL_DB.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[mysql@localhost svc]$ mysql -uroot -p < local_ALL_DB.sql
Enter password: 
ERROR 1071 (42000) at line 27529: Specified key was too long; max key length is 768 bytes

error log)

[Warning] InnoDB: Cannot add field `xxx` in table `SUB`.`TALBE1` because after adding it, the row size is 2017 which is greater than maximum allowed size (1982) for a record on index leaf page.
[Warning] InnoDB: Cannot add field `xxxx` in table `SUB`.`TALBE2` because after adding it, the row size is 2008 which is greater than maximum allowed size (1982) for a record on index leaf page.
[Warning] InnoDB: Cannot add field `xxxxx` in table `SUB`.`TALBE3` because after adding it, the row size is 1998 which is greater than maximum allowed size (1982) for a record on index leaf page.

안된다. 이유는 기존 16k 에서 생성한 varchar의 데이터가 innodb_large_prefix = on 으로 cnf가 설정되어있으면

767 byte에서 3072byte까지 늘어나게 되는데 이미 테이블에 생성된 데이터는 3072 byte 까지 컬럼에 데이터를 쌓게 되어있는 것을 강제로 4k로 변경하다보니 too long size 에러가 나는 것이었다.

참조사이트 : https://m.blog.naver.com/PostView.nhn?blogId=parkjy76&logNo=220977433117&proxyReferer=http%3A%2F%2Fwww.google.co.kr%2Furl%3Fsa%3Dt%26rct%3Dj%26q%3D%26esrc%3Ds%26source%3Dweb%26cd%3D3%26ved%3D2ahUKEwiHjMXJ3LfoAhXS3mEKHX0uDY0QFjACegQIAxAB%26url%3Dhttp%253A%252F%252Fm.blog.naver.com%252Fparkjy76%252F220977433117%26usg%3DAOvVaw1uoARWaLjxBllMwpQMuuBu

4) 실패

기존 데이터로 4k 테스트를 해보고 싶었지만 쌓인 데이터를 에러때마다 건건이 변경하지 않는한 restore가 불가능했다.

innodb_page_size는 한번 설정하면 변경하기 아주 어렵다는 것을 확인했다는 것에 만족하고 테스트 종료한다.

5) innodb_page_size 테스트관련 참조사이트

https://mariadb.com/resources/blog/does-innodb-page-size-matter/

InnoDB Page Size: Benchmarking Performance | MariaDB

Does InnoDB page size matter? We benchmark performance and look at response times to see if it does. View our results (with graphs).

mariadb.com

그래프를 다시 보니 이게 16k 32k 64k 테스트였다.. 스레드개수를 4k라고 착각했다니;;

(xfs /4k는 데이터 마이그레이션이 필요해서 날잡아서 나중에 다시한다.)

6) 결론

SAS를 SSD로 바꾸면서 최적의 튜닝옵션을 찾기위한 테스트였다.

일단 SSD의 성능이 우수한 것은 확인했고, 파일시스템 XFS를 제대로 테스트 못한 것은 아쉽다.

다만 현재 varchar를 3072byte까지 확장하여 데이터를 쌓은 DB에서 XFS/4K는 현실적으로 쓰기 어렵고 4k로 테스트한 것은 아니지만 XFS 자체가 그렇게 높은 성능을 보이는 것도 아니었다. 아래는 성능 테스트 결과값이다.

6-1) SAS VS SSD

SSD 압승이다. SATA2에서 10분이상 걸리던 것이 Ext4나 XFS나 6분정도로 수행되는 것으로 보면

디스크만 바꿔도(SAS -> SSD) 40%이상 개선효과가 있는 것을 확인했다.

6-2) Ext4 VS XFS default page size =16k Test!!

아래는 파일시스템 Ext4와 XFS를 테스트한 값이다.

테스트 query는 기존에 cost를 많이 써서 개선이 필요한 쿼리로 테스트했다. 많은 function과 많은 join이 있는..

횟수	Ext4 / page size 16k	XFS / page size 16k
1	5분 4초	6분 43초
2	5분 3초	5분 43초
3	5분 10초	5분 59초
4	5분 7초	5분 48초
5	5분 59초	5분 39초
6	5분 3초	5분 39초
7	5분 8초	6분 50초
8	5분 5초	6분 39초
9	5분 14초	5분 43초
10	5분 10초	5분 39초

결과적으로 보면 mysql에서 page size가 16k (default)라면 굳이 XFS 쓸 필요 없을 거 같다.

'RDB > mysql' 카테고리의 다른 글

[error] mysqldump: Got errno 28 on write (0)	2020.04.02
SSD 파티션 튜닝 2 (0)	2020.03.31
[에러] mysql DB disk 폴더 이동후 재시작 (0)	2020.03.26
mysqldump Table wildcard % 백업하기 (0)	2020.03.16
[error] This function has none of DETERMINISTIC, NO SQL .. (0)	2020.03.04

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

[에러] mysql DB disk 폴더 이동후 재시작

RDB/mysql 2020. 3. 26. 09:38

서비스 장비가 클라우드가 아니라면 lvm 구성된 디스크에 추가도 가능하지만

SSD등.. 디스크를 추가하고 파티션생성후 새로운 디스크로 mysql을 통째로 이관할 일이 생겼다.

상용 장비에는 실제 적용은 xtrabackup으로 백업후 restore를 했다. 이게 잴 안전한 방법이다.

하지만 테스트장비에서 테스트할 때는 귀찮기도 해서 그냥 폴더를 통쨰로 복사하는 방법을 선택했고

바로 재시작이 안되는 것을 확인했다.

에러로그를 확인하고 기록한다.

// mysqldb관련 폴터 통째로 tar압축후 를 /svc -> /svc2로 이동후 압축풀고 
// 권한을 mysql:mysql로 변경후 재시작을 했진만 아래 에러로 재시작이 안되는 것을 확인
[mysql@localhost ~]$ service mysqld start
Starting MySQL....The server quit without updating PID file[실패]2/mysqldb_data/localhost.localdomain.pid).

//error 로그를 확인)
mysqld: File '/svc/mysqldb_logs/binary/mysql-bin.000006' not found (Errcode: 2 - No such file or directory)

mysqldb 폴더를 /svc2로 이동했지만 binary 로그는 여전히 이전 /svc 폴더에서 찾고있어 에러가나는 것을 확인
binary 로그를 백업하고 전부 삭제함

[mysql@localhost binary]$ ls
mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.index

[root@localhost binary]# mkdir backup
[root@localhost binary]# ls
backup  mysql-bin.000001  mysql-bin.000002  mysql-bin.000003  mysql-bin.000004  mysql-bin.000005  mysql-bin.000006  mysql-bin.index
[root@localhost binary]# mv mysql-bin.* backup/
[root@localhost binary]# ls

[mysql@localhost ~]$ service mysqld start
Starting MySQL..                                           [  OK  ]

정상적으로 DB가 올라오는 것을 확인할 수 있다.

'RDB > mysql' 카테고리의 다른 글

SSD 파티션 튜닝 2 (0)	2020.03.31
SSD 파티션 튜닝 1 (0)	2020.03.26
mysqldump Table wildcard % 백업하기 (0)	2020.03.16
[error] This function has none of DETERMINISTIC, NO SQL .. (0)	2020.03.04
[장애] innodb Dead Lock 문제 (0)	2020.03.03

dung beetle

취미는 데이터 수집 직업은 MYSQL과 함께 일하는 DBA의 소소한 일상 이야기

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

'RDB'에 해당되는 글 62건

[튜닝] 니 형이 뭐야?

'RDB > mysql' 카테고리의 다른 글

slave online hot backup 구축

# 테스트기 환경

1) 백업전 준비

2) 백업

3) 재생성

3-1) apply log 복구

4) slave replication 연결

'RDB > mysql' 카테고리의 다른 글

[튜닝] mysql function 성능 이슈

'RDB > mysql' 카테고리의 다른 글

mysql 컬럼 타입 datetime vs timestamp 차이

'RDB > mysql' 카테고리의 다른 글

[error] mysqldump: Got errno 28 on write

'RDB > mysql' 카테고리의 다른 글

SSD 파티션 튜닝 2

'RDB > mysql' 카테고리의 다른 글

SSD 파티션 튜닝 1

'RDB > mysql' 카테고리의 다른 글

[에러] mysql DB disk 폴더 이동후 재시작

'RDB > mysql' 카테고리의 다른 글

공지사항

카테고리

태그목록

글 보관함

달력

링크

dung beetle

LATEST FROM OUR BLOG

LATEST COMMENTS

BLOG VISITORS

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

« 2025/04 »
일	월	화	수	목	금	토
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30