JJON's StorY

pt-kill은 이해하기 어렵고 예제가 많지 않습니다. pt-kill의 가장 일반적인 사용법에 대해 알아 봅시다.

pt-kill 은 SHOW PROCESSLIST에서 쿼리를 캡처하고 필터링한 다음 종료하거나 인쇄합니다. 용도는 너무 많은 리소스를 소비할 수 있는 쿼리를 감시하고 종료하는 것입니다.

보통은 쿼리 종료에 사용하지만 주어진 옵션에 따라 출력도 할 수 있습니다.

일반적으로 pt-kill 은 MySQL에 연결하여 SHOW PROCESSLIST에서 쿼리를 가져 오지만 파일에서 SHOW PROCESSLIST 출력을 읽을 수 있습니다. 이 경우 pt-kill 은 MySQL에 연결하지 않으며 '--kill' 옵션이 효과가 없습니다. '--print'는 파일을 읽을 때 대신 사용해야 합니다. 파일을 읽는 기능을 '--test-matching'으로 사용하면 SHOW PROCESSLIST를 캡처하고 나중에 pt-kill 로 일치하는 항목이 적절한 쿼리를 종료하는지 확인할 수 있습니다.

일반적으로 하나 이상의 '--match'옵션 을 지정해야 합니다 . 그렇지 않으면 '--match-all' 옵션으로 모든 쿼리와 일치하도록 지정할 수 있습니다. 일치하는 쿼리가 없을 경우 '--ignore' 사용 할 수 있습니다..

다음과 같은 쿼리를 실행하는 sysbench 도구를 실행하고 있다고 가정해 보겠습니다.

select distinct c from sbtest1 where id between 1 and 20 order by c
select distinct c from sbtest2 where id between 5 and 25 order by c
select distinct c from sbtest3 where id between 3 and 7 order by c
select distinct c from sbtest1 where id between 2 and 8 order by c
select distinct c from sbtest3 where id between 15 and 30 order by c

동시에 다른 프로세스에서 다음을 실행하고 있습니다.

SELECT SLEEP 10

그런 다음 위 쿼리가 실행되는 동안 다음 매개변수(2초 이상 수행 중인 select|SELECT 쿼리) 를 사용하여 pt-kill 을 시작합니다.

pt-kill --kill-busy-commands=Query --run-time 30m --interval 1 --busy-time 2s --print --match-info "(select|SELECT)"

출력에 다음과 같은 내용이 표시됩니다.

# 2019-06-20T11:40:23 KILL 31 (Query 2 sec) SELECT SLEEP(10)
# 2019-06-20T11:40:23 KILL 513 (Execute 0 sec) SELECT DISTINCT c FROM sbtest48 WHERE id BETWEEN 4980 AND 5079 ORDER BY c
# 2019-06-20T11:40:23 KILL 524 (Execute 0 sec) SELECT c FROM sbtest24 WHERE id=4997
# 2019-06-20T11:40:23 KILL 512 (Execute 0 sec) SELECT c FROM sbtest48 WHERE id BETWEEN 5001 AND 5100 ORDER BY c
# 2019-06-20T11:40:23 KILL 516 (Execute 0 sec) SELECT SUM(k) FROM sbtest41 WHERE id BETWEEN 4988 AND 5087

'select sleep 10'은 '--busy-time 2s' 초과하므로 종료해야 하지만 실행 시간이 0초인 다른 쿼리도 종료되는 이유는 무엇일까요?

먼저 프로그램이 동작하는 기준을 확인 해야 합니다.

첫째, '--group-by'에 따라 쿼리를 버킷으로 그룹화 합니다. 명령줄에는 그룹화 기준이 없으므로 찾은 모든 쿼리가 포함된 버킷은 하나만 있습니다. 두 번째 단계는 일치 기준을 확인하는 것입니다. 지정된 모든 기준이 논리적 'OR'을 통해 결합된다는 점이 중요합니다.

위에 예제에는 두 가지 일치 조건이 있습니다.

'--busy-time 2s', '--match-info "(select|SELECT)"'

'select sleep 10' 쿼리는 두가지 조건에 모두 일치 하지만 일지 기준은 'OR'을 통해 적용 되므로 조건 중 하나만 일치해도 실행 되므로 sysbench 쿼리 또한 적용 대상이 됩니다.

그러면 '--busy-time' 대신에 '--each-busy-time'을 사용하면 어떻게 될까요?

쿼리가 종료되지 않습니다.

Bucket:
default

Queries:
- SELECT SLEEP (10)
- SELECT c FROM sbtest24 WHERE id=4997

보기 쉽게 만들었습니다. 동일한 버킷에 10초 동안 수행되는 쿼리와 0초 동안 수행하는 쿼리가 있습니다. 수행되는 모든 쿼리가 2초 이상 실행이 되어야지만 기준이 일치 합니다.

그러면 다시 처음으로 돌아가서 2초 이상 수행 중인 select|SELECT 쿼리를 종료 하려면 아래와 같이 수행하면 됩니다.

pt-kill --kill-busy-commands=Query --run-time 30m --interval 1 --each-busy-time 2s --print --match-info "(select|SELECT)" --group-by=fingerprint

'--group-by'를 사용하여 2개의 버킷을 생성 하였습니다.

매칭 기준인 '--each-busy-time'은 그룹에 개별적으로 적용 됩니다.

Bucket:
select sleep(?)
 
Queries:
- (Query 3 sec) SELECT SLEEP(10)

Bucket:
select c from sbtest? where id=?
 
Queries:
# (Execute 0 sec) SELECT DISTINCT c FROM sbtest9 WHERE id BETWEEN 4985 AND 5084 ORDER BY c
# (Execute 0 sec) SELECT SUM(k) FROM sbtest32 WHERE id BETWEEN 4986 AND 5085
# (Execute 0 sec) SELECT c FROM sbtest8 WHERE id=5983
# (Execute 0 sec) SELECT DISTINCT c FROM sbtest20 WHERE id BETWEEN 5570 AND 5669 ORDER BY c
# (Execute 0 sec) SELECT c FROM sbtest30 WHERE id BETWEEN 4504 AND 4603 ORDER BY c
# (Execute 0 sec) SELECT c FROM sbtest8 WHERE id BETWEEN 4988 AND 5087
# (Execute 0 sec) SELECT c FROM sbtest27 WHERE id=5013

첫 번째 그룹의 경우 SELECT가 2초 이상 실행되고 나서 종료되므로 일치합니다.

두 번째 그룹에 대해 쿼리는 모두 2초보다 커야합니다. 따라서 두 번째 그룹의 쿼리는 종료되지 않으며 처음에 원하는 것을 달성했습니다.

pt-kill에 대한 기본적은 사용법은 다음에서 확인 가능 합니다.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-kill.html

사용법

pt-kill  [ 옵션 ]  [ DSN ]

60초 이상 실행되는 쿼리 종료:

pt-kill --busy-time 60 --kill

60초 이상 실행되는 쿼리를 종료 하지 않고 출력:

pt-kill --busy-time 60 --print

sleep 프로세스를 확인하고 10초마다 모두 종료:

pt-kill --match-command Sleep --kill --victims all --interval 10

모든 로그인 프로세스 출력:

pt-kill --match-state login --print --victims all

'SHOW PROCESSLIST'에서 어떤 쿼리가 일치하는지 확인:

mysql -e "SHOW PROCESSLIST" > proclist.txt 
pt-kill --test-matching proclist.txt --busy-time 60 --print

MySQL을 업그레이드 한 후 정상적으로 수행되던 쿼리가 "ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause" 에러와 함께 수행이 되지 않는 경우가 있습니다.

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'test.web_log.user_id' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by

SQL_MODE

먼저 SQL_MODE 의 개념을 부터 확인 합니다.

MySQL은 쿼리 및 유효성 검사의 구문에 영향을 주는 다양한 SQL 모드를 사용하여 작동할 수 있습니다. sql_mode 변수의 설정 값에 따라 쿼리가 유효하고 정기적으로 실행되거나 유효성 검사 오류가 발생하여 실행할 수 없음을 의미합니다.

가장 오래된 버전의 MySQL은 사용자가 "forgiving mode"에서 작동하도록 설계되었기 때문에 의미상 올바르지 않은 쿼리를 작성하는 데 익숙해졌습니다. 사용자는 SQL 표준 준수나 규칙에 관계없이 모든 종류의 구문적으로 유효한 쿼리를 작성할 수 있습니다. 이후 쿼리 유효성 검사를 위해 MySQL이 더 제한적인 방식으로 작동하도록 sql_mode를 도입하여 수정 되었습니다.

5.7부터는 기본값이 더 제한적이며 사용자가 5.7 또는 8.0으로 마이그레이션한 후 예기치 않은 쿼리 실패 문제가 발생하는 이유입니다.

sql_mode의 변수는 구성 파일 (/etc/my.cnf ) 또는 실시간으로 변경 할 수 있습니다. 변수의 범위는 GLOBAL 및 SESSION일 수 있으므로 단일 연결에 대한 목적에 따라 변경할 수 있습니다.

sql_mode의 변수 값을 더 추가 할 수 있고, 다른 동작을 제어하기 위해 콤마로 분리합니다. 예를 들어, 날짜가 유효한지 여부를 확인하기 위해 MySQL에 날짜에 zero인 날짜를 '0000-00-00'으로 처리하는 방법을 지시할 수 있습니다. "forgiving mode"에서(또는 sql_mode 변수가 비어 있는 경우) 문제 없이 이러한 값을 삽입할 수 있습니다.

# set sql mode to "forgiving mode" 
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
 
mysql> create table t1( mydate date );
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t1 values('0000-00-00');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1;
+------------+
| mydate     |
+------------+
| 0000-00-00 |
+------------+
1 row in set (0.00 sec)

그러나 이것은 TRADITIONAL 모드 에서 설명한 올바른 동작이 아닙니다 . 잘못된 데이터나 잘못된 결과를 피하기 위해 소스 코드의 날짜를 확인해야 합니다.

다음은 MySQL이 오류를 발생시키도록 TRADITIONAL 모드로 동적으로 변경하는 방법입니다.

mysql> set session sql_mode='TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values('0000-00-00');
ERROR 1292 (22007): Incorrect date value: '0000-00-00' for column 'mydate' at row 1

이 외에도 사용할 수 있는 다른 모드가 많이 있습니다. 모든 모드를 다루는 것이 목표는 아니므로 자세한 내용과 예는 공식 문서를 참조하십시오.

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

ONLY_FULL_GROUP_BY 문제

5.7 또는 8.0으로 마이그레이션할 때 가장 자주 발생하는 오류 원인에 초점을 맞춰 보겠습니다. 5.7에는 5.6보다 더 제한적인 기본 SQL 모드가 있습니다. 이는 sql_mode 변수에 대한 특정 설정 없이 이전 my.cnf 파일을 복사하여 MySQL을 업그레이드하는 경우에 해당됩니다.

웹 페이지에 대한 클릭을 저장하는 샘플 테이블을 만들어 보겠습니다. 

Query OK, 0 rows affected (0.03 sec)
 
mysql> insert into web_log(page_url,user_id,ts) values('/index.html',1,'2019-04-17 12:21:32'),
    -> ('/index.html',2,'2019-04-17 12:21:35'),('/news.php',1,'2019-04-17 12:22:11'),('/store_offers.php',3,'2019-04-17 12:22:41'),
    -> ('/store_offers.php',2,'2019-04-17 12:23:04'),('/faq.html',1,'2019-04-17 12:23:22'),('/index.html',3,'2019-04-17 12:32:25'),
    -> ('/news.php',2,'2019-04-17 12:32:38');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
 
mysql> select * from web_log;
+----+--------------------+---------+---------------------+
| id | page_url           | user_id | ts                  |
+----+--------------------+---------+---------------------+
|  1 | /index.html        |       1 | 2019-04-17 12:21:32 |
|  2 | /index.html        |       2 | 2019-04-17 12:21:35 |
|  3 | /news.php          |       1 | 2019-04-17 12:22:11 |
|  4 | /store_offers.php  |       3 | 2019-04-17 12:22:41 |
|  5 | /store_offers.html |       2 | 2019-04-17 12:23:04 |
|  6 | /faq.html          |       1 | 2019-04-17 12:23:22 |
|  7 | /index.html        |       3 | 2019-04-17 12:32:25 |
|  8 | /news.php          |       2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+

이제 가장 많이 방문한 페이지를 계산하는 쿼리를 실행하려고 합니다.

# sql mode to "forgiving"
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+
4 rows in set (0.00 sec)

쿼리는 작동하지만 실제로는 정확하지 않습니다. page_url 이 groupby 함수의 열이고 계산을 위해 고유한 값을 원한다는 것은 쉽게 이해할 수 있습니다. 또한 방문 열은 카운터입니다. 하지만 user_id는 열은 무엇을 나타냅니까? user_id 에 대해 반환 된 값이 그룹의 값 중 하나일 수 있도록 page_url로 그룹화했습니다 . 같은 페이지 url을 방문한 것은 1번 사용자뿐 아니라 2번, 3번 사용자도 방문했습니다. 

user_id 열의 값은 그룹의 임의 항목으로 간주해야 합니다 .

어쨌든 정답은 쿼리가 의미적으로 정확하지 않다는 것입니다. group by 함수의 일부가 아닌 열에서 값을 반환하는 의미가 없기 때문입니다. 그럼 다음 쿼리는 기존 SQL에서 유효하지 않을 것으로 예상됩니다.

테스트해 봅시다.

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT page_url, user_id, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'test.web_log.user_id' which is not functionally 
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

이제 예상대로 오류가 발생했습니다.

SQL 모드 ONLY_FULL_GROUP_BY 는 TRADITIONAL 모드의 일부이며 5.7부터 기본적으로 활성화됩니다.

최신 버전의 MySQL로 마이그레이션한 후 이러한 문제를 많이 겪습니다.

해결 방법 1 - 쿼리 다시 작성

group by에 포함되지 않은 열을 조회하는 것은 올바르지 않으므로 해당 열을 제거하여 쿼리를 다시 작성할 수 있습니다.

mysql> SELECT page_url, COUNT(*) AS visits 
    -> FROM web_log 
    -> GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------+
| page_url          | visits |
+-------------------+--------+
| /index.html       |      3 |
| /news.php         |      2 |
| /store_offers.php |      2 |
| /faq.html         |      1 |
+-------------------+--------+

문제의 영향을 받는 쿼리가 많은 경우 쿼리를 검색하고 다시 작성하기 위해 많은 작업을 수행해야 합니다. 또는 쿼리가 사용자가 할 수 없거나 만지고 싶지 않은 레거시 애플리케이션의 일부일 수 있습니다.

그러나 이 해결 방법은 데이터베이스 구성을 제한해서 SQL 유효성 검사 측면에서 올바른 쿼리를 작성하게 하는 방법입니다.

해결 방법 2 - forgiving mode로 돌아가기

MySQL의 구성을 변경하고 "forgiving mode"로 돌아갈 수 있습니다.

또는 기본값 에서 ONLY_FULL_GROUP_BY 만 삭제할 수 있습니다 . MySQL 5.7의 기본 SQL 모드는 ONLY_FULL_GROUP_BY, STRINCT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER 모드를 포함합니다.

#set the complete "forgiving" mode
mysql> SET GLOBAL sql_mode='';

# alternatively you can set sql mode to the following
mysql> SET GLOBAL sql_mode='STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_ENGINE_SUBSTITUTION';

해결 방법 3 – 집계 함수 사용

애플리케이션 이 어떤 유효한 이유로 user_id 필드를 검색해야 하거나 소스 코드를 변경하기에는 너무 복잡한 경우 SQL 모드 구성 변경을 피하기 위해 집계 함수에 의존할 수 있습니다.

예를 들어 MAX() , MIN() 또는 GROUP_CONCAT() 집계 함수를 사용할 수 있습니다.

mysql> SET SESSION sql_mode='ONLY_FULL_GROUP_BY';

mysql> SELECT page_url, MAX(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------+--------+
| page_url          | MAX(user_id) | visits |
+-------------------+--------------+--------+
| /index.html       |            3 |      3 |
| /news.php         |            2 |      2 |
| /store_offers.php |            3 |      2 |
| /faq.html         |            1 |      1 |
+-------------------+--------------+--------+

mysql> SELECT page_url, GROUP_CONCAT(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+-----------------------+--------+
| page_url          | GROUP_CONCAT(user_id) | visits |
+-------------------+-----------------------+--------+
| /index.html       |                 1,2,3 |      3 |
| /news.php         |                   1,2 |      2 |
| /store_offers.php |                   3,2 |      2 |
| /faq.html         |                     1 |      1 |
+-------------------+-----------------------+--------+

MySQL은 문제를 해결하기 위한 특정 함수인 ANY_VALUE()도 제공합니다.

mysql> SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits FROM web_log GROUP BY page_url ORDER BY COUNT(*) DESC;
+-------------------+--------------------+--------+
| page_url          | ANY_VALUE(user_id) | visits |
+-------------------+--------------------+--------+
| /index.html       |                  1 |      3 |
| /news.php         |                  1 |      2 |
| /store_offers.php |                  3 |      2 |
| /faq.html         |                  1 |      1 |
+-------------------+--------------------+--------+

결론

SQL-92 호환 쿼리를 작성해야 하기 때문에 해결 방법 1을 선호합니다. 표준을 따르는 것은 종종 모범 사례로 간주됩니다.

해결 방법 2는 응용 프로그램 코드를 변경할 수 없는 경우에 좋습니다. 하지만, 궁극적인 문제를 해결 하기 위해 SQL-92 호환되지 않는 쿼리를 재 작성하는 장기적인 계획을 가지는 것이 좋습니다.

인덱스 처리에 대해 이해하고 있다면 데이터 삽입을 위한 적절한 공간을 예약하려는 경우에 도움이 될 수 있습니다. MySQL 5.7부터 이전 릴리스에서 사용된 하향식 접근 방식이 아닌 상향식 접근 방식을 적용하여 InnoDB용 보조 인덱스를 구축하는 방식으로 변경했습니다.

인덱스 구축 프로세스

기존 데이터가 있는 테이블에 인덱스를 작성하기 위한 단계가 있습니다.

1. 읽기 단계(클러스터형 인덱스에서 읽고 보조 인덱스 항목 작성)
2. 병합 정렬 단계
3. 삽입 단계(정렬된 레코드를 보조 색인에 삽입)

버전 5.6까지 MySQL은 한 번에 하나의 레코드를 삽입하여 보조 인덱스를 구축했습니다. 이것은 "하향식" 접근 방식입니다. 삽입 위치에 대한 검색은 루트(위쪽)에서 시작하여 적절한 리프 페이지(아래쪽)에 도달합니다. 레코드는 커서가 가리키는 리프 페이지에 삽입됩니다. 삽입 위치를 찾고 페이지 분할 및 병합(root 및 non-root에서)을 수행하는 데 비용이 많이 듭니다. 페이지 분할 및 병합이 너무 많이 발생하는지 어떻게 알 수 있습니까?

MySQL 5.7부터 인덱스 추가 중 삽입 단계는 "Bulk Load for Index"라고도 하는 "Sort Index Build"를 사용합니다. 이 접근 방식에서 인덱스는 "상향식"으로 구축됩니다. 즉, 리프 페이지(하단)가 먼저 빌드된 다음 비-리프 레벨이 루트(위)까지 빌드됩니다.

사용 사례

정렬된 인덱스 빌드는 다음과 같은 경우에 사용됩니다.

• ALTER TABLE t1 ADD INDEX (or CREATE INDEX)
• ALTER TABLE t1 ADD FULLTEXT INDEX
• ALTER TABLE t1 ADD COLUMN, ALGORITHM=INPLACE
• OPTIMIZE TABLE t1

마지막 두 사용 사례의 경우 ALTER는 중간 테이블을 생성합니다. 중간 테이블 인덱스(기본 및 보조 모두)는 "Sort Index Build"를 사용하여 빌드됩니다.

알고리즘

1. 레벨 0에서 페이지를 작성하십시오. 또한 이 페이지에 대한 커서를 작성하십시오.
2. 가득 찰 때까지 레벨 0의 커서를 사용하여 페이지에 삽입합니다.
3. 페이지가 가득 차면 형제 페이지를 만듭니다(아직 형제 페이지에 삽입하지 마십시오).
4. 현재 전체 페이지에 대한 노드 포인터(자식 페이지의 최소 키, 하위 페이지 번호)를 만들고 노드 포인터를 한 수준 위(상위 페이지)에 삽입합니다.
5. 상위 레벨에서 커서가 이미 위치했는지 확인하십시오. 그렇지 않은 경우 상위 페이지와 레벨에 대한 커서를 작성하십시오.
6. 상위 페이지에 노드 포인터 삽입
7. 상위 페이지가 가득 차면 3, 4, 5, 6단계를 반복합니다.
8. 이제 형제 페이지에 삽입하고 커서가 형제 페이지를 가리키도록 합니다.
9. 모든 삽입의 끝에는 가장 오른쪽 페이지를 가리키는 각 수준에 커서가 있습니다. 모든 커서를 커밋합니다(페이지를 수정한 미니 트랜잭션을 커밋하고 모든 래치를 해제함을 의미).

단순성을 위해 위의 알고리즘은 압축된 페이지 및 BLOB(외부 저장 BLOB) 처리에 대한 세부 정보를 건너뛰었습니다.

상향식 인덱스 구축 과정

예를 사용하여 상향식 보조 인덱스가 구축되는 방식을 살펴보겠습니다. 다시 간단하게 하기 위해 리프 및 비 리프 페이지에 허용되는 최대 레코드 수가 3개라고 가정합니다.

CREATE TABLE t1 (a INT PRIMARY KEY, b INT, c BLOB);
 
INSERT INTO t1 VALUES (1, 11, 'hello111');
INSERT INTO t1 VALUES (2, 22, 'hello222');
INSERT INTO t1 VALUES (3, 33, 'hello333');
INSERT INTO t1 VALUES (4, 44, 'hello444');
INSERT INTO t1 VALUES (5, 55, 'hello555');
INSERT INTO t1 VALUES (6, 66, 'hello666');
INSERT INTO t1 VALUES (7, 77, 'hello777');
INSERT INTO t1 VALUES (8, 88, 'hello888');
INSERT INTO t1 VALUES (9, 99, 'hello999');
INSERT INTO t1 VALUES (10, 1010, 'hello101010');
ALTER TABLE t1 ADD INDEX k1(b);

InnoDB는 기본 키 필드를 보조 인덱스에 추가합니다. 보조 인덱스 k1의 레코드는 형식 (b, a)입니다. 

정렬 단계 후 레코드는 (11,1), (22,2), (33,3), (44,4), (55,5), (66,6), (77,7), (88,8), (99,9), (1010, 10)입니다.

초기 삽입 단계

레코드(11,1)부터 시작하겠습니다.

1. 레벨 0(리프 레벨)에서 페이지를 작성하십시오.
2. 페이지에 커서를 만듭니다.
3. 모든 삽입물은 가득 찰 때까지 이 페이지로 이동합니다.

화살표는 현재 커서가 가리키는 위치를 나타냅니다. 현재 페이지 번호 5에 있으며 다음 삽입물은 이 페이지로 이동합니다.

두 개의 여유 슬롯이 더 있으므로 레코드 (22,2) 및 (33,3)을 삽입하는 것은 간단합니다.

다음 레코드(44, 4)의 경우 5페이지가 꽉 찼습니다. 단계는 다음과 같습니다.

페이지가 채워지면 색인 작성

1. 형제 페이지 만들기 – 6페이지
2. 아직 형제 페이지에 삽입하지 마십시오.
3. 커서에서 페이지 커밋, 즉 미니 트랜잭션 커밋, 래치 해제 등
4. 커밋의 일부로 노드 포인터를 만들고 [현재 레벨 + 1]의 부모 페이지에 삽입합니다. 즉, 레벨 1에서
5. 노드 포인터는 형식(자식 페이지의 최소 키, 하위 페이지 번호)입니다. 페이지 번호 5의 최소 키는 (11,1)입니다. 상위 수준에서 레코드((11,1),5)를 삽입합니다.
6. 레벨 1의 상위 페이지는 아직 존재하지 않습니다. MySQL은 페이지 번호 7과 페이지 번호 7을 가리키는 커서를 생성합니다.
7. 페이지 번호 7에 ((11,1),5) 삽입
8. 이제 레벨 0으로 돌아가서 페이지 번호 5에서 6으로 또는 그 반대로 링크를 생성하십시오.
9. 레벨 0의 커서는 이제 형제 페이지 번호 6을 가리킵니다.
10. 페이지 번호 6에 (44,4) 삽입

(55,5) 및 (66,6)의 다음 삽입물은 간단하며 6페이지로 이동합니다.

레코드 삽입(77,7)은 상위 페이지(페이지 번호 7)가 이미 존재하고 두 개 이상의 레코드를 위한 공간이 있다는 점을 제외하고는 (44,4)와 유사합니다. 노드 포인터((44,4),8)를 먼저 7페이지에 삽입한 다음 (77,7)을 형제 페이지 8에 기록합니다.

레코드 (88,8) 및 (99,9)의 삽입은 8페이지에 두 개의 여유 슬롯이 있기 때문에 간단합니다.

노드 포인터((77,7),8)를 수준 1의 부모 페이지(페이지 번호 7)에 삽입합니다.

MySQL은 레벨 0에서 형제 페이지 번호 9를 생성합니다. 레코드 (1010,10)을 페이지 9에 삽입하고 커서를 이 페이지로 변경합니다.

모든 수준에서 커서를 커밋합니다. 위의 예에서 데이터베이스는 레벨 0에서 페이지 9를 커밋하고 레벨 1에서 페이지 7을 커밋합니다. 이제 상향식으로 구축된 완전한 B+-트리 인덱스가 있습니다!

Index fill factor

전역 변수 " innodb_fill_factor "는 삽입에 사용할 Btree 페이지의 공간 양을 설정합니다. 기본값은 100이며 전체 페이지가 사용됨을 의미합니다(페이지 headers, trailers 제외). innodb_fill_factor = 100의 예외가 있습니다. 클러스터형 인덱스는 페이지 공간의 1/16이 여유 공간으로 유지됩니다. 즉. 6.25% 공간은 향후 DML을 위해 예약되어 있습니다.

값이 80이면 MySQL은 페이지의 80%를 삽입에 사용하고 20%는 향후 업데이트를 위해 남겨둡니다.

innodb_fill_factor가 100인 경우 보조 인덱스에 미래의 삽입을 위해 남아있는 여유 공간이 없습니다. 인덱스 추가 후 테이블에서 더 많은 DML이 예상되는 경우 페이지 분할 및 병합이 다시 발생할 수 있습니다. 이러한 경우 80-90 사이의 값을 사용하는 것이 좋습니다. 이 변수 값은 OPTIMIZE TABLE 또는 ALTER TABLE DROP COLUMN, ALGORITHM=INPLACE로 다시 생성된 인덱스에도 영향을 줍니다.

인덱스가 훨씬 더 많은 디스크 공간을 차지하기 때문에 너무 낮은 값(예: 50 미만)을 사용하면 안 됩니다. 값이 낮으면 인덱스에 더 많은 페이지가 있고 인덱스 통계 샘플링이 최적이 아닐 수 있습니다. 옵티마이저는 잘못된 통계로 비효율적인 실행 계획을 선택할 수 있습니다.

정렬된 인덱스 빌드의 장점

1. 페이지 분할(압축 테이블 제외) 및 병합이 없습니다.
2. 삽입 위치에 대한 반복 검색이 없습니다.
3. 삽입은 redo log에 기록되지 않으므로(페이지 할당 제외) redo log 하위 시스템에 대한 부담이 적습니다.

MySQL 구성 변수의 설정은 데이터베이스 시스템의 성능에 근본적인 영향을 미칩니다. 때로는 한 변수를 변경하는 것이 다른 변수에 어떤 영향을 미칠 수 있는지 예측하는 것이 약간 까다로울 수 있습니다. 특히 결과가 매우 직관적이지 않은 경우를 처리할 때 그렇습니다. 따라서 여기에서는 open_files_limit이 innodb_open_files보다 높게 설정하면 어떻게 되는지 살펴보겠습니다.

다음을 사용하여 MySQL 구성 파일에서 열린 파일의 최대 수를 설정할 수 있습니다.

open_files_limit=10000

이 값이 설정되지 않은 경우 기본값(MySQL 5.7에서는 5,000)을 사용해야 합니다.

open_files_limit 값이 설정되면 infinity로 설정되지 않는 한 SystemD의 LIMIT_NOFILES를 사용합니다(CentOS 7에서는 65536을 사용하지만 수동으로 지정하면 훨씬 더 높은 값이 가능함).

[root@centos7-pxc57-3 ~]# grep open_files_limit /etc/my.cnf
open_files_limit=10000
[root@centos7-pxc57-3 ~]# grep LimitNOFILE /lib/systemd/system/mysqld.service.d/limit_nofile.conf
LimitNOFILE=infinity
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 65536              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=infinity/LimitNOFILE=20000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 20000              |
+--------------------+
[root@centos7-pxc57-3 ~]# perl -pi -e ’s/LimitNOFILE=20000/LimitNOFILE=5000/‘ /lib/systemd/system/mysqld.service.d/limit_nofile.conf && systemctl daemon-reload && systemctl restart mysqld
[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@open_files_limit”
+--------------------+
| @@open_files_limit |
+--------------------+
| 5000               |
+--------------------+

위에서 볼 수 있듯이 MySQL은 open_files_limit을 시스템이 허용하도록 구성된 값 보다 높은 값을 설정할 수 없으며 open_files_limit너무 높게 설정되면 기본값으로 최대값으로 다시 설정됩니다.

그것은 매우 간단해 보이지만, MySQL이 동시에 얼마나 많은 .ibd 파일을 열수 있는지 innodb_open_files에 따라 결정됩니다.

파일을 열기 위해서는 open_files_limit 보다 낮아야 합니다. 만약 더 높게 설정하려고 하면 MySQL은 로그 파일에 warning을 출력합니다.

[root@centos7-pxc57-3 ~]# grep innodb_open_files /var/log/mysqld.log 
2018-09-21T08:31:06.002120Z 0 [Warning] InnoDB: innodb_open_files should not be greater than the open_files_limit.

warning을 출력되지 않도록 값을 낮추어야 합니다.

[root@centos7-pxc57-3 ~]# mysql -e “SELECT @@innodb_open_files”
+---------------------+
| @@innodb_open_files |
+---------------------+
| 2000                |
+---------------------+

왜 2000 으로 설정 되었을까요?

innodb_open_files 너무 높게 설정 하면 문서에 따라 다음과 같이 기본값으로 설정이 되기 때문입니다.

innodb_file_per_table이 활성화되어 있지 않으면 300이고, 300과 table_open_cache 중 더 높은 값으로 설정 됩니다. 
5.6.6 이전에는 기본값이 300입니다.

그리고 table_open_cache는 MySQL 5.6.7 이하 버전의 경우 기본값은 400이고 5.6.8 이후 버전은 2000입니다.

참고로 table_open_cache 완전히 다른 설정입니다. innodb_open_files은 서버가 한 번에 열어둘 수 있는 InnoDB 파일( .ibd)의 수를 제어합니다 . table_open_cache은 서버가 한 번에 열 수 있는 테이블 정의 파일( .frm)의 수 를 제어합니다 .