쿼리 최적화
[느린 쿼리 탐지]
잘못된 애플리케이션 설계, 부적합한 데이터모델, 부족한 하드웨어 등으로 느려질 수 있으나 쿼리 최적화 방법으로 성능 개선가능.
대부분의 애플리케이션에서 쿼리는 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
}
}