DB Library

현상

권한 부여 및 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 생성) 진행

참고

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

내용

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

참고

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

현상

threads 및 connection 증가

mysqldump FLUSH TABLES 지속

해결방안

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

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

* 고비용 쿼리 튜닝

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

* Kernel parameter 변경

FOUND_ROWS 적용 전

페이징 처리를 위해 limit 사용 시 전체 게시물의 수를 알기 위해 쿼리를 한번 더 해야 한다.

1. 해당 조건을 만족하는 게시물의 전체 수 조회
2. 그 페이지에서 보여줄 내용 조회

FOUND_ROWS 적용 후

SQL_CALC_FOUND_ROWS & FOUND_ROWS

SELECT 최상단에 SQL_CALC_FOUND_ROWS 라는 옵션만 주고 FOUND_ROWS() 를 사용하면 된다.

쿼리는 마찬가지로 두번이지만 두 번째 쿼리는 훨씬 빠르다.

첫번째 쿼리 검색 결과 : 10개

두번째 쿼리 카운트 값 : WHERE조건에 해당되는 Rows 카운트 ( 1~100사이에 누락된 번호가 없다면 99을 리턴 )

주의사항

1. SQL_CALC_FOUND_ROWS 키워드는 UNION의 첫 번째 SELECT에 나타나야 한다.
2. FOUND_ROWS()의 값은 UNION ALL을 사용하는 경우에만 정확합니다. ALL이 없는 UNION을 사용하면 중복 제거가 발생하고 FOUND_ROWS()의 값은 근사치만 된다.
3. UNION에 LIMIT가 없는 경우 SQL_CALC_FUND_ROWS는 무시되고 UNION을 처리하기 위해 생성된 임시 테이블의 행 수를 반환한다.

4. 복제시) FOUND_ROWS() 은 statement-based 형태로는 안정적으로 복제되지 않습니다, 이경우 row-based 형태로 자동 변경되어 복제가 됩니다.

※ MySQL 4.7.16 이전 버전에는 버그가 있어, 원하는 결과를 리턴하지 않음

* 5.7.16 이전 버전의 Query중 SybQuery가 있다면, SubQuery 내의 결과 Row 수를 FOUND_ROWS()로 Retrun
* 5.7.16 버그 픽스 이후는 전체쿼리문을 하나의 구문으로 보고 결과 ROW수를 FOUND_ROWS()로 return
  FOUND_ROWS() 함수의 기능은 이전 SELECT 된 쿼리의 ROWS를 RETURN 하는 함수(즉, 5.7버전의 기능이 정확)

FOUND_ROWS() 함수는 MySQL 8.0.17에서 더 이상 사용되지 않으며 향후 버전의 MySQL에서 제거될 것으로 예상된다..

참고

https://string.tistory.com/93

현상

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충돌을 해결하였음)

MySQL 성능 최적화 작업 중 하나로 DISK I/O를 줄이는 방법중 하나

참고

http://jason-heo.github.io/mysql/2014/03/05/innodb-barracuda.html

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

테이블 생성시 auto_increment 설정

CREATE TABLE people 
(
    number INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255)
);

AUTO_INCREMENT 확인

다음에 삽입될 Auto_increment(number 컬럼)의 값 확인

SHOW TABLE STATUS WHERE name = '테이블 이름';
SHOW TABLE STATUS LIKE '테이블 이름';

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 82
    Create_time: 2021-12-31 15:02:42
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  초기값 변경

AUTO_INCREMENT를 초기화(변경)할 때는 변경할 AUTO_INCREMENT의 값이 해당 컬럼에서 가장 큰 값보다 커야 합니다

ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table _people auto_increment=5
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show table status where name = 'people' \G;
*************************** 1. row ***************************
           Name: _people
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 6
 Avg_row_length: 2730
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 5
    Create_time: 2021-12-31 15:57:51
    Update_time: 2021-12-31 15:10:03
     Check_time: NULL
      Collation: utf8mb4_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

AUTO_INCREMENT  1부터 순서대로 정렬

레코드 삭제와 업데이트 등과 같은 이유로 AUTO_INCREMENT로 설정된 컬럼의 숫자가 난잡하게 될 수 있습니다. 이때 보기 좋게 1부터 순서대로 정렬할 수 있습니다.

SET @count=0;
UPDATE 테이블 이름 SET 컬럼 이름=@count:=@count+1;

mysql> SET @count=0;
mysql> UPDATE people SET number=@count:=@count+1;

# 데이터 정렬 후 AUTO_INCREMENT 값을 변경
ALTER TABLE 테이블 이름 AUTO_INCREMENT=변경할 숫자;

mysql> alter table people AUTO_INCREMENT=4;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

예제

AUTO_INCREMENT를 5로 변경

 

 

 

 

 

 

 

 

참고

https://m.blog.naver.com/dldudcks1779/222006115309

 처음과 끝은 각각 ^과 $로 표현할 수 있어요. 예를 들어서, '^a'라면, a로 시작하는 패턴입니다. 그리고 'E$'이라면, E로 끝나는 패턴입니다. 그러면 '^eat$'은 무엇을 의미할까요? "eat"를 찾으라는 거겠네요. 그리고, .은 임의의 문자를 의미하는데요. '^...$'은, 길이가 3인 문자열이 패턴에 매칭된다는 이야기입니다.

 이 쿼리를 수행해 봅시다. 그러면 문자열이 시작하고 임의의 문자가 3개 나오고 끝납니다. 그 말은, "Tom"이나 "Bob"도 matching이 된다는 소리일 거에요.

 결과는, _name 필드의 문자열 길이가 3인 것들만 출력됩니다.

 그런데, 우리는 _name이 모음으로 시작하는 것들을 구한다고 해 봅시다. LIKE를 쓰면 대충 10개의 조건절을 and로 연결하거나, 아니면 left 함수로 1개의 문자열을 가져온 것이, IN에 속해있는지. 등 조금 복잡하게 쿼리를 작성해야 할 거에요.

 [...] 패턴은 ...에 속하는 문자 집합이 패턴에 걸린다는 의미인데요. 예를 들자면 이런 경우를 봅시다.

 이 쿼리는 무엇을 의미할까요? 일단 문자열이 시작했다는 ^가 들어와 있고, []가 있네요. 안에 것을 보니까 aAeE가 있는데, 첫 문자의 가능 집합이 a, A, e, E라는 것입니다. 즉, _name 필드가 이 4개의 문자로 시작하는 레코드들을 모두 출력하라는 의미입낟.

 쿼리의 결과는 다음과 같습니다. 그러면, a, A, e, E라는 문자가, 첫 번째에도 나오고 두 번째에도 나와야 하는 경우는 어떻게 작성해야 할까요? 이 때, {n}이라는 키워드를 씁니다. 해당 패턴이 n회 반복된다.

 이 문장의 의미는, string이 시작하고 나서, a나 A나 e나 E가 2회 반복되어야 한다는 것입니다. 즉, aa~나 aA~나 ae~나 aE나, ... , EE가 해당 패턴에 걸릴 거에요. 그러면 저 셋 중에는, eat가 있는 필드만 걸리겠네요.

 하핫. 만약에 1회 이상 2회 미만 그러한 패턴이 나타나는 패턴이 참이 되게 하고 싶다면 어떻게 하면 될까요? {n,m}을 쓰시면 됩니다. 이것은 패턴이 n회 이상, m회 이하 등장한다는 의미입니다. Arie, Asia, eat 셋 중에, 다음 패턴을 만족하는 것이 몇 개나 있을까요?

 일단, a나 A나 e나 E가 나타나는 패턴이 최소 1회, 최대 1회 반복이 되고, 임의의 문자가 하나 나오면 A?나 A?나 e?나 E?가 저것과 매치가 될 거에요. 그러면, 저 셋 중에서 만족하는 것이 없어요. 그런데, 아래와 같은 경우에는 이야기가 달라집니다.

 보시면 패턴이 1회 이상 2회 이하 반복되고, 임의의 문자 하나가 들어오고 끝나면 만족을 하는데요.

 eat는 만족합니다. e가 aAeE 패턴에 만족하고, a 또한 aAeE 조건에 만족합니다. 2회 반복되었고, 다음에 t는 임의의 문자 중 하나입니다. 개행을 제외한. 따라서 eat가 있는 레코드만 출력이 됩니다. [^...]은 무엇을 의미할까요? ...에 속하지 않는 문자 집합을 의미합니다.

 위 쿼리를 실행해 봅시다. 그러면 문자열이 시작하고, 첫 번째 문자가 a나 A가 아니여야 합니다. _name 필드가 그러한 것만 고르는 query입니다. 한 번 실행을 해 봅시다.

 Asia랑 Anie는 빠졌음을 알 수 있어요.

 *과 +이 남았네요. *은 패턴이 0번 이상, +은 1번 이상 나타난다는 것을 의미합니다. 예를 들어서, 다음과 같은 것을 생각해 봅시다.

 이것은 문자열이 시작했을 때, A,a,E,e가 최소 0회 이상 나오는 패턴과 매칭이 됩니다.

 실행 결과는 아래와 같이 나옵니다. 시작 문자가 어느 것이던 상관 없다는 겁니다. 그런데 +가 붙으면 이야기가 달라지는데요. 이것은 최소 1회 이상 문제의 패턴이 반복되어야 한다는 겁니다.

 쿼리를 이렇게 작성해 봅시다.

 그러면 A나 a나 E나 e로 시작하는 것들만 나올 거에요. 마지막으로, |가 있는데요. 이것은 or를 의미합니다.

 다음 쿼리는 A로 시작하거나, B로 시작하는 패턴을 찾으라는 겁니다.

 결과는 3개의 row가 리턴됩니다.

 그러면 _name이 모음으로 시작하면서, 모음으로 끝나는 것들은 어떻게 구하면 좋을까요? 일단, 저는 다음과 같은 쿼리를 수행해 보겠습니다. 당연하게도, 길이가 1짜리면서 모음인 데이터를 추가한 겁니다.

 다음에, 쿼리를 생각해 봅시다. 일단 문자열이 시작해야 하고, [aeiouAEIOU] 라는 pattern이 들어와야 할 겁니다. 다음에 임의의 문자가 0개 이상 오고, [aeiouAEIOU] 패턴이 온 다음에 끝나야 겠지요.

 그러면, 이런 식으로 작성할 수 있을 겁니다. 그런데, 이 경우 길이가 1인 데이터는 매칭이 되지 않을 겁니다.

 결과를 확인해 보면, "A"라는 것은 matched가 되지 않았음을 알 수 있어요. 그러면 어떻게 작성하면 좋을까요?

 이렇게 작성하면 어떻게 수행이 될까요? 일단, 첫 패턴이 모음인지 검사합니다. 그 다음에 괄호 안으로 들어갈 건데요. 판단 state를 그려보면 아래와 같습니다.

 보시면 모음이 나온 후에 바로 Terminate가 되거나, 아니면 임의의 문자가 0회 이상 나온 후에, 모음으로 끝나는 패턴만 regexp에 걸리게끔 하고 있어요. 조금 어려운 예제였습니다. 그러면, 모음으로 시작하거나, 모음으로 끝나는 건 어떻게 작성하면 좋을까요?

 Conditions를 괄호로 묶고 |를 붙이면 됩니다. 어떤 조건인가요? ^[모음들]이거나, [모음들]$이겠지요. 이 둘을 괄호로 묶어준 다음에 |로 연결해 주시면 됩니다. 정규 표현식을 연습하는 사이트는 여러 곳이 있습니다. 거기에서 연습해 보셔도 좋을 듯 싶습니다.

참고

https://codingdog.tistory.com/entry/mysql-regexp-%EB%B3%B5%EC%9E%A1%ED%95%9C-%ED%8C%A8%ED%84%B4-%EB%A7%A4%EC%B9%AD%EC%9D%84-%ED%95%B4-%EB%B4%85%EC%8B%9C%EB%8B%A4

현상

높은 비중의 Thread State 상태가 유지되면서, CPU사용량이 높아지면서 성능 이슈가 발생

원인

Front-End 서버에서 MySQL에 만들어둔 SP 사용시 SP의 Body 호출이 필요함

MySQL.PROC에 해당 정보가 있지만 권한이 없어 INFORMATION_SCHEMA 정보를 가져가게 되면서 발생

해결 방안

db 접속 계정에 MySQL DB의 PROC 테이블에 SELECT 권한 부여

현상

MySQL의 SP를 사용하는 DB에서 지속적으로 Memory가 상승하는 현상 발생

원인

MySQL 내부적으로 SP 내 Subquery 사용 시 Memory Leak 이 발생

참조: The optimizer sometimes generates an index for a derived table (subquery in the FROM clause).

If this occurred for a statement executed within a stored program, a memory leak could occur. (Bug #76349, Bug #20728894)

해결방안

해당 버그가 5.6.27 이후 fixed 된 것으로 확인되어 MySQL upgrade

upgrade 후 memory leak 현상 해소