MYSQL 복제 실패 - The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master

DB Troubleshooting/MySQL 2023. 2. 23. 13:48

현상

Last_IO_Error: The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master. Error: 

 

해소

server_uuid master 와slave를 다르게 설정

 

auto.cnf 파일명 삭제

 mv auto.cnf auto.cnf.bak

 

my.cnf 파일 수정

[mysqld]
server-id  = 111  --> 222

 

mysql 재시작

 systemctl restart myslqd

 

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MySQL 접속 에러 error while loading shared libraries: libssl.so.1.1 / libcrypto.so.1.1  (0) 2022.08.26
mysql에서 잘못된 numa 사용으로 swap 발생하여 성능 지연 발생  (0) 2022.08.02
MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  (0) 2022.04.05
MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
  • By SJinDBA

MySQL 접속 에러 error while loading shared libraries: libssl.so.1.1 / libcrypto.so.1.1

DB Troubleshooting/MySQL 2022. 8. 26. 14:36

MySQL 접속 시 에러 발생

mysql: error while loading shared libraries: libssl.so.1.1: cannot open shared object file: No such file or directory

mysql: error while loading shared libraries: libcrypto.so.1.1: cannot open shared object file: No such file or directory

 

수정 방법

/user/lib64/ 폴더에, 아래 라이브러리 복사 후 mysql 계정으로 소유권 변경

-rwxr-xr-x  1 mysql mysql 657635 Dec  9  2019 libssl.so.1.1
-rwxr-xr-x   1 mysql mysql 3056988 Aug 26 01:21 libcrypto.so.1.1

 

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MYSQL 복제 실패 - The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master  (0) 2023.02.23
mysql에서 잘못된 numa 사용으로 swap 발생하여 성능 지연 발생  (0) 2022.08.02
MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  (0) 2022.04.05
MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
  • By SJinDBA

mysql에서 잘못된 numa 사용으로 swap 발생하여 성능 지연 발생

DB Troubleshooting/MySQL 2022. 8. 2. 14:04

현상

서비스 도중 swap을 사용하여 서비스 지연발생

 

원인

OS상 numa가 활성화 되어 있으나 mysql에서는 사용하지 않도록 설정되어, node간 균등하지 않게 memory를 사용함.

memory를 많이 사용한 node에서 memory가 부족하다고 판단하여 swap을 사용하여 처리 지연 발생

 

분석

swap 사용 유무 체크

  3.4G swap 사용중

~]# free -h
              total        used        free      shared  buff/cache   available
Mem:            62G         53G        3.6G        3.1G        4.9G        4.7G
Swap:          4.0G        3.4G        645M

OS numa 사용 확인
  numa를 사용하는 경우 node 0, node 1 두개로 표현됨
  아래 경우는 OS상 numa를 사용하는 형태
  node 0 free : 208MB
  node 1 free : 3123MB

~]# numactl --hardware
available: 2 nodes (0-1)
node 0 cpus: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
node 0 size: 32436 MB
node 0 free: 208 MB
node 1 cpus: 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63
node 1 size: 32767 MB
node 1 free: 3123 MB
node distances:
node   0   1
  0:  10  21
  1:  21  10

numa 메모리 실제 사용량 확인
  Active(anon)이 App에서 할당하여 사용하는 메모리
  아래는 20G, 15G로 불균현 확인됨
  node 0 : 20G 사용중 8G Free
  node 1 : 15G 사용중 8G Free

~]# numastat -cm

Per-node system memory usage (in MBs):
                 Node 0 Node 1 Total
                 ------ ------ -----
MemTotal          32436  32768 65204
MemFree            8271   8287 16558
MemUsed           24165  24481 48646
Active            20294  16002 36296
Inactive           2359   7289  9648
Active(anon)      20275  15289 35564
Inactive(anon)     2338   6588  8926
Active(file)         19    713   732
Inactive(file)       21    701   723
Unevictable           0      0     0
Mlocked               0      0     0
Dirty                 0      0     0
Writeback             0      0     0
FilePages           126   5531  5657
Mapped                1    114   115
AnonPages         22528  17760 40288
Shmem                 1   3205  3206
KernelStack           7     38    46
PageTables           43     69   112
NFS_Unstable          0      0     0
Bounce                0      0     0
WritebackTmp          0      0     0
Slab                206    210   416
SReclaimable        144     66   210
SUnreclaim           62    144   206
AnonHugePages         2      0     2
HugePages_Total       0      0     0
HugePages_Free        0      0     0
HugePages_Surp        0      0     0

mysqld에서 사용하는 numa 메모리 확인
  mysql pid 확인 --> mysql
  node 0 : 22G
  node 1 : 17G

~]# ps -ef | grep mysql
mysql    50416     1  0 Mar03 ?        00:00:00 ~~~/mysql/bin/mysqld_safe --defaults-file=~~~/my.cnf --pid-file=~~~mysql.pid
mysql    51489 50416  7 Mar03 ?        11-03:43:53 ~~~/mysql/bin/mysqld --defaults-file=~~~/my.cnf --basedir=~~~/mysql --datadir=~~~/data/ ~~~~~~( 실행 옵션 )~~~~~~~~~

~]# numastat 51489

Per-node process memory usage (in MBs) for PID 51489 (mysqld)
                           Node 0          Node 1           Total
                  --------------- --------------- ---------------
Huge                         0.00            0.00            0.00
Heap                         9.57           13.82           23.39
Stack                        0.00            0.02            0.02
Private                  22322.57        17031.40        39353.97
----------------  --------------- --------------- ---------------
Total                    22332.15        17045.23        39377.38

node별 메모리 사용량 확인

  node0은 local_node사용이 높고
  node1은 other_node사용이 높음

 ~]# numastat
                           node0           node1
numa_hit              3671867451     16116171368
numa_miss               47375279     12004935107
numa_foreign         12004935107        47375279
interleave_hit             20488           19934
local_node            3671943494     16124076268
other_node              47299236     11997030207

MySQL numa 설정 확인

  innodb_numa_interleave OFF 로 설정되어 있지 않음

  만약 ON으로 변경 시, mysql 재시작 필요

mysql> show variables like '%numa%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_numa_interleave | OFF   |
+------------------------+-------+
1 row in set (0.00 sec)

OS numa policy 확인
  default 청잭으로 numa 사용중 ( default 정책의 경우 app이 설치된 node를 우선적으로 할당하는 구조 )
  cpu는 64개

~]# numactl --show
policy: default
preferred node: current
physcpubind: 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
cpubind: 0 1
nodebind: 0 1
membind: 0 1

numa 정책 옵션 설정
  bind : 특정 node 에서 메모리를 할당 받도록 강제하는 정책
  preferred : 특정 node 에서 메모리를 먼저 할당 받도록 하는 정책, 해당 node 메모리가 부족할 경우 다른 node 에서 할당
  interleave : 여러 node 에서 균등하게 할당받도록 하는 정책

Option
Descriptions
--membind=nodes, -m nodes
지정된 노드에 있는 메모리만 할당
해당 노드 메모리가 모두 차면 swap이 발생한다.
--cpunodebind=nodes, -N nodes
지정된 노드에 있는 CPU에서만 프로세스가 돌아가도록 설정
메모리 할당도 해당 노드해서만 이루어진다. 메모리의 지역성으로 성능이 올라갈 수 있지만, 반대쪽 노드의 CPU를 잘 활용하지 못하는 단점이 있다.
membind 보다 장점은 더이상 지정된 노드에 메모리가 없으면 다른 노드의 메모리를 활용한다는 점이다.
--physcpubind=cpus, -C cpus
cpunodebind 와 다른점은 노드를 지정하지 않고 CPU 번호를 지정한다.
즉 node0, node1에 있는 CPU들을 지정할 수 있다.
--preferred=node
지정된 노드에 있는 메모리를 우선적으로 할당
membind와 차이점은 지정된 노드의 메모리가 차면 다른 노드의 메모리를 활용한다. 상황에 따라서는 cpunodebind 와 다르지 않게 동작할 수도 있다.
--interleave=nodes, -i nodes
지정된 여러 노드에서 공평하게 메모리를 할당 (라운드로빈)
all 로 하면 모든 노드로 분배된다.
예제)
numactl --physcpubind=+0-4,8-12 myapplic arguments Run myapplic on cpus 0-4 and 8-12 of the current cpuset.
numactl --interleave=all bigdatabase arguments Run big database with its memory interleaved on all CPUs.
numactl --cpubind=0 --membind=0,1 process Run process on node 0 with memory allocated on node 0 and 1.
numactl --cpubind=0 --membind=0,1 -- process -l Run process as above, but with an option (-l) that would be confused with a numactl option.
numactl --preferred=1 numactl --show Set preferred node 1 and show the resulting state.
numactl --interleave=all --shmkeyfile /tmp/shmkey Interleave all of the sysv shared memory region specified by /tmp/shmkey over all nodes.
numactl --offset=1G --length=1G --membind=1 --file /dev/shm/A --touch Bind the second gigabyte in the tmpfs file /dev/shm/A to node 1.
numactl --localalloc /dev/shm/file Reset the policy for the shared memory file file to the default localalloc policy.

 

대응

1. OS와 MySQL 둘다 numa를 사용하거나, 사용하지 않도록 설정을 맞추어줌

    >> OS 설정 변경을 위해서는 장비 재시작, MySQL 설정 변경을 위해서는 DB 재시작이 필요함.

          서비스 도중이라, 이후 점검시 진행

2. OS 물리 메모리 free공간을 확보.
    >> 배치량이 많지 않아, MySQL memory buffer pool을 줄여 물리 메모리 공간 확보

3. numa 사용으로 불균등하게 메모리가 사용되어 swap이 발생함.
    cpu와 memory를 효율적으로 사용하지 못하겠지만 최대한 swap을 사용하지 않도록 하기 위해 아래 설정을 실행

    numactl --interleave=all mysqld

 

참고

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=sory1008&logNo=221222077917

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MYSQL 복제 실패 - The slave I/O thread stops because a fatal error is encountered when it try to get the value of SERVER_ID variable from master  (0) 2023.02.23
MySQL 접속 에러 error while loading shared libraries: libssl.so.1.1 / libcrypto.so.1.1  (0) 2022.08.26
MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  (0) 2022.04.05
MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
  • By SJinDBA

Unable to manually failover a SQL Availability Group ( Cluster Log 추출 )

DB Troubleshooting/MSSQL 2022. 4. 7. 23:17

현상

MSSQL AlwaysOn 설정 이후 auto failover 혹은 manually failover 가 실패

에러 로그

“Failed to perform a manual failover of the availability group“…..“Failed to bring availability group <name> online. The operation timed out”……“41131”

해결

Event Viewer UI를 통한 확인

  1. Go to Event Viewer
  2. Expand Applications and Services Logs
  3. Expand Microsoft
  4. Expand Windows
  5. Expand Failover Clustering
The log file is stored in system32\winevt\Logs

PowerShell을 사용하여 Clustger Error Log 확인

Get-ClusterLog -Destination C:\temp

“The use does not have permission to perform this action. (297)” 문구 확인

권한 부여

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM];
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM];

 

참고

https://geekshangout.com/unable-to-manually-failover-a-sql-availability-group/

 

저작자표시 비영리 변경금지

'DB Troubleshooting > MSSQL' 카테고리의 다른 글

MSSQL 이중화(AlwaysOn) 구성 후 쿼럼(Quorum) 장비 이슈로, 쿼럼 삭제 or 재구성 작업  (0) 2022.04.07
[MSSQL] DB single_user 변경시 deadlock 이슈  (0) 2021.12.29
  • By SJinDBA

MSSQL 이중화(AlwaysOn) 구성 후 쿼럼(Quorum) 장비 이슈로, 쿼럼 삭제 or 재구성 작업

DB Troubleshooting/MSSQL 2022. 4. 7. 22:23

현상 

Quorum 상태가 Online 으로 수정이 되지 않고 Fail로 유지

원인

쿼럼 서버 장비가 다운되거나 통신이 되지 않는 경우 발생 할 수 있음

수정

Fail 상태로 유지되는 쿼럼의 경우 일반적인 방법의 삭제(우 클릭 후 Remove)가 되지 않는 경우가 많음

이 경우 쿼럼 설정 창에서 쿼럼을 제거하고 다시 구성 진행

 

Failover Cluster Manager -> More Actions -> Configure Cluster Quorum Settings ...

3번째 매뉴의 'Select Quorum Witness'에서 'Do not configure a quorum witness'를 선택하여 쿼럼 삭제 후 재구성

저작자표시 비영리 변경금지

'DB Troubleshooting > MSSQL' 카테고리의 다른 글

Unable to manually failover a SQL Availability Group ( Cluster Log 추출 )  (0) 2022.04.07
[MSSQL] DB single_user 변경시 deadlock 이슈  (0) 2021.12.29
  • By SJinDBA

MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

DB Troubleshooting/MySQL 2022. 4. 5. 23:43

현상

권한 부여 및 DB 복원시 실행이 되지 않고 에러 발생

 

원인

실행 계정에 SYSTEM_USER 권한 부재

복원하는 DB의 procedure가 있는경우, definer로 설정되지 않은 계정으로 복원 시도

 

에러 문구

ERROR 1227 (42000) at line 375: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

 

해결

복원하는 DB의 procedure definer 이슈인 경우, definer로 설정된 계정으로 DB 복원(procedure 생성) 진행

// system_user 권한 추가 형태

[linux]# mysql -u admin_user -p -P 3306 TestDB < TestDB.sql
ERROR 1227 (42000) at line 375: Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

mysql> show grants for 'admin_user'@'localhost';
+---------------------------------------------------------------------------------------------
| Grants for admin_user@localhost
+---------------------------------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO `admin_user`@`localhost` WITH GRANT OPTION
+---------------------------------------------------------------------------------------------
1 rows in set (0.00 sec)

mysql> grant system_user on *.* to 'admin_user'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show grants for 'admin_user'@'localhost';
+---------------------------------------------------------------------------------------------
| Grants for admin_user@localhost
+---------------------------------------------------------------------------------------------
| GRANT ALL PRIVILEGES ON *.* TO `admin_user`@`localhost` WITH GRANT OPTION
| GRANT SYSTEM_USER ON *.* TO `admin_user`@`localhost`
+---------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)


[linux]# mysql -u admin_user -p -P 3306 TestDB < TestDB.sql
[linux]#

 

참고

https://dev.mysql.com/blog-archive/the-system_user-dynamic-privilege/

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MySQL 접속 에러 error while loading shared libraries: libssl.so.1.1 / libcrypto.so.1.1  (0) 2022.08.26
mysql에서 잘못된 numa 사용으로 swap 발생하여 성능 지연 발생  (0) 2022.08.02
MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
MySQL max_allowed_packet 설정 오류로 Replication Error  (0) 2022.03.17
  • By SJinDBA

MySQL db down - btrfs filesystem full

DB Troubleshooting/MySQL 2022. 4. 4. 22:16

내용

mysql 재기동이 되지 않는 상황

 

원인

btrfs 파일시스템 메타데이타 100%으로, 실 사용률 50% 이하 임에도 불구하고 100% 사용률로 인한 mysql error 발생

 

btrfs ?

Btrfs(B-tree file system 또는 Butter file system)는 데이터 관리 및 안정성을 강화한 최신 파일시스템이며 페이스북의 크리스 메이슨이 개발을 지휘하여 2013년 이후 안정화되어 사용

btrfs vs ext4

Btrfs 장점

실시간 오류 정정 기능과 스냅샷을 이용하여 볼륨 복원이 가능하여 장애 복원성이 좋습니다.

또한 압축 기능 제공과 SSD 드라이브에 최적화되어 있어 일부 실험에서는 압축+SSD 조합을 통해 EXT4 보다 4배 이상의 성능을 보여주는 탁월한 읽기/쓰기 성능을 제공합니다.

 

Btrfs 단점

장점이던 스냅샷이 이번에는 단점입니다. ^^

스냅샷 이미지의 저장을 위해 디스크 공간을 추가로 사용하며 스냅샷 용량 증가의 특성상 디스크 사용량 예측이 어려운 것이 단점입니다.

또한, 스냅샷 이미지 생성과 디스크 단편화 문제로 자동 조각 모음을 처리할 때 성능 저하가 발생할 수 있습니다

 

EXT4 장점

EXT1, EXT2, EXT3을 거쳐 발전한 EXT4는 데스크톱과 워크스테이션 등에서 오랫동안 성능을 검증 받았습니다. 또한 Btrfs보다 범용적인 파일시스템으로 호환성이 좋고 다른 파일시스템보다 CPU 사용율이 낮습니다.

 

EXT4 단점

디스크 블록 지연 할당에 의한 데이터 유실 가능성이 있습니다. EXT4는 성능 향상을 위해 데이터를 디스크에 즉시 기록하지 않는데 이런 EXT4의 특성으로 데이터를 디스크에 기록하기 전에 발생하는 시스템 충돌이나 전원 문제로 데이터 유실 가능성이 있습니다. 물론 이런 문제에 대비하여 저널링 기능이 있어 복구할 수 있고 OS 레벨에서도 보정하지만 이런 동작은 성능을 저하시키고 문제 발생 가능성에 대비를 해야하는 등 운영자를 고민하게 만듭니다.

 

해결

btrfs rebalancing

// 용량 확인
# df -h
Filesystem              Size  Used Avail Use% Mounted on
/dev/mapper/sda4_crypt   38G   12G   13M 100% /

// btrfs filesystem 확인
# btrfs filesystem df /
Data, single: total=9.47GiB, used=9.46GiB
System, DUP: total=8.00MiB, used=16.00KiB
System, single: total=4.00MiB, used=0.00
Metadata, DUP: total=13.88GiB, used=1.13GiB
Metadata, single: total=8.00MiB, used=0.00

// btrfs rebalancing
btrfs balance start -m /mountpoint
btrfs balance start -m /mnt

$ sudo df -h /
Filesystem              Size  Used Avail Use% Mounted on
/dev/mapper/sda4_crypt   38G   12G   26G  31% /

 

참고

https://askubuntu.com/questions/464074/ubuntu-thinks-btrfs-disk-is-full-but-its-not
https://www.ihee.com/144 [희야의 소소한 일상]

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

mysql에서 잘못된 numa 사용으로 swap 발생하여 성능 지연 발생  (0) 2022.08.02
MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  (0) 2022.04.05
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
MySQL max_allowed_packet 설정 오류로 Replication Error  (0) 2022.03.17
InnoDB Engine Auto_increment 동작  (0) 2022.03.15
  • By SJinDBA

MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리

DB Troubleshooting/MySQL 2022. 3. 17. 20:03

현상

threads 및 connection 증가

mysqldump FLUSH TABLES 지속

해결방안

* 고비용 쿼리 실행 시점과 backup 실행 시점을 겹치지 않도록 조정

* master 장비에서 백업이 실행되고 있다면, 이중화 구성 후 Slave 장비에서 백업 실행

* 고비용 쿼리 튜닝

* 장비 성능 점검 후 메모리 증설
* Flush method 변경 및 Swap 강제 해제

# /etc/my.cnf
innodb_flush_method = O_DIRECT
 
# shell
swapoff -a
swapon -a

* Kernel parameter 변경

# /etc/sysctl.conf
vm.swappiness = 60 -> 0
 
# shell
swapoff -a
swapon -a
저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MySQL - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation  (0) 2022.04.05
MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL max_allowed_packet 설정 오류로 Replication Error  (0) 2022.03.17
InnoDB Engine Auto_increment 동작  (0) 2022.03.15
[MySQL] _INFORMATION_SCHEMA.PARAMETERS 조회로 인한 Checking Permission 부하  (0) 2021.12.29
  • By SJinDBA

MySQL max_allowed_packet 설정 오류로 Replication Error

DB Troubleshooting/MySQL 2022. 3. 17. 12:55

에러 로그

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event ‘binlog.000201’ at 5480571

분석

slave max_allowed_packet size (  1 M )

max_allowed_packet
서버에 전달 또는 서버로부터 받게 되는 패킷의 최대 길이
MySQL에서의 Packet이라 함은 Single SQL 문 또는 Replication이 이루어지는 Master에서 Slave로의 Binary Log Event를 의미한다.
MySQL 서버의 DEFAULT max_allowed_packet size 는 1 M 이다.

에러 로그는 master의 bin log packet이 허용할 수 있는 최대치를 초과했다는 에러 내용

해결 방법

mysql> set global max_allowed_packet = 1024*1024*100
mysql> set session max_allowed_packet = 1024*1024*100

재시작시 설정값 저장을 위해 my.cnf 파일도 함께 수정

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MySQL db down - btrfs filesystem full  (0) 2022.04.04
MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
InnoDB Engine Auto_increment 동작  (0) 2022.03.15
[MySQL] _INFORMATION_SCHEMA.PARAMETERS 조회로 인한 Checking Permission 부하  (0) 2021.12.29
[MySQL] Stored Procedure Memory Leak  (0) 2021.12.29
  • By SJinDBA

InnoDB Engine Auto_increment 동작

DB Troubleshooting/MySQL 2022. 3. 15. 23:59

현상

MySQL 재시작 후 Table 내 AUTO_INCREMENT 값이 감소하는 현상이 발생

(예. 재시작 전: Auto_Increment 4000 , 재시작 후 Auto_Increment 3800)

원인

요약: MySQL InnoDB에서는 마지막 AUTO_INCREMENT을 메모리에만 저장

상세:

MySQL에서 InnoDB 엔진의 경우 AUTO_INCREMENT 값을 메모리에 저장하고 Insert 시 증가

 * 이슈 발생의 경우 tb_user 에서 data를 삭제 시 동일한 구조의 tb_user_del 값으로 insert 후 삭제하게 되는데

Table 의 AUTO_INCREMENT 값이 4000인 상태에서 SEQ가 3800~4000까지의 data를 tb_user_del 로 insert하고 삭제한 후 MySQL를 재시작하게 되면서 또 다시 SEQ가 3800부터 insert됨
(SEQ는 tb_user와 tb_user_del 모두 PK로 설정됨) 

재시작 이후 SEQ가 3800~4000인 data를 tb_user_del에 insert하면서 PK충돌이 일어나게 됨

해결 방법

AUTO_INCREMENT 를 MySQL 재시작시에도 유지하기 위해서는 엔진을 MyIsam으로 변경하거나 마지막 AUTO_INCREMENT값을 따로 기록을 하여야 함.

(발생한 이슈 해결을 위하여, tb_user_del의 PK를 nonclustered index로 변경하여 PK충돌을 해결하였음)

 

저작자표시 비영리 변경금지

'DB Troubleshooting > MySQL' 카테고리의 다른 글

MySQL 고비용 쿼리로 인한 Memory 고갈 후 swap 사용으로 지연 처리  (0) 2022.03.17
MySQL max_allowed_packet 설정 오류로 Replication Error  (0) 2022.03.17
[MySQL] _INFORMATION_SCHEMA.PARAMETERS 조회로 인한 Checking Permission 부하  (0) 2021.12.29
[MySQL] Stored Procedure Memory Leak  (0) 2021.12.29
MySQL - Got timeout reading communication packets  (0) 2021.06.03
  • By SJinDBA

to Top

티스토리툴바