<div id="readability-page-1" class="page"><div><div><h2 id="a03a">Chapter 1. Query Response Time</h2><div><a rel="noopener follow" href="/@garimoo?source=post_page-----5cc3ee54c45b--------------------------------"><div aria-hidden="false"><p><img alt="GARIMOO" src="https://miro.medium.com/v2/resize:fill:88:88/1*SYHVtZBzKmV2d85kqKKmUA@2x.jpeg" width="44" height="44" loading="lazy"></p></div></a><a href="https://medium.com/garimoo?source=post_page-----5cc3ee54c45b--------------------------------" rel="noopener follow"><div aria-hidden="false"><p><img alt="garimoo" src="https://miro.medium.com/v2/resize:fill:48:48/1*ZDgCLPD-Ss-eAKs5PZxOtQ.png" width="24" height="24" loading="lazy"></p></div></a></div></div><figure></figure><ul><li id="5d46">performance is query response time<br> → MySQL에서 쿼리를 실행하는데 걸리는 시간을 의미<br> → response time, query time, execution time 등으로 나타낼 수 있음</li></ul><h2 id="edd2">Query Reporting</h2><p id="9b9a">원천 데이터인 쿼리 메트릭을 유의미한 쿼리 리포트로 변경하는 과정</p><ul><li id="746b">source: slow log</li><li id="1ead">aggregation: SQL → digest text → digest hash</li><li id="a69c">reporting</li></ul><h2 id="7210">Query profile</h2><ul><li id="1d60">Total query time: 각 쿼리별 수행 시간<br> → adds up all the time MySQL spends executing each query</li><li id="e84c">Percentage execution time<br> → total query time(per query) / total execution time (all queries)</li><li id="fac8">Query load<br> → total query time (per query) / clock time (time range)<br> → Query load greater than 1.0 indicates query concurrency</li></ul><h2 id="ef9c">Query Metrics</h2><h2 id="efb7">Query Time</h2><ul><li id="4861">쿼리가 수행된 시간, lock time 을 포함하는 메트릭</li><li id="fb69">MySQL에서 모든 작업은 계층적이기 때문에 각 작업에 소요된 시간을 알 수 있음</li></ul><ol><li id="b9a4">Transactions : top level event</li><li id="f48b">statements: queries, to which query metrics apply</li><li id="fd00">Stages: parsing, opening a table, file sort 등의 각 프로세스</li><li id="fa50">Waits: events that take time</li></ol><h2 id="f897">Lock Time</h2><p id="f22b">The time spent acquiring locks during query execution</p><ul><li id="61d2">MySQL 서버는 table lock / metadata lock 관리</li><li id="c176">스토리지 엔진은 row level lock 관리</li></ul><blockquote><p id="c97d"><strong>Lock time from the Performance Schema does not include row lock waits</strong>, only table and metadata lock waits. By contrast, <strong>lock time from the slow query log includes all locks waits</strong>: metadata, table, and row.</p></blockquote><ul><li id="ef82">nonlocking != nonblocking<br> → SELECT은 shared metadata lock을 잡으며, 이는 blocking을 유발할 수 있음</li></ul><pre><span id="6988">[Note]<p>*   Lock time can be significantly greater than innodb_lock_wait_timeout because this system variable applies to each row lock.<br>*   Locking and transaction isolation levels are related.<br>*   InnoDB locks every row it accesses including rows it does not write.<br>*   Locks are released on transaction commit or rollback, and sometimes during query execution.<br>*   InnoDB has different types of locks: record, gap, next-key, and more.</p></span></pre><figure></figure><ul><li id="7df0">Performance Schema에서 확인할 수 있는 lock time에는 1,2 번이 포함</li><li id="c8d3">slow log에서 확인할 수 있는 lock time에는 1,2,3,5,7 번이 포함</li></ul><h2 id="7507">Rows examined</h2><p id="e262">The number of rows that MySQL accessed to find matching rows (not counting any processing internal to storage engines)</p><h2 id="2f1d">Rows sent</h2><p id="630e">The number of rows sent to the client</p><ul><li id="5cf8">rows sent &lt; rows examined → poor query or index selectivity</li><li id="74db">rows sent == rows examined → ideal case</li><li id="f7ca">rows sent &gt; rows examined → 가능한 경우도 있지만 드문 케이스</li></ul><h2 id="6a7f">Rows affected</h2><p id="392d">The number of rows inserted, updated, or deleted</p><ul><li id="ff58">영향을 받은 행 수가 너무 큰 경우 쿼리가 잘못된 것이라고 판단할 수 있음</li><li id="0d9c">배치 크기가 너무 큰 경우 이 값이 크게 나타날 수 있는데, 너무 큰 배치 사이즈는 복제지연, history list length, lock time 등 전반적인 퍼포먼스에 영향을 줄 수 있어서 주의해야 함</li></ul><h2 id="9886">Select scan</h2><p id="d9d8">The number of full table scan on the first table accessed</p><ul><li id="6fa6">인덱스를 타지 않고 full scan 하는 것을 의미</li><li id="1f65">이 값이 0이 아니면 쿼리를 변경해야 함</li></ul><h2 id="38ca">Select full join</h2><p id="7fcb">The number of full table scans on tables joined</p><ul><li id="3bab">join할 때 인덱스를 타지 않는다는 것을 의미<br> → EXPLAIN 했을 때 type: ALL</li><li id="a35a">Select full join is worse than select scan because the number of full joins that occur on a table during query execution is equal to the produt of rows from the preceding tables</li></ul><h2 id="da49">Created tmp disk tables</h2><p id="4d57">The number of temporary tables created on disk</p><ul><li id="8e87">in-memory temptable 보다 커지면 disk에 쓰는데, disk 속도가 메모리보다 훨씬 느리기 때문에 문제가 될 수 있음</li></ul><h2 id="694f">Query Analysis</h2><ul><li id="baf7">Average is overly optimistic</li><li id="0a56">Percentile is an assumption</li><li id="0b4a"><strong>Maximum is the best representation</strong></li></ul><h2 id="e86b">Improving Query Response Time</h2><ul><li id="753f">Direct Query Optimization<br> → <a href="https://dev.mysql.com/doc/refman/8.1/en/select-optimization.html" rel="noopener ugc nofollow" target="_blank">“Optimizing SELECT Statements”</a> 내용처럼 쿼리를 수정하는 방법</li><li id="d73b">Indirect Query Optimization<br> → data와 access pattern을 바꾸는 방법<br> → reducing data size improves query response time</li></ul></div></div>

Stories by GARIMOO on Medium

Chapter 1. Query Response Time
performance is query response time
 → MySQL에서 쿼리를 실행하는데 걸리는 시간을 의미
 → response time, query time, execution time 등으로 나타낼 수 있음
Query Reporting
원천 데이터인 쿼리 메트릭을 유의미한 쿼리 리포트로 변경하는 과정
source: slow log
aggregation: SQL → digest text → digest hash
reporting
Query profile
Total query time: 각 쿼리별 수행 시간
 → adds up all the time MySQL spends executing each query
Percentage execution time
 → total query time(per query) / total execution time (all queries)
Query load
 → total query time (per query) / clock time (time range)
 → Query load greater than 1.0 indicates query concurrency
Query Metrics
Query Time
쿼리가 수행된 시간, lock time 을 포함하는 메트릭
MySQL에서 모든 작업은 계층적이기 때문에 각 작업에 소요된 시간을 알 수 있음
Transactions : top level event
statements: queries, to which query metrics apply
Stages: parsing, opening a table, file sort 등의 각 프로세스
Waits: events that take time
Lock Time
The time spent acquiring locks during query execution
MySQL 서버는 table lock / metadata lock 관리
스토리지 엔진은 row level lock 관리
Lock time from the Performance Schema does not include row lock waits, only table and metadata lock waits. By contrast, lock time from the slow query log includes all locks waits: metadata, table, and row.
nonlocking != nonblocking
 → SELECT은 shared metadata lock을 잡으며, 이는 blocking을 유발할 수 있음
[Note]
*   Lock time can be significantly greater than innodb_lock_wait_timeout because this system variable applies to each row lock.
*   Locking and transaction isolation levels are related.
*   InnoDB locks every row it accesses including rows it does not write.
*   Locks are released on transaction commit or rollback, and sometimes during query execution.
*   InnoDB has different types of locks: record, gap, next-key, and more.
Performance Schema에서 확인할 수 있는 lock time에는 1,2 번이 포함
slow log에서 확인할 수 있는 lock time에는 1,2,3,5,7 번이 포함
Rows examined
The number of rows that MySQL accessed to find matching rows (not counting any processing internal to storage engines)
Rows sent
The number of rows sent to the client
rows sent < rows examined → poor query or index selectivity
rows sent == rows examined → ideal case
rows sent > rows examined → 가능한 경우도 있지만 드문 케이스
Rows affected
The number of rows inserted, updated, or deleted
영향을 받은 행 수가 너무 큰 경우 쿼리가 잘못된 것이라고 판단할 수 있음
배치 크기가 너무 큰 경우 이 값이 크게 나타날 수 있는데, 너무 큰 배치 사이즈는 복제지연, history list length, lock time 등 전반적인 퍼포먼스에 영향을 줄 수 있어서 주의해야 함
Select scan
The number of full table scan on the first table accessed
인덱스를 타지 않고 full scan 하는 것을 의미
이 값이 0이 아니면 쿼리를 변경해야 함
Select full join
The number of full table scans on tables joined
join할 때 인덱스를 타지 않는다는 것을 의미
 → EXPLAIN 했을 때 type: ALL
Select full join is worse than select scan because the number of full joins that occur on a table during query execution is equal to the produt of rows from the preceding tables
Created tmp disk tables
The number of temporary tables created on disk
in-memory temptable 보다 커지면 disk에 쓰는데, disk 속도가 메모리보다 훨씬 느리기 때문에 문제가 될 수 있음
Query Analysis
Average is overly optimistic
Percentile is an assumption
Maximum is the best representation
Improving Query Response Time
Direct Query Optimization
 → “Optimizing SELECT Statements” 내용처럼 쿼리를 수정하는 방법
Indirect Query Optimization
 → data와 access pattern을 바꾸는 방법
 → reducing data size improves query response time
Efficient MySQL Performance 정리 (1) was originally published in garimoo on Medium, where people are continuing the conversation by highlighting and responding to this story.

Efficient MySQL Performance 정리 (1)

댓글