<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Sunguck Lee on Medium]]></title>
        <description><![CDATA[Stories by Sunguck Lee on Medium]]></description>
        <link>https://medium.com/@sunguck.lee?source=rss-9aa344a4b49b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*o6BGfYAyzR-QKENy</url>
            <title>Stories by Sunguck Lee on Medium</title>
            <link>https://medium.com/@sunguck.lee?source=rss-9aa344a4b49b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 09:43:04 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sunguck.lee/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[MySQL Gap Lock (두번째 이야기)]]></title>
            <link>https://medium.com/daangn/mysql-gap-lock-%EB%91%90%EB%B2%88%EC%A7%B8-%EC%9D%B4%EC%95%BC%EA%B8%B0-49727c005084?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/49727c005084</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[innodb]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Mon, 02 Oct 2023 05:49:13 GMT</pubDate>
            <atom:updated>2023-10-05T03:59:58.009Z</atom:updated>
            <content:encoded><![CDATA[<h3>Why ?</h3><p>DBMS 서버를 공부하는 데 있어서, “<strong>왜?</strong>”라는 질문을 가지는 것은 매우 중요해요. 그런데 MySQL 서버를 공부하다 보면, 풀리지 않는 의문들로 머리 속이 복잡해지는 경우가 많아요. 어느 MySQL 전문가는 이런 말을 할 정도로 MySQL 서버에는 수많은 의문들이 있는 것 같아요. 😵‍💫</p><blockquote>MySQL 서버에 대해서 너무 깊이 생각하면, 어느 순간 여러분은 의문 투성이 지옥에서 벗어나지 못하게 될 수 있어요. 왜 MySQL 서버는 Supremum Gap lock을 필요로 할까? 왜 Redundant Gap lock을 사용할까? 왜 Implicit lock을 사용하고 다시 이를 Explicit lock으로 변환할까? 등등 질문들이 끝없이 떠오르게 되고, 결국 여러분은 이런 풀리지 않는 질문들속에서 헤어나오지 못하게 될 거에요. 부디 질문을 멈추고 MySQL 서버로부터 여러분 자신을 구하세요. !!</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*AOLQG0fpD4x72bMNopo3pA.png" /></figure><p>오늘은 MySQL 서버의 잠금에서 미로 같은 혼란을 자주 유발하는 Gap lock에 대한 이슈 하나를 좀 살펴보려고 해요.</p><h3>이해할 수 없는 잠금 현상</h3><p>REPEATABLE-READ 격리 수준을 사용하는 MySQL 서버에서 간단한 잠금 테스트를 해 보려고 해요. 우선 아래와 같이 레코드 3건을 가지는 테이블을 생성해요.</p><pre>SESSION-1 &gt; CREATE TABLE lock_supremum (<br>  id int NOT NULL AUTO_INCREMENT,<br>  fd1 char(250) NOT NULL,<br>  PRIMARY KEY (id)<br>);<br><br>SESSION-1 &gt; INSERT INTO lock_supremum VALUES (3,&#39;dummy-3&#39;), <br>             (5,&#39;dummy-5&#39;), <br>             (7,&#39;dummy-7&#39;);<br><br>SESSION-1 &gt; SELECT * FROM lock_supremum;<br>+----+---------+<br>| id | fd1     |<br>+----+---------+<br>|  3 | dummy-3 |<br>|  5 | dummy-5 |<br>|  7 | dummy-7 |<br>+----+---------+</pre><p>이제 아래 2개 문장을 실행해서, 각 문장이 어떤 잠금을 가지는지 확인 &amp; 비교해보려고 해요.</p><p><strong>1. </strong>SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 5 FOR UPDATE;</p><pre>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 5 FOR UPDATE;<br>+----+---------+<br>| id | fd1     |<br>+----+---------+<br>|  3 | dummy-3 |<br>|  5 | dummy-5 |<br>+----+---------+<br><br>SESSION-3 &gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+---------------+-------------+-----------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |<br>+-----------------------+------------+-----------+---------------+-------------+-----------+<br>|                 44585 | NULL       | TABLE     | IX            | GRANTED     | NULL      |<br>|                 44585 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |<br>|                 44585 | PRIMARY    | RECORD    | X             | GRANTED     | 5         |<br>+-----------------------+------------+-----------+---------------+-------------+-----------+</pre><p><strong>2. </strong>SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 7 FOR UPDATE;</p><pre>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum WHERE id BETWEEN 3 AND 7 FOR UPDATE;<br>+----+---------+<br>| id | fd1     |<br>+----+---------+<br>|  3 | dummy-3 |<br>|  5 | dummy-5 |<br>|  7 | dummy-7 |<br>+----+---------+<br><br>SESSION-3 &gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>|                 44586 | NULL       | TABLE     | IX            | GRANTED     | NULL                   |<br>|                 44586 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 3                      |<br>|                 44586 | PRIMARY    | RECORD    | X             | GRANTED     | supremum pseudo-record |<br>|                 44586 | PRIMARY    | RECORD    | X             | GRANTED     | 5                      |<br>|                 44586 | PRIMARY    | RECORD    | X             | GRANTED     | 7                      |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+</pre><p>1번 쿼리는 id BETWEEN 3 AND 5 범위의 레코드를 FOR UPDATE로 잠금을 걸면서 SELECT 했으며, 2번 쿼리도 id BETWEEN 3 AND 7 범위의 레코드를 동일하게 FOR UPDATE 로 잠금을 걸면서 SELECT 하는 쿼리에요. 그런데 2번 쿼리에서 조금 이상한 것은 supremum psuedo-record에 대해서 X-lock 을 걸고 있다는 거에요. 1번 쿼리는 정확히 조건 범위의 레코드만 X-lock 이 걸린 것을 확인할 수 있는데, 2번 쿼리는 id=3부터 7까지의 레코드뿐만 아니라, id=7 다음에 위치한 supremum psuedo-record 에도 잠금을 걸고 있어요.</p><h3>이 현상이 무슨 문제가 되나요 ?</h3><p>위에서 살펴본 것과 같이 테이블의 마지막(Primary Key 정렬 순서상) 레코드 뒤에 위치한 supremum psuedo-record에 걸리는 X-lock이 어떤 문제를 유발하는지 간단히 살펴볼게요. 동일한 SELECT .. FOR UPDATE 문장이 실행중일때 다른 세션에서 INSERT 문장을 실행해보면, 쉽게 supremum에 걸려있는 X-lock의 영향 범위를 확인할 수 있어요.</p><pre>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum WHERE id BETWEEN 5 AND 7 FOR UPDATE;<br><br>SESSION-2 &gt; INSERT INTO lock_supremum VALUES (9, &#39;dummy-9&#39;);</pre><p>Session-1 트랜잭션이 COMMIT 되지 않으면, Session-2의 INSERT 문장은 끝나지 않고 계속 잠금 대기를 해요. 이때 잠금 상태는 performance_schema.data_locks 테이블을 SELECT 해보면 쉽게 확인할 수 있어요.</p><pre>SESSION-3 &gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+--------------------+-------------+------------------------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE          | LOCK_STATUS | LOCK_DATA              |<br>+-----------------------+------------+-----------+--------------------+-------------+------------------------+<br>|                 44587 | NULL       | TABLE     | IX                 | GRANTED     | NULL                   |<br>|                 44587 | PRIMARY    | RECORD    | X,INSERT_INTENTION | WAITING     | supremum pseudo-record |<br>|                 44586 | NULL       | TABLE     | IX                 | GRANTED     | NULL                   |<br>|                 44586 | PRIMARY    | RECORD    | X,REC_NOT_GAP      | GRANTED     | 5                      |<br>|                 44586 | PRIMARY    | RECORD    | X                  | GRANTED     | supremum pseudo-record |<br>|                 44586 | PRIMARY    | RECORD    | X                  | GRANTED     | 7                      |<br>+-----------------------+------------+-----------+--------------------+-------------+------------------------+</pre><p>위의 결과를 보면, Session-2의 44587번 트랜잭션이 supremum pseudo-record에 대해서 X,INSERT_INTENSION 잠금을 대기( Waiting)하고 있다는 것을 알 수 있어요. 여기에서 잠금 대기가 발생하는 이유는 X,INSERT_INTENSION 잠금과 X 잠금은 서로 호환되지 않기 때문이에요. 즉, 여기에서는 Session-1의 트랜잭션이 완료( COMMIT 또는 ROLLBACK )되어야만 Session-2의 INSERT 는 완료될 수 있다는 거죠. 만약 Session-1이 innodb_lock_wait_timeout (50초) 보다 오랜 시간동안 잠금을 해제하지 않는다면, Session-2는 Innodb lock wait timeout exceeded 라는 에러를 발생하게 될 거에요.</p><p>여기까지는 “당연한 것 아닌가?”라고 생각하실 수 있어요. 그런데 supremum psuedo-record 에서 supremum은 인덱스의 키 값이 가질 수 있는 가장 큰 가상의 값을 의미해요. 즉, supremum pseudo-record의 X-lock은 테이블의 인덱스에 (현재) 존재하는 가장 큰 값 이후의 모든 간격을 잠그는 잠금인 거에요. 그래서 테이블에 잠재적으로 INSERT될 수 있는 모든 값들이 저장될 간격을 잠궈버리면서, 동시 처리 성능에 상당히 부정적인 영향을 미치게 되는 거에요. 특히 다음에 저장될 값이 항상 증가하기만 하는AUTO_INCREMENT Primary Key를 사용하는 테이블에서는 이 현상의 영향 범위가 훨씬 더 크게 느껴질 수 있어요.</p><p>만약 여러분이 사용하는 테이블이 큐(Queue)와 비슷한 처리를 하는 형태로 사용된다면, 많은 쓰레드에서 이 테이블에 INSERT를 함과 동시에 다른 쓰레드에서는 레코드를 SELECT 하게 되는거죠. 이때 Subscriber는 동기화 처리를 위해서 SELECT .. FOR UPDATE 구문을 사용하게 되는데, 이때 SELECT .. FOR UPDATE 가 실행중인 동안에는 INSERT 를 동시에 실행하지 못하게 되는 동시 처리 문제가 발생하게 되는거에요.</p><h3>이 현상은 왜 발생하나요 ?</h3><p>이제 SELECT .. WHERE id BETWEEN 3 AND 7 FOR UPDATE 구문이 왜 id&gt;7 범위의 간격(Gap)에 잠금을 걸어야 하는지 의문이 생기게 되었을거에요. 충분히 납득할만한 이유가 있는 것일까요 ? 우선 이 질문에 대한 답변을 한다면, “그렇지 않다&quot;라고 생각해요. 그래서 <a href="https://bugs.mysql.com/bug.php?id=112106">MySQL 버그 레포트</a>(사실은 Bug report가 아니라 <strong>Feature request</strong>를 했던 것이지만)를 했지만, MySQL 담당자는 “버그 아님&quot;이라는 답변만 해주었어요. 그리고 이 잠금이 필요한 이유에 대해서 설명을 부탁했지만, 좋은 답변은 듣지 못했어요.</p><p>이 문제에 대해서 “Why?”를 고민하는데 많은 주말과 퇴근 후 시간들을 투자하고서야, 비로소 나름의 (조금은 허무한) 답변을 찾게 되었어요.</p><p>처음에는 왜 테이블의 마지막 레코드에 대해서만 이런 현상(Supremum gap lock 잠금)이 발생할까를 집중적으로 고민했어요. 소스 코드를 뒤지고 인터넷을 뒤져봤지만 납득할만한 이유는 보이지 않았어요. <a href="https://dba.stackexchange.com/questions/326565/why-innodb-locks-index-till-the-end">Stack exchange</a>에는 전혀 논리적이지 않은 내용도 정답인 것처럼 적어둔 답변도 있었어요🤣.</p><p>그러던 어느날 테이블의 마지막 레코드가 아니라, 모든 데이터 페이지의 마지막 레코드에서 모두 이 현상이 있는 것 아닐까라는 의문이 들었어요. 그래서 아래와 같이 많은 레코드를 가진 테이블을 생성하고, B-Tree의 중간쯤에 위치한 데이터 페이지의 마지막 레코드에 대해서 동일 SELECT .. FOR UPDATE 문장으로 테스트를 실행해봤어요.</p><pre>/* 하나의 레코드가 254 바이트를 사용하도록 테이블 준비 */<br>mysql&gt; CREATE TABLE lock_supremum2 (<br>         id INT NOT NULL AUTO_INCREMENT,<br>         fd1 CHAR(250) NOT NULL,<br>         PRIMARY KEY (id)<br>       ) CHARSET=latin1 COLLATE=latin1_bin;<br><br>mysql&gt; INSERT INTO lock_supremum2 SELECT NULL, &#39;dummy&#39; <br>         FROM information_schema.COLUMNS LIMIT 150;<br><br>mysql&gt; SELECT MAX(id), COUNT(*) FROM lock_supremum2;<br>+---------+----------+<br>| MAX(id) | COUNT(*) |<br>+---------+----------+<br>|     150 |      150 |<br>+---------+----------+</pre><p>위와 같이 테이블을 생성하고 INSERT 문장을 실행하면, lock_supremum2 테이블은 1부터 시작해서 150까지 연속된 id 값을 가지는 150건의 레코드를 가지게 되요. 아주 컴팩트하게 레코드를 저장했기 때문에, Primary Key를 구성하는 B-Tree 인덱스의 리프 페이지(Leaf page)는 대략 55개 정도의 레코드를 저장하게 되었어요. 이렇게 데이터가 저장된 B-Tree의 구조를 그림으로 살펴보면 아래와 같아요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*X35SbdNPGzQxnQpz5M_OrQ.png" /><figcaption>Primary Key의 B-Tree 구조</figcaption></figure><p>이제 각 리프 페이지의 마지막 id 값을 BETWEEN으로 조회하는 쿼리를 실행해서, 어떤 레코드가 잠기는지 살펴보도록 할게요.</p><pre>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum2 WHERE id BETWEEN 26 AND 27 FOR UPDATE;<br>SESSION-1*&gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>|               1890665 | NULL       | TABLE     | IX            | GRANTED     | NULL                   |<br>|               1890665 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 26                     |<br>|               1890665 | PRIMARY    | RECORD    | X             | GRANTED     | supremum pseudo-record |<br>|               1890665 | PRIMARY    | RECORD    | X             | GRANTED     | 27                     |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br><br>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum2 WHERE id BETWEEN 81 AND 82 FOR UPDATE;<br>SESSION-1*&gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>|               1890666 | NULL       | TABLE     | IX            | GRANTED     | NULL                   |<br>|               1890666 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 81                     |<br>|               1890666 | PRIMARY    | RECORD    | X             | GRANTED     | supremum pseudo-record |<br>|               1890666 | PRIMARY    | RECORD    | X             | GRANTED     | 82                     |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br><br>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; SELECT * FROM lock_supremum2 WHERE id BETWEEN 136 AND 137 FOR UPDATE;<br>SESSION-1*&gt; SELECT * FROM performance_schema.data_locks;<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>| ENGINE_TRANSACTION_ID | INDEX_NAME | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA              |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+<br>|               1890667 | NULL       | TABLE     | IX            | GRANTED     | NULL                   |<br>|               1890667 | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 136                    |<br>|               1890667 | PRIMARY    | RECORD    | X             | GRANTED     | supremum pseudo-record |<br>|               1890667 | PRIMARY    | RECORD    | X             | GRANTED     | 137                    |<br>+-----------------------+------------+-----------+---------------+-------------+------------------------+</pre><p>예상했던대로 테이블의 마지막 레코드가 문제가 아니라, 테이블을 구성하는 데이터 페이지의 마지막 레코드였기 때문에 supremum pseudo-record 에 Gap lock이 걸린 것이었어요.</p><h3>왜 supremum 레코드를 잠그나요 ?</h3><p>아래 문장이 실행되면, MySQL 서버는 BETWEEN 처리를 위해서 id=136 인 레코드를 찾고 그 레코드부터 id≤137 인 지점까지 하나씩 순서대로 읽으면서(스캔) 잠금을 걸어요.</p><p>SELECT FROM lock_supremum2 WHERE id BETWEEN 136 AND 137 FOR UPDATE</p><p>이 과정을 자세히 살펴보면 아래와 같아요.</p><ol><li>id=136인 레코드 읽기</li><li>WHERE 조건에 일치하므로 레코드 잠금</li><li>id=136 보다 큰 (첫번째) 레코드인 id=137 읽기</li><li>WHERE 조건에 일치하므로 레코드 잠금 (레코드 및 간격까지 잠금)</li><li>id=137보다 큰 다음 레코드 (supremum) 읽기</li><li>supremum 레코드는 가상 레코드이므로 잠금 (레코드 및 간격까지 잠금)</li><li>B-Tree의 리프 레벨에서 다음 페이지 읽기</li><li>다음 페이지에서 id=137 보다 큰 (첫번째) 레코드인 id=138 읽기</li><li>WHERE 조건에 일치하지 않으므로 잠금 걸지 않음</li><li>쿼리 완료</li></ol><p>그래서 WHERE id BETWEEN 136 AND 137 문장은 id=137 이후에 존재하는 supremum pseudo-record + gap에 대해서 잠금을 거는 것이죠.</p><p>그런데 만약 id=137 레코드가 (리프 페이지에서) 마지막 레코드가 아니고 그 뒤에 id=138 이 있었다면, MySQL 서버는 id=138 레코드를 읽고 스캔을 끝냈을 거에요. 그래서 이 경우에는 supremum 레코드 잠금이 필요치 않은거죠. 그리고 테이블의 마지막 레코드인 경우에는 이 현상이 더 심각하고 폭넓은 영향을 미치게 되는 것이고요.</p><p>&quot;왜 WHERE 조건에 부합되지도 않는 간격을 잠그는 걸까?&quot;라는 질문의 답은 의외로 간단한 것이었어요. 너무 오래전에 메뉴얼에서 봤던 내용이라, 머릿속에서 잊혀져 버렸던 거죠.</p><blockquote>By default, InnoDB operates in REPEATABLE-READ transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents phantom rows</blockquote><p>REPEATABLE-READ 격리 수준을 사용하는 MySQL 서버에서는 검색하고 스캔했던 인덱스 레코드를 잠근다는 아주 기본적인 규칙 때문에 잠금이 발생하고 있었던 것이죠. 사용자 데이터가 아닌 시스템 레코드(supremum)까지도 말이죠.</p><h3>이 현상을 어떻게 피할 수 있나요 ?</h3><p>이렇게 테이블에서 가장 큰 값 이후의 간격이 잠기는 현상을 회피할 수 있는 방법은 다음과 같이 2가지가 있어요.</p><ol><li>트랜잭션 격리 수준을 READ-COMMITTED 로 변경</li><li>id BETWEEN 149 AND 150 대신 id IN (149, 150) 으로 쿼리 변경</li></ol><p>MySQL 서버의 supremum 레코드 잠금은 사실 레코드 자체에 대한 잠금이 아니라 Gap 에 대한 잠금이에요. 위의 잠금 내용 조회 결과에서 supremum pseudo record 잠금은 X-lock (Record lock 과 Gap lock이 결합된 Next-key lock)으로 표시되어 있지만, MySQL 서버는 supremum 레코드에 걸린 Next-key lock은 Gap-lock으로만 해석해요. 그런데 트랜잭션의 격리 수준이 REPEATABLE-READ 가 아닌 READ-COMMITTED가 사용되면, MySQL 서버는 레코드 간의 간격에 대한 Gap-lock 을 거의 사용하지 않아요. 그래서 READ-COMMITTED 에서는 Gap-lock 의 성격만 가진 supremum 잠금은 불필요해요.</p><p>그리고 쿼리를 id BETWEEN ? AND ? 가 아니라 id IN (?, ?, ?, ..) 형태로 사용하면, MySQL 서버는 내부적으로 id=? 조건 여러 개를 합친 것으로 해석해요. 이때 id=? 검색 조건(Primary Key를 포함한 유니크 인덱스를 구성하는 모든 컬럼에 대한 동등 검색 조건)은 UNIQUE 조건이기 때문에REPEATABLE-READ 격리 수준에서도 Gap-lock 을 사용하지 않아요. 그래서id=?와 같은 단일 레코드를 보장하는 동등 비교 조건의 쿼리는 supremum 레코드 잠금이 사용되지 않아요. 만약 트랜잭션의 격리 수준을 READ-COMMITTED로 변경할 수 없다면, 2번 방법을 사용하는 것도 좋은 회피책이라 생각해요. 크게 신경쓰지 않고 있지만, 은연중에 우리 프로그램은 (MySQL 서버의 기본 격리 수준인) REPEATABLE-READ 격리 수준에 많이 의존하고 있을 수 있기 때문에, 격리 수준을 바꾸는 것은 조심해야 해요.</p><h3>(추가로) infimum 과 supremum</h3><p>B-Tree의 중간에 위치한 리프 페이지의 supremum pseudo-record Gap lock으로 인해서 정말 INSERT가 안되는 현상이 동일하게 발생하는지 아래와 같이 눈으로 확인해볼 수 있어요.</p><pre>/* INSERT 테스트를 위해서 B-Tree 리프 페이지의 경계에 있는 id=82와 id=83 레코드 삭제 */<br>SESSION-1 &gt; BEGIN;<br>SESSION-1*&gt; DELETE FROM lock_supremum2 WHERE id IN (82, 83);<br>SESSION-1*&gt; COMMIT;<br><br>/* id&gt;=80 AND id&lt;=81 조건의 레코드를 조회하면서 Exclusive Lock 획득 */<br>SESSION-2 &gt; BEGIN;<br>SESSION-2*&gt; SELECT * FROM lock_supremum2 WHERE id BETWEEN 80 AND 81 FOR UPDATE;<br>+----+-------+<br>| id | fd1   |<br>+----+-------+<br>| 80 | dummy |<br>| 81 | dummy |<br>+----+-------+<br>2 rows in set (0.00 sec)<br><br><br>/* id=83은 INSERT가 되지만, id=82 인 레코드는 Wait하게 됨 */<br>SESSION-3 &gt; BEGIN;<br><br>SESSION-3*&gt; INSERT INTO lock_supremum2 VALUES (83, &#39;dummy&#39;);<br>Query OK, 1 row affected (0.00 sec)<br><br>SESSION-3*&gt; INSERT INTO lock_supremum2 VALUES (82, &#39;dummy&#39;);<br>ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction</pre><p>Session-2의 SELECT .. FOR UPDATE 로 인해서 2번째 리프 페이지(page-no=6)의 supremum pseudo-record 에는 배타적 잠금이 걸렸어요. 이 상태에서 Session-3에서 id=83 레코드를 INSERT하면, 이 레코드는 3번째 리프 페이지(page-no=7)에 저장되기 때문에 Session-2에서 점유하고 있는 배타적 잠금에 관계없이 INSERT가 정상 완료될 수 있어요. 하지만 id=82 레코드를 INSERT하면, 2번째 리프 페이지(page-no=6)의 supremum pseudo-record 에 걸려있는 배타적 잠금 때문에 실행되지 못하고 대기해요. 그리고 위의 예제에서는 50초 동안 대기하다가, 결국 Lock wait timeout exceeded 에러로 실패하는 것을 확인할 수 있어요.</p><p>직전에 살펴보았던 예제에서 한가지 더 이상한 것은, id=83 레코드는 SELECT .. FOR UPDATE 의 배타적 잠금 영향을 받지 않았다는 것인데요. 이 부분을 더 자세히 살펴보기 위해서는, B-Tree 인덱스 페이지의 구조를 더 자세히 살펴볼 필요가 있어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/605/1*Zdhi4za9A6mGI9dCu-joSw.png" /><figcaption>B-Tree 페이지의 인덱스 키 구성</figcaption></figure><p>위 그림에서는 각 인덱스 페이지에 infimum 과 supremum 이라는 인덱스 키가 2개 더 추가되었는데, 이는 MySQL 서버에서 관리하는 가상의 최소 &amp; 최대 레코드를 의미해요. 여기에서 가상의 최소 &amp; 최대 값이란 해당 데이터 페이지내에서 최소 및 최대를 의미해요. MySQL 서버에서 infimum 레코드는 잠금의 대상이 아니지만, supremum 레코드는 사용자 데이터와 비슷하게 잠금의 대상이에요.</p><p>그래서 SELECT .. WHERE id BETWEEN 80 AND 81 FOR UPDATE 구문이 실행되면 MySQL 서버는 id=80인 레코드부터 page-no=6 페이지의 supremum 레코드까지 잠금을 걸기 때문에, page-no=7 페이지의 제일 앞부분에 저장되는 id=83 레코드는 대기없이 INSERT되었어요. 하지만 ipage-no=6 페이지의 마지막에 저장되어야 하는 id=82 레코드는 INSERT되지 못하고 대기하고 있었던 것이죠.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=49727c005084" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-gap-lock-%EB%91%90%EB%B2%88%EC%A7%B8-%EC%9D%B4%EC%95%BC%EA%B8%B0-49727c005084">MySQL Gap Lock (두번째 이야기)</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[VARCHAR vs TEXT]]></title>
            <link>https://medium.com/daangn/varchar-vs-text-230a718a22a1?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/230a718a22a1</guid>
            <category><![CDATA[엔지니어링]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Sun, 25 Jun 2023 09:40:59 GMT</pubDate>
            <atom:updated>2023-06-25T09:42:37.631Z</atom:updated>
            <content:encoded><![CDATA[<h3>개요</h3><p>MySQL 서버를 포함한 RDBMS를 사용하다 보면, No-SQL DBMS 서버에 비해서 많은 데이터 타입들을 가지고 있다는 것을 알고 있을거에요. 하지만 RDBMS를 사용하면서 이런 다양한 데이터 타입에 대해서 정확한 용도와 특성을 모르면 RDBMS 서버가 어렵게 구현하고 있는 장점을 놓쳐 버릴 가능성이 높아요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*no8xpiO-h88d-8JaoLnUKw.png" /></figure><p>오늘은 많은 개발자와 DBA들이 잘 모르고 있는 MySQL 서버의 VARCHAR 와 TEXT 타입의 특성과 작동 방식에 대해서 좀 살펴보려고 해요.</p><h3>VARCHAR 타입 궁금증</h3><p>MySQL 서버를 사용해 본 개발자라면 누구나 한번쯤은 이런 궁금증을 가져 본 적이 있을거에요.</p><ul><li>만약 10 글자 이하로만 저장된다면 컬럼의 타입을 VARCHAR(10)으로 하거나 VARCHAR(1000)으로 해도 아무런 차이가 없는 것 아닐까? 오히려 VARCHAR(1000)으로 만들어 두면 나중에 더 큰 값을 저장해야 할 때 더 유연하게 대응할 수 있지 않을까 ?</li></ul><p>아래와 같이 모든 컬럼을 VARCHAR(1000) 타입으로 또는 모든 컬럼을 TEXT 타입으로 생성한 테이블은 어떻게 생각하나요 ? 이런 모델링이 잘못되었다고 생각한다면 그 근거는 무엇인가요 ?</p><pre>CREATE TABLE user (<br>  id       BIGINT NOT NULL,<br>  name     VARCHAR(1000),<br>  phone_no VARCHAR(1000),<br>  address  VARCHAR(1000),<br>  email    VARCHAR(1000),<br>  PRIMARY KEY(id)<br>);<br></pre><p>그냥 지금까지 습관적으로 해오던 데이터 모델링 방법과는 다르기 때문에 잘못된 것일까요 ? MySQL 서버가 내부적으로 어떻게 작동하는지를 모르면, 이 질문에 명확한 답변을 하기는 어려울 수 있어요.</p><p>테이블의 컬럼이 많은 경우, 이 질문에 대해서 명확한 답변이 될만한 근거가 한가지 있어요. 간단한 테스트를 위해서 길이가 매우 긴 VARCHAR 컬럼을 가진 테이블을 한번 만들어 볼까요 ?</p><pre><br>mysql&gt; CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(1000000));<br>ERROR 1074 (42000): Column length too big for column &#39;fd1&#39; (max = 16383); use BLOB or TEXT instead<br><br>mysql&gt; CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd1 VARCHAR(16383));<br>ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs<br><br>mysql&gt; CREATE TABLE tb_long_varchar (id INT PRIMARY KEY, fd VARCHAR(16382));<br>Query OK, 0 rows affected (0.19 sec)<br><br>mysql&gt; ALTER TABLE tb_long_varchar ADD fd2 VARCHAR(10);<br>ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs</pre><p>tb_long_varchar 테이블은 하나의 VARCHAR 컬럼이 있는데, VARCHAR 타입의 최대 저장 가능 길이를 어느 정도로 하느냐에 따라서 테이블을 생성하지 못하게 되는 것을 확인할 수 있어요. 그리고 네번째 ALTER TABLE 문장의 실행 예제를 보면, 새로운 컬럼 추가가 실패한 것을 알 수 있어요. 이는 (에러 메시지에서도 잘 설명하고 있듯이) 이미 tb_long_varchar 테이블은 하나의 레코드가 저장할 수 있는 최대 길이가 65,535 바이트를 초과했기 때문에 더이상 새로운 컬럼을 추가할 수 없게 된 거에요.</p><p>이 예제를 통해서 MySQL 서버에서는 하나의 VARCHAR 컬럼이 너무 큰 길이를 사용하면, 다른 컬럼들이 사용할 수 있는 최대 공간의 크기가 영향을 받게 된다는 것을 확인했어요. 그래서 MySQL 서버에서는 레코드 사이즈 한계로 인해서, VARCHAR 타입의 최대 저장 길이 설정시에 공간을 아껴서 설정해야 해요.</p><p><em>이는 MySQL 서버 메뉴얼에서 이미 자세히 설명하고 있어요. 참고로 </em><em>TEXT나 </em><em>BLOB와 같은 </em><em>LOB 컬럼은 이 제한 사항에 거의 영향을 미치지 않아요. 그래서 많은 컬럼을 가진 테이블에서는 </em><em>VARCHAR 타입 대신 </em><em>TEXT 타입을 사용해야 할 수도 있어요.</em></p><p>그런데 VARCHAR 타입의 길이 설정에 주의해야 하는 이유가 이거 하나 뿐일까요 ? 예를 들어서 추가로 새로운 컬럼이 필요치 않아서 아래와 같이 테이블을 모델링했다면, 이건 아무 문제가 없는 걸까요 ?</p><pre>CREATE TABLE user (<br>  id       BIGINT NOT NULL,<br>  name     VARCHAR(4000),<br>  phone_no VARCHAR(4000),<br>  address  VARCHAR(4000),<br>  email    VARCHAR(4000),<br>  PRIMARY KEY(id)<br>);</pre><h3>TEXT 타입 궁금증</h3><p>VARCHAR 타입은 저장 길이 설정에 대한 주의가 필요하다는 것을 간단히 한번 살펴보았어요. 그런데 VARCHAR 대신 TEXT 타입을 사용하면 길이 제한 문제가 싹 사라진다는 것을 쉽게 확인할 수 있어요. 그래서 아래와 같이 테이블을 만들면 VARCHAR 타입의 길이 설정에 대한 제약뿐만 아니라 저장하는 값의 길이 제한도 훨씬 크고 유연하게 테이블을 만들 수 있어요.</p><pre>CREATE TABLE user (<br>  id       BIGINT NOT NULL,<br>  name     TEXT,<br>  phone_no TEXT,<br>  address  TEXT,<br>  email    TEXT,<br>  PRIMARY KEY(id)<br>);</pre><p>여기에서 또 하나의 궁금증이 생기기 시작할거에요.</p><ul><li>문자열 저장용 컬럼을 생성할 때, VARCHAR와 TEXT 중에서 굳이 VARCHAR를 선택할 이유가 있을까 ? TEXT 타입은 저장 가능 길이도 훨씬 크고 테이블 생성할 때 굳이 길이 제한을 결정하지 않아도 되니 더 좋은 것 아닐까 ? 근데 왜 우리가 모델링하는 테이블에서 대부분 문자열 저장용 컬럼은TEXT 컬럼이 아니라 VARCHAR 컬럼이 사용될까 ?</li></ul><h3>VARCHAR vs TEXT</h3><p>일반적인 RDBMS에서, TEXT(또는 CLOB)나 BLOB와 같은 대용량 데이터를 저장하는 컬럼 타입을 LOB(Large Object) 타입이라고 해요. 그리고 RDBMS 서는 LOB 데이터를 Off-Page 라고 하는 외부 공간에 저장해요. 일반적인 RDBMS에서와 같이, MySQL 서버도 레코드의 컬럼 데이터는 B-Tree (Clustering Index)에 저장(이를 Inline 저장이라고 함)하지만, 용량이 큰 LOB 데이터는 B-Tree 외부의 Off-Page 페이지(MySQL 서버 메뉴얼에서는 External off-page storage라고 해요)로 저장해요.</p><p>하지만 MySQL 서버는 LOB 타입의 컬럼을 항상 Off-Page로 저장하지는 않고, 길이가 길어서 저장 공간이 많이 필요한 경우에만 Off-Page로 저장해요.</p><p>예를 들어서 아래와 같이 2개의 레코드가 있을 때, 1번 레코드(id=1)의 fd 컬럼에 8,100 글자(8,100바이트)를 저장하면 Off-Page가 아닌 B-Tree(Clustering Index)에 Inline으로 저장해요. 하지만 2번 레코드(id=2)의 fd 컬럼에 8,101글자(8,101바이트)를 저장하면, MySQL 서버는 fd 컬럼을 Off-Page로 저장해요. 이는 MySQL 서버의 레코드 포맷에 따라서 조금씩 다르게 작동하는데, 이 예제는 innodb_default_row_format=DYNAMIC 설정을 기준으로 테스트해본 결과에요.</p><pre>CREATE TABLE tb_lob (<br>  id INT PRIMARY KEY,<br>  fd TEXT<br>);<br><br>INSERT INTO tb_lob VALUES (1, REPEAT(&#39;A&#39;,8100));  -- // Inline 저장소<br>INSERT INTO tb_lob VALUES (2, REPEAT(&#39;A&#39;,8101));  -- // Off-Page 저장소</pre><p>MySQL 서버의 레코드 크기 제한은 65,535 바이트이지만, InnoDB 스토리지 엔진의 레코드 크기 제한은 페이지(블록)의 크기에 따라서 달라지는데, 대부분 페이지 크기의 절반이 InnoDB 스토리지 엔진의 최대 레코드 크기 제한으로 작동해요.</p><p>InnoDB 스토리지 엔진은 레코드의 전체 크기가 이 제한 사항(16KB 페이지에서는 8,117 바이트)을 초과하면 길이가 긴 컬럼을 선택해서 Off-Page로 저장하게 되는데, 이 예제의 두번째 레코드(id=2)의 fd 컬럼 값이 커서 이 컬럼을 Off-Page로 저장한 것이에요.</p><p><em>MySQL 서버의 InnoDB </em><em>row_format에 따른 </em><em>Off-Page 저장 방식 차이는 </em><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html"><em>MySQL 서버 메뉴얼</em></a><em>을 참고해주세요. 페이지 크기가 64KB인 경우 InnoDB 최대 레코드 크기의 제한 사항이 예외적으로 조금 다르므로, MySQL 서버와 InnoDB 스토리지 엔진의 레코드 크기 제한에 대한 자세한 설명은 </em><a href="https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html#row-size-limits"><em>MySQL 서버 메뉴얼</em></a><em>을 참고해주세요.</em></p><p>그런데 동일한 테스트를 아래와 같이 VARCHAR 타입 컬럼으로 해보면, VARCHAR 컬럼에 저장된 값이 큰 경우에도 Off-Page로 저장된다는 것이에요.</p><pre>CREATE TABLE tb_varchar (<br>  id INT PRIMARY KEY,<br>  fd VARCHAR<br>);<br><br>INSERT INTO tb_varchar VALUES (1, REPEAT(&#39;A&#39;,8100)); -- // Inline 저장소<br>INSERT INTO tb_varchar VALUES (2, REPEAT(&#39;A&#39;,8101)); -- // Off-Page 저장소</pre><p>VARCHAR 타입은 인덱스를 생성할 수 있는 반면 LOB 타입은 인덱스 생성을 할 수 없다는 이야기를 하는 사람도 있지만, 사실은 둘다 최대 크기 길이 제한만 충족시켜 주면 인덱스를 생성 할 수 있어요.</p><pre>-- // 컬럼 그대로 사용시, 인덱스 생성 불가<br>mysql&gt; ALTER TABLE tb_varchar ADD INDEX ix_fd (fd);<br>ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes<br><br>mysql&gt; ALTER TABLE tb_lob ADD INDEX ix_fd (fd);<br>ERROR 1170 (42000): BLOB/TEXT column &#39;fd&#39; used in key specification without a key length<br><br>-- // 컬럼 값의 길이(프리픽스)를 지정하면, 인덱스 생성 가능<br>mysql&gt; ALTER TABLE tb_varchar ADD INDEX ix_fd ( fd(50) );<br>mysql&gt; ALTER TABLE tb_lob ADD INDEX ix_fd ( fd(50) );</pre><p>B-Tree 인덱스뿐만 아니라 전문 검색 인덱스도 TEXT 타입과 VARCHAR 타입 컬럼 모두 동일하게 생성할 수 있어요. 보면 볼수록 TEXT와 VARCHAR의 차이가 명확해지기 보다는 오히려 모호해지고 있다는 느낌일 거에요. 도대체 TEXT 컬럼과 VARCHAR 컬럼의 차이는 무엇이며, 어떤 경우에 TEXT 타입을 사용하고 어떤 경우에 VARCHAR 타입을 사용해야 할까요 ?</p><h3>VARCHAR와 TEXT의 메모리 활용</h3><p>MySQL 서버는 스토리지 엔진과 Handler API를 이용해서 데이터를 주고 받는데, 이때 MySQL 엔진과 InnoDB 스토리지 엔진은 uchar* records[2] <a href="https://github.com/mysql/mysql-server/blob/mysql-cluster-8.0.33/sql/table.h#L1438">메모리 포인터</a>를 이용해서 레코드 데이터를 주고 받아요. 이때 records[2] 메모리 객체는 실제 레코드의 데이터 크기에 관계 없이 최대 크기로 메모리를 할당해둬요. VARCHAR 타입은 최대 크기가 설정되기 때문에 메모리 공간을 records[2] 버퍼에 미리 할당받아둘 수 있지만, TEXT나 BLOB와 같은 LOB 컬럼 데이터의 경우 실제 최대 크기만큼 메모리를 할당해 두면 메모리 낭비가 너무 심해지는 문제가 있어요. 그래서 records[2] 포인터가 가리키는 메모리 공간은 VARCHAR는 포함하지만 TEXT 컬럼을 위한 공간은 포함하지 않아요.</p><p>uchar* records[2] 메모리 공간은 TABLE 구조체(struct) 내에 정의되어 있으며 TABLE 구조체는 MySQL 서버 내부에 캐싱되어서 여러 컨넥션에서 공유해서 사용될 수 있도록 구현되어 있어요. 즉, records[2] 메모리 버퍼는 처음 한번 할당되면 많은 컨넥션들에 의해서 재사용될 수 있도록 설계된 것이에요.<br>하지만 TEXT나 BLOB과 같은 LOB 컬럼을 위한 메모리 공간은 records[2]에 미리 할됭되어 있지 않기 때문에 매번 레코드를 읽고 쓸 때마다 필요한 만큼 메모리가 할당되어야 해요.</p><p>예를 들어서 아래와 같은 테이블을 생성했다면,</p><pre>CREATE TABLE tb_lob (<br>  id INT PRIMARY KEY,<br>  fd TEXT<br>);<br><br>CREATE TABLE tb_varchar1 (<br>  id INT PRIMARY KEY,<br>  fd VARCHAR(100)<br>);<br><br>CREATE TABLE tb_varchar2 (<br>  id INT PRIMARY KEY,<br>  fd VARCHAR(10000)<br>);</pre><p>tb_lob 테이블을 위한 records[2] 버퍼 공간은 16 * 2 바이트만큼 할당되고, tb_varchar1 테이블의 records[2] 버퍼 공간으로는 408 * 2 바이트를 할당해요. 그리고 마지막 tb_varchar2 테이블을 위해서는 40008 * 2 바이트를 할당해요.</p><ul><li>tb_lob 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 TEXT 값을 위한 포인터 공간 8바이트 그리고 헤더 공간 4바이트</li><li>tb_varchar1 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 VARCHAR(100)타입 컬럼을 위한 공간 400바이트 그리고 헤더 공간 4바이트</li><li>tb_varchar2 테이블은 INT 타입의 컬럼(id)을 위한 4 바이트와 VARCHAR(10000) 타입 컬럼을 위한 공간 40000바이트 그리고 헤더 공간 4바이트</li></ul><p>그래서 VARCHAR 타입의 컬럼을 읽을 때는 새롭게 메모리를 할당받는 것이 아니라 TABLE 구조체의 records[2] 버퍼를 이용해요. 하지만 TEXT나 BLOB와 같은 LOB 타입의 컬럼을 읽을 때는 (미리 할당해 둔 메모리 공간이 없기 때문에) 매번 필요한 크기만큼 메모리를 할당해서 사용후 해제해야 해요. LOB 컬럼의 값을 읽기 위해서 할당 및 해제하는 메모리 공간은 Performance_schema에 의해서 측정되지 않아요 (MySQL 8.0.33 기준). 그래서 LOB용 메모리 할당 해제가 실행되는지 알 수 없어서 성능 영향도를 파악하기가 어려운 상황이에요. 한가지 더 주의해야 할 것은 VARCHAR 타입에 저장된 값의 길이가 길어서 Off-Page로 저장된 경우, MySQL 서버는 TABLE 객체의 records[2] 버퍼를 사용하지 못하고 새롭게 메모리 공간을 할당해서 사용해요. 그래서 VARCHAR 타입에 매우 큰 값이 빈번하게 저장되는 경우는 주의가 필요해요.</p><h3>컬럼 타입 선정 규칙</h3><p>MySQL 서버의 내부적인 작동에서, VARCHAR와 TEXT 타입의 큰 차이점을 살펴보았어요. 지금까지 살펴본 내용을 토대로 VARCHAR나 TEXT 타입을 선택하는 규칙을 다음과 같이 정리해 볼 수 있어요.</p><h4>VARCHAR</h4><ul><li>최대 길이가 (상대적으로) 크지 않은 경우</li><li>테이블 데이터를 읽을 때 항상 해당 컬럼이 필요한 경우</li><li>DBMS 서버의 메모리가 (상대적으로) 충분한 경우</li></ul><h4>TEXT</h4><ul><li>최대 길이가 (상대적으로) 큰 경우</li><li>테이블에 길이가 긴 문자열 타입 컬럼이 많이 필요한 경우</li><li>테이블 데이터를 읽을 때 해당 컬럼이 자주 필요치 않은 경우</li></ul><p><strong>상대적</strong>이라는 단어가 많이 사용된 것은 DBMS 서버의 스펙이나 데이터 모델 그리고 유입되는 트래픽에 따라서 미치는 영향도가 다르기 때문이에요. 뿐만 아니라 DBMS 서버의 튜닝은 생산성(속도)과 효율성 사이에서 최적점(sweet-spot)을 찾는 과정이기 때문에 숫자 값 하나를 모든 판단의 기준으로 정하는 것은 불가능해요.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=230a718a22a1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/varchar-vs-text-230a718a22a1">VARCHAR vs TEXT</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL Optimizer Error]]></title>
            <link>https://medium.com/daangn/mysql-optimizer-error-e438aa02e622?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/e438aa02e622</guid>
            <category><![CDATA[엔지니어링]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Mon, 15 May 2023 08:39:57 GMT</pubDate>
            <atom:updated>2023-05-17T01:44:25.570Z</atom:updated>
            <content:encoded><![CDATA[<h3>옵티마이저 에러 &amp; 힌트</h3><p>RDBMS의 쿼리 최적화 기능(Optimizer)은 MySQL 서버 뿐만 아니라 모든 RDBMS에서 매우 복잡한 부분 중 하나에요. 옵티마이저는 제한된 정보를 이용해서 매우 짧은 시간내에 쿼리의 최적화된 실행 계획을 수립해야 하기 때문에, 매우 정교하면서도 빠르게 실행되어야 해요.</p><figure><img alt="세상에서 가장 빠른 고양이" src="https://cdn-images-1.medium.com/max/1024/1*lJ3uiHohUXzcp5mGHP8hEA.png" /><figcaption>세상에서 가장 날쌘 고양이</figcaption></figure><p>이런 이유로 많은 상용 RDBMS에서 아직도 옵티마이저 관련 오류가 빈번하게 발생해요. 그래서 많은 상용 RDBMS들은, 이런 문제를 회피할 수 있도록 옵티마이저 힌트 기능을 제공하고 있어요. 하지만 옵티마이저 힌트는 인덱스의 이름을 프로그래밍 코드에 명시해야 하며, RDBMS의 옵티마이저는 주어진 인덱스 힌트를 매우 신뢰하기 때문에 더 나은 실행 계획이 있다 하더라도 SQL에 주어진 힌트를 사용하는 경향이 있어요. SQL 문장에서 옵티마이저 힌트를 한번 사용하게 되면, 왠만해서는 그 힌트를 변경하지 않아요. 하지만 시간이 지나면서 DBMS의 데이터는 계속 바뀌게 되고 예전의 최적 실행 계획이 지금은 최적이 아닌 경우가 발생할 수 있기 때문에 가능하면 옵티마이저 힌트를 사용하지 않는 것을 추천해요.</p><p>오늘은 상당히 치명적인 옵티마이저 오류 케이스를 살펴보고, 옵티마이저가 왜 그런 잘못된 선택을 했는지 원인을 분석하는 과정을 한번 살펴보고, 이를 회피할 수 있는 방법은 어떤 것들이 있는지도 한번 살펴보려고 해요.</p><h3>실행 계획 오류 (ref vs range) 케이스</h3><p>우선 대상 테이블의 구조와 테이블 정보는 다음과 같아요.</p><pre>CREATE TABLE tab (<br>  id bigint NOT NULL AUTO_INCREMENT,<br>  bid bigint NOT NULL,<br>  created_at datetime DEFAULT NULL,<br>  ...<br>  PRIMARY KEY (id),<br>  KEY ix_bid_id (bid,id),<br>  KEY ix_bid_createdat (bid,created_at)<br>);<br><br>mysql&gt; SHOW INDEXES FROM tab;<br>+-------+------------+------------------+--------------+-------------+-------------+<br>| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Cardinality |<br>+-------+------------+------------------+--------------+-------------+-------------+<br>| tab   |          0 | PRIMARY          |            1 | id          |    11797584 |<br>| tab   |          1 | ix_bid_id        |            1 | bid         |      581387 |<br>| tab   |          1 | ix_bid_id        |            2 | id          |    11797584 |<br>| tab   |          1 | ix_bid_createdat |            1 | bid         |      561151 |<br>| tab   |          1 | ix_bid_createdat |            2 | created_at  |    11702179 |<br>+-------+------------+------------------+--------------+-------------+-------------+</pre><p>이제 ix_bid_createdat(bid, created_at) 인덱스와 ix_bid_id(bid, id) 인덱스를 사용할 것으로 예상되는 2개 쿼리의 실행 계획을 한번 확인해볼게요.</p><pre><br>mysql&gt; EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>+------+-----------+---------+-------+---------+----------+-------------+<br>| type | key       | key_len | ref   | rows    | filtered | Extra       |<br>+------+-----------+---------+-------+---------+----------+-------------+<br>| ref  | ix_bid_id | 8       | const | 1632722 |    33.33 | Using where |<br>+------+-----------+---------+-------+---------+----------+-------------+<br><br>mysql&gt; EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND id&lt;14025956;<br>+------+------------------+---------+-------+---------+----------+--------------------------+<br>| type | key              | key_len | ref   | rows    | filtered | Extra                    |<br>+------+------------------+---------+-------+---------+----------+--------------------------+<br>| ref  | ix_bid_createdat | 8       | const | 1426712 |    50.00 | Using where; Using index |<br>+------+------------------+---------+-------+---------+----------+--------------------------+</pre><p>MySQL 옵티마이저가 뭔가 잘못된 인덱스를 선택한 것이 보이시죠. 그냥 깊이 생각하지 않아도 첫번째 쿼리는 ix_bid_createdat(bid, created_at)인덱스를 사용하면 커버링 인덱스로 매우 빠르게 처리될 수 있고, 두번째 쿼리는 ix_bid_id(bid, id)인덱스를 사용하면 똑같이 커버링 인덱스로 빠르게 처리될 쿼리라는 것을 알수 있어요. 하지만 MySQL 옵티마이저는 2개 쿼리에 대해서 거꾸로 인덱스를 선택해버렸어요.</p><p>이로 인해서 2개 쿼리는 0.2초면 완료된 쿼리가 3초 가까이 걸려서 완료되었어요. 뿐만 아니라 디스크 읽기 작업과 CPU 사용량 또한 상당히 많이 높아졌고, 결과적으로 DBMS 서버의 모든 쿼리가 지연되는 문제가 발생하기도 해요.</p><h3>간단한 해결 방법</h3><p>다음과 같이 옵티마이저 힌트를 이용하면 어렵지 않게 회피할 수 있어요.</p><pre>SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*) <br>FROM tab<br>WHERE bid=1198442 <br>  AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br><br>SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*) <br>FROM tab<br>WHERE bid=1198442 <br>  AND id&lt;14025956;</pre><p>힌트를 사용한 쿼리의 실행 계획을 한번 살펴보면 다음과 같아요. 하지만 오늘은 왜 옵티마이저가 이런 판단을 했는지, 옵티마이저 힌트 없이 최적화 오류를 회피할 수 있는 방법은 없는지 깊이 있게 한번 생각해보려고 해요.</p><pre>SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*) <br>FROM tab<br>WHERE bid=1198442 <br>  AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>+-------+------------------+---------+------+---------+----------+--------------------------+<br>| type  | key              | key_len | ref  | rows    | filtered | Extra                    |<br>+-------+------------------+---------+------+---------+----------+--------------------------+<br>| range | ix_bid_createdat | 14      | NULL | 1490996 |   100.00 | Using where; Using index |<br>+-------+------------------+---------+------+---------+----------+--------------------------+</pre><p>여기에서 주의깊게 봐야 할 부분은 성능이 느린 실행 계획에서는 type 필드의 값이 ref 였었는데, 옵티마이저 힌트를 사용해서 성능이 개선된 쿼리의 실행 계획에서는 range로 바뀌었다는 것이에요. 실제 이 쿼리의 최적화 오류는 ref 접근 방법의 비용 계산이 너무 낮게 계산되면서 발생한 문제인데, ref 접근 방법의 비용이 이렇게 낮게 계산된 이유를 이제부터 살펴보려고 해요.</p><h3>쿼리 비용 계산</h3><p>우선 MySQL 서버의 쿼리 비용 계산 과정을 살펴보기 전에, MySQL 서버에서 사용하는 2가지 단위 작업에 대한 비용 상수(메뉴얼에서는 이를 Cost Model 이라고 해요)에 대해서 이해를 해야 하는데, MySQL 서버에서 단위 작업에 대한 비용 상수는 아래와 같이 확인할 수 있어요. 이 비용 상수에 대한 자세한 설명은 <a href="http://www.yes24.com/Product/Goods/103415627">Real MySQL 1권</a>을 참고해주세요. 우선 지금은 비용 상수는 변경없이 MySQL 서버의 기본 값을 그대로 사용중이라는 것을 기억해주세요.</p><pre>mysql&gt; SELECT * FROM mysql.server_cost;<br>+------------------------------+------------+---------------+<br>| cost_name                    | cost_value | default_value |<br>+------------------------------+------------+---------------+<br>| disk_temptable_create_cost   |       NULL |            20 |<br>| disk_temptable_row_cost      |       NULL |           0.5 |<br>| key_compare_cost             |       NULL |          0.05 |<br>| memory_temptable_create_cost |       NULL |             1 |<br>| memory_temptable_row_cost    |       NULL |           0.1 |<br>| row_evaluate_cost            |       NULL |           0.1 |<br>+------------------------------+------------+---------------+<br><br>mysql&gt; SELECT * FROM mysql.engine_cost;<br>+------------------------+------------+---------------+<br>| cost_name              | cost_value | default_value |<br>+------------------------+------------+---------------+<br>| io_block_read_cost     |       NULL |             1 |<br>| memory_block_read_cost |       NULL |          0.25 |<br>+------------------------+------------+---------------+</pre><p>이제 MySQL 서버에서 다음 쿼리가 ix_bid_id(bid, id)인덱스와 ix_bid_createdat(bid, created_at)인덱스를 사용할 때, 각각 쿼리 처리에 필요한 비용이 얼마일지 예측한 값을 비교해보았어요.</p><pre>mysql&gt; EXPLAIN FORMAT=JSON<br>       SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*) <br>       FROM tab<br>       WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>{<br>  &quot;query_block&quot;: {<br>    &quot;select_id&quot;: 1,<br>    &quot;cost_info&quot;: {<br>      &quot;query_cost&quot;: &quot;236946.95&quot;<br>    },<br>    &quot;table&quot;: {<br>      &quot;table_name&quot;: &quot;tab&quot;,<br>      &quot;access_type&quot;: &quot;ref&quot;,<br>      &quot;key&quot;: &quot;ix_bid_id&quot;,<br>      &quot;used_key_parts&quot;: [<br>        &quot;bid&quot;<br>      ],<br>      &quot;key_length&quot;: &quot;8&quot;,<br>      &quot;rows_examined_per_scan&quot;: 1632722,<br>      &quot;rows_produced_per_join&quot;: 544186,<br>      &quot;filtered&quot;: &quot;33.33&quot;,<br>      &quot;cost_info&quot;: {<br>        &quot;read_cost&quot;: &quot;73674.75&quot;,<br>        &quot;eval_cost&quot;: &quot;54418.62&quot;,<br>        &quot;prefix_cost&quot;: &quot;236946.95&quot;,<br>        &quot;data_read_per_join&quot;: &quot;24M&quot;<br>      },<br>      ...<br>    }<br>  }<br>}<br><br>mysql&gt; EXPLAIN FORMAT=JSON<br>       SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*) <br>       FROM tab<br>       WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>{<br>  &quot;query_block&quot;: {<br>    &quot;select_id&quot;: 1,<br>    &quot;cost_info&quot;: {<br>      &quot;query_cost&quot;: &quot;302192.75&quot;<br>    },<br>    &quot;table&quot;: {<br>      &quot;table_name&quot;: &quot;tab&quot;,<br>      &quot;access_type&quot;: &quot;range&quot;,<br>      &quot;key&quot;: &quot;ix_bid_createdat&quot;,<br>      &quot;used_key_parts&quot;: [<br>        &quot;bid&quot;,<br>        &quot;created_at&quot;<br>      ],<br>      &quot;key_length&quot;: &quot;14&quot;,<br>      &quot;rows_examined_per_scan&quot;: 1490996,<br>      &quot;rows_produced_per_join&quot;: 1490996,<br>      &quot;filtered&quot;: &quot;100.00&quot;,<br>      &quot;using_index&quot;: true,<br>      &quot;cost_info&quot;: {<br>        &quot;read_cost&quot;: &quot;153093.15&quot;,<br>        &quot;eval_cost&quot;: &quot;149099.60&quot;,<br>        &quot;prefix_cost&quot;: &quot;302192.75&quot;,<br>        &quot;data_read_per_join&quot;: &quot;68M&quot;<br>      },<br>      ...<br>    }<br>  }<br>}</pre><p>ix_bid_id(bid, id) 인덱스를 사용한 경우 전체 쿼리 비용은 234128.51(query_cost)이며, ix_bid_createdat(bid, created_at) 인덱스를 사용한 경우 쿼리 비용은 302192.75(query_cost)였다는 것을 확인할 수 있어요. 이 결과만 보면, 옵티마이저는 ix_bid_id 인덱스를 사용하는 실행 계획이 더 빠를 것이라고 예측했다는 것을 알 수 있죠. 하지만 실제 쿼리 실행 결과는 10배나 더 걸렸다는 것을 위해서 확인했죠.</p><p>MySQL 의 쿼리 비용은 참조해야 하는 모든 테이블의(read_cost + eval_cost)의 합이에요. read_cost는 CPU 기반 처리 작업의 비용이며, eval_cost 는 디스크 또는 메모리에서 데이터 페이지 읽기 작업의 비용을 의미해요. 이번 예제는 조인이 없기 때문에 단순히 read_cost와 eval_cost의 합이 전체 쿼리 비용(query_cost)이 되요. 그런데 ix_bid_createdat 인덱스의 경우 query_cost=(read_cost + eval_cost) 수식이 성립되지만, ix_bid_id 인덱스의 경우 query_cost는 (read_cost + eval_cost)와 일치하지 않아요. 이는 ref 접근 방법의 비용 표현에서 일부가 누락되었기 때문이에요.</p><p>또한 MySQL 8.0 버전부터는 테이블의 데이터 페이지가 메모리(InnoDB Buffer Pool)에 얼마나 적재되어 있는지 비율도 같이 계산되도록 개선되었는데, 이 비율은 다음과 같이 확인해 볼 수 있어요.</p><pre>SELECT tables.NAME as table_name,<br>    indexes.NAME as index_name,<br>    stats.stat_value as n_total_pages,<br>    cached.N_CACHED_PAGES as n_cached_pages,<br>    (stats.stat_value - cached.N_CACHED_PAGES) as n_not_cached_pages,<br>    (100.0*cached.N_CACHED_PAGES/stats.stat_value) as cached_ratio,<br>    (100-(100.0*cached.N_CACHED_PAGES/stats.stat_value)) as not_cached_ratio<br>  FROM information_schema.INNODB_TABLES AS tables<br>    INNER JOIN information_schema.INNODB_INDEXES indexes ON indexes.TABLE_ID = tables.TABLE_ID<br>    INNER JOIN information_schema.INNODB_CACHED_INDEXES cached ON cached.INDEX_ID = indexes.INDEX_ID<br>    INNER JOIN mysql.innodb_index_stats stats ON stats.database_name=substring_index(tables.NAME,&#39;/&#39;,1) AND stats.table_name=substring_index(tables.NAME,&#39;/&#39;,-1) AND stats.index_name=indexes.NAME AND stats.stat_name=&#39;size&#39;<br>  WHERE tables.NAME=&#39;test/tab&#39;;<br>+------------+------------------+---------------+----------------+--------------------+--------------+------------------+<br>| table_name | index_name       | n_total_pages | n_cached_pages | n_not_cached_pages | cached_ratio | not_cached_ratio |<br>+------------+------------------+---------------+----------------+--------------------+--------------+------------------+<br>| test/tab   | PRIMARY          |         47680 |          30829 |              16851 |     64.65814 |         35.34186 |<br>| test/tab   | ix_bid_id        |         28672 |             11 |              28661 |      0.03836 |         99.96164 |<br>| test/tab   | ix_bid_createdat |         36608 |             11 |              36597 |      0.03005 |         99.96995 |<br>+------------+------------------+---------------+----------------+--------------------+--------------+------------------+</pre><p>이제 각 인덱스를 사용하는 실행 계획의 비용 계산 과정을 살펴보려고 해요. 그런데 MySQL 서버 내부 코드 로직은 접근 방법에 따라서 매우 다양한 형태의 계산식을 활용해요. 그래서 아래의 계산식이 모든 쿼리의 비용 계산에 사용될 수 있는 공식은 아니며, 지금 살펴보고 있는 쿼리에만 적용된다는 것을 기억해주세요. 하지만 다음 계산식을 통해서 MySQL 옵티마이저가 어떻게 쿼리의 비용을 예측하는지 대략적으로 살펴볼 수 있을 거에요. MySQL에서 ref와 range는 모두 인덱스를 효율적으로 사용하는 실행 계획이긴 하지만, 두 접근 방법의 비용 계산 방식은 매우 다르게 구현되어 있어요.</p><p><strong>ix_bid_id 인덱스 활용 실행 계획</strong></p><pre><br>total_pages_of_table = 47680<br>estimated_rows = 1632722<br>pct_cached_pages(Primary) = 64.65814 %<br>pct_not_cached_pages(Primary) = (100 - 64.65814) %<br><br>row_evaluate_cost = 0.10<br>memory_block_read_cost = 0.25<br>disk_block_read_cost = 1.00<br><br>worst_seek_cost(84095.62) = (estimated_rows/10 * pct_cached_pages * 0.25) + (estimated_rows/10 * pct_not_cached_pages * 1)<br>total_scan_cost(24558.25) = (total_pages_of_table * pct_cached_pages * 0.25) + (total_pages_of_table * pct_not_cached_pages * 1)<br><br>eval_cost(163272.2) = estimated_rows * row_evaluate_cost<br>read_cost(73674.75) = MIN(worst_seek_cost, total_scan_cost*3)<br><br>query_cost(236946.95) = read_cost + eval_cost</pre><p><strong>ix_bid_createdat 인덱스 활용 실행 계획</strong></p><pre>estimated_rows = 1490996<br>pct_cached_pages(Index ix_bid_createdat) = 0.03005 %<br>pct_not_cached_pages(Index ix_bid_createdat) = (100 - 0.03005) %<br><br>row_evaluate_cost = 0.10<br>memory_block_read_cost = 0.25<br>disk_block_read_cost = 1.00<br><br>keys_per_block(373.36) = 16 * 1024 / 2 / (8+5+1+8) + 1<br>read_index_pages(3994.45) = (estimated_rows + keys_per_block - 1) / keys_per_block<br>read_time(3993.55) = (read_index_pages * pct_cached_pages * 0.25) + (read_index_pages * pct_not_cached_pages * 1)<br><br>eval_cost(149099.6) = estimated_rows * row_evaluate_cost<br>read_cost(153093.15) = eval_cost + read_time<br><br>query_cost(302192.75) = read_cost + eval_cost</pre><p>이 계산 과정에서 ix_bid_id 인덱스가 채택될 수밖에 없는 결정적인 이유는 이 인덱스를 사용할 때 실행 계획의 read_cost를 매우 낮은 수준으로 판단했기 때문이에요. read_cost를 계산할 때 MySQL 옵티마어지는 worst_seek_cost와 total_scan_cost 2개의 값중에서 최소값을 선택하는데, worst_seek_cost는 ix_bid_id 인덱스를 읽고 레코드 건수만큼 데이터 페이지를 읽는 비용을 의미하고, total_scan_cost는 테이블의 모든 데이터 페이지를 읽는 비용을 의미해요. 그런데 total_scan_cost가 너무 낮은 비용으로 평가되면서 쿼리의 실행 계획이 잘못되기 시작한거죠. total_scan_cost 가 너무 낮은 비용으로 계산된 이유는 ref 실행 계획의 비용은 ix_bid_id 인덱스가 아닌 PRIMARY 인덱스의 데이터 페이지 읽기를 계산하는데, 일반적으로 인덱스보다 데이터 파일의 페이지가 메모리에 상주할 가능성이 높기 때문이에요.</p><p>처음에 살펴보았던 아래 쿼리도 ix_bid_id 가 아닌 ix_bid_createdat 인덱스를 사용하게 되는 이유도 동일해요.</p><pre>SELECT COUNT(*) FROM tab WHERE bid=1198442 AND id&lt;14025956;</pre><p>range 접근 방식보다 ref 접근 방식의 비용이 낮게 계산되면서, 쿼리 조건에 딱 맞는 인덱스는 사용하지 못하고 조금 비슷한 패턴의 인덱스만 사용하도록 실행 계획이 수립되고 있었던 거죠.</p><h3>Cost Model 해결 방법</h3><p>아래 쿼리가 ix_bid_id 인덱스가 아닌 ix_bid_createdat 인덱스를 사용하도록 하려면, 옵티마이저의 단위 비용 상수를 조정하여 ref 접근 방법의 비용을 더 높이거나 range 접근 방법의 비용을 더 낮춰 줘야 해요</p><pre>SELECT COUNT(*) <br>FROM tab<br>WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);</pre><p>그런데 row_evaluate_cost 단위 비용 상수외에는 두 실행 계획의 비용 차별성을 만들어낼 수가 없어요. 뿐만 아니라 이 쿼리가 ix_bid_createdat 을 사용하지 못하고 ix_bid_id 를 사용하게 되는 가장 큰 이유는 (위의 비용 계산식에서 눈치챘겠지만), 읽는 레코드에 대해서 eval_cost (row_evaluate_cost) 가 전체 비용에 두번이나 영향을 미치고 있다는 것이에요.</p><p>그래서 우리는 row_evaluate_cost 비용 상수를 더 낮은 값으로 설정해서, ix_bid_createdat 의 실행 계획 비용을 상대적으로 더 낮추는 전략을 고려해볼 수 있어요.</p><pre>UPDATE mysql.server_cost <br>  SET cost_value=0.03 /* 데이터 분포도에 따라서 옵티마이저는 여전히 ix_bid_id 인덱스를 선호할 수 있음*/ <br>  WHERE cost_name=&#39;row_evaluate_cost&#39;;<br><br>FLUSH OPTIMIZER_COSTS;<br>-- // 현재 세션은 종료하고, 새로 MySQL 서버에 로그인해야 변경된 비용으로 쿼리 실행 계획이 수립되는 것을 확인할 수 있어요.</pre><p>이제 성능 문제가 있었던 쿼리의 실행 계획을 다시 살펴볼게요.</p><pre>mysql&gt; EXPLAIN SELECT COUNT(*) FROM tab WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>+-------+------------------+---------+------+---------+----------+--------------------------+<br>| type  | key              | key_len | ref  | rows    | filtered | Extra                    |<br>+-------+------------------+---------+------+---------+----------+--------------------------+<br>| range | ix_bid_createdat | 14      | NULL | 1490996 |   100.00 | Using where; Using index |<br>+-------+------------------+---------+------+---------+----------+--------------------------+</pre><p>쿼리의 실행 계획이 ix_bid_createdat 인덱스를 사용하도록 변경되었어요.</p><pre>mysql&gt; EXPLAIN FORMAT=JSON<br>       SELECT /*+ INDEX(tab ix_bid_id) */ COUNT(*) <br>       FROM tab<br>       WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>  ...<br>  &quot;cost_info&quot;: {<br>    &quot;read_cost&quot;: &quot;73429.79&quot;,<br>    &quot;eval_cost&quot;: &quot;16325.59&quot;,<br>    &quot;prefix_cost&quot;: &quot;122411.45&quot;,<br>    &quot;data_read_per_join&quot;: &quot;24M&quot;<br>  },<br><br>mysql&gt; EXPLAIN FORMAT=JSON<br>       SELECT /*+ INDEX(tab ix_bid_createdat) */ COUNT(*) <br>       FROM tab<br>       WHERE bid=1198442 AND created_at&lt;DATE_SUB(NOW(), INTERVAL 9 HOUR);<br>  ...<br>  &quot;cost_info&quot;: {<br>    &quot;read_cost&quot;: &quot;48727.16&quot;,<br>    &quot;eval_cost&quot;: &quot;44729.88&quot;,<br>    &quot;prefix_cost&quot;: &quot;93457.04&quot;,<br>    &quot;data_read_per_join&quot;: &quot;68M&quot;<br>  },</pre><p>실제 쿼리의 비용을 한번 살펴보면, ix_bid_id 인덱스를 사용하는 실행 계획은 전체 비용이 122411.45로 떨어졌고, ix_bid_createdat인덱스를 사용하는 실행 계획은 93457.04로 비용이 더 크게 떨어진 것을 확인할 수 있어요.</p><h3>주의 사항</h3><p>mysql.engine_cost 와 mysql.server_cost 테이블을 이용해서 단위 작업 비용을 튜닝하는 작업은 매우 신중히 진행되어야 해요. 이 테이블의 단위 작업 비용은 지금 튜닝하고자 하는 쿼리 뿐만 아니라, DBMS에 실행되는 모든 쿼리의 실행 계획에 영향을 미치기 때문에 잘못 설정된 값은 DBMS 서버를 즉시 응답 불능 상태로 만들어버릴 수도 있어요. 반드시 단위 작업 비용들을 큰 값 또는 작은 값으로 설정하게 되면 어떤 실행 계획이 더 자주 사용되고 어떤 실행 계획이 덜 자주 사용되도록 바뀔지 예측을 해보고, 변경 적용할 것을 권장드려요.</p><p>또한 이 케이스의 옵티마이저 오류는 이미 버그 레포트가 되어 있으며(Oracle에서도 버그로 인식), 현재 최신 버전인 MySQL 8.0.33 버전에서도 아직 개선되진 않았지만, 조만간 개선이 될 거라 예상해요. 그래서 MySQL 서버의 Cost Model에 대해서 전문적인 지식이 없다면, 당분간은 옵티마이저 힌트로 버그를 회피하고, 버그가 해결되면 버전 업그레이드를 하는 방법도 좋아 보여요.</p><h3>참고</h3><p><strong>Ref Access Cost 관련 주요 코드</strong></p><ul><li><a href="https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/sql_optimizer.cc#L5827-L5838"><em>https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/sql_optimizer.cc#L5827-L5838</em></a></li><li><a href="https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L6005-L6017"><em>https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L6005-L6017</em></a></li></ul><p><strong>Range Access Cost 관련 주요 코드</strong></p><ul><li><a href="https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/sql_planner.cc#L820-L835"><em>https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/sql_planner.cc#L820-L835</em></a></li><li><a href="https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L6019-L6037"><em>https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L6019-L6037</em></a></li><li><a href="https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L5891-L5899"><em>https://github.com/mysql/mysql-server/blob/mysql-8.0.33/sql/handler.cc#L5891-L5899</em></a></li></ul><p>당근마켓에서 함께 고민을 나누고 싶다면 <a href="https://bit.ly/3BKmvSY">여기를 눌러</a> 당근마켓 채용 공고를 확인해보세요!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e438aa02e622" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-optimizer-error-e438aa02e622">MySQL Optimizer Error</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL BOOLEAN 컬럼]]></title>
            <link>https://medium.com/daangn/mysql-boolean-%EC%BB%AC%EB%9F%BC-7abd9b35c664?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/7abd9b35c664</guid>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[엔지니어링]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Mon, 20 Mar 2023 07:58:45 GMT</pubDate>
            <atom:updated>2023-03-20T08:08:18.035Z</atom:updated>
            <content:encoded><![CDATA[<p>PostgreSQL 서버와 같은 RDBMS 서버는 네이티브하게 BOOLEAN 컬럼 타입을 지원하고 있어요. 그래서 [TRUE | FALSE] 또는 [YES | NO] 와 같은 값들을 이용해서 불리언 값을 저장할 수 있어요. 물론 Oracle RDBMS와 같이 불리언 타입을 지원하지 않는 RDBMS도 있어요. 그런데, MySQL 서버는 외관은 BOOLEAN 타입을 지원하는 것처럼 보이지만, 내부적으로는 BOOLEAN 타입을 지원하지 않아서 조금 혼란스러운 경우도 있어요. 이번에는 MySQL 서버의 BOOLEAN 타입 사용에 있어서 실수하지 않는 방법을 살펴보려고 해요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3JeUHOzJKfjo6s3yFAuoKg.png" /><figcaption>Boolean Data Type</figcaption></figure><h3>BOOLEAN 컬럼 활용</h3><pre>mysql&gt; CREATE TABLE users (<br>         id INT AUTO_INCREMENT PRIMARY KEY, <br>         name VARCHAR(20), <br>         enabled BOOL /* 또는 BOOLEAN */, <br>       );<br><br>mysql&gt; SHOW CREATE TABLE users;<br>CREATE TABLE `users` (<br>  `id` int NOT NULL AUTO_INCREMENT,<br>  `name` varchar(20) DEFAULT NULL,<br>  `enabled` tinyint(1) DEFAULT NULL,<br>  PRIMARY KEY (`id`)<br>)</pre><p>BOOL 또는 BOOLEAN 컬럼 타입을 사용해서 테이블을 생성하면, MySQL 서버에서 실제 테이블은 TINYINT(1)로 컬럼이 생성되는 것을 확인할 수 있어요. 그리고 BOOLEAN 타입의 컬럼에 값을 저장할거나 조회할 때는 [TRUE | FALSE] 또는 [1 | 0] 을 사용해요.</p><pre>mysql&gt; INSERT INTO users VALUES (1, &#39;Matt&#39;, TRUE);<br>mysql&gt; INSERT INTO users VALUES (2, &#39;Lara&#39;, 0);<br><br>mysql&gt; SELECT * FROM users;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>|  2 | Lara |       0 |<br>+----+------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=TRUE;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>+----+------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=1;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>+----+------+---------+</pre><h3>WHERE 절의 BOOLEAN 타입 사용</h3><p>WHERE 조건절에 BOOLEAN 타입 컬럼을 사용하는 경우, 반드시 [TRUE | FALSE] 또는 [1 | 0]만을 사용해야 해요. 그렇지 않은 경우 원하는 검색 결과가 나오지 않을 수 있어요. C/C++ 언어에서와 같이 0은 FALSE이며 0이 아닌 값은 TRUE로 판독하는 경우도 있지만, WHERE 절에서 BOOLEAN 컬럼에 대해서 정수 값으로 비교 조건을 사용할 때에는 0 또는 1로만 비교할 수 있어요.</p><pre>mysql&gt; SELECT * FROM users WHERE enabled=0 /* FALSE */;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  2 | Lara |       0 |<br>+----+------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=1 /* TRUE */;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>+----+------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=2 /* 정수 2는 TRUE도 아니고 FALSE도 아님 */;<br>Empty set (0.01 sec)</pre><h3>BOOLEAN 컬럼의 인덱스 활용</h3><p>BOOLEAN 타입 컬럼의 경우, 다음과 같이 비교 상수 값 없이 컬럼만 WHERE 절에 명시해서 사용할 수도 있어요. 컬럼이 이미 BOOLEAN 타입이므로, 아무런 문제가 없어 보이고, 쿼리 결과도 기대했던 대로 출력되는 것을 확인할 수 있어요.</p><pre>mysql&gt; SELECT * FROM users WHERE enabled;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>+----+------+---------+</pre><p>그럼 이제 enabled 컬럼에 인덱스를 생성하고, 위의 쿼리가 어떤 실행 계획으로 처리되는지를 한번 살펴볼게요.</p><pre>mysql&gt; ALTER TABLE users ADD INDEX ix_enabled_name (enabled, name), ALGORITHM=inplace, LOCK=none;<br><br>-- // 테스트를 위해서 적절히 테스트용 레코드를 INSERT 해둡니다.<br>mysql&gt; SELECT COUNT(*) FROM users;<br>+----------+<br>| COUNT(*) |<br>+----------+<br>|   786272 |<br>+----------+<br><br>mysql&gt; EXPLAIN SELECT * FROM users WHERE enabled AND name=&#39;Matt&#39;;<br>+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+<br>| id | select_type | table | type  | key             | key_len | ref  | rows | filtered | Extra                 |<br>+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+<br>|  1 | SIMPLE      | users | range | ix_enabled_name | 2       | NULL |    2 |    10.00 | Using index condition |<br>+----+-------------+-------+-------+-----------------+---------+------+------+----------+-----------------------+<br>Note (Code 1003): /* select#1 */ select `test`.`users`.`id` AS `id`,`test`.`users`.`name` AS `name`,`test`.`users`.`enabled` AS `enabled` from `test`.`users` where ((`test`.`users`.`name` = &#39;Matt&#39;) and (0 &lt;&gt; `test`.`users`.`enabled`))</pre><p>예상대로 ix_enabled_name 을 사용해서 쿼리가 최적화되는 것을 확인할 수 있어요. 그런데 여기에서 한가지 조금 이상해 보이는 것이 있어요. 실행 계획 하단의 Note를 보면 MySQL 옵티마이저가 파싱된 쿼리를 재조합한 결과를 보면, where (0 &lt;&gt; `test`.`users`.`enabled`) 를 확인할 수 있어요. 이는 쿼리의 WHERE enabled 라는 구문이 WHERE enabled=TRUE 로 해석된 것이 아니라 WHERE enabled&lt;&gt;0 으로 해석되었다는 것을 의미해요.</p><p>쿼리의 WHERE 조건절의 enable조건이 부정 조건으로 처되었는데, 어떻게 ix_enabled_name 인덱스를 이용해서 최적화가 될 수 있었을까요 ? 이는 MySQL 서버의 옵티마이저가 enabled&lt;&gt;0 조건을 인덱스를 사용할 수 있는 NULL&lt;enabled&lt;0 AND 0&lt;enabled 조건으로 변환해서 실행했기 때문이에요. 이는 <a href="https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TRACE.html">옵티마이저 트레이스</a> 결과를 살펴 보면 쉽게 확인할 수 있어요.</p><pre>  &quot;chosen_range_access_summary&quot;: {<br>    &quot;range_access_plan&quot;: {<br>      &quot;type&quot;: &quot;range_scan&quot;,<br>      &quot;index&quot;: &quot;ix_enabled_name&quot;,<br>      &quot;rows&quot;: 2,<br>      &quot;ranges&quot;: [<br>        &quot;NULL &lt; enabled &lt; 0&quot;,<br>        &quot;0 &lt; enabled&quot;<br>      ] /* ranges */<br>    } /* range_access_plan */,<br>    &quot;rows_for_plan&quot;: 2,<br>    &quot;cost_for_plan&quot;: 1.21,<br>    &quot;chosen&quot;: true<br>  } /* chosen_range_access_summary */</pre><p>그리고 쿼리의 실행 계획에서 key_len이 2바이트인 것을 봐도, enabled 컬럼까지만 인덱스 레인지 스캔으로 읽었다는 것을 알 수 있어요. 실제로는 name=&#39;Matt&#39; 조건이 쿼리 성능에 훨씬 더 도움이 되는 조건인데, 이 조건은 쿼리 최적화에 사용되지 못하면서 쿼리의 성능은 떨어지게 되죠.</p><h3>왜 주의가 필요한가 ?</h3><p>요즘은 프로젝트에서 ORM을 많이 이용하고 있고, 그만큼 ORM에 대한 신뢰도 높은 편인 것 같아요. 하지만 다양한 ORM들을 경험해보면, ORM을 개발했던 사람들이 다양한 RDBMS에 대해서 깊은 지식을 가지고 있지 않으며 또한 다양한 성능 검증을 거치지 않고 릴리즈되고 있다는 느낌이 많았어요.</p><p>GoLang에서 많이 사용되는 ORM Framework인 <a href="https://github.com/ent/ent/">ent (An Entity Framework For Go)</a>를 이용한 간단한 쿼리 예제를 한번 살펴 볼게요.</p><pre>package main<br><br>import (<br>    &quot;context&quot;<br>    &quot;log&quot;<br>    &quot;fmt&quot;<br><br>    &quot;entdemo/ent&quot;<br>    &quot;entdemo/ent/user&quot;<br><br>    _ &quot;github.com/go-sql-driver/mysql&quot;<br>)<br><br>func main() {<br>    client, err := ent.Open(&quot;mysql&quot;, &quot;entgo-user:entgo-pass@tcp(127.0.0.1:3306)/test?parseTime=True&quot;)<br>    if err != nil {<br>        log.Fatalf(&quot;Failed to connect : %v&quot;, err)<br>    }<br>    defer client.Close()<br>    ctx := context.Background()<br><br>    u, err := QueryUser(ctx, client)<br>    if err != nil {<br>        log.Fatal(err)<br>    }<br><br>    log.Println(&quot;Fetched : &quot;, u)<br>}<br><br><br>func QueryUser(ctx context.Context, client *ent.Client) (*ent.User, error) {<br>    u, err := client.User.Query().Where( <br>                    user.Enabled(true), <br>                    user.Name(&quot;Matt&quot;) ).Only(ctx)<br>    if err != nil {<br>        return nil, fmt.Errorf(&quot;Failed to query : %w&quot;, err)<br>    }<br><br>    return u, nil<br>}</pre><p>이 예제가 실행되면, ent go Framework는 MySQL 서버로 아래와 같은 쿼리를 실행해요.</p><pre>SELECT `users`.`id`, <br>       `users`.`name`, <br>       `users`.`enabled`<br>FROM `users` <br>WHERE `users`.`enabled` <br>      AND `users`.`name` = &#39;Matt&#39; <br>LIMIT 2</pre><p>ent go Framework는 WHERE enabled=TRUE 형태의 조건이 아니라, 성능 문제를 야기할 수 있는 WHERE enabled 형태의 쿼리를 생성하는 것을 확인할 수 있어요.</p><h3>BOOLEAN 컬럼 주의 사항</h3><p>BOOLEAN 타입으로 컬럼을 생성한다 하더라도, 네이티브 BOOLEAN 타입이 없는 MySQL 서버는 (이미 위에서 확인해본 바와 같이) TINYINT 정수 타입 컬럼으로 생성해요. 뿐만 아니라 TINYINT 컬럼이기 때문에 [0 | 1]만 저장할 수 있는 것이 아니라, -128부터 +127까지의 정수 값을 모두 저장할 수 있어요. 그런데 TINYINT 컬럼에 [0 | 1] 이외의 정수 값을 혼용해서 사용하는 경우 [TRUE | FALSE] 비교는 예상과는 다른 결과를 보여줄 때도 있으니 주의가 필요해요.<br>간단히 다음 예제를 보면서, 각 쿼리의 예상 결과가 어떤 것이었는지 그리고 출력된 결과와 일치하는지 한번 생각해보면 도움이 될것 같아요.</p><pre>mysql&gt; INSERT INTO users VALUES (1, &#39;Matt&#39;, TRUE);<br>mysql&gt; INSERT INTO users VALUES (2, &#39;Lara&#39;, FALSE);<br>mysql&gt; INSERT INTO users VALUES (3, &#39;Shane&#39;, 3);<br>mysql&gt; INSERT INTO users VALUES (4, &#39;Ford&#39;, 4);<br><br>mysql&gt; SELECT * FROM users;<br>+----+-------+---------+<br>| id | name  | enabled |<br>+----+-------+---------+<br>|  1 | Matt  |       1 |<br>|  2 | Lara  |       0 |<br>|  3 | Shane |       3 |<br>|  4 | Ford  |       4 |<br>+----+-------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled;<br>+----+-------+---------+<br>| id | name  | enabled |<br>+----+-------+---------+<br>|  1 | Matt  |       1 |<br>|  3 | Shane |       3 |<br>|  4 | Ford  |       4 |<br>+----+-------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=TRUE;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  1 | Matt |       1 |<br>+----+------+---------+<br><br>mysql&gt; SELECT * FROM users WHERE enabled=FALSE;<br>+----+------+---------+<br>| id | name | enabled |<br>+----+------+---------+<br>|  2 | Lara |       0 |<br>+----+------+---------+</pre><h3>결론</h3><p>가능하다면 MySQL 서버에서는 (네이티브 BOOLEAN 타입을 지원하지 않기 때문에), 1 타입이나 [TRUE | FALSE] 값 보다는 TINYINT 컬럼을 사용하고 [0 | 1] 만을 저장하는 형태를 고려하는 것이 오히려 더 혼란을 방지할 수 있는 방법이 아닐까 싶기도 해요. 그래도 BOOLEAN 타입이나 [TRUE | FALSE]를 사용하고 싶다면, [0 | 1] 이외의 값이 저장되지 않도록 일관성을 유지하는 것이 더 좋아 보여요. 그리고 값의 유효성 체크를 엄격하게 하고자 한다면, ENUM(&#39;FALSE&#39;,&#39;TRUE&#39;)과 같은 형태도 고민해볼 가치는 있어 보여요.</p><p>그리고 BOOLEAN 타입 컬럼은 ORM 없이 쿼리를 직접 작성해서 사용하는 경우에도 조심해야 하겠지만, ORM을 사용해서 쿼리를 직접 작성하지 않는 경우에는 어떤 쿼리가 실행되는지 보이지 않기 때문에 더 주의가 필요해요. BOOLEAN 타입 케이스뿐만 아니라 여러 케이스에서 ORM 들이 생성하는 쿼리 문장이 성능과는 거리가 먼 형태를 많이 보였어요. 가능하다면, ORM이 자동으로 생성하는 쿼리 문장들의 실행 계획을 직접 확인하는 과정이 필요해 보여요.</p><p>당근마켓에서 함께 고민을 나누고 싶다면 <a href="https://bit.ly/3BKmvSY">여기를 눌러</a> 당근마켓 채용 공고를 확인해보세요!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7abd9b35c664" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-boolean-%EC%BB%AC%EB%9F%BC-7abd9b35c664">MySQL BOOLEAN 컬럼</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL CATS (Contention-Aware Transaction Scheduling)]]></title>
            <link>https://medium.com/daangn/mysql-cats-contention-aware-transaction-scheduling-71fe6956e87e?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/71fe6956e87e</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[엔지니어링]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Sun, 06 Nov 2022 05:15:43 GMT</pubDate>
            <atom:updated>2022-11-06T05:15:43.165Z</atom:updated>
            <content:encoded><![CDATA[<p>전통적으로 MySQL 서버는 트랜잭션의 잠금 허가(Grant)는 순수하게 대기(Waiting) 순서에 의존했는데, 이를 FCFS(First Come First Served, 또는 FIFO라고도 함) 스케쥴링이라고 해요. 하지만 이런 FCFS 기반의 트랜잭션 스케줄링에서는 잠금 대기중인 트랜잭션이 기존에 어떤 잠금을 가지고 있는지 전혀 고려하지 않기 때문에 많은 프로그램 코드에서 사용하는 잠금 업그레이드의 경우 심각한 데드락 현상을 유발했어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o55BFqDqOVymwPNKX-jquQ.png" /><figcaption>CATs</figcaption></figure><p>오늘은 MySQL 서버의 새로운 트랜잭션 스케쥴링인 CATS(Contention-Aware Transaction Scheduling)가 어떻게 잠금 업그레이드를 해결해줄 수 있는지 살펴보려고 해요.</p><h3>잠금 업그레이드</h3><p>아래 예제는 MySQL 서버의 예전 메뉴얼에 소개되고 있던 데드락 예제인데, 이 예제는 대표적인 잠금 업그레이드 시에 발생할 수 있는 데드락을 보여주고 있어요. (<a href="https://bugs.mysql.com/bug.php?id=109005">메뉴얼 버그 제보</a> 이후, 이 내용은 <a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlock-example.html">MySQL 8.0 메뉴얼</a>에서는 다른 예제로 변경되었고, <a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-example.html">MySQL 5.7 메뉴얼</a>에서만 확인할 수 있어요.)</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZQZCSOU8isF6FRldJXzCYg.png" /><figcaption>(예전) MySQL 메뉴얼의 데드락 예시</figcaption></figure><p>이 예제에서 “TRX-A”가 먼저 Shared-Lock을 획득한 상태에서, “TRX-B”가 레코드 삭제 쿼리를 실행하면서 동일 레코드에 대해서 Exclusive-Lock을 요청하면서 “TRX-B”는 대기 큐에서 기다려요. “TRX-A”에서 동일하게 삭제 쿼리를 실행하면, MySQL 8.0.28 이하 버전까지의 FCFS 트랜잭션 스케쥴링에서는 다음 그림과 같이 “TRX-A”의 Exclusive-Lock 요청도 대기 큐의 “TRX-B” 뒤에 추가 추가해요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KYnKlmwP2Rdf4iuYrInL5w.png" /><figcaption>잠금 대기 큐(Wait Queue)</figcaption></figure><p>그런데 “TRX-A”가 이미 해당 레코드에 대해서 Shared-Lock을 가지고 있는 상태이기 때문에, (“TRX-A”가 가진 Shared-Lock을 중간에 포기하지 않는 이상) “TRX-B”는 절대 Exclusive-Lock을 가질 수 없게 되죠. 하지만 “TRX-A”는 Exclusive-Lock을 획득해서 처리가 완료되기 때문에 Exclusive-Lock을 획득하기 전에는 절대 레코드의 Shared-Lock을 풀지 않는 상황이 되면서 데드락 상황이 발생하게 된 거죠.</p><h3>트랜잭션 우선순위</h3><p>MySQL 서버의 CATS는 이렇게 잠금을 경합하는 여러 트랜잭션에 가중치(Weight)를 부여하고, 대기 큐의 순서에 관계없이 가중치가 높은 트랜잭션에게 먼저 잠금을 할당해주는 방식으로 데드락을 회피할 수 있도록 해줘요(각 트랜잭션의 가중치는 INFORMATION_SCHEMA.INNODB_TRX 테이블의 trx_schedule_weight 컬럼을 통해서 확인할 수 있어요). MySQL 서버의 CATS 스케쥴러는 각 트랜잭션에서 잠금 요청이 들어오면, 각 트랜잭션의 스케쥴링 가중치(trx_schedule_weight)를 갱신하고, 그 가중치를 기준으로 잠금 할당(Grant) 순서를 변경해서 처리해요.</p><p>여기에서 한가지 주의해야 할 것은, 각 트랜잭션으로부터 잠금 요청이 오면 그때 대기 큐의 트랜잭션들을 가중치에 맞게 순서를 바꾸는 것이지, 데드락이 발생하면 그 때 가중치에 맞게 잠금 할당 순서를 바꾸는 것은 아니에요. 즉 CATS 스케쥴러를 사용하는 버전에서도 일단 데드락이 발생하면, 관련된 트랜잭션중 일부는 “ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction” 에러를 보게 된다는 것을 의미해요.</p><p>MySQL 8.0.20 이전 버전까지는 FCFS와 CATS 트랜잭션 스케쥴링이 모두 사용되었었는데, 평상시에는 FCFS 스케쥴링만 사용되다가 잠금 경합이 심해지면 CATS 스케쥴링이 사용되는 형태로 구현되어 있었어요. 그런데 MySQL 8.0.20 버전부터는 항상 CATS 트랜잭션 스케쥴링이 사용되도록 변경되었어요. 그리고 MySQL 8.0.29 버전에서는 <a href="https://bugs.mysql.com/bug.php?id=21356">2006년 레포팅된 케케묵은 버그</a>가 CATS 트랜잭션 스케쥴링의 도움으로 개선되면서, 위에서 살펴 본 잠금 업그레이드로 인한 데드락 문제는 더이상 MySQL 서버에서는 발생하지 않게 개선된 것이에요.</p><p>즉, MySQL 8.0.29 버전부터는 “TRX-A”는 가중치(trx_schedule_weight) 값이 1이 부여되고 “TRX-B”는 가중치 값이 0(NULL)이 부여되기 때문에, “TRX-A”가 늦게 잠금을 요청했음에도 불구하고 “TRX-B” 보다 먼저 Exclusive-Lock을 허가(Grant)받게 되면서 데드락 상황을 회피할 수 있게 되었어요.</p><p>MySQL 서버의 CATS 스케쥴러에 대한 자세한 내용은 아래 메뉴얼과 워크로그에서 살펴 볼 수 있어요.</p><ul><li><a href="https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-scheduling.html">https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-scheduling.html</a></li><li><a href="https://dev.mysql.com/worklog/task/?id=13468">https://dev.mysql.com/worklog/task/?id=13468</a></li></ul><h3>이제 더 이상 데드락은 없는 것인가 ?</h3><p>CATS라는 이름을 보면 뭔가 대단한 느낌이 들고, MySQL 서버의 모든 데드락 문제를 해결해줄 것처럼 보여요. 하지만 (서비스의 특성에 따라서) 아직 그 효과는 매우 크진 않을 수도 있어요.</p><p>서비스 환경에서 우리가 자주 경험하는 데드락은 동일 프로그램 코드가 여러 쓰레드에 의해서 동시에 실행될 때 발생하는 것들이 대부분이에요. 데드락의 원인이 되는 트랜잭션들이 모두 동일한 SQL 문장들을 실행하면서 발생하기 때문에, (“<strong>동일 코드의 멀티 쓰레드 실행으로 인한 데드락</strong>” 예시와 같이) 경합하는 트랜잭션들이 동시에 Shared-Lock(Gap 또는 Record Lock)을 가진 상태에서 Exclusive-Lock이나 Insert-Intention-Lock을 요청하면서 데드락이 발생해요. 하지만 MySQL 서버의 CATS 스케쥴러가 이미 각 트랜잭션에 할당된 잠금을 회수하는 것은 아니기 때문에, 이런 경우 데드락을 회피할 수 없는거죠.</p><h4>동일 코드의 멀티 쓰레드 실행으로 인한 데드락</h4><p>다음 예제는 “TRX-A”와 “TRX-B”가 똑같이 deadlock 테이블에 INSERT를 실행하는데, 제 3의 트랜잭션인 “TRX-X”가 잠금 경합을 유발하는 방아쇠 역할을 하게 되면 데드락이 발생하는 케이스에요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JTsaCKyuD6T5Vo9YLUpC0w.png" /><figcaption>동일 프로그램 코드가 서로 경합하면서 데드락이 발생하는 케이스</figcaption></figure><p>“TRX-X”가 먼저 deadlock 테이블의 pk=2 인 레코드를 DELETE하고 아직 COMMIT을 하지 않은 상태에서, “TRX-A”와 “TRX-B”가 deadlock 테이블에 pk=2 인 레코드를 INSERT하려고 해요. 이때 “TRX-X”는 pk=2 레코드에 대해서 Exclusive-Lock을 점유(Grant)한 상태이고, “TRX-A”와 “TRX-B”는 deadlock 테이블에 이미 pk=2 레코드가 존재하는 것을 확인하고, 먼저 Shared-Lock을 동시에 획득(Grant)한 이후 pk=2 레코드에 대해서 Exclusive-Lock을 대기(Wait)해요. 이 상태에서 “TRX-X”의 DELETE가 COMMIT 되면 “TRX-A”와 “TRX-B”는 동시에 pk=2 레코드에 대해서 Exlusive-Lock을 획득할 수 있는 상태가 되지만, 이미 “TRX-A”와 “TRX-B”가 각자 Shared-Lock을 획득하고 있는 상태이기 때문에 누구에게도 Exclusive-Lock을 허가(Grant)해주지 못하는 데드락 상황이 발생하게 되는 거에요.</p><blockquote>MySQL 서버에서는 INSERT 문장이 UNIQUE INDEX의 중복 레코드를 만나게 되면 Duplicate key error가 발생하게 되는데, 이렇게 중복 레코드를 만나면 Exclusive-Lock이 아니라 Shared-Lock을 먼저 걸어요. 이는 트랜잭션내에서 여러번 문장이 실행되어도 동일한 결과를 낼 수 있도록 설계하고 구현했기 때문이에요.</blockquote><p>이 예제의 경우, “TRX-A”와 “TRX-B”는 Shared-Lock을 가진 상태에서 Exclusive-Lock을 요청하면서 동일하게 trx_schedule_weight=1 을 가지게 되요. 그런데 이렇게 동일한 trx_schedule_weight 값을 가지는 트랜잭션이 경합하는 경우, MySQL CATS는 더 먼저 시작한 트랜잭션(TRX-A)에 우선권을 부여해요. 하지만 이 예제에서는 먼저 실행된 “TRX-A” 트랜잭션에 우선권을 부여한다 하더라도, “TRX-B”에게 이미 허가(Grant)된 Shared-Lock을 회수(Revoke)할 수 없기 때문에 “TRX-A”에게 Exclusive-Lock을 허가해줄 수가 없어요. 그래서 결국 데드락 상황이 발생하게 되는거죠.</p><p>여기에서 설명하지 않은 InnoDB 스토리지 엔진의 레코드 잠금 관련된 중요한 비밀이 하나 있어요. 제일 먼저 이 비밀을 찾아서 <a href="https://open.kakao.com/o/gx274hbe">Real MySQL 오픈챗</a>으로 오시는 분께 커피 쿠폰을 선물로 드릴게요.</p><h3>추가로</h3><p>이런 형태의 데드락은 UNIQUE INDEX를 많이 가진 테이블에서 자주 발생해요. 그래서 불필요한 UNIQUE INDEX는 최소화해서 테이블을 설계하는 것이 좋아요.</p><p>당근마켓에서 함께 고민을 나누고 싶다면 <a href="https://bit.ly/3BKmvSY">여기를 눌러</a> 당근마켓 채용 공고를 확인해보세요!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=71fe6956e87e" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-cats-contention-aware-transaction-scheduling-71fe6956e87e">MySQL CATS (Contention-Aware Transaction Scheduling)</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[JSON vs TEXT]]></title>
            <link>https://medium.com/daangn/json-vs-text-c2c1448b8b1f?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/c2c1448b8b1f</guid>
            <category><![CDATA[데이터베이스]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Sat, 06 Aug 2022 06:02:49 GMT</pubDate>
            <atom:updated>2022-08-15T05:09:49.498Z</atom:updated>
            <content:encoded><![CDATA[<h3>MySQL JSON vs. TEXT</h3><blockquote><strong>JSON 타입 컬럼으로 저장하는 것이 좋을까요 ? <br></strong><strong>TEXT타입 컬럼이 좋을까요 ?</strong></blockquote><p>MySQL 서버도 JSON 타입 컬럼을 지원하기 시작하면서, 사용자에게는 편의성과 생산성 측면에서 더 많은 선택지가 생겼어요.</p><p>그런데 사실 이는 새로운 고민의 시작이기도 해요. 특히 JSON 데이터를 저장할때, JSON 타입을 사용하는 것이 좋을지 아니면 TEXT 타입 컬럼이 좋을지 고민하는 경우가 많이 있을 것 같아요. 오늘은 <strong>JSON vs. TEXT 판단 기준</strong>을 위한 가이드라인을 공유해 보려고 해요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/898/1*rOcSIJ7898jjOtsIJ84rXw.png" /><figcaption>JSON</figcaption></figure><h3>성능 비교 (대용량 컬럼)</h3><p>각 컬럼 타입별로 지원하는 기능과 성능은 컬럼 타입 선정에서 매우 중요한 판단 기준이에요. 그래서 먼저 JSON 컬럼 타입과 TEXT 컬럼 타입의 성능을 먼저 살펴보려고 해요.</p><p>성능 테스트를 위해서, 아래와 같이 간단한 테이블을 만들고 16000건 정도의 레코드를 준비했어요. 두 테이블의 data 컬럼은 타입만 다르고, 저장된 값은 똑같이 준비했어요. 두 테이블 모두 data컬럼의 값이 매우 커서, 테이블의 레코드에 같이 저장되지 못하고, External page (off-page)로 저장된다는 것을 기억해주세요.</p><pre>CREATE TABLE huge_json (<br>  id INT AUTO_INCREMENT PRIMARY KEY,<br>  data JSON<br>);</pre><pre>CREATE TABLE huge_text (<br>  id INT AUTO_INCREMENT PRIMARY KEY,<br>  data MEDIUMTEXT<br>);</pre><pre>-- // 테스트 데이터 생성<br>-- // 4000여개 필드를 가진 100KB 정도의 Json 값을 data 컬럼에 저장</pre><pre>SELECT AVG(LENGTH(data)) FROM huge_json; -- // 107352 바이트<br>SELECT AVG(LENGTH(data)) FROM huge_text; -- // 107352 바이트</pre><pre>SELECT COUNT(*) FROM huge_json; -- // 16000 건<br>SELECT COUNT(*) FROM huge_text; -- // 16000 건</pre><p>준비된 2개 테이블에 대해서, 아래와 같은 몇 가지 테스트 결과를 살펴보고자 해요.</p><pre>1) SELECT COUNT(id)               FROM huge_[ json | text ];<br>2) SELECT COUNT(data)             FROM huge_[ json | text ];<br>3) SELECT COUNT(data-&gt;&gt;&#39;$.field&#39;) FROM huge_json;</pre><pre>4) SELECT id   FROM huge_[ json | text ];<br>5) SELECT data FROM huge_[ json | text ];</pre><p>위의 테스트 쿼리들은 MySQL 서버에서 다음과 같은 처리들을 수행해요.</p><ul><li>SELECT COUNT(id) MySQL 서버는 이 쿼리를 처리하는 도중, 테이블의 JSON 이나 TEXT 컬럼을 전혀 접근하지 않아요. (즉 External page 를 전혀 접근하지 않아요)</li><li>SELECT COUNT(data) MySQL 서버는 쿼리를 처리하기 위해서, data 컬럼의 값을 꼭 읽어야 해요. (즉, External page 를 접근해야 해요. 하지만 MySQL 서버는 JSON 컬럼의 경우에도, Json parsing작업을 필요로 하진 않아요.)</li><li>SELECT COUNT(data-&gt;&gt;&#39;$.field&#39;) MySQL 서버는 쿼리를 처리하기 위해서, data 컬럼의 값을 읽고 Json parsing 작업을 필요로 해요.</li></ul><p>이런 특성 덕분에 쿼리 처리의 각 단계별 성능 비교 테스트에 적합해 테스트 쿼리로 선정했어요.</p><p>테스트 결과, SELECT COUNT(...) 쿼리들은 다음과 같은 성능을 보여주었어요. 이 그래프에 표시된 시간은 쿼리 실행 시점부터 모든 결과 데이터를 전송 받는 시점까지를 밀리초 단위로 수집한 것이에요. 그리고 그래프의 Y 축은 Log 눈금이어서, 눈금 간격간 값의 변화는 매우 크다는 것을 기억해주세요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3bDnNUwIK87X3YctcfNL2g.png" /></figure><p>예상했던 대로, JSON 컬럼의 값을 전혀 접근하지 않는 경우(1번 쿼리)와 JSON 컬럼의 값을 읽더라도 Parsing 하지 않는 경우(2번 쿼리)에는 TEXT 타입과 JSON 타입을 사용하는 경우 거의 비슷한 성능을 보여주었어요. (TEXT 타입과 JSON 타입간 미세한 차이를 보이긴 하지만, 오늘 이야기에서는 동일한 결과로 간주해요.)</p><p>그런데 JSON Parsing 작업이 필요한 SELECT COUNT($-&gt;&gt;&#39;$.field&#39;) 쿼리(3번 쿼리)는 SELECT COUNT(data) 쿼리보다 조금 더 시간이 걸린 것을 확인할 수 있어요. 아마도 이 시간이 16,000건 JSON 데이터를 Parsing 하는데 걸린 시간이라고 볼 수 있을 것 같아요. 예상보다 JSON 데이터를 분석하는데 시간이 많이 걸리진 않아요. 참고로, MySQL 서버의 JSON Parsing 과정은 부분적으로 실행되지 않고, 항상 Full Parsing을 실행해요. 즉, JSON 값의 제일 첫번째 필드를 가져오는 작업과 마지막 필드를 가져오는 데 걸리는 시간은 크지 않다는 의미예요.</p><p>이제 JSON 과 TEXT 컬럼의 값을 클라이언트로 가져오는 쿼리의 성능 테스트를 살펴 볼게요. 이 그래프에 표시된 시간은 쿼리 실행 시점부터 모든 결과 데이터를 전송 받는 시점까지(클라이언트에서 쿼리 결과를 처리하는데 걸린 시간은 포함되지 않음)를 수집한 것이에요. 그리고 그래프의 Y 축은 Log 눈금이어서, 눈금 간격간 값의 변화는 매우 크다는 것을 기억해주세요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YwFmII2hFiURCs3paK6Fbw.png" /></figure><p>SELECT id 쿼리(4번 쿼리)는 TEXT 와 JSON 컬럼의 값을 가져오지 않기 때문에 거의 동일한 시간이 걸린 것을 알 수 있어요. 하지만 TEXT 와 JSON 컬럼의 값을 가져오는 테스트(5번 쿼리)에서는 JSON 컬럼이 TEXT 컬럼보다 2.5배 이상 많은 시간이 소요된 것을 확인할 수 있어요.</p><p>SELECT COUNT(data) 쿼리와 달리 SELECT data 쿼리의 경우, TEXT 컬럼과 JSON 컬럼의 값을 읽고 처리후 클라이언트로 전송해야 해요. TEXT 컬럼의 경우 문자열로 해석하는 작업이 필요하고, JSON 컬럼의 경우 MySQL 서버 내부적인 Binary JSON 저장 포맷으로 변환해야 해요. 이때, TEXT 컬럼 대비 JSON 컬럼의 변환 처리가 훨씬 복잡하기 때문에 4번과 5번 쿼리는 큰 성능 차이를 보이게 된 거예요. SELECT COUNT(data) 쿼리보다 SELECT data 쿼리가 처리 시간이 훨씬 많이 걸린 또다른 이유는 네트워크 전송에 소요된 시간 때문(JSON 데이가 매우 큰 값이어서 16,000건 전송이 상당한 시간이 걸렸어요)이에요.</p><p>그런데 SELECT data-&gt;&gt;&#39;$.field&#39; 쿼리(3번)와 SELECT data 쿼리(5번) 쿼리는 둘 모두 JSON Parsing 작업이 필요한데, 이 두 쿼리의 성능 차이가 매우 크게 발생하는 것은 이상하죠. 이는 JSON 컬럼의 값을 네트워크로 전송하는 방식 때문이에요. MySQL 서버는 데이터 파일에서 읽은 JSON 데이터를 Binary JSON DOM구조로 관리하는데, 네트워크로 전송하기 위해서는 Binary JSON데이터를 문자열로 변환하면서 Serialization 작업이 필요해요. 이 과정에서 JSON 데이터의 모든 필드별로 타입 변환 과정이 필요하기 때문에, JSON 값의 필드 개수만큼 타입 변환 및 Serialization 작업이 호출되면서 시간이 많이 걸린 거예요. PC에서 진행한 간단한 테스트에 의하면, 100KB JSON 타입 컬럼의 Serialization에는 1 밀리 초 이상 걸렸지만, TEXT 타입의 컬럼을 Serialization에는 1 마이크로 초도 걸리지 않았어요.</p><h3>성능 비교 (소용량 컬럼)</h3><p>지금까지는 100KB 정도의 대용량 컬럼에 대해서 성능을 살펴보았는데요. 이번에는 600 바이트 정도의 소용량 (테이블의 구조와 레코드 건수는 동일한 상태에서) TEXT 타입과 JSON 타입 컬럼의 성능을 비교해 보았어요. 절대적인 시간은 많이 줄었지만 여전히 TEXT 타입과 JSON 타입 컬럼의 데이터를 가져오는 쿼리는 큰 시간 차이(비율)를 보였어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*racFfljSW8My5znEPtG9oA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sZb4-FePXu-pUxqS9gaIkw.png" /></figure><h3>기능 비교</h3><p>이제 JSON 타입과 TEXT 타입 컬럼의 기능 차이를 살펴보려고 하는데요. 여기에서 기능 비교 기준은 JSON 타입 컬럼과 TEXT 타입 컬럼에 JSON 데이터를 저장한다고 가정할 때의 차이예요.</p><p>JSON 타입 컬럼은 MySQL 서버가 JSON 데이터의 구조(Json DOM)를 인식하고 있다는 게 가장 큰 차이예요. 아래에서 살펴볼 JSON 컬럼과 TEXT 컬럼의 모든 차이는 이로 인한 것들인데, JSON 컬럼은 TEXT 컬럼 대비 아래와 같은 몇가지 장점들을 가지고 있어요.</p><ul><li>JSON 타입은 MySQL server-side에서 필드의 값을 조회 및 변경 가능</li><li>JSON 타입은 특정 필드 변경시 in-place 업데이트 가능</li><li>JSON 타입은 특정 필드에 대해서 인덱스를 생성 가능</li></ul><p>TEXT 타입 컬럼에 저장된 1MB JSON 데이터의 일부분만 변경하고자 해도, 1MB를 통째로 업데이트해야 해요. 하지만 JSON 타입 컬럼에 저장된 경우에는, MySQL 서버의 JSON 함수들을 이용해서 특정 필드만 변경할 수 있어요. 뿐만 아니라 MySQL 서버는 변경되는 필드가 고정 길이인 경우, in-place 업데이트를 할 수 있어요. 즉, 1MB 데이터를 모두 디스크에 업데이트하는 것이 아니라 변경된 필드의 값만 디스크에 기록하면 업데이트가 완료되기 때문에 빠르게 처리할 수 있어요.</p><p>또한 JSON 컬럼에 저장된 데이터의 특정 필드를 추출해서 인덱스(함수 기반 인덱스)를 생성하고, 빠른 검색에 활용할 수 있어요.</p><h3>결론적으로</h3><p>지금까지 살펴봤던 성능과 기능을 종합적으로 판단해보면, 아래와 같은 가이드라인을 만들어 볼 수 있어요.</p><p>아래 요건이 필요한 경우에는 JSON 타입 컬럼 사용이 좋아요. 이런 요건들이 필요치 않은 경우라면, TEXT 타입 컬럼을 사용하면 JSON 타입 컬럼보다 빠른 성능 효과를 얻을 수 있어요.</p><ul><li>JSON 데이터의 특정 필드만 접근이 가능해야 한 경우</li><li>JSON 데이터의 특정 필드(고정 길이 필드)만 자주 업데이트되는 경우</li><li>JSON 데이터의 특정 필드로 인덱스 생성이 필요한 경우</li></ul><h3>마지막으로 하나 더 !</h3><p>일반적으로 온라인 트랜잭션(OLTP) 처리 용도의 RDBMS에서는, 가볍고 빠른 쿼리들이 매우 빈번하게 처리되는 경우가 대부분이에요. 이런 온라인 트랜잭션 처리용 DBMS 서버에서는 수십 KB이상의 데이터를 한두 개의 컬럼에 저장하고, 이를 빈번하게 접근하는 것은 상당히 큰 부하를 유발해요. 수십 수백 KB 이상의 데이터는, 높은 CPU 사용량과 느린 응답 속도 그리고 네트워크 대역폭 사용량까지 걱정해야 하는 상황이 발생할 수도 있어요. 만약 여러분이 성능에 매우 민감한 DBMS 서버를 사용하고 있다면, DBMS 서버에 저장되는 데이터는 최대한 컴팩트하게 유지하시는 것을 추천해요.</p><p>만약 큰 데이터를 꼭 저장해야 한다면, 별도의 DBMS 서버로 분리하거나 DBMS 이외의 다른 저장소를 활용하는 방안도 고려해보실 것을 추천해요.</p><p>당근마켓에서 함께 고민을 나누고 싶다면 <a href="https://bit.ly/3BKmvSY">여기를 눌러</a> 당근마켓 채용 공고를 확인해보세요!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c2c1448b8b1f" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/json-vs-text-c2c1448b8b1f">JSON vs TEXT</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Index Dive 비용 최적화]]></title>
            <link>https://medium.com/daangn/index-dive-%EB%B9%84%EC%9A%A9-%EC%B5%9C%EC%A0%81%ED%99%94-1a50478f7df8?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/1a50478f7df8</guid>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[execution-plan]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Thu, 23 Jun 2022 08:08:44 GMT</pubDate>
            <atom:updated>2022-06-23T08:08:44.213Z</atom:updated>
            <content:encoded><![CDATA[<blockquote><strong>MySQL 서버의 실행계획 수립(</strong><strong><em>Index Dive</em>) 단계에서 많은 CPU와 Disk storage 자원이 소모되는 경우, 어떻게 튜닝할 수 있을까?</strong></blockquote><p>MySQL 서버는 여러 통계 정보를 이용해 가장 빠른 쿼리 실행 방식을 결정하는데, 이를 우리는 흔히 최적화라고 해요. Oracle이나 PostgreSQL 서버는 쿼리의 실행 계획을 캐시해서 재활용하는 반면, MySQL 서버에서는 아직 쿼리 실행 계획의 캐시 또는 재활용이 필수적으로 사용되지 않는 편이에요. MySQL 서버의 쿼리 실행 계획이 커넥션내에서만 캐시되고 재활용될 수 있기 때문이에요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*B0cnSlSPaTVQKyS_jc0WxQ.png" /></figure><p>MySQL 서버에서 쿼리가 실행되면, 옵티마이저(Optimizer)는 통계 정보뿐만 아니라 실제 테이블의 데이터를 샘플링해서 확인 후, 최종 사용할 실행 계획을 선택해요. 이때 실제 데이터를 샘플링해서 확인하는 과정을 <a href="https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_random_dive">Index Dive 또는 Random Index Dive</a>라고 해요.</p><p>이런 이유로 MySQL 서버의 실행 계획 수립 단계는 조금 무겁게 느껴져요. 아직 개선이 필요한 부분이기도 하고요. 하지만, 일반적으로 우리가 자주 사용하는 가벼운 쿼리들에 대해서는 MySQL 서버의 실행 계획 수립 과정이 거의 문제되지 않아요. 때로는 이렇게 직접 데이터를 샘플링해서 살펴보는 과정으로 인해서 선택되는 실행 계획 자체는 다른 DBMS 서버보다 정확할 때도 있고요.</p><p>오늘은 MySQL 서버의 실행 계획 수립의 Index Dive 과정으로 인해서 의도치 않게 많은 CPU와 Disk storage 자원이 소모되는 경우와 튜닝 방법에 대해서 살펴보려고 해요.</p><h3>Performance Schema 확인</h3><p>MySQL 서버의 성능에 대해서 관심이 많은 경우, MySQL 서버의 Performance Schema 또는 AWS의 Performance Insights를 살펴본 적이 있을 거예요. 일반적으로 Performance Schema 또는 Performance Insights에는 쿼리의 실행(executing) 자체에 많은 자원이 사용되어야 하는데, 가끔은 아래와 같이 통계 수집(statistics) 단계에서 많은 자원이 소모되는 경우를 확인할 수 있을 거예요. Performance Schema가 어렵다면, AWS의 Performance Insights를 참조해도 동일한 정보를 수집할 수 있어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bAlDOmaMI0QxWKN7-XxNFw.png" /><figcaption>Performance Schema (튜닝 전)</figcaption></figure><p>이 차트를 살펴보면, 쿼리의 통계 수집 단계인 statistics 항목이 실제 쿼리의 실행 단계인 executing보다는 훨씬 높은 비중을 차지하고 있다는 것을 확인할 수 있어요. 이 차트는 쿼리의 실행 계획 수집 단계에서 많은 자원을 소모하고 있다는 거죠.</p><p>Index Dive는 최소의 데이터 페이지만 검토하기 때문에, 일반적으로는 통계 수집 단계에서 이렇게 높은 CPU나 Disk 리소스를 소모하지 않아요. 통계 수집 단계가 튜닝의 대상이 되지도 않고요. 하지만 아래와 같이 WHERE 조건절이 사용되는 경우에는 상황이 달라질 수 있어요.</p><ul><li>많은 개수의 엘리멘트가 IN (list)에 사용되는 경우</li><li>IN (list) 조건 자체가 여러 번 사용되는 경우</li><li>IN (list) 조건들이 사용할 수 있는 인덱스가 많은 경우</li></ul><h3>문제 쿼리 패턴</h3><p>MySQL 서버를 포함한 모든 RDBMS에서는 쿼리가 실행되면, 해당 쿼리의 최적 실행 경로를 찾기 위해 사용 가능한 모든 처리 방법의 예상 비용을 계산하게 돼요. 여기에서 “사용 가능한 모든 처리 방법”이 중요한데요. 테이블의 인덱스가 많으면 많을수록 사용 가능한 방법이 많아지게 되면서 실행 계획 수립 과정이 복잡해져요. 예를 들어, 아래와 같은 테이블을 생각해볼게요.</p><pre>CREATE TABLE articles (<br>  id BIGINT NOT NULL,<br>  user_id BIGINT NOT NULL,<br>  category_id INT NOT NULL,<br>  ...<br>  PRIMARY KEY (id),<br>  INDEX ix_userid (user_id),<br>  INDEX ix_userid_categoryid (user_id, category_id),<br>  INDEX ix_userid_categoryid_fd1 (user_id, category_id, fd1),<br>  INDEX ix_userid_categoryid_fd2 (user_id, category_id, fd2),<br>  INDEX ix_userid_categoryid_fd3 (user_id, category_id, fd3)<br>);</pre><p>articles 테이블에 아래와 같이 2개의 IN (list) 조건을 가진 쿼리를 실행하면,</p><pre>SELECT *<br>FROM articles<br>WHERE user_id IN (1,2,3,4,5, ..., 200)<br>  AND category_id IN (1,2,3,4,5, ..., 200);</pre><p>MySQL 서버는 IN (list)에 나열된 모든 값의 (user_id, category_id) 조합에 대해 통계 정보를 수집해요. 즉, (user_id, category_id) 조합 40000개(200 * 200)에 대해서 일치하는 레코드 건수가 몇 건이나 될지 예측하는 작업을 수행하는 거죠.</p><p>그런데 articles 테이블에는 이 쿼리를 실행하기 위해서 사용 가능한 인덱스가 5개나 돼요. 그래서 이런 작업을 5번이나 반복하게 되고요. 이 정도의 실행 계획 수립 과정은 실제 쿼리 실행보다는 통계 정보 수집에 더 많은 CPU나 Disk 자원이 소모돼요.</p><h3>쿼리 튜닝</h3><p>단순히 생각해보면 인덱스 개수를 줄이면 통계 정보 수집 시간을 단축시킬 수 있을 것 같지만, 그렇지 않아요. 다른 쿼리를 위해서 사용되는 인덱스라면 삭제할 수가 없고, 불필요한 인덱스를 삭제해서 최소화한다 하더라도 여전히 통계 수집이 많은 시간을 소모할 수도 있거든요.</p><p>물론 인덱스를 최소화(최적화)하는 전략은 SELECT 뿐만 아니라 INSERT나 UPDATE 그리고 DELETE 문장의 성능을 위해서라도 꼭 필요해요. 그리고 eq_range_index_dive_limit 시스템 변수도 Index Dive 튜닝에 도움이 될 수 있는데, 이는 마지막 부분에서 다시 살펴보도록 할게요.</p><p>만약 인덱스를 최적화한 이후에도 성능이 만족스럽지 않다면, MySQL 서버의 옵티마이저 힌트를 이용해서 실행 계획 수립 단계를 최소화할 수도 있어요. MySQL 8.0 버전에서 사용할 수 있는 힌트는 아래 3가지인데요.</p><ol><li>FORCE INDEX (ix_userid_categoryid)</li><li>USE INDEX (ix_userid_categoryid)</li><li>SELECT /*+ INDEX (articles ix_userid_categoryid) */</li></ol><p>1번과 2번 힌트는 MySQL 서버의 버전에 관계없이 사용할 수 있지만, 3번 힌트는 MySQL 5.7 이상의 버전에서만 사용할 수 있어요. 3개 힌트 모두 옵티마이저가 articles 테이블의 ix_userid_categoryid 인덱스를 사용하도록 유도한다는 공통점이 있어요.</p><p>차이점도 있는데요. 2번과 3번은 옵티마이저가 해당 인덱스의 Index Dive를 거쳐서 효율적인 경우에, 다른 인덱스를 무시하고 쿼리 실행 계획을 수립해요. 반면, 1번은 2번이나 3번과 비슷한 방식으로 작동하지만, 아래 몇 가지 조건만 충족되면 Index Dive 과정을 건너뛰게 돼요.</p><ul><li>단일 테이블 쿼리인 경우</li><li>단일 인덱스에만 FORCE INDEX 힌트 사용된 경우</li><li>Subquery가 없는 경우</li><li>Fulltext 인덱스가 사용되지 않는 경우</li><li>GROUP BY와 DISTINCT가 없는 경우</li><li>ORDER BY 절이 없는 경우</li></ul><p>적절한 방식으로 쿼리를 튜닝한다면, Performance schema의 그래프는 아래와 같이 statistics 단계의 CPU 및 Disk storage 자원 소모 비용이 executing 보다 훨씬 낮은 수준으로 떨어지는 것을 확인할 수 있어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*z1My7bGNCTdrBBhcuPpdLw.png" /><figcaption>Performance Schema (튜닝 후)</figcaption></figure><p>Performance schema의 내용은 단계별로 소모된 비용의 비율을 확인하기에는 좋지만, 실제 성능 개선 효과는 CPU 사용률 차트를 보는 것이 효율적이에요. 아래 그래프는 예시와 동일한 형태의 쿼리를 튜닝한 이후 CPU 변화를 보여주고 있어요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*24XEXalr0B3b4JGr1fg_wA.png" /><figcaption>튜닝 전 후, CPU 사용량 비교</figcaption></figure><p>이런 경우가 흔하게 발생하는 건 아니에요. IN (list) 비교 조건에서 list 의 엘리멘트가 많은 경우 그리고 IN (list) 비교 조건이 하나의 WHERE 조건절에 여러 번 사용된 쿼리가 statistics 단계의 시간 소모가 크다면, 우선 FORCE INDEX 힌트를 이용해서 최적의 인덱스를 사용하도록 강제화시키는 방법을 고려해보는 것이 좋아요. 물론 쿼리가 FORCE INDEX 힌트에 명시하는 인덱스를 항상 사용한다는 것을 먼저 확인하고 적용해야 해요.</p><h3>추가로</h3><p>이런 패턴(IN (list))의 쿼리 최적화에서, MySQL 서버의 옵티마이저 작동 방식에 영향을 미치는 2개 시스템 변수가 있어요.</p><ul><li>eq_range_index_dive_limit</li><li>range_optimizer_max_mem_size</li></ul><p>eq_range_index_dive_limit 는 동등비교 조건의 개수를 기준으로, 실행 계획 수립 단계의 Index Dive를 할지 단순히 통계 정보만 활용할지 여부를 결정해요. 예를 들어서 eq_range_index_dive_limit=200 으로 설정된 경우, MySQL 서버 옵티마이저는 동등 비교 회수가 200을 넘으면 Index Dive를 실행하지 않고 통계 정보만을 이용해서 실행 계획을 수립해요. 그래서 아래 쿼리의 경우, 동등 비교 회수는 40000번(200 * 200)이 필요하기 때문에 eq_range_index_dive_limit 시스템 변수에 설정된 200을 넘어서게 되어서, Index Dive 가 사용되지 않아요.</p><pre>CREATE TABLE articles (<br>  id BIGINT NOT NULL,<br>  user_id BIGINT NOT NULL,<br>  category_id INT NOT NULL,<br>  ...<br>  PRIMARY KEY (id),<br>  INDEX ix_userid_categoryid (user_id, category_id)<br>);</pre><pre>SELECT *<br>FROM articles<br>WHERE user_id IN (1,2,3,4,5, ..., 200)<br>  AND category_id IN (1,2,3,4,5, ..., 200);</pre><p>그런데 eq_range_index_dive_limit 시스템 변수는 순수하게 동등 비교의 인덱스 활용 케이스에만 적용돼요. 그래서 아래와 같이 id&gt;10000 또는 id&lt;20000과 같이 동등 비교가 아닌 조건이 포함되면 Index Dive 사용을 피할 수 없어요.</p><pre>SELECT *<br>FROM articles<br>WHERE user_id IN (1,2,3,4,5, ..., 200)<br>  AND category_id IN (1,2,3,4,5, ..., 200)<br>  AND id&gt;10000;</pre><p>위의 쿼리는 ix_userid_categoryid 인덱스를 사용하게 되는데, MySQL 서버의 Primary Key는 클러스터링 키이기 때문에 내부적으로 ix_userid_categoryid 인덱스는 (user_id, category_id, id) 컬럼 조합으로 인덱스가 구성돼요. 결국 실제 쿼리의 전체 검색 조건은 동등 조건이 아니고 아래와 같이 “크다 또는 작다”와 같은 범위 조건으로 해석이 되기 때문에, eq_range_index_dive_limit 시스템 변수는 아무런 영향을 못 미치게 돼요.</p><pre>SELECT *<br>FROM articles<br>WHERE (user_id=1 AND category_id=1 AND id&gt;10000)<br>   OR (user_id=1 AND category_id=2 AND id&gt;10000)<br>   OR ...<br>   OR (user_id=2 AND category_id=1 AND id&gt;10000)<br>   OR (user_id=2 AND category_id=2 AND id&gt;10000)<br>   OR ...;</pre><p>Index Dive 에 영향을 미치는 또 다른 시스템 변수로는 range_optimizer_max_mem_size 가 있어요. MySQL 서버는 IN (list) 조건이 사용되면, list 엘리멘트 값들을 메모리에 먼저 로드해서 중복 값을 제거하고 사용 가능한 형태로 확장(fan-out)을 해야 하는데요. 이때 메모리를 무한정 사용하도록 허용하지 않아요. range_optimizer_max_mem_size 시스템 변수는 MySQL 서버가 이런 용도로 사용 가능한 메모리 크기를 설정하는데, 만약 range_optimizer_max_mem_size 시스템 변수에 설정된 메모리 크기를 초과하면 MySQL 서버는 해당 실행 계획을 포기해요. 즉, ix_userid_categoryid 인덱스를 활용하는 실행 계획은 선택할 수 없고, 다른 최적의 실행 계획을 선택해요. 문제는 다른 최적의 실행 계획이 없다면, 풀 테이블 스캔을 하게 될 수도 있어요.</p><p>그런데 FORCE INDEX 힌트가 사용되면, range_optimizer_max_mem_size 시스템 변수에 설정된 메모리 공간을 넘어서는 경우에도 ix_userid_categoryid 인덱스를 활용한 실행 계획을 사용할 수 있어요.</p><h3>결론적으로</h3><p>쿼리에 IN (list) 패턴이 사용된다 하더라도, 아래와 같은 경우에는 크게 걱정하지 않아도 괜찮아요.</p><ul><li>WHERE 절에 하나의 IN (list)조건만 사용되는 경우</li><li>IN (list) 에 사용된 엘리멘트의 개수가 많지 않은 경우</li><li>IN (list) 조건이 인덱스를 사용하지 못하는 경우</li></ul><p>하지만 IN (list)에 많은 엘리멘트가 사용되거나 하나의 쿼리에서 인덱스를 사용하는 IN (list)조건이 여러 번 사용된다면, 이 글의 내용을 참고해서 여러 방식으로 쿼리를 변경해가면서 최적의 성능을 찾는 것이 좋아요.</p><p>이번 이야기도 재미있게 읽으셨다면, 그리고 더 많은 고민을 함께 하고 싶으시다면! <a href="https://open.kakao.com/o/gx274hbe"><strong>Real MySQL 오픈 챗</strong></a> 참여 또는 <a href="https://team.daangn.com/jobs/"><strong>당근 마켓 개발자와 DBA로 지원</strong></a>해주세요.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1a50478f7df8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/index-dive-%EB%B9%84%EC%9A%A9-%EC%B5%9C%EC%A0%81%ED%99%94-1a50478f7df8">Index Dive 비용 최적화</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL Command Line Interface]]></title>
            <link>https://medium.com/daangn/mysql-command-line-interface-21de489e7db5?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/21de489e7db5</guid>
            <category><![CDATA[데이터베이스]]></category>
            <category><![CDATA[프로그래밍]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Sat, 21 May 2022 23:37:10 GMT</pubDate>
            <atom:updated>2022-05-21T23:37:10.410Z</atom:updated>
            <content:encoded><![CDATA[<p>요즘은 세상의 모든 컴퓨터 화면이 그래픽 인터페이스로 바뀐 것 처럼 보입니다. 업무를 하면서도 주변의 컴퓨터를 돌아보면, 까만 색 바탕의 녹색 글자(물론 컬러는 다를 수 있겠지만)는 찾아보기 어렵죠.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xEwk5bj3_nzED31D1pbv6Q.png" /><figcaption>누구나 한번쯤은 ASCII art로 터미널 화면을 꾸며 보지 않았을까요 ?</figcaption></figure><p>MySQL도 MySQL Workbench나 3rd-party로 제공되는 다양한 형태의 SQL 인터페이스 프로그램들을 제공하고 있는데, 이들 대부분은 그래픽 인터페이스를 기반으로 하고 있습니다. 사용자의 취향에 따라서 차이는 있겠지만, 많은 사용자들이 화면에 많은 기능(버튼과 팝업 메뉴들)들이 한 눈에 보이는 그래픽 기반 인터페이스를 선호하고 있죠.</p><p>하지만 누군가는 여전히 텍스트 기반의 명령어 인터페이스(Command line interface)를 더 선호하고, 때로는 그래픽 인터페이스보다 효율적인 업무 환경을 제공한다고 생각합니다. 개인적으로 어떤 DBMS든지 Command Line Interface(CLI)를 선호하는 편인데, MySQL CLI-client는 Oracle이나 PostgreSQL CLI-client에 비해서 불편한 점이 꽤 있는 편이긴 합니다. 오늘은 MySQL CLI-client를 변형하면 어떻게 달라질 수 있는지를 보여 드리려고 해요.</p><p>문장의 간결함을 위해서, 이제부터는 높임말은 생략할게요.</p><p>MySQL CLI-client 코드는 <a href="https://github.com/sunguck/mysql-server/tree/matt/mysql-rich-client/client">mysql-src/client 디렉토리</a>에 있는데, MySQL client의 기능 대부분은 <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/mysql.cc">mysql-src/client/mysql.cc 파일</a>에 저장되어 있다. MySQL 서버 코드보다는 매우 간단하고 짧은 편이어서, C/C++ 전문가가 아니어도 어렵지 않게 읽고 코드를 고칠 수 있다. MySQL client 코드를 설명하는 것은 너무 지루한 이야기이므로, 여기에서는 미리 추가해 둔 기능들을 어떻게 사용하는지 살펴보고자 한다. 이 예시들이 MySQL client를 직접 개선해서 사용해 볼 수 있는 시작점이 될 수 있기를 기대한다. .</p><p>오늘은 아래와 같은 기능들을 MySQL client에 추가해서 사용하는 예시들을 살펴보고자 한다.</p><ul><li>MySQL client의 프롬프트 내용 추가 및 컬러 변경</li><li>MySQL client의 단축 명령 추가</li><li>파일에 저장된 SQL 실행</li><li>특정 케이스에 대한 주의 사항 표시</li></ul><h3>프롬프트 변경</h3><p>MySQL client의 프롬프트는 아래와 같이 설정 파일의 prompt 옵션을 통해서 변경할 수 있다. prompt 옵션의 설정 방법은 <a href="https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html">MySQL 메뉴얼</a>(페이지 하단부)에 자세히 설명되어 있으니, 아래 내용이 잘 이해되지 않는다면 참고하도록 하자.</p><pre>[mysql]<br>prompt=&quot;\\u@\\h:[\\d] &gt; &quot;</pre><p>MySQL client의 프롬프트로 설정할 수 있는 값들은 매우 다양하다. 하지만 가끔은 더 새로운 욕심이 생긴다. 대표적으로 Proxy를 이용해서 MySQL 서버에 접속하는 경우, MySQL client는 실제 접속된 서버의 정보를 알아낼 수 없다. 그리고 MySQL 서버는 자신이 프라이머리인지 레플리카인지를 공식적으로 관리하지 않기 때문에, 복제의 역할에 관련된 내용을 프롬프트에 표시해 주지 못한다. <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/mysql.cc#L5347-L5364">MySQL client의 코드를 변경</a>해서, 아래 내용들이 표시되도록 기능을 추가해보았다.</p><ul><li>\X는 별도의 호스트 목록 파일에서 대상 서버를 찾아서 그 서버의 이름을 표시</li><li>\Z 는 연결된 서버가 프라이머리인지 레플리카 역할인지를 표시</li><li>prompt_color_primary와 prompt_color_replica 옵션을 이용하여, 프라이머리인 경우와 레플리카인 경우 프롬프트의 컬러를 다르게 표시</li></ul><pre>[mysql]<br>prompt=&quot;\\u@\\X(\\Z):[\\d] \\R:\\m:\\s\\T&gt;&quot;<br>prompt_color_primary=magenta<br>prompt_color_replica=yellow</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YGJHkQ0L5NJxQu7EGgfz6A.png" /></figure><p>외부 파일에 설정된 내용(port → server_hostname 맵핑)을 참고해서 해당 서버의 이름을 프롬프트에 표시하고, 그 뒤에는 레플리케이션의 역할을 표시하고 있다. 마지막으로 프라이머리이기 때문에, 프롬프트의 색깔이 붉은 계열의 magenta 로 표시되었다.</p><h3>MySQL client의 단축 명령</h3><p>PostgreSQL client 를 사용하면서 느낀 장점 중 하나는 많은 메타 정보 SELECT 문장들이 단축 명령으로 확인이 가능하다는 것이었다. 때로는 단축 명령이 너무 많아서 복잡해 보이기도 하지만, 하루 종일 고생하는 손가락과 빠른 조회를 위해서는 매우 훌륭한 기능임은 확실해 보였다. 그래서 MySQL client에서도 몇 가지 자주 사용하는 <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/mysql.cc#L5726-L6024">조회 SQL을 위한 단축 명령을 사용할 수 있도록 코드를 변경</a>해보았다.</p><p>여기에서 구현된 단축 명령은 \\ 로 시작되어야 하며, 그 뒤에 한글자 또는 두글자의 알파벳으로 구성된다. 지원되는 단축 명령의 목록을 확인하고자 한다면, \\ 만 입력하고 엔터를 누르면 볼 수 있도록 해두었다.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5VZEAncvztSV_DUpQ3Qrbw.png" /><figcaption>단축 명령 목록 확인</figcaption></figure><p>아마도 MySQL client를 자주 사용하는 경우, 느끼는 가장 큰 고통은 데이터베이스 목록 조회를 할 때 사용하는 SHOW DATABSAES 명령 타이핑일 것이다. 대부분의 키보드가 왼쪽 손에 몰려있고, 특히 S와 E 의 순서가 자주 틀린다는 것에 동의할 것이다. 이제 추가해 둔 단축 명령으로 데이터베이스 목록을 쉽게 확인하는 방법을 살펴보자.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/950/1*ch4jCcLoNHh9AyG0rCMd5w.png" /></figure><p>\\d 단축 명령으로 SHOW DATABASES 명령이 실행되고, 결과가 출력된 것을 볼 수 있다. “이게 뭐라고?”라고 생각하는 사람도 있겠지만, MySQL client를 하루 종일 사용하는 입장에서는 (주관적인 판단으로) 획기적인 기능이라고 확신한다. 이 이외에도 사용자 목록과 함수나 프로시져 목록 그리고 잠금 대기나 장시간 활성 상태의 트랜잭션 목록 조회 등 자주 사용하는 명령들을 \\ 단축 명령어로 쉽게 실행할 수 있다.</p><p>예시된 단축 명령 이외에도 많은 기능들을 추가해두었으므로, 더 많은 예제들이 궁금하다면 <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/README.md#%EB%8B%A8%EC%B6%95-%EB%AA%85%EB%A0%B9%EC%96%B4-mysql-cli-%EB%82%B4%EC%9E%A5-sql">여기 링크의 README.md 파일</a>을 참고해보자.</p><h3>SQL 파일 실행</h3><p>아마도 MySQL client를 자주 경험해 본 사용자라면, MySQL client의 source 또는 \. 명령이 정확히 이 목적을 위한 기능이라는 것을 기억할 것이다. 그런데 Oracle client를 사용해 본 사용자라면, MySQL client의 source 명령에서는 SQL 파라미터(바인딩 변수) 설정 기능이 누락되어 있다는 것이 얼마나 큰 불편함인지 잘 알고 있을 것이다. 물론 세션 변수를 이용하면 바인딩 변수 역할을 대체할 수 있다. 하지만 세션 변수를 사용하기 위해서는, 파일에 저장된 SQL 문장에서 어떤 세션 변수가 정의되어야 하는지를 모두 기억해야 하며, 더 큰 문제는 세션 변수는 SQL 문장의 특정 위치에는 사용될 수 없다는 것이다. <a href="https://dev.mysql.com/doc/refman/8.0/en/query-attributes.html">MySQL 8.0.23 client 부터 도입된 Query attributes 기능</a> 또한 이런 문제들을 해결하는데 큰 도움이 되지는 않는 것으로 보인다.</p><p>그래서 이런 불편함을 회피하기 위해서, <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/mysql.cc#L5891-L5940">\\x 단축 명령을 이용해서 지정된 디렉토리의 SQL 파일을 실행할 수 있도록 기능을 추가</a>했다. \\x 단축 명령은 아래 디렉토리에서 인자로 명시된 SQL 파일을 찾아서 실행한다.</p><ol><li>MYSQL_SCRIPTDIR 환경 변수에 설정된 디렉토리</li><li>~/.mysqlrc/ 디렉토리</li></ol><p>MYSQL_SCRIPTDIR 환경 변수가 별도로 설정되지 않으면, ~/.mysqlrc/ 디렉토리에서 SQL 파일을 검색하게 된다. \\x 명령으로 SQL 파일을 실행하는 방법은 다음과 같다. 실행할 SQL 파일의 확장자는 반드시 .sql 이어야 하며, \\x 명령 사용시에는 .sql 확장자를 명시하지 않아야 한다. 이는 단순히 입력의 편의를 위한 것일 뿐, 다른 목적은 없다.</p><p>테스트를 위해서 아래 내용의 ~/.mysqlrc/test.sql 파일을 준비하고,</p><pre>SELECT &#39;#{STRING_VARIABLE}&#39; as string_var LIMIT #{LIMIT_N_ROWS};</pre><p>MySQL client에서 \\x test 명령을 실행해보자.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/968/1*bXE5mZGEXaV4AD5sbGunKQ.png" /></figure><p>\\x test 명령은 ~/.mysqlrc/test.sql 파일을 읽어서 실행하는데, 만약 SQL 문장에 바인딩 변수가 정의되어 있으면, 터미널에 사용자가 변수 값을 입력할 수 있도록 변수명을 표시하고 입력을 기다리게 된다. 위의 예제에서는 2개의 바인딩 변수( #{STRING_VARIABLE}과 #{LIMIT_N_ROWS})를 입력받아서 쿼리를 실행한 것이다. 예시에서 보인 바와 같이 \\x 명령으로 처리되는 바인딩 변수는 세션 변수 또는 표준 바인딩 변수 형태가 아니기 때문에, 아래 규칙을 준수해서 SQL 파일에 사용되어야 한다.</p><ul><li>SQL 파라미터는 #{변수명}포맷으로 명시</li><li>(바인딩 변수의 타입을 인식하지 못하기 때문에) 문자열 타입인 경우 SQL 문장에서 따옴표를 포함해서 &#39;#{STRING_VAR}&#39; 와 같이 사용</li></ul><p>참고로, \\x 명령은 배치 형태로 처리되지 않기 때문에, 파일의 모든 내용을 메모리로 읽어 들인 후 실행하기 때문에, source 명령처럼 매우 큰 SQL 파일을 실행하지는 못한다.</p><h3>특정 케이스에 대한 주의 사항 표시</h3><p>MySQL 서버를 사용함에 있어서, 각 회사별로 주의해야 하는 사항들이 다양하게 있을 것이다. 예를 들어서 특정 시스템 변수가 활성화된 경우 경고 메시지를 출력하거나, Production DBMS 서버는 경고 메시지를 표시한다는 등의 요건들이 있을 수 있다. 여기에서는 간단한 예시를 위해서, MySQL 서버의 innodb_adaptive_hash_index가 활성화된 경우 <a href="https://github.com/sunguck/mysql-server/blob/matt/mysql-rich-client/client/mysql.cc#L1466-L1467">로그인 시점에 경고 메시지를 출력하도록 코드를 추가</a>해보았다.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F2ZJhEdWOVWi95YhLiTvNQ.png" /></figure><p>여기에서 살펴본 것은 간단한 예시일 뿐이며, 각 회사 또는 DBA별로 필요로 하는 경고 메시지를 넣어두면 실수를 최소화할 수 있지 않을까 생각된다. 조금만 더 신경쓰면, 경고 메시지의 색깔을 조정해서 더 눈의 띄도록 만들 수도 있을 것이다.</p><p>오늘 예시에 사용된 MySQL client 소스 코드는 아래 github에서 다운로드할 수 있습니다.</p><p><a href="https://github.com/sunguck/mysql-server/tree/matt/mysql-rich-client">GitHub - sunguck/mysql-server at matt/mysql-rich-client</a></p><p>얼마나 많은 MySQL 사용자들이 Command Line Interface를 사용하고 있을지는 모르겠지만, 오늘 글이 많은 사용자들에게 힘들고 지루한 일상의 소소한 재미꺼리가 되고, 나아가서는 MySQL client를 사랑하시는 분들의 손가락 관절 노화방지에도 도움이 될 수 있기를 기대합니다.</p><p>이번 이야기도 재미있게 읽으셨다면, 그리고 더 많은 것들을 경험하고 싶으시다면, <a href="https://open.kakao.com/o/gx274hbe"><strong>Real MySQL 오픈 챗</strong></a> 참여 또는 <a href="https://team.daangn.com/jobs/"><strong>당근 마켓 개발자와 DBA로 지원</strong></a>해주세요.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=21de489e7db5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-command-line-interface-21de489e7db5">MySQL Command Line Interface</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MySQL Online-DDL]]></title>
            <link>https://medium.com/daangn/mysql-online-ddl-faf47439084c?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/faf47439084c</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[dba]]></category>
            <category><![CDATA[rdbms]]></category>
            <category><![CDATA[ddl]]></category>
            <category><![CDATA[mysql]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Mon, 25 Apr 2022 13:23:20 GMT</pubDate>
            <atom:updated>2022-04-25T13:23:20.992Z</atom:updated>
            <content:encoded><![CDATA[<p>당근 마켓의 서비스는 쉬지 않고 발전하고 있어요. 하지만 이런 소프트웨어의 개선 작업은 단순히 버튼 한번 클릭으로 완성되는 것은 아니에요. 때로는 마치 전쟁을 치루는 것 같은 과정을 거치는 경우도 많아요.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*G7Q4xqbUR-rcjQH6rk9RDQ.jpeg" /></figure><p>누군가 “소프트웨어 엔지니어는 달리는 기차의 바퀴를 바꿔 끼우는 사람들”이라는 이야기를 했던 기억이 나는데요. 그 중에서도 DBMS의 구조 변경은 단연코 기차의 바퀴를 빼고 바꾸는 핵심 작업이라고 할 수 있으며, 특히 대용량 테이블의 구조 변경 작업은 “서비스 영향도 최소화”와 “최단 시간 완료” 사이에서 최적의 지점(Sweet spot)을 찾아야 해요. 오늘은 MySQL 서버가 서비스중인 상태에서, 테이블의 구조를 변경하는 작업에서 주의해야 할 이야기를 하나 하려고 해요.</p><p>문장의 간결함을 위해서, 이제부터는 높임말은 생략할게요.</p><p>대부분의 MySQL 서버 사용자가 알고 있듯이, MySQL 서버는 2011년부터 Online DDL이라는 기능을 제공하고 있다. Online DDL은 테이블의 구조를 변경(스키마 변경)을 실행하는 도중에도, INSERT나 UPDATE와 같은 DML들을 실행할 수 있도록 해주는 기능이다. MySQL 서버에서 Online DDL이 지원되지 않았던 시절에는 pt-online-schema-change와 같은 3-rd party 도구들을 이용해야 했지만, (이런 도구들은 불필요한 작업이 너무 많이 수반되어 서비스 영향도가 높은 편이어서) 이제는 대부분의 경우 MySQL 서버의 Online DDL을 이용한다.</p><p>MySQL 서버의 Online DDL은 필수적인 기능이지만, MySQL 서버가 Online DDL을 지원하기 시작한지 10년 넘어가는 지금에도 치명적인 문제점을 가지고 있다. 그 문제점이 왜 발생하는지, 어떤 경우에 Online DDL 보다는 pt-online-schema-change 도구를 선택해야 하는지를 살펴보도록 하자.</p><p><a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html">MySQL 서버의 Online DDL 메뉴얼</a>을 살펴보면, 다음과 같이 알쏭 달쏭한 제한 사항이 숨어 있다.</p><blockquote>When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction.</blockquote><p>한글 번역은 쉽게 되지만, 메뉴얼의 “<em>only temporary</em>”는 어떤 케이스인지 그리고 왜 그렇게 작동하는지에 대한 설명과 예시가 없어서, 어떤 경우에 주의해야 하는지 예측할 수가 없다. 그래서 메뉴얼의 “<em>duplicate entry in only temporary</em>”가 어떤 케이스를 의미하는지를 예제로 살펴보도록 하겠다.</p><p>아래와 같은 구조를 가진 테이블에서, PRIMARY KEY와 UNIQUE INDEX를 서로 맞바꾸는 경우를 한번 생각해보자.</p><pre>-- // 테이블의 구조<br>CREATE TABLE test.dup_error(<br>    id INT AUTO_INCREMENT NOT NULL, <br>    uid INT NOT NULL, <br>    v DATETIME, <br>    PRIMARY KEY (id),<br>    UNIQUE KEY ux_uid (uid)<br>);</pre><pre>-- // 인덱스 교체를 위한 Online DDL<br>ALTER TABLE test.dup_error <br>    DROP PRIMARY KEY, <br>    DROP INDEX ux_uid, <br>    ADD PRIMARY KEY(uid), <br>    ADD UNIQUE INDEX ux_id(id), <br>    ALGORITHM=inplace, LOCK=none;</pre><p>아마도 다른 컨넥션에서 INSERT나 UPDATE와 같은 DML이 실행되지 않는다면, 특별히 문제없이 스키마 변경은 완료될 것이다. 물론 테이블의 레코드가 매우 많다면 시간이 상당히 걸릴수도 있다. 그렇다 하더라도 언젠가는 Online DDL은 완료될 것이고, PRIMARY KEY와 UNIQUE INDEX는 교체되어 있을 것이다.</p><p>Online DDL을 사용하는 이유는 스키마 변경을 실행하는 중에도 INSERT나 UPDATE와 같은 DML이 실행될 수 있도록 하기 위함이다. 그래서 Online DDL이 실행되는 중에도 DML은 계속 유입될 것이다. 물론 DML이 너무 빈번하게 실행된다면, 변경 내용을 임시로 저장하는 로깅용 버퍼(innodb_online_alter_log_max_size 시스템 변수로 크기 조절)의 공간이 부족해서 실패할 수도 있다. 하지만 이는 우리에게 너무 익숙한 제약이므로, 여기에서는 설명을 생략하도록 하겠다.</p><p>그런데 Online DDL의 마지막 단계 작업(임시 버퍼에 로깅된 변경 내역을 테이블로 적용하는 단계)중 아래와 같은 에러가 발생하면서 실패하는 경우를 가끔 보았을 것이다.</p><pre>ERROR 1062 (23000) at line 1: <br>  Duplicate entry &#39;1&#39; for key &#39;dup_error.PRIMARY&#39;</pre><p>문제는 이 에러는 Online DDL 작업의 제일 마지막 마무리 단계에서만 발생하기 때문에, 불필요한 시간 낭비를 초래할 수도 있다. 예를 들어서, Online DDL 작업이 3~4 시간이 걸리는 작업인데 문제를 유발하는 DML이 Online DDL이 시작되자 마자 발생했다면, 어차피 결국 실패할 운명의 Online DDL을 3~4 시간동안 가슴 졸이며 기다리게 되는 것이다.</p><p>또한 한번 이 에러가 발생하는 DBMS에서는 두번 세번 Online DDL을 다시 실행해도 에러가 발생할 가능성이 매우 높다. 정확한 원인을 알지 못하면, 시간 낭비 뿐만 아니라 DBMS 서버의 자원까지 낭비하게 되는 것이다. 하지만 이 에러의 원인에 대해서 MySQL 서버 메뉴얼은 쉽게 이해할 수 있는 설명이 없어서, 많은 사용자들이 대략 이런 경우 “Duplicate entry”가 발생한다는 감만 가지고 있는 것이다.</p><p>MySQL 서버의 Online DDL은 SQL 수준이 아니라 스토리지 엔진 수준에서 구현되었다. 그래서 Online DDL은 사용자가 실행한 DML 문장이 어떤 형태인지 알지 못하며, 중복 에러가 발생해서 실패했는지에 대한 정보를 갖지 못한다. 그래서 Online DDL은 스토리지 엔진 수준으로 들어오는 데이터 변경 정보를 임시 버퍼 공간에 쌓았다가, 최종 시점에 버퍼의 내용을 순서대로 적용(Apply)하는 방식으로 처리된다. 이런 처리 과정 때문에 Online DDL이 실행되는 동안 다른 컨넥션에서 (스키마가 변경되는 테이블에) 중복된 레코드를 INSERT하는 경우, 성공 또는 실패 여부와 관계없이 Online DDL은 최종적으로 실패하게 된다.</p><p>이 과정을 확인하기 위한 간단한 테스트 시나리오를 살펴보자.</p><pre>## 테스트 테이블 생성<br>$ echo &quot;DROP TABLE IF EXISTS test.dup_error; CREATE TABLE test.dup_error(id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, uid INT NOT NULL, v DATETIME, UNIQUE KEY ux_uid(uid));&quot; | mysql -u tester<br> <br>## 테이블 용량 뻥튀기를 위해서 컬럼 추가<br>$ seq -f &quot;ALTER TABLE test.dup_error ADD COLUMN c%02.0f CHAR(255) DEFAULT &#39;&#39;;&quot; 1 30 | mysql -u tester</pre><pre>## 테스트 데이터 적재 (빠른 적재를 위해서 옵션 조정 후 INSERT 실행)<br>$ for n in {1..200000}; do printf &quot;INSERT INTO test.dup_error (id, uid) VALUES (%d, %d);\n&quot; $n $n; done | mysql -u tester --init-command=&quot;SET sql_log_bin=off; SET GLOBAL innodb_flush_log_at_trx_commit=0;&quot;</pre><p><strong>테스트 케이스 (1)</strong></p><pre>## 2개 터미널에서 동시에 실행<br><br># 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)<br>#       Online DDL 실행 후 1초후 실행되도록 &quot;sleep 1&quot; 먼저 실행)<br>session-1&gt; SELECT SLEEP(1);<br>session-1&gt; INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1) <br>             ON DUPLICATE KEY UPDATE v=NOW();<br>==&gt; NO-ERROR</pre><pre># 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)<br>session-2&gt; ALTER TABLE test.dup_error <br>             DROP PRIMARY KEY, <br>             DROP INDEX ux_uid, <br>             ADD UNIQUE INDEX ux_id(id), <br>             ADD PRIMARY KEY(uid), <br>             ALGORITHM=inplace, LOCK=none;<br>==&gt; ERROR 1062 (23000) at line 1: <br>      Duplicate entry &#39;1&#39; for key &#39;dup_error.PRIMARY&#39;</pre><p><strong>테스트 케이스 (2)</strong></p><pre>## 2개 터미널에서 동시에 실행<br><br># 세션-1 (UNIQUE KEY 중복으로 실패하는 INSERT 실행)<br>#       Online DDL 실행 후 1초후 실행되도록 &quot;sleep 1&quot; 먼저 실행)<br>session-1&gt; SELECT SLEEP(1);<br>session-1&gt; INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1);<br>==&gt; ERROR 1062 (23000) at line 1: <br>      Duplicate entry &#39;1&#39; for key &#39;dup_error.ux_uid&#39;</pre><pre># 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)<br>session-2&gt; ALTER TABLE test.dup_error <br>             DROP PRIMARY KEY, <br>             DROP INDEX ux_uid, <br>             ADD UNIQUE INDEX ux_id(id), <br>             ADD PRIMARY KEY(uid), <br>             ALGORITHM=inplace, LOCK=none;<br>==&gt; ERROR 1062 (23000) at line 1: <br>      Duplicate entry &#39;1&#39; for key &#39;dup_error.PRIMARY&#39;</pre><p><strong>테스트 케이스 (3)</strong></p><pre>## 2개 터미널에서 동시에 실행<br><br># 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)<br>#       Online DDL 실행 후 1초후 실행되도록 &quot;sleep 1&quot; 먼저 실행)<br>#       INSERT .. ON DUPLICATE KEY UPDATE 후, 롤백 실행<br>session-1&gt; SELECT SLEEP(1);<br>session-1&gt; BEGIN;<br>session-1&gt; INSERT INTO test.dup_error (id, uid) VALUES (NULL, 1) <br>             ON DUPLICATE KEY UPDATE v=NOW();<br>session-1&gt; ROLLBACK;<br>==&gt; NO-ERROR</pre><pre># 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)<br>session-2&gt; ALTER TABLE test.dup_error <br>             DROP PRIMARY KEY, <br>             DROP INDEX ux_uid, <br>             ADD UNIQUE INDEX ux_id(id), <br>             ADD PRIMARY KEY(uid), <br>             ALGORITHM=inplace, LOCK=none;<br>==&gt; ERROR 1062 (23000) at line 1: <br>      Duplicate entry &#39;1&#39; for key &#39;dup_error.PRIMARY&#39;</pre><p>세번째 테스트 케이스에서는 에러없이 실행된 INSERT .. ON DUPLICATE KEY UPDATE 문장을 롤백해도, Online DDL은 에러가 발생하는 것을 확인할 수 있다.</p><p>3개의 테스트 케이스에서 살펴본 것처럼, UNIQUE KEY 인덱스에 중복되는 값이 한번이라도 저장만 되면 (설령 Duplicate key error로 실패하더라도), Online DDL 문장은 최종 단계에서 실패하게 된다. 즉, 중복 에러 발생으로 인해서 INSERT가 UPDATE 방식으로 처리되거나 또는 실패로 완료된 DML이라 하더라도, Online DDL에서는 INSERT + DELETE 방식으로 처리된다. 이 과정에서 INSERT가 실행되면 Duplicate Key Error가 발생하게 되는 것이다. 물론 바로 이어서 DELETE가 실행될 것이므로 Duplicate Key Error는 해소되겠지만, MySQL 서버에서 이런 상태가 허용될 수가 없기 때문에 Online DDL은 최종적으로 에러를 발생하게 되는 것이다. 이렇게 일시적인 중복 키 에러 상태를 MySQL 서버 메뉴얼에서는 “<em>duplicate entry in only temporary</em>”라고 표현한 것이다.</p><p>추가로 메뉴얼에서는 “<em>it is possible to encounter a duplicate key entry error</em>”로 “발생할 수 있음”이라고 명시하고 있지만, 위에서 살펴본 3개 테스트 케이스에 해당하면 Online DDL은 100% 실패한다. 물론 예외 케이스가 간혹 있긴 하지만, 메뉴얼의 “<em>possible</em>”은 실패 빈도를 너무 낮게 표현한 것처럼 느껴진다.</p><p>Online DDL이 실행되는 중 INSERT .. ON DUPLICATE KEY UPDATE 문장이 UPDATE로 처리되어도, 문제없이 완료되는 경우도 있다. INSERT .. ON DUPLICATE KEY UPDATE 문장에서 Auto-Increment PK 컬럼에 NULL을 사용하는 경우(INSERT 항목에 PK 컬럼을 명시하지 않고 묵시적으로 NULL이 저장되는 케이스 포함)에만 에러가 발생했으며, (아래 예제에서와 같이) 명시적으로 값을 저장하는 경우에는 Online DDL의 에러가 발생하지 않는다.</p><pre>## 2개 터미널에서 동시에 실행<br><br># 세션-1 (UNIQUE KEY중복으로 UPDATE되도록 INSERT .. ON DUPLICATE KEY 실행)<br>#       Online DDL 실행 후 1초후 실행되도록 &quot;sleep 1&quot; 먼저 실행)<br>#       PK 컬럼에 &quot;1&quot;을 명시적으로 저장<br>session-1&gt; SELECT SLEEP(1);<br>session-1&gt; INSERT INTO test.dup_error (id, uid) VALUES (1, 1) <br>             ON DUPLICATE KEY UPDATE v=NOW();<br>==&gt; NO-ERROR</pre><pre># 세션-2 (PK와 UNIQUE INDEX를 swap하는 DDL 실행)<br>session-2&gt; ALTER TABLE test.dup_error <br>             DROP PRIMARY KEY, <br>             DROP INDEX ux_uid, <br>             ADD UNIQUE INDEX ux_id(id), <br>             ADD PRIMARY KEY(uid), <br>             ALGORITHM=inplace, LOCK=none;<br>==&gt; NO-ERROR</pre><p>INSERT .. ON DUPLICATE KEY UPDATE .. 문장이 사용되고 있다면, 먼저 SELECT 해서 INSERT를 할지 UPDATE를 할지 구분해서 실행하도록 쿼리를 바꿔서 사용하면 Online DDL의 실패를 막을 수 있을 듯 보인다. 하지만 이보다는 pt-online-schema-change를 이용해서 스키마 변경하는 방법이 더 나을 것으로 보인다. 테이블에 UNIQUE 인덱스를 생성하는 경우는 대부분 이런 케이스의 가능성을 내포하고 있다.</p><p>아마도 “pt-online-schema-change 사용하면 이런 고민 할 필요 없지 않을까?”라고 생각이 들 수도 있겠지만, pt-online-schema-change는 그 나름대로의 자원 낭비와 문제점을 가지고 있기 때문에 상황에 맞는 최적의 방법을 선택하는 것이 중요해 보인다.</p><p>이제 MySQL 서버의 메뉴얼에서 이야기하는 “<em>duplicate entry in only temporary</em>”가 이해될 것으로 보인다. 테스트 케이스를 보면 쉽게 이해되지만, 테스트 케이스를 보기 전에는 이해하기 어려운 경우라고 생각한다. 특히 세번째 테스트 케이스처럼, 롤백된 경우에도 3~4시간 뒤에 Online DDL은 Duplicate Key Error만 덩그러니 출력하고 실패해 버리는 것은 (왜 이렇게 밖에 할 수 없는지 이유를 알게 된 지금에도) 이해하기 어렵다. 빠른 시간내에 MySQL 서버의 Online DDL이 더 나은 형태로 개선되기를 기대해 본다.</p><p>간단한 이야기를 너무 장황하게 기술했다는 생각이 들 수도 있겠지만 이렇게 상세한 이야기를 기술한 것은, 실패 케이스를 회피하면서 Online DDL을 최적으로 활용하기 위해서는 문제의 원인을 정확하게 이해하는 것이 중요하기 때문이다.</p><p>이번 이야기도 재미있게 읽으셨다면, 그리고 더 많은 것들을 경험하고 싶으시다면, <a href="https://open.kakao.com/o/gx274hbe"><strong>Real MySQL 오픈 챗</strong></a> 참여 또는 <a href="https://team.daangn.com/jobs/"><strong>당근 마켓 개발자와 DBA로 지원</strong></a>해주세요.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=faf47439084c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/mysql-online-ddl-faf47439084c">MySQL Online-DDL</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Memory Allocator for MongoDB]]></title>
            <link>https://medium.com/daangn/memory-allocator-for-mongodb-1953f9cee06c?source=rss-9aa344a4b49b------2</link>
            <guid isPermaLink="false">https://medium.com/p/1953f9cee06c</guid>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[memories]]></category>
            <category><![CDATA[dba]]></category>
            <category><![CDATA[mongo]]></category>
            <category><![CDATA[mongodb]]></category>
            <dc:creator><![CDATA[Sunguck Lee]]></dc:creator>
            <pubDate>Sun, 17 Apr 2022 11:40:03 GMT</pubDate>
            <atom:updated>2022-04-17T11:40:03.211Z</atom:updated>
            <content:encoded><![CDATA[<p>DBMS 서버에서 메모리 관리는 매우 중요한 부분중 하나에요. DBMS 서버의 메모리 관리는 서버의 안정적인 운영뿐만 아니라 성능과도 직결되는 부분이 기 때문이죠.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OnAafjoGsVTVFlg22CvuWw.png" /></figure><p>이번 이야기에서는 MongoDB의 아래 2가지 궁금증을 풀어 보려고 해요.</p><ul><li>mongos의 과다한 메모리 사용량의 원인</li><li>TCMalloc보다는 JEMalloc이 나은 선택인 경우</li></ul><p><em>여기에서 부터는 문장의 간결함을 위해서 높임말은 생략할게요.</em></p><p>MySQL 서버는 리눅스 서버가 지금처럼 안정적이지 않던 시절부터 개발되고 사용되면서, MySQL 서버는 예전부터 운영 체제가 지원하는 느린 메모리 관리보다는 자체적으로 MEM_ROOT라고 불리는 Arena 기반의 Memory-Allocator(메모리 할당자) 코드를 사용해 왔다. 이는 지금도 마찬가지이며, 여전히 Memory-Allocator는 메모리 할당과 해제에 있어서 안정성과 성능 향상에 큰 역할을 하고 있다. 물론 MySQL 서버의 모든 코드가 MEM_ROOT를 사용하는 것은 아니다. 대표적으로 Stored Routine(Stored Function과 Stored Procedure)과 UDF 등은 각자의 Memory Allocator 또는 운영 체제의 기본 Memory Allocator를 사용하고 있다. 그렇다 하더라도 핵심적인 기능은 MEM_ROOT를 사용하기 때문에, MySQL 서버에서는 의도하지 않게 메모리 사용량이 급증한다거나 해제되지 않는 문제는 흔치 않다.</p><p>오늘의 이야기 주제는 MongoDB 서버에서의 메모리 할당과 해제에 대한 이야기이므로, 이제 MongoDB 서버로 돌아가 보자. MongoDB 서버는 사실 그다지 오래되지 않은 DBMS 서버이며, 처음부터 C++로 개발되었다. 메모리 할당과 해제를 직접 처리하는 C 언어와는 달리, C++에서는 (일반적으로) Heap Memory의 할당과 해제가 매우 많이 발생한다. 즉 별도의 Memory Allocator를 사용하지 않으면 리눅스 운영 체제의 기본 Memory Allocator인 PTMalloc2를 사용하게 된다. MongoDB에서도 PTMalloc2보다는 다른 더 나은 Memory Allocator를 인지하고 있었으며, 이를 위해서 MongoDB는 <a href="http://goog-perftools.sourceforge.net/doc/tcmalloc.html">TCMalloc</a>을 코드 수준에서 내장하고 있다.</p><p>리눅스 운영 체제에서 MySQL 이나 MongoDB 서버를 운영해본 경험이 있다면, Memory Allocator에 대한 논쟁과 토론들을 많이 보았을 것이다. 일반적으로 리눅스의 기본 Memory Allocator인 PTMalloc2보다는 TCMalloc이나 JEMalloc이 선호되고 있다는 블로그들을 자주 접했을 것이다. 그런데 TCMalloc과 JEMalloc 둘 중에서 어떤 것을 선택해야 할지에 대한 고민에 대한 내용은 그다지 많지 않다. 물론 <a href="https://jira.mongodb.org/browse/SERVER-24268">MongoDB에서도 TCMalloc 대신 JEMalloc을 도입하는 부분에 대해서 검토를 했었던 것</a>으로 보인다. 하지만 MongoDB에서는 JEMalloc 도입이 그다지 큰 이익이 없을 것으로 판단한 것으로 보인다.</p><p>서론이 길었는데 이제 MongoDB 서버에서 TCMalloc이 어떤 문제로 연결될 수 있는지를 간단히 살펴보자. MongoDB는 다양한 형태의 운용이 가능한데, 그 중에서 <a href="https://www.mongodb.com/docs/manual/sharding/#sharded-cluster">Sharded Cluster 배포</a>인 경우 모든 요청들은 mongos(MongoDB Proxy)를 거쳐야 한다. MongoDB 서버는 메모리 설정을 위한 파라미터들이 제공되지만 mongos는 이런 설정 자체가 없으며, 전통적으로 많은 사용자들이 mongos는 많은 메모리를 사용하지 않으며 일반적인 경우 100~200MB 정도로 할당해도 충분하다고 알고 있다. 그래서 mongos는 별도의 서버를 할당하지 않고 MongoDB 서버와 같은 서버에 실행하기도 한다. 실제 MongoDB의 관리형 서비스인 Atlas MongoDB에서도 mongod(MongoDB 서버)와 mongos를 동일 인스턴스에 배포해서 서비스를 제공하고 있기도 하다.</p><p>mongos를 통해서 작은 크기의 데이터를 주고 받는 경우에는 mongos의 메모리 사용량은 크게 올라가지 않는다. 이런 경우 우리가 알고 있는 것처럼100 ~ 200MB 수준의 메모리만 사용하게 된다. 그런데 mongos를 통해서 아주 큰 데이터를 읽어오는 경우, mongos는 일시적으로 많은 데이터를 버퍼링해야 하며 순간적으로 메모리 사용량이 증가할 수 있다. 그렇다고 mongos가 모든 필요한 데이터를 MongoDB 서버로부터 가져와서 버퍼링을 한다는 의미는 아니다. mongos는 적절한 페이징 사이즈만큼의 도큐먼트를 가져와서 클라이언트가 가져갈 때까지 버퍼링을 하기 때문에 1~2개의 클라이언트가 대량의 데이터를 읽어 간다고 해서 심각한 메모리 사용을 유발하지는 않는다.</p><p>그런데 만약 대용량 테이블을 파티션해서 30개 정도의 쓰레드가 동시에 읽어간다면 어떻게 될까? 당연히 1개의 쓰레드가 읽을 때보다 30배나 더 많은 메모리를 한번에 소진하게 될 것이다. 아래 차트는 N개의 쓰레드를 이용해서 20건을 읽는 쿼리와 테이블의 모든 레코드를 가져가는 쿼리를 실행할 때, mongos의 메모리 사용량 관계를 보여주고 있다. 쓰레드가 80개를 넘어서면서 메모리 사용량이 떨어지는 것은, 메모리 할당과 해제 오버헤드로 인해서 처리 성능이 떨어졌기 때문이다.</p><figure><img alt="mongos 메모리 사용량 차이(20건 쿼리 vs 풀 스캔 쿼리)" src="https://cdn-images-1.medium.com/max/1024/1*7YE-86XADpR_HXUeLTI53Q.png" /></figure><p>이렇게 많은 쓰레드로 동시에 대량 데이터를 읽는 경우가 있을까라고 생각할 수 있지만, MongoDB 서버의 데이터를 BigQuery나 다른 분석 DBMS로 복사할 때에는 (의도했던 아니던) 흔하게 발생하는 상황이기도 하다. 요즘처럼 이런 데이터 복사 작업을 오픈 소스 소프트웨어를 활용하는 경우, 내부적인 작동 방식에 대한 이해없이 빠르게 데이터를 가져가기 위해서 동시 작업 쓰레드를 60개씩 설정하는 경우도 흔히 발생한다. 이렇게 동시에 많은 쓰레드로 작업을 하게 되면, mongos 서버는 매우 많은 메모리를 한번에 소진하게 되는데, MongoDB 테이블의 도큐먼트 하나 하나의 크기가 큰 경우에는 더 심각하고 빠른 메모리 소진을 유발하게 된다. 때로는 mongos를 위해서 최대 2GB 정도의 여유를 두고 설치한 서버에서 MongoDB 서버보다 mongos가 더 많은 메모리를 사용하고 있을 수도 있고, 결국 메모리 부족으로 MongoDB 서버나 mongos 프로세스는 강제 종료될 것이다.</p><p>mongos가 메모리를 대량으로 점유해버리는 경우를 살펴보았다. 이제 이런 경우 TCMalloc과 JEMalloc이 어떻게 반응하는지를 살펴보자. 아래 차트는 20개 정도의 쓰레드로 대량 쿼리를 실행할 때, mongos의 Memory Allocator가 TCMalloc인 경우와 JEMalloc인 경우의 메모리 사용량 변화를 보여주고 있다.</p><figure><img alt="Memory Usage (TCMalloc vs JEMalloc)" src="https://cdn-images-1.medium.com/max/1024/1*DM3hAxR5bTuCf8BbEICIaw.png" /></figure><p>위 차트를 보면, mongos를 통해서 대량 쿼리를 실행하는 동안(GetMore 호출이 실행되는 도중) JEMalloc은 대략 1.3GB정도를 사용하고 있는 반면 TCMalloc은 2.4GB정도를 사용하고 있다. 그리고 대량 쿼리를 강제 종료하면 JEMalloc은 메모리 사용량이 2.2GB까지 솟구치는 것을 현상을 볼 수 있다. (이 과정이 중요한 것은 아니지만) 이는 TCMalloc의 메모리 할당과 해제 성능이 JEMalloc보다 낮아서 클라이언트가 mongos로부터 쿼리 결과를 빠르게 가져가지 못해서 mongos가 더 많은 데이터를 버퍼링해야 했기 때문에 메모리 사용량이 처음부터 2.4GB까지 솟구친 것이며, JEMalloc의 경우는 TCMalloc보다는 빠르게 처리했기 때문에 mongos가 버퍼링해야 할 데이터들이 많지 않았기 때문이다. (이는 차트상 TCMalloc의 GetMore 요청의 처리 수가 JEMalloc 대비 기복이 심한 것을 보면 이해할 수 있다.)</p><p>이 차트에서 더 주의깊게 살펴봐야 하는 지점은, 타임라인의 마지막 부분에서 JEMalloc의 메모리 사용량이 뚝 떨어지는 부분이다. 반면 TCMalloc은 메모리 사용량이 그 상태로 유지되는 것을 확인할 수 있다. 여기에서는 타임라인이 잘려있지만, 테스트후 대기했던 하루 정도까지 TCMalloc의 메모리 사용량은 2.4GB 수준에서 줄어들지 않았다. 그러면 375초 지점에서는 무슨 일이 있었던 것일까 ? 이는 mongos 서버의 Cursor Timeout(cursorTimeoutMillis) 파라미터가 300초(기본 값)로 설정되어 있기 때문이다. 쿼리를 강제 종료한 75초 지점부터 300초가 지나면서, 쿼리에 사용되었던 Cursor가 타임아웃으로 무효화되면서 Cursor가 가지고 있던 데이터 버퍼링용 메모리 공간이 불필요해진 것이다.</p><p>JEMalloc은 Cursor가 타임아웃되고 삭제되면서 반환된 메모리 공간을 리눅스 서버(운영 체제)로 반환했지만, TCMalloc은 반환된 메모리를 리눅스 서버로 반환하지 않고 자기 자신의 로컬 캐시 공간에 그대로 유지하기 때문에 이런 차이를 보이는 것이다. TCMalloc과 같이 리눅스 서버로부터 한번 할당받은 메모리 공간을 자체적으로 캐시하게 되면, 이후 유입되는 쿼리들의 처리를 위해서 (리눅스 서버로부터 다시 할당받지 않아도 되기 때문에) 더 빠르게 메모리 할당과 해제 작업을 처리할 수 있지만, JEMalloc은 그 반대로 느리게 작동하게 될 것이다.</p><p>용도에 따라서 TCMalloc과 JEMalloc은 장점과 단점이 명확하다. 대략적으로 테스트 케이스에서 보인 쿼리들의 요건들을 고려해서 적절한 Memory Allocator를 선택하면 될 것으로 보인다.</p><ul><li>대용량 쿼리가 매우 빈번하게 실행되거나</li><li>mongos를 위해서 충분한 메모리를 가지고 있다면</li></ul><p>예를 들어서, 위 2개 조건에 부합된다면 TCMalloc을, 그렇지 않다면 JEMalloc이 더 나은 선택일 것이다. 대용량 쿼리가 빈번하지 않은 경우 TCMalloc 처럼 불필요하게 사용되지도 않을 메모리를 Memory Allocator가 가지고 있으면, 다른 프로세스가 메모리를 사용하지 못하는 낭비가 발생하게 될 것이다.</p><p>참고로, MongoDB configruation으로 제어할 수 있는 TCMalloc의 tcmallocReleaseRate 파라미터는 리눅스 서버로의 메모리 반환에 아무런 영향을 미치지 못했으며, tcmalloc.heap_limit_mb 파라미터가 지원되는 TCMalloc 2.9.1 버전을 MongoDB 서버에 패치해도 특별히 개선 효과를 보이진 않았다.</p><p>일반적으로 MongoDB 서버는 온라인 트랜잭션 처리 용도(OLTP)로 사용되는데, 이런 OLTP 처리용 DBMS 서버에서 이런 대용량 쿼리는 빈번하게 실행되지 않으며, mongos의 과도한 메모리 사용은 OLTP 쿼리 처리의 성능에 상당한 영향을 미치게 된다. 그래서 개인적으로 생각해볼 때, MongoDB 서버의 모든 프로그램은 TCMalloc보다는 JEMalloc으로 전환되어야 하지 않을까 생각한다. 하지만 이미 MongoDB에서는 JEMalloc을 검토했었고, TCMalloc을 그대로 유지하기로 결정한 듯 보인다. 만약 MongoDB를 직접 설치해서 사용하면서 메모리 공간이 넉넉하지 않은 사용자라면, MongoDB 서버의 Memory Allocator를 JEMalloc으로 전환해 보는 것도 좋은 시도가 아닐까 생각된다.</p><p>앞에서 설명했던 것처럼, MongoDB 서버는 이미 코드 수준에서 TCMalloc Memory Allocator를 사용하도록 구현되어 있다. 그래서 MongoDB 서버가 JEMalloc을 사용하도록 변경하기 위해서는, MongoDB 서버가 System Memory Allocator를 사용하도록 새로 빌드 후 LD_PRELOAD 설정을 이용해서 JEMalloc을 사용하도록 변경해야 한다.</p><h4>MongoDB Rebuild (Percona MongoDB 예시)</h4><pre>$ python3 buildscripts/scons.py \<br>  MONGO_VERSION=4.4.6-8-psmdb-system-allocator \<br>  CC=/opt/rh/devtoolset-8/root/usr/bin/gcc \<br>  CXX=/opt/rh/devtoolset-8/root/usr/bin/g++ \<br>  AR=/opt/rh/devtoolset-8/root/usr/bin/ar \<br>  CPPPATH=&quot;${AWS_LIBS}/include&quot; \<br>  LIBPATH=&quot;${AWS_LIBS}/lib64&quot; \<br>  --jlink=2 \<br>  --install-mode=legacy \<br>  --disable-warnings-as-errors \<br>  --ssl \<br>  --opt=on \<br>  --use-sasl-client \<br>  --wiredtiger \<br>  --audit \<br>  --inmemory \<br>  --hotbackup \<br>  --allocator=system \<br>  mongod mongos</pre><h4>mongos의 systemctl 스크립트</h4><pre>## /lib/systemd/system/mongos.service</pre><pre>[Unit]<br>Description=MongoDB Proxy (mongos)<br>After=time-sync.target network.target</pre><pre>[Service]<br>Type=forking<br>User=mongod<br>Group=mongod<br>PermissionsStartOnly=true<br>LimitFSIZE=infinity<br>LimitCPU=infinity<br>LimitAS=infinity<br>LimitNOFILE=64000<br>LimitNPROC=64000<br>ExecStart=/usr/bin/env bash -c &quot;MALLOC_CONF=&#39;dirty_decay_ms:100,muzzy_decay_ms:2000&#39; LD_PRELOAD=/usr/lib64/libjemalloc.so.2 /usr/bin/mongos -f /etc/mongos.conf &gt; /var/log/mongo/mongos.stdout 2&gt; /var/log/mongo/mongos.stderr&quot;<br>PIDFile=/var/run/mongo/mongos.pid</pre><pre>[Install]<br>WantedBy=multi-user.target</pre><p>mongos의 메모리 이야기를 재미있게 읽으셨다면, 그리고 더 많은 것들을 경험하고 싶으시다면, <a href="https://open.kakao.com/o/gx274hbe"><strong>Real MySQL 오픈 챗</strong></a>에 참여 또는 <a href="https://team.daangn.com/jobs/"><strong>당근 마켓 개발자와 DBA로 지원</strong></a>해주세요.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=1953f9cee06c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/daangn/memory-allocator-for-mongodb-1953f9cee06c">Memory Allocator for MongoDB</a> was originally published in <a href="https://medium.com/daangn">당근 기술 블로그</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>