개발에서 반영요청한 테이블 스키마를 보던 중에 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와 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까지 생성할 수 있다.
#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는 순차적이지 않습니다).
[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 에러가 나는 것이었다.
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 ]
ERROR 1418 (HY000) at line 25: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
#확인해보면 함수생성관련 value가 off인것을 알수 있다.
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
#수정
mysql> SET GLOBAL log_bin_trust_function_creators = ON;
이제 함수를 생성할 수 있다.
mysql 5.5 defualt 옵션으로 발생하는 것으로 5.7이상은 default가 on으로 되어있다.
mysql innodb 엔진은 기본적으로 오라클에 버금가는 우수한 퍼포먼스를 가지고 있다고 생각했다.
하지만 default 옵션이라는 것이 오늘 발목을 잡았다.
결론적으로 상용서비스에 Dead Lock이 발생하였고 120초 동안 장애가 발생하였다.
다행이도 innodb는 데드락이 발생하면 120초 후에 unlock 하는 기능이 default로 구현되어있다.
원인은 mysql innodb 엔진의 트랜잭션 Level 설정으로 발생한 것인데 innodb 엔진의 Default 기본설정인 REPEATABLE-READ이다. REPEATABLE-READ에서는 현재 Select버전을 보장하기 위해 동일세션 내에 Snapshot을 이용하는데, 이경우 해당 데이터에 관하서 암묵적으로 Lock과 비슷한 효과가 발생한다고 한다. 즉 Select 작업이 종료될 때까지 해당 데이터 변경 작업이 불가능하다는 것까지 확인하였다.
다만 select count(*) into value from .. 쿼리에 데드락이 발생한 것은 좀 의외였고
snapshot 기능이 REPEATABLE-READ 설정시 테이블 통쨰로 떠서 동일세션 내에서는 한번뜨고 변경되지 않기 때문에 동일 세션에서 같은 위치에 값을 불러서 변수에 담을 때 데드락이 걸리는 가슴아픈 상황을 맞이하였다.
#장애로그
mysql> SHOW ENGINE INNODB STATUS\G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-03-03 12:47:25 0x7f6187421700
*** (1) TRANSACTION:
TRANSACTION 522522335, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3
MySQL thread id 10194076, OS thread handle 140057084225280, query id 417845128547 172.xx.x.xxx mhpapp statistics
select count(*) into varCnt
from TALBE_XXX
where TYP_CD='ZZZ'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table TABLE_XXX trx id 522522335 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 43555354; asc CUST;;
1: len 6; hex 00001f250de6; asc % ;;
2: len 7; hex 4000005c6d1875; asc @ \m u;;
3: len 4; hex 8022dc6b; asc " k;;
"*** (2) TRANSACTION:
TRANSACTION 522522086, ACTIVE 10 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 4
MySQL thread id 10200077, OS thread handle 140056857810688, query id 417845128829 172.xx.x.xx mhpapp statistics
select count(*) into varCnt
from TALBE_XXX
where TYP_CD='YYY'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TABLE_XXX` trx id 522522086 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
#Lock 발생 로그부분 확인
-->동일지점을 호출
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TALBE_XXX` trx id 522522335 lock_mode X locks rec but not gap waiting
RECORD LOCKS space id 1247 page no 3 n bits 72 index PRIMARY of table `TALBE_XXX` trx id 522522086lock_mode X locks rec but not gap
해결방법은 isolation level을 READ-COMMITED로 바꾸면 되는데, 상용서비스에 DB의 트랜잭션 레벨을 테스트없이 변경한다는 것은 말도 안되는 것이고, 리플리케이션 관련 binary log 부분도 변경이 필요해서 당장 무중단으로 반영하긴 어렵다는 것을 확인했다.
일단 처음 발생한 것이고 서비스 고객의 홈페이지 or 어플사용량이 증가하면서 대량 트랜잭션이 발생할 때 간혹 발생하는 현상이라는 점과 DEAD LOCK에 빠지더라도 감지해서 자동으로 락을 푸는 기능이 있기 때문에 일단은 그대로 두고
지속적으로 발생하면 READ-COMMITED 으로 변경하고 리플리케이션 binary log도 격리수준에 맞춰서 MIXED에서 Row based로 바꿔야할 거같다.
좀 더 모니터링을 해보고 결정한다.
#Transaction isolation Level 정리
1) Read uncommitted - 다름 트랜잭션이 Commit 전 상태를 볼 수 있음 Binary log가 자동으로 Row Based 로 기록됨 (Statement 설정 불가, Mixed 설정시 자동 변환)
2) Read-Committed - Commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 특정 타 트랜잭션이 Commit을 수행하면 해당 데이터 를 Read할 수 있음 Binary log가 자동으로 Row Based로 기록됨 (Statament 설정 불가, Mixed 설정 시 자동 변환)
3) Repeatable Read - Mysql InnoDB 스토리지 엔진의 Default isolation level Select 시 현재 데이터 버전의 Snapshot을 만들고, 그 Snapshot으로부터 데이터를 조회 동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함
4) SERIALIZBLE - 가장 높은 isolation level 로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 Shared lock이 걸림 가장 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가
#REPEATABLE-READ vs READ-COMMITED 차이정리 동일세션 내에 snapshot을 이용하는 것은 같지만 REPEATABLE-READ은 동일세션이라면 한번 뜨고 끝이지만, READ-COMMITED은 동일세션이라도 이벤트 발생시마다 snatshot을 뜬다.
