Support to improve a specific N1QL

SQL++
1

Hi guys,

I’m facing a problem with how to create a proper index for a specific N1QL that I have. I already created a composite index, alternative index, and also a single index, and when I run this N1QL in my database it takes 2 min.
Today I have a server on AWS with 3 machines r5.2xlarge (64GB RAM each one) and my bucket has 6.7 mi documents. The current setup is a couchbase 5.0.1 community, however, I created another new server with the same setup to start using couchbase 6.5.1 community and I didn’t have any improvement.

Here is the N1QL:

SELECT invoices.syncID,
       invoices.type,
       invoices.ownerName,
       invoices.entryNumber,
       invoices.date,
       invoices.dateShort,
       invoices.dueDate,
       invoices.dueDateShort,
       invoices.hasDiscount,
       invoices.ledgerId,
       invoices.matterName,
       invoices.matter,
       invoices.totalAmountInclGst,
       invoices.totalReceivedInclGst,
       invoices.totalWriteOffInclGst,
       invoices.totalOutstandingInclGst,
       invoices.amountInclGst,
       invoices.hasFixedFee,
       invoices.isDraft,
       invoices.owner,
       invoices.totalDisbursementsInclGst,
       invoices.totalAllowancesInclGst,
       invoices.totalTasksInclGst,
       matters.archived,
       firms.syncID AS firmSyncID,
       firms.name AS firmName,
       firms.email AS firmEmail,
       solicitors.syncID solicitorSyncID,
       solicitors.firstName,
       solicitors.lastName,
       solicitors.email AS solicitorEmail,
       barristerSummary.totalClerkFeeInclGst,
       barristerSummary.totalClerkFeeExclGst,
       barristerSummary.totalClerkFeeGst,
       barristerSummary.capAmount
FROM barbooks invoices  LEFT
    JOIN barbooks matters ON KEYS invoices.matter LEFT
    JOIN barbooks solicitors ON KEYS matters.solicitor LEFT
    JOIN barbooks firms ON KEYS matters.contact.firm.syncID LEFT
    JOIN barbooks barristerSummary ON KEYS "barristerSummary:" || invoices.owner
WHERE IFMISSINGORNULL(invoices.isDraft, FALSE) = FALSE
    AND invoices.bbDeleted = FALSE
    AND invoices.docVersion = 1
    AND invoices.type IN ["RegularInvoice", "InterestInvoice"]
    AND invoices.chamberId IN ['chamber:1'];

Someone could advise me on how can I achieve better performance on it?

2 
CREATE INDEX  ix1 ON barbooks( docVersion,  bbDeleted, IFMISSINGORNULL(isDraft, FALSE), chamberId) 
WHERE type IN [“RegularInvoice”, “InterestInvoice”]

If requerired USE INDEX (ix1) HINT

Also see with above index the following query any better Assume none of them are ARRAYs

SELECT iv.syncID, iv.type, iv.ownerName, iv.entryNumber, iv.date, iv.dateShort, iv.dueDate,
       iv.dueDateShort, iv.hasDiscount, iv.ledgerId, iv.matterName, iv.matter, iv.totalAmountInclGst,
       iv.totalReceivedInclGst, iv.totalWriteOffInclGst, iv.totalOutstandingInclGst, iv.amountInclGst,
       iv.hasFixedFee, iv.isDraft, iv.owner, iv.totalDisbursementsInclGst, iv.totalAllowancesInclGst,
       iv.totalTasksInclGst, matters.*, bs.*
FROM barbooks AS iv
LET  matters = (SELECT m.archived,
                       (SELECT s.syncID AS solicitorSyncID, s.firstName, s.lastName, s.email AS solicitorEmail
                        FROM barbooks AS s USE KEYS m.solicitor)[0].*,
                       (SELECT f.syncID AS firmSyncID, f.name AS firmName, s.email AS firmEmail
                        FROM barbooks AS f USE KEYS m.contact.firm.syncID)[0].*
                FROM barbooks AS m USE KEYS iv.matter
               )[0],
       bs = (SELECT bs.totalClerkFeeInclGst, bs.totalClerkFeeExclGst, bs.totalClerkFeeGst, bs.capAmount
             FROM barbooks AS bs USE KEYS "barristerSummary:" || iv.owner)[0]
WHERE IFMISSINGORNULL(iv.isDraft, FALSE) = FALSE
      AND iv.bbDeleted = FALSE
      AND iv.docVersion = 1
      AND iv.type IN ["RegularInvoice", "InterestInvoice"]
      AND iv.chamberId IN ["chamber:1"];

https://index-advisor.couchbase.com/

3

Hi @vsr1,

Thank you for your help, mate. I created the index above and now I got better performance, however, this is still taking a bit longer than I was expecting. With this index, the first N1QL was run in 23s and your N1QL suggestion in 41s. Is there another thing that I have to do to improve it?

Btw, thanks for the index advisor link. It is gonna help me a lot in future queries.

4

If you are using CE , query service only uses 4 cores. Also you have 5 level joins.

5

Got it.

All good mate. I did some testing using my API and the performance is better when we run via console. You helped me a lot. You can close this ticket.

Thank you!

6

Hi @vsr1,

Sorry for bothering you again. I’m gonna use the same post. I’m using this N1QL and the performance is fine. However, I was doing some stress tests (I’m calling the same end point 50 times in parallel), and I’ve got a problem. When I call this end-point (in which runs this N1LQ above), I got this error:

message:“An unknown N1QL error occured. This is usually related to an out-of-memory condition. Check the errors responseBody property or inspect the cluster logs for further details.”

I know this error which means that I don’t have enough memory (correct me if I’m wrong). But in my scenario, I will probably have more than 50 customers requesting it. Today I use view index and works fine, but I would like to migrate everything to N1QL. What would you suggest for me as a best practice?

Note: I changed my AWS env. Before I was using 3 machines r5.2xlarge (64GB RAM each one) . Now, I have a cluster with 4 machines r5.4xlarge (128GB). Also, even when I call this end-point 30 times I have the same error. However, in my real scenario, I will have more than 150 requests easily.

Thank you!

7

How you are calling endpoint and what version of CB
In CE each query service will use 4 cores and 4*4 = 16 concurrent queries , but can queue 4*256.
Unless you use SDK you need to distribute the requests each query service.

Above error means you don’t have ports open check the all required ports are open. Error: An unknown N1QL error occured. This is usually related to an out-of-memory condition

8

This portion is likely from libcouchbase, so you’re likely using one of the SDKs on top of it (Python, PHP, node, etc.). If it’s an unknown error, that likely means error 5000 was returned to the SDK. I’d probably recommend trying to correlate this time wise to the server log, as there is probably some more information there.