몽고 디비 인 액션 8장-2

쿼리 최적화

[느린 쿼리 탐지]

잘못된 애플리케이션 설계, 부적합한 데이터모델, 부족한 하드웨어 등으로 느려질 수 있으나 쿼리 최적화 방법으로 성능 개선가능.

대부분의 애플리케이션에서 쿼리는 100ms 이내에 실행되어야 안전한다.

//v2.6 log
Mon Sep 30 21:48:58.066 [conn20] query stocks.values query: { query: {
stock_symbol: "GOOG" }, orderby: { date: -1.0 } }
 ntoreturn:1 ntoskip:0 nscanned:4308303 scanAndOrder:1 keyUpdates:0
numYields: 3 locks(micros) r:4399440
 nreturned:1 reslen:194 4011ms

//v3.0 log
2015-09-11T21:17:15.414+0300 I COMMAND [conn99] command green.$cmd command:
insert { insert: "system.indexes", documents: [ { _id:
ObjectId('55f31aab9a50479be0a7dcd7'), ns: "green.users", key: {
addresses.zip: 1.0 }, name: "zip" } ], ordered: false } keyUpdates:0
writeConflicts:0 numYields:0 reslen:40 locks:{ Global: { acquireCount: { r:
1, w: 1 } }, MMAPV1Journal: { acquireCount: { w: 9 } }, Database: {
acquireCount: { W: 1 } }, Collection: { acquireCount: { W: 1 } }, Metadata: {
acquireCount: { W: 5 } } } 102ms

느린 쿼리 경고 메시지 : stocks.values에 대한 쿼리, 정렬이 수행, 실행하는데 4초 걸림

grep -E '[0-9]+ms' mongod.log

해당 경고 로그를 grep하는 명령어

프로파일러 사용

//디폴트로 사용 불가능인 상태이므로 상태 가능으로 변경 <-> 불가능은 레벨0
//특정 데이터베이스 선택 및 프로파일링 수준 지정 (읽기, 쓰기를 로그 기록)
use stocks
db.setProfilingLevel(2)

//150밀리초 이상이 소요된 쿼리 조회
db.system.profile.find({millis: {$gt: 150}})
db.system.profile.find().sort({$natural: -1}).limit(5).pretty()

// 출력 결과
{
 "op" : "query",
 "ns" : "stocks.values",  //컬렉션의 이름
 "query" : {
 "query" : { },
 "orderby" : {
 "close" : -1
 }
 },
 "ntoreturn" : 1,
 "ntoskip" : 0,
 "nscanned" : 4308303, //스캔된 도큐먼트 수
 "scanAndOrder" : true,
 "keyUpdates" : 0,
 "numYield" : 3,
 "lockStats" : {
 "timeLockedMicros" : {
 "r" : NumberLong(12868747),
 "w" : NumberLong(0)
 },
"timeAcquiringMicros" : {
 "r" : NumberLong(1838271),
 "w" : NumberLong(5)
 }
 },
 "nreturned" : 1, //반환된 도큐먼트 수
 "responseLength" : 194,
 "millis" : 11030, //응답속도 밀리초 단위
 "ts" : ISODate("2013-09-30T06:44:40.988Z"),
 "client" : "127.0.0.1",
 "allUsers" : [ ],
 "user" : ""
}

느린 쿼리 분석 : 인덱스 추가, 인덱스 재구성 및 데이터 모델 수정

// explain 사용
db.values.find({}).sort({close: -1}).limit(1).explain()
{
 "cursor" : "BasicCursor", //인덱스를 사용했다면 BtreeCursor
 "isMultiKey" : false,
 "n" : 1, //반환된 수
 "nscannedObjects" : 4308303,
 "nscanned" : 4308303, //스캔된 수
 "nscannedObjectsAllPlans" : 4308303,
 "nscannedAllPlans" : 4308303,
 "scanAndOrder" : true,
 "indexOnly" : false,
 "nYields" : 4,
 "nChunkSkips" : 0,
 "millis" : 10927, // 11초 소요
 "indexBounds" : { },
 "server" : "localhost:27017"
}
//values 컬렉션 count
db.values.count()
4308303

//컬렉션 전체 스캔, n과 nscanned가 거의 비슷한 값을 가지는게 이상적

//v3.0 샘플
> db.inventory.find({}).sort({"quantity": -1}).limit(1).
explain("executionStats")
{
 "queryPlanner" : {
 "plannerVersion" : 1,
 "namespace" : "tutorial.inventory",
 "indexFilterSet" : false,
 "parsedQuery" : {
 "$and" : [ ]
 },
 "winningPlan" : {
 "stage" : "SORT",
 "sortPattern" : {
 "quantity" : -1
 },
 "limitAmount" : 1,
 "inputStage" : {
 "stage" : "COLLSCAN",
 "filter" : {
 "$and" : [ ]
 },
 "direction" : "forward"
 }
 },
 "rejectedPlans" : [ ]
 },
 "executionStats" : {
 "executionSuccess" : true,
 "nReturned" : 1,
 "executionTimeMillis" : 0,
 "totalKeysExamined" : 0,
 "totalDocsExamined" : 11,
 "executionStages" : {
 "stage" : "SORT",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 16,
 "advanced" : 1,
 "needTime" : 13,
 "needFetch" : 0,
"saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "sortPattern" : {
 "quantity" : -1
 },
 "memUsage" : 72,
 "memLimit" : 33554432,
 "limitAmount" : 1,
 "inputStage" : {
 "stage" : "COLLSCAN",
 "filter" : {
 "$and" : [ ]
 },
 "nReturned" : 11,
 "executionTimeMillisEstimate" : 0,
 "works" : 13,
 "advanced" : 11,
 "needTime" : 1,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "direction" : "forward",
 "docsExamined" : 11
 }
 }
 },
 "serverInfo" : {
 "host" : "rMacBook.local",
 "port" : 27017,
 "version" : "3.0.6",
 "gitVersion" : "nogitversion"
 },
 "ok" : 1
}

인덱스 추가 후 재시도

//인덱스 생성
db.values.createIndex({close: 1})

//explain 재실행
db.values.find({}).sort({close: -1}).limit(1).explain()
{
 "cursor" : "BtreeCursor close_1 reverse",
 "isMultiKey" : false, 
"n" : 1,
 "nscannedObjects" : 1,
 "nscanned" : 1,
 "nscannedObjectsAllPlans" : 1,
 "nscannedAllPlans" : 1,
 "scanAndOrder" : false,
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 0, //1밀리초도 안걸림
 "indexBounds" : { //쿼리가 전체 인덱스에 대한 것임을 나타냄
 "name" : [
 [
 {
 "$maxElement" : 1
 },
 {
 "$minElement" : 1
 }
 ]
 ]
 },
 "server" : "localhost:27017"
}

인덱싱된 키 사용

//500보다 큰 종가에 대한 쿼리 explain
> db.values.find({close: {$gt: 500}}).explain()
{
 "cursor" : "BtreeCursor close_1",
 "isMultiKey" : false,
 "n" : 309,
 "nscannedObjects" : 309,
 "nscanned" : 309,
 "nscannedObjectsAllPlans" : 309,
 "nscannedAllPlans" : 309,
 "scanAndOrder" : false, 
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 1,
 "indexBounds" : { //하한선은 500 , 상한선은 무한대
 "close" : [
 [
 500,
 1.7976931348623157e+308
 ]
 ]
 },
 "server" : "localhost:27017"
}

MongoDB 쿼리 옵티마이저 : 해당 쿼리를 가장 효율적으로 실행하기 위해 어떤 인덱스를 사용할지 결정하는 소프트웨어
- scanAndOrder 를 피한다. 쿼리가 정렬을 포함하고 있으면 인덱스를 사용한 정렬 시도
- 유용한 인덱스 제한 조건으로 모든 필드를 만족시킨다. 쿼리 셀렉터에 지정된 필드에 대한 인덱스를 최대한 사용
- 쿼리가 범위를 내포하거나 정렬을 포함하면 마지막 키에 대한 범위나 정렬에 도움이 되는 인덱스를 선택

//GOOG에 대해 200이 넘는 종가 조회
db.values.find({stock_symbol: "GOOG", close: {$gt: 200}})
//두 개의 인덱스 && close키가 마지막
db.values.createIndex({stock_symbol: 1, close: 1})

db.values.find({stock_symbol: "GOOG", close: {$gt: 200}}).explain()
{
 "cursor" : "BtreeCursor stock_symbol_1_close_1",
 "isMultiKey" : false,
 "n" : 730,
 "nscannedObjects" : 730,
 "nscanned" : 730,
 "nscannedObjectsAllPlans" : 730,
 "nscannedAllPlans" : 730,
 "scanAndOrder" : false,
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 2,
 "indexBounds" : {
 "stock_symbol" : [
 [
 "GOOG",
 "GOOG"
 ]
 ],
 "close" : [
 [
 200,
 1.7976931348623157e+308
 ]
 ]
 },
 "server" : "localhost:27017"
}
// 인덱스를 나용하기 위해 축약된 getIndexKeys() 사용
db.values.getIndexKeys()
[
 {
 "_id" : 1
 },
 {
 "close" : 1
 },
 {
 "stock_symbol" : 1
 }
]

> db.inventory.find({"quantity": 500,
"type":"toys"}).limit(1).explain("executionStats")
{
 "queryPlanner" : {
 "plannerVersion" : 1,
 "namespace" : "tutorial.inventory",
 "indexFilterSet" : false,
 "parsedQuery" : {
 "$and" : [
 {
 "quantity" : {
 "$eq" : 500
 }
 },
{
 "type" : {
 "$eq" : "toys"
 }
 }
 ]
 },
 "winningPlan" : {
 "stage" : "LIMIT",
 "limitAmount" : 0,
 "inputStage" : {
 "stage" : "KEEP_MUTATIONS",
 "inputStage" : {
 "stage" : "FETCH",
 "filter" : {
 "type" : {
 "$eq" : "toys"
 }
 },
 "inputStage" : {
 "stage" : "IXSCAN",
 "keyPattern" : {
 "quantity" : 1
 },
 "indexName" : "quantity_1",
 "isMultiKey" : false,
 "direction" : "forward",
 "indexBounds" : {
 "quantity" : [
 "[500.0, 500.0]"
 ]
 }
 }
 }
 }
 },
 "rejectedPlans" : [ ]
 },
 "executionStats" : {
 "executionSuccess" : true,
 "nReturned" : 1,
 "executionTimeMillis" : 1,
 "totalKeysExamined" : 2,
 "totalDocsExamined" : 2,
 "executionStages" : {
 "stage" : "LIMIT",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 3,
 "advanced" : 1,
 "needTime" : 1,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
"invalidates" : 0,
 "limitAmount" : 0,
 "inputStage" : {
 "stage" : "KEEP_MUTATIONS",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 2,
 "advanced" : 1,
 "needTime" : 1,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 0,
 "invalidates" : 0,
 "inputStage" : {
 "stage" : "FETCH",
 "filter" : {
 "type" : {
 "$eq" : "toys"
 }
 },
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 2,
 "advanced" : 1,
 "needTime" : 1,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "docsExamined" : 2,
 "alreadyHasObj" : 0,
 "inputStage" : {
 "stage" : "IXSCAN",
 "nReturned" : 2,
 "executionTimeMillisEstimate" : 0,
 "works" : 2,
 "advanced" : 2,
 "needTime" : 0,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "keyPattern" : {
 "quantity" : 1
 },
 "indexName" : "quantity_1",
 "isMultiKey" : false,
 "direction" : "forward",
 "indexBounds" : {
 "quantity" : [
 "[500.0, 500.0]"
 ]
},
 "keysExamined" : 2,
 "dupsTested" : 0,
 "dupsDropped" : 0,
 "seenInvalidated" : 0,
 "matchTested" : 0
 }
 }
 }
 }
 },
 "serverInfo" : {
 "host" : "rMacBook.local",
 "port" : 27017,
 "version" : "3.0.6",
 "gitVersion" : "nogitversion"
 },
 "ok" : 1
}

//인덱스 추가
> db.inventory.createIndex( { quantity: 1, type: 1 } )
{
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
}

> db.inventory.find({"quantity": 500,
"type":"toys"}).limit(1).explain("executionStats")
{
 "queryPlanner" : {
 "plannerVersion" : 1,
 "namespace" : "tutorial.inventory",
 "indexFilterSet" : false,
 "parsedQuery" : {
 "$and" : [
 {
 "quantity" : {
 "$eq" : 500
 }
 },
 {
 "type" : {
 "$eq" : "toys"
 }
 }
 ]
 },
"winningPlan" : {
 "stage" : "LIMIT",
 "limitAmount" : 0,
 "inputStage" : {
 "stage" : "FETCH",
 "inputStage" : {
 "stage" : "IXSCAN",
 "keyPattern" : {
 "quantity" : 1,
 "type" : 1
 },
 "indexName" : "quantity_1_type_1",
 "isMultiKey" : false,
 "direction" : "forward",
 "indexBounds" : {
 "quantity" : [
 "[500.0, 500.0]"
 ],
 "type" : [
 "[\"toys\", \"toys\"]"
 ]
 }
 }
 }
 },
 "rejectedPlans" : [
 {
 "stage" : "LIMIT",
 "limitAmount" : 1,
 "inputStage" : {
 "stage" : "KEEP_MUTATIONS",
 "inputStage" : {
 "stage" : "FETCH",
 "filter" : {
 "type" : {
 "$eq" : "toys"
 }
 },
 "inputStage" : {
 "stage" : "IXSCAN",
 "keyPattern" : {
 "quantity" : 1
 },
 "indexName" : "quantity_1",
 "isMultiKey" : false,
 "direction" : "forward",
 "indexBounds" : {
 "quantity" : [
 "[500.0, 500.0]"
 ]
 }
 }
 }
 }
 }
]
 },
 "executionStats" : {
 "executionSuccess" : true,
 "nReturned" : 1,
 "executionTimeMillis" : 1,
 "totalKeysExamined" : 1,
 "totalDocsExamined" : 1,
 "executionStages" : {
 "stage" : "LIMIT",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 2,
 "advanced" : 1,
 "needTime" : 0,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "limitAmount" : 0,
 "inputStage" : {
 "stage" : "FETCH",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 1,
 "advanced" : 1,
 "needTime" : 0,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "docsExamined" : 1,
 "alreadyHasObj" : 0,
 "inputStage" : {
 "stage" : "IXSCAN",
 "nReturned" : 1,
 "executionTimeMillisEstimate" : 0,
 "works" : 1,
 "advanced" : 1,
 "needTime" : 0,
 "needFetch" : 0,
 "saveState" : 0,
 "restoreState" : 0,
 "isEOF" : 1,
 "invalidates" : 0,
 "keyPattern" : {
 "quantity" : 1,
 "type" : 1
 },
 "indexName" : "quantity_1_type_1",
 "isMultiKey" : false,
 "direction" : "forward",
 "indexBounds" : {
"quantity" : [
 "[500.0, 500.0]"
 ],
 "type" : [
 "[\"toys\", \"toys\"]"
 ]
 },
 "keysExamined" : 1,
 "dupsTested" : 0,
 "dupsDropped" : 0,
 "seenInvalidated" : 0,
 "matchTested" : 0
 }
 }
 }
 },
 "serverInfo" : {
 "host" : "rMacBook.local",
 "port" : 27017,
 "version" : "3.0.6",
 "gitVersion" : "nogitversion"
 },
 "ok" : 1
}

쿼리 플랜과 HINT() 보여주기

//기존 복합 인덱스 삭제 후 개별 인텍스 생성
db.values.dropIndex("stock_symbol_1_close_1")
db.values.createIndex({stock_symbol: 1})
db.values.createIndex ({close: 1})

// true를 전달하는 것은 쿼리 옵티마이저가 시도하는 플랜의 리스트 포함
db.values.find({stock_symbol: "GOOG", close: {$gt: 200}}).explain(true)
{
 "cursor" : "BtreeCursor stock_symbol_1",
 "isMultiKey" : false,
 "n" : 730,
 "nscannedObjects" : 894,
 "nscanned" : 894, //스캔한 도큐먼트 수
 "nscannedObjectsAllPlans" : 1097,
 "nscannedAllPlans" : 1097,
 "scanAndOrder" : false,
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 4, //쿼리 수행 시간
 "indexBounds" : {
 "stock_symbol" : [[ // 일치함(equality)에 대한 질의
 "GOOG", //동일한 인덱스 범위
 "GOOG"
 ]
 ]
 },
 "allPlans" : [ //시도한 쿼리 플랜의 배열
 {
 "cursor" : "BtreeCursor close_1", 
 "n" : 0,
 "nscannedObjects" : 102,
 "nscanned" : 102,
 "indexBounds" : {
 "close" : [
 [
 200,
 1.7976931348623157e+308
 ]
 ]
 }
 },
 {
 "cursor" : "BtreeCursor stock_symbol_1",
 "n" : 730,
 "nscannedObjects" : 894,
 "nscanned" : 894,
 "indexBounds" : {
 "stock_symbol" : [
 [
 "GOOG",
 "GOOG"
 ]
 ]
 }
 },
 {
 "cursor" : "BasicCursor", //BasicCursor를 사용한 컬렉션 스캔
 "n" : 0,
 "nscannedObjects" : 101,
 "nscanned" : 101,
 "indexBounds" : { }
 }
 ],
 "server" : "localhost:27017"
}

//close 인덱스를 사용하지 않는 이유를 알기위해 hint 사용, hint()는 쿼리 옵티마이저로 하여금 강제로 특정 인덱스를 사용하도록 만듦
query = {stock_symbol: "GOOG", close: {$gt: 200}}
db.values.find(query).hint({close: 1}).explain()
{
 "cursor" : "BtreeCursor close_1",
 "isMultiKey" : false,
 "n" : 730,
 "nscannedObjects" : 5299,
 "nscanned" : 5299, //스캔되는 값이 확연히 많음 왜?????
 "nscannedObjectsAllPlans" : 5299,
 "nscannedAllPlans" : 5299,
 "scanAndOrder" : false,
 "indexOnly" : false,
 "nYields" : 0,
 "nChunkSkips" : 0,
 "millis" : 22,
 "indexBounds" : {
 "close" : [
 [
 200,
 1.7976931348623157e+308
 ]
 ]
 },
 "server" : "localhost:27017"
}

쿼리 플랜 캐시 : 옵티마이저가 어떻게 캐시를 하고 선택한 쿼리 플랜의 사효를 만료하는가의 내용.

{
 pattern: {
 stock_symbol: 'equality',
 close: 'bound',
index: {
 stock_symbol: 1
 },
 nscanned: 894
 }
}

Leave a Comment Cancel reply