대용량 조회쿼리부분 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을 뜬다.
고객의 요구사항은 테이블의 컬럼을 선언할 때 데이형을 TEXT 정도가 아니면 받기 어렵다. 결국 문자열 검색을 요구하고.. like 검색이 시작되고..
데이터가 늘어나면서 한글 문자열 검색은 like로 슬슬 성능이 떨어지니 다른 대안을 검토 중이다.
1.사전요구사항
1) like 검색보다 빨라야 한다.
2). like 검색만큼 정확해야 한다.
2. 구글링
1. 기존 mysql 에서 지원하는 FullText 인덱스 사용 (mysql 5.6 이상만 지원)
FULLTEXT 인덱스, n-gram 파서 인덱스
2. 검색엔진도입
스핑크스, 엘라스틱서치,루씬
3. 작업시작
1. Mysql 내 지원인덱스 적용
#인덱스 적용전
보통 와일드카드 검색을 많이 한다.
seelct * from TB_CM_ADI_xxx
where CONTENTS like '%서비스%';
--> 수행시간 0.5초
--> 결과값 3119건
#Plan
1 SIMPLE TB_CS_xxx TB_CM_ADI_xxx ALL 105344 11.11 Using where
# FULLTEXT 인덱스적용
#구분자(Stopword) 기법 전문의 내용을 공백이나 탭(띄어쓰기) 또는 마침표와 같은 문장 기호, 그리고 사용자가 정의한 문자열을 구분자로 등록합니다. 구분자 기법은 이처럼 등록된 구분자를 이용해 키워드를 분석해 내고, 결과 단어를 인덱스로 생성해 두고 검색에 이용하는 방법을 말합니다. 일반적으로 공백이나 쉼표 또는 한국어의 조사 등을 구분자로 많이 사용합니다. MySQL의 내장 전문 검색(FullText search) 엔진은 구분자 방식만으로 인덱싱할 수 있습니다. 구분자 기법은 문서의 본문으로부터 키워드를 추출해 내는 작업이 추가로 필요할 뿐, 내부적으로는 B-Tree 인덱스를 그대로 사용합니다. 전문 검색 인덱스의 많은 부분은 B-Tree의 특성을 따르지만 전문 검색 엔진을 통해 조회되는 레코드는 검색어나 본문 내용으로 정렬되어 조회되지는 않습니다. 전문 검색에서 결과의 정렬을 일치율(Match percent)이 높은 순으로 출력되는 것이 일반적입니다. 구분자 기법으로 전문 검색을 사용할 때는 문장 기호뿐 아니라 특정 단어를 일부러 구분자로 등록할 수도 있습니다. 예를 들어 MySQL 매뉴얼을 페이지 단위로 잘라서 테이블에 저장하고 전문 검색을 구현한다고 해봅시다. 이 경우 테이블의 모든 레코드에는 "MySQL"이라는 단어가 포함돼 있을 것입니다. 이 경우 "MySQL"이라는 단어로 검색하면 테이블의 모든 레코드가 일치하므로 검색의 효과가 없어집니다. 이럴 때는 "MySQL"이라는 단어를 구분자에 등록하고 전문 검색 인덱스에 포함하지 않게 해주는 것이 좋습니다. 많은 인터넷 사이트에서 "Stopword"를 "불용어"로 해석하고 있지만, 이보다는 "구분자"라는 표현이 더 적절한 해석이라고 볼 수 있습니다. 이 기법의 알고리즘에서 "Stopword"는 "검색에 사용할 수 없다"보다는 "검색어를 구분해주는 기준(문장 기호나 특정 문자열)이다"의 의미가 더 강하기 때문입니다.
// TEXT 컬럼 중에 잴 데이터 많은 테이블을 찾는다.
select * from information_schema.COLUMNS
where TABLE_SCHEMA = 'PRIME'
and COLUMN_TYPE = 'TEXT';
//인덱스 생성 전에 mysql parameter 설정값을 확인한다.
//문자열 검색 variable 확인
//MyIsam 은 아래값 수정
mysql> show global variables like 'ft_min%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| ft_min_word_len | 4 |
+-----------------+-------+
1 row in set (0.00 sec)
//Innodb는 이거 수정한다.
mysql> show global variables like 'innodb_ft_min%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3 |
+--------------------------+-------+
1 row in set (0.00 sec)
//2글자 검색이다. 다 2로 수정하고 DB를 재시작한다.
vi /etc/my.cnf
---------------------------------------------
ft_min_word_len =2
innodb_ft_min_token_size = 2
//FullText 인덱스 넣어본다.
alter table TB_CM_ADI_SVC
add FULLTEXT index IDX_CM_ADI_SVC_02 (CONTENTS);
select * from TB_CM_ADI_xxx where MATCH(CONTENTS) AGAINST ('서비스'); 1 SIMPLE TB_CS_xxx fulltext IDX_CM_ADI_xxx_01 IDX_TB_CS_xxx_01 0 const 1 100 Using where; Ft_hints: sorted
인덱스 타는 것은 확인했지만 와일드카드 검색이 안된다..
select * from TB_CS_xxx
where MATCH(CONTENTS) AGAINST ('*서비스*' in boolean mode)
#order by xxx_SEQ;
-->수행시간 30초
-->결과값 2775건
위처럼 와일드 카드 검색을 할 수 있지만 결과값은 like검색과 차이가 있다.
일단 순서정렬이 서비스와 가장가까운 값부터 정렬하도록 해서 만약 seq가 있다면 order by를 따로 써야할 거 같고..
결과값도 2775건이다. like검색과 비교해보니
예를 들어 _서비스 처럼 서비스 문자열 앞에 특수문자가 오면 검색이 안되었다.
그리고 가장 중요한건 성능이 별로다 like가 더 빠르다. 굳이 쓸 이유 없다.
# N-gram 파서
N-그램(N-Gram) 기법 하지만 각 국가의 언어는 띄어쓰기가 전혀 없다거나 문장 기호가 전혀 다른 경우가 허다합니다. 이런 다양한 언어에 대해 하나의 규칙을 적용해 키워드를 추출해내기란 쉽지 않습니다. 또한 구분자 방식은 추출된 키워드의 일부(키워드의 뒷부분)만 검색하는 것은 불가능하다는 단점도 있습니다. 이러한 부분을 보완하기 위해 지정된 규칙이 없는 전문도 분석 및 검색을 가능하게 하는 방법이 N-그램이라는 방식입니다.
N-그램이란 본문을 무조건적으로 몇 글자씩 잘라서 인덱싱하는 방법입니다. 구분자에 의한 방법보다는 인덱싱 알고리즘이 복잡하고, 만들어진 인덱스의 크기도 상당히 큰 편입니다. 트리톤(Tritonn)이나 스핑크스(Sphinx)에서는 다른 인덱싱 방법도 제공하지만, 이 알고리즘이 주로 사용됩니다. N-그램에서 n은 인덱싱할 키워드의 최소 글자(또는 바이트) 수를 의미하는데, 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱하는 2-Gram(또는 Bi-Gram이라고도 한다) 방식이 많이 사용됩니다. 2-Gram 인덱싱 기법은 2글자 단위의 최소 키워드에 대한 키를 관리하는 프론트엔드(Front-end) 인덱스와 2글자 이상의 키워드 묶음(n-SubSequence Window)를 관리하는 백엔드(Back-end) 인덱스 2개로 구성됩니다. 인덱스의 생성 과정은 다음과 같이 2가지 단계로 나눠서 처리됩니다. 첫 번째 단계로, 문서의 본문을 2글자보다 큰 크기로 블록을 구분해서 백엔드 인덱스(3)을 생성 두 번째 단계로, 백엔드 인덱스의 키워드들을 2글자씩 잘라서 프론트엔드 인덱스(6)을 생성
n=1: 'a', 'b', 'c', 'd' n=2: 'ab', 'bc', 'cd' n=3: 'abc', 'bcd' n=4: 'abcd' 그리고, 기본적으로 InnoDB에서 제공해주는 ngram의 최소 토큰 사이즈(ngram_token_size)는 2이고, 위에서 “n=2” 부터 토큰을 만들게 됩니다. 당연한 이야기겠지만, n-수치가 낮을수록 토큰 수가 많아질 것이기에, 모든 검색어들이 3글자부터 시작된다면 이 수치를 3으로 상향 조정하는 것도 인덱싱 관리 및 사이즈 안정성에 도움이 되겠습니다.
//인덱스 적용
ALTER TABLE TB_CM_ADI_xxx ADD FULLTEXT INDEX IDX_CM_ADI_xxx_01(CONTENTS) WITH PARSER ngram;
//기본이 2다 굳이 수정할 필요없다.
mysql> show global variables like 'ngram_token_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+
1 row in set (0.01 sec)
#테스트
select * from TB_CM_ADI_xxx where MATCH(CONTENTS) AGAINST ('+서비스+' in boolean mode)
-->수행시간 0.4초
-->결과값 3119건
like %서비스% 성능비슷하고 (와일드카드 검색) 결과값도 일치한다.
대용량테이블로 검색테스트는 해보지 않았지만 이정도면 like검색을 대체해도 될 거 같다.
select * from TB_CM_ADI_xxx where MATCH(CONTENTS) AGAINST ('서비스' in natural language mode)
-->수행시간 0.4초
--> 결과값 8159건 --> 자연어로 와일드카드 검색시 ngram default 값이 2자리라서 서비스 를 검색했지만 '서비' ,'비스'로도 검색되서
