Performance Troubleshooting

SQL++
1

Hi
Is there any documentation or howtos on troubleshooting performance.
We have statements (mostly parameterised) via java SDK that typically return sub second. but periodically they will timeout after 7.5 seconds (deterrmined by client).

If a query takes say 5-7 seconds - how to track down WHY.

thanks

2

Optimize N1QL Performance Using Request Profiling , Page 341

3

Thanks.
But if I set profiling and run it in real time it will complete in less than 1ms.
But at 10pm last night there were 100 timeouts.

How do you track down cause of that ? As there is no information collected on timeout (status).

4

select * from system:completed_requests;
Will have any query that taken more than 1 sec.
Check the request that in question PhaseCounts/Phases etc. Also check query.log/indexer.log and other logs around that time and see what will be the issue.

5

Yup.
But when it times-out - all that information is not collected.
eg

[
{
“completed_requests”: {
“clientContextID”: “2c5ed051-6f2a-4fde-a13c-c40e06d49cc9”,
“elapsedTime”: “7.504030664s”,
“errorCount”: 1,
“node”: “hcb005fp01p.qvcdev.qvc.net:8091”,
“phaseOperators”: {
“authorize”: 1
},
“remoteAddr”: “10.102.22.203:49300”,
“requestId”: “47c649b4-9333-4f0c-8ac2-cfb02da0ac43”,
“requestTime”: “2019-10-31 04:54:17.603223514 -0400 EDT”,
“resultCount”: 0,
“resultSize”: 0,
“scanConsistency”: “scan_plus”,
“serviceTime”: “7.503855764s”,
“state”: “timeout”,
“statement”: “SELECT META(ice_us).id AS _ID, META(ice_us).cas AS _CAS, ice_us.* FROM ice_us WHERE (globalUserId = $1 AND name = $2) AND _class = “com.qvc.common.couchbase.model.user.collections.Collection””,
“userAgent”: “couchbase-java-client/2.7.4 (git: 2.7.4, core: 1.7.4) (Linux/3.10.0-693.2.2.el7.x86_64 amd64; Java HotSpot™ 64-Bit Server VM 1.8.0_131-b11)”,
“users”: “saveditemsjms_us_p,saveditemsjms_us_p”
}
},

============================
heres a GOOD RUN below

        "completed_requests": {
            "clientContextID": "413c4e9a-bd41-4f71-a50b-8efb89ad21d2",
            "elapsedTime": "1.124319385s",
            "errorCount": 0,
            "node": "hcb004fp01p.qvcdev.qvc.net:8091",
            "phaseCounts": {
                "fetch": 1,
                "indexScan": 1
            },
            "phaseOperators": {
                "authorize": 1,
                "fetch": 1,
                "indexScan": 1
            },
            "remoteAddr": "10.102.22.204:39254",
            "requestId": "8bea4499-5af5-4f2a-8fef-a1b0cc7b7b44",
            "requestTime": "2019-10-26 12:52:38.356118846 -0400 EDT",
            "resultCount": 1,
            "resultSize": 2761,
            "scanConsistency": "scan_plus",
            "serviceTime": "1.124169239s",
            "state": "completed",
            "statement": "SELECT META(`ice_us`).id AS _ID, META(`ice_us`).cas AS _CAS, `ice_us`.* FROM `ice_us` WHERE (`globalUserId` = $1 AND `name` = $2) AND `_class` = \"com.qvc.common.couchbase.model.user.collections.Collection\"",
            "userAgent": "couchbase-java-client/2.7.4 (git: 2.7.4, core: 1.7.4) (Linux/3.10.0-693.2.2.el7.x86_64 amd64; Java HotSpot(TM) 64-Bit Server VM 1.8.0_131-b11)",
            "users": "saveditemsjms_us_p,saveditemsjms_us_p"
        }
6

Successful one says IndexScan suppose to produce 1 document (phaseCounts. indexScan), timed out one doesn’t have anything. You are using scan_plus not sure if there any indexer or network issues at that time. Check lohs around this time 2019-10-31 04:54:17.603223514 -0400 EDT

cc @jeelan.poola

7

Yep - checked logs - cant see anything apart from some errors that are happenign all day every day … not specific at this time. Have a ticket open for that one,.