Creating the SGI faster

Couchbase Server
21

@varun.velamuri In a MDS Config : a Covered index took ~16 minutes on 184 M docs … The covered index on 11 data keys … where the entire document having ~50 keys . This covered index is non-partitioned .
There is no other activity ran while this index are being created.
I have 4 data and query nodes and 4 index and query nodes running in Pureblock storage and each node has been given 75 CPU max
The statistics graph looks like below while index creation was in progress …

index-time-nonpartitioned
index-time-nonpartitioned2385×1920 240 KB

for same index, partitioned in 3 nodes with partition by hash took : ~42 mins .
The statistics looks like below for one of the index node .

index-time-partitioned
index-time-partitioned2411×1987 231 KB

Note that the bucket after load is Uncompressed , no compaction ran and no fragmentation (because it says for Plasma index no need for fragmentation to run on bucket)

Those performance benchmark you have shared is absolutely what are we looking for because the timings has to be improved because we are talking about index on 150 B doc eventually.
IndexServiceMemQuota is 50GB in each node for 3 index nodes .

I am trying to find out where are having the bottleneck . Can you share all possible informations index-scan, index-cap , servicers what you need to look for to help further.

1 Like
22

@eldorado, Looking at the screen shots, I see 0 total indexed items and 0B average item size for non-partitioned index while I see 32.6M total indexed items and 45B average item size for partitioned index.

After the build finishes, can you compare the stats for both the indexes and see if there is any discrepancy.

23

thats is true number … for cross-check I have started rebuilding them again … and I see 0 total index items and 0 B avg item size for non-partition one . (this is captured during index build time ) .
Sorry in previous response there is typo I want to mean 4 data and query nodes and NOT 74

24

@eldorado, Please ignore my earlier comment. I missed the part that index build was in progress. I will check and come back to you with the performance benchmark for covered index.

The cluster configuration we used contains 1 KV, 1KV+n1ql, 4 KV+index nodes. Index memory quota is set to 70G. The CPU is set to 12 cores, indexed 100M docs. The index was not a covered index.

25

No worries … what I am seeing from your benchmark is that the CPU / Mem / Nodes capacity for my usecases are better than yours and technically that should perform better than your numbers … Only thing that could be difference is index on cover for 11 keys and its on 50 keys each documents . not sure if this matters most …
also I have verified CPU for 3 index nodes its : 45 total cpu max to 75 total cpu in K8s config and 50 GB RAM each node so total 150 GB RAM .
As I understand Indexing is a huge IO bound operation ? How can I check if IO is bottleneck and how can I leverage the full power of each CPU in this task …I see its 60% not utilized .
thanks

26

You can use iostat utility to measure the io utilization. In our benchmark, we see 100% disk utilisation.

I am no expert in K8s. When you say 45 total cpu, does it mean 45 cores of CPU per node? Can you try with the non-partitioned and partitioned version for the index: CREATE INDEX myindex ON bucket-1() PARTITION BY HASH(META().id) WITH {“defer_build”: true};

Also, can you share your index definitions so that we are on the same page.

27

iostat -dx 10 on the indexer node doesn’t show any util% above 3-7% . Not sure if I am interpreting right way .
I guess CPU is logical CPU in K8s world 45 logical CPU core as primary which can be increased to 75 if required by resources but I will double check system folks . I guess the 6 node Kubernetes server where it is hosted it has 48 physical CPU core with Hyper threading .
The above syntax doesn’t work unless I create PRIMARY INDEX . did you mean that ? So I tried below … Index is going on since last 15 mins

CREATE PRIMARY INDEX ALL_RATES_TEST_INDEX ON ALL_RATES WITH { "num_partition":8 }

The cover index definition was :

CREATE INDEX ALL_RATES_COVER_INDEX ON ALL_RATES(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,SOURCE_CODE,LAST_SHOP_DTTM,RATE,PROPERTY_NAME)

The partition Index definition was :

CREATE INDEX ALL_RATES_DXPPART_INDEX ON ALL_RATES(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,LAST_SHOP_DTTM,RATE,PROPERTY_NAME) PARTITION BY hash(SOURCE_CODE,GUESTS,LOS,K_PROPERTY,ROOM_PRODUCT_DESCRIPTION,ARRIVAL_DATE,VALID_FROM_DTTM,VALID_TO_DTTM,LAST_SHOP_DTTM,RATE,PROPERTY_NAME) WITH { "num_partition":3 }

below gives syntax error at PARTITION:

CREATE INDEX myindex ON ALL_RATES PARTITION BY HASH(META().id) WITH {"defer_build": true}

28

I had bucket-1(secondary-key) in the original index definition. The secondary-key was enclosed in <> which the editor ignored.

Can you try CREATE INDEX myindex ON ALL_RATES(SOURCE_CODE) PARTITION BY HASH(META().id) WITH {“defer_build”: true}.

Can you paste a sample document so that I can mimic the same schema.

29

this syntax is working fine … the challenge with deferred build is I can’t see the % of build progress …
Build is going on for last 5+ mins

This is sample document : metadata key : 1000002824310

{
  "K_PROPERTY": 350001851,
  "TC_BID": 4189,
  "PROPERTY_NAME": "SOME PROPERTY NAME WHICH IS OF THIS LENGTH",
  "SOURCE_CODE": "GDQ",
  "SOURCE_NAME": "GDS Rates (incl Qualified)",
  "LOS": 1,
  "GUESTS": 1,
  "ARRIVAL_DATE": "2018-08-12",
  "CURRENCY_CODE": "USD",
  "CURRENCY_DATE": "2018-08-12",
  "RATE_DESC_MD5": "340505171841EA222F499DDF1AF4E35C",
  "RATE_DESC": "A1KRAC-BEST AVAILABLE INCLUDES BREAKFAST STANDARD KING BED NON SMOKING ROOM - CANCELLATION POLICY: CANCEL BEFORE (TIME) - GUARANTEE POLICY: CREDIT CARD GUARANTEE REQUIRED",
  "VALID_FROM_DTTM": "2018-08-12 11:25:04",
  "VALID_TO_DTTM": "3999-12-31 12:00:00",
  "SHOP_STATUS": "S",
  "LINE_STATUS": "S",
  "LAST_SHOP_DTTM": "2018-08-12 11:25:04",
  "IS_CURRENT": "Y",
  "RATE": 107,
  "STRIKE_RATE": 0,
  "TAX_INCLUSIVE": "N",
  "ERROR_DTTM": null,
  "ROOM_PRODUCT_DESCRIPTION": "Standard",
  "EXTRA_TEXT_MD5": "5B8832B50151CE105400EADDD5E5D48E",
  "DAILY_RATE_TEXT": null,
  "RATE_DESC_BIT_FLAGS": "201327624",
  "IS_GOVT": "N",
  "IS_AUTO": "N",
  "IS_SENIOR": "N",
  "IS_CORP": "N",
  "IS_OTHER_QUAL": "N",
  "IS_UNQUALIFIED": "Y",
  "IS_RESTRICTED": "N",
  "IS_RESTR_ADVCANCEL": "N",
  "IS_RESTR_ADVPURCH": "N",
  "IS_RESTR_DEPREQ": "N",
  "IS_RESTR_NONREFUND": "N",
  "IS_PACKAGE": "N",
  "HAS_WIFI": "N",
  "HAS_MEALS_AND_BEV": "Y",
  "HAS_PARKING": "N",
  "HAS_PARKFLY": "N",
  "HAS_OTHER": "N",
  "HAS_GIFTS": "N",
  "SOURCE_ROOM_TYPE": "UNKNOWN",
  "LOWEST_RATE_IND": "N"
}
30

Also I have the bucket un-Compressed and total bucket size 755 GB … does that matter ?
Also wonder what is : indexer.numSliceWriters=3
are you aware of …

31

Sorry for not making this explicitly clear earlier. After create index, you also need to do BUILD INDEX on bucket-1(myindex). Create index with {“defer_build”:true} will only create the index metadata. I will not process mutations.

32

Well… new to me … yes I am doing so … started building I see the progress %'s …

33

index.numSliceWriters represents the number of threads that will be writing to disk (per partition). By default, this will be the number of cores that indexer run-time can use. Unless you have changed this setting explicitly, this seems too less. Can you paste the results of curl -u Administrator:asdasd http://localhost:9102/settings from each of the indexer nodes

34

now its challenge because as stated in other thread in my K8s node I don’t see any ports open corresponds to 9102 because for kubernetes all I can see is WebAdmin ports and 8091 … 8096 plus 11210 … all outside is what I can access from http URL or through curl

35

Ok. Can you share the indexer.log from all the indexer nodes? The indexer.log can be found at /opt/couchbase/var/lib/couchbase/logs location on each of the indexer node

36

indexer.zip (3.0 MB)

I have uploaded it … note that this log while index is building
the last index you have asked to build is still in progress … 15 minutes for 60% progress

37

The num_cpu_core is set to 48 but the CPU utilisation does not go beyond 1000%. Given that your disk utilisation is only 3-5%, this might be the bottleneck. The max_cpu_percent is ‘0’ which means indexer can use all cores available. This means that indexer.numSliceWriters should ideally be 48. From where did you see the “indexer.numSliceWriters=3” setting. We need to change it.

Can you ssh into the indexer node and try accessing port 9102. We need access to this port to change the settings.

38

And finally the index built in ~24 mins :

CREATE INDEX myindex ON ALL_RATES(SOURCE_CODE) PARTITION BY hash((meta().id)) WITH { "defer_build":true, "num_partition":8 }

39

Ohh my bad I just copy paste : indexer.numSliceWriters=3 from : https://issues.couchbase.com/browse/MB-29132
It was an example and not the real settings … I am interested to know what it is for my setup … let me see if I can access 9102 somehow from inside box and what ports are open there

40

there you go … I got it …

root@cb-cluster-0004:/opt/couchbase/bin# curl -u Administrator:password http://10.42.7.88:9102/settings | jq
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 2909 0 2909 0 0 710k 0 --:–:-- --:–:-- --:–:-- 946k
{
“indexer.settings.allow_large_keys”: true,
“indexer.settings.bufferPoolBlockSize”: 16384,
“indexer.settings.build.batch_size”: 5,
“indexer.settings.compaction.abort_exceed_interval”: false,
“indexer.settings.compaction.check_period”: 30,
“indexer.settings.compaction.compaction_mode”: “circular”,
“indexer.settings.compaction.days_of_week”: “Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday”,
“indexer.settings.compaction.interval”: “00:00,00:00”,
“indexer.settings.compaction.min_frag”: 30,
“indexer.settings.compaction.min_size”: 524288000,
“indexer.settings.compaction.plasma.manual”: false,
“indexer.settings.compaction.plasma.optional.decrement”: 5,
“indexer.settings.compaction.plasma.optional.min_frag”: 20,
“indexer.settings.compaction.plasma.optional.quota”: 25,
“indexer.settings.corrupt_index_num_backups”: 1,
“indexer.settings.cpuProfFname”: “”,
“indexer.settings.cpuProfile”: false,
“indexer.settings.enable_corrupt_index_backup”: false,
“indexer.settings.fast_flush_mode”: true,
“indexer.settings.gc_percent”: 100,
“indexer.settings.inmemory_snapshot.fdb.interval”: 200,
“indexer.settings.inmemory_snapshot.interval”: 200,
“indexer.settings.inmemory_snapshot.moi.interval”: 20,
“indexer.settings.largeSnapshotThreshold”: 200,
“indexer.settings.log_level”: “info”,
“indexer.settings.maxVbQueueLength”: 0,
“indexer.settings.max_array_seckey_size”: 10240,
“indexer.settings.max_cpu_percent”: 0,
“indexer.settings.max_seckey_size”: 4608,
“indexer.settings.max_writer_lock_prob”: 20,
“indexer.settings.memProfFname”: “”,
“indexer.settings.memProfile”: false,
“indexer.settings.memory_quota”: 53687091200,
“indexer.settings.minVbQueueLength”: 250,
“indexer.settings.moi.debug”: false,
“indexer.settings.moi.persistence_threads”: 24,
“indexer.settings.moi.recovery.max_rollbacks”: 2,
“indexer.settings.moi.recovery_threads”: 48,
“indexer.settings.num_replica”: 0,
“indexer.settings.persisted_snapshot.fdb.interval”: 5000,
“indexer.settings.persisted_snapshot.interval”: 5000,
“indexer.settings.persisted_snapshot.moi.interval”: 600000,
“indexer.settings.persisted_snapshot_init_build.fdb.interval”: 5000,
“indexer.settings.persisted_snapshot_init_build.interval”: 5000,
“indexer.settings.persisted_snapshot_init_build.moi.interval”: 600000,
“indexer.settings.plasma.recovery.max_rollbacks”: 2,
“indexer.settings.recovery.max_rollbacks”: 2,
“indexer.settings.scan_getseqnos_retries”: 30,
“indexer.settings.scan_timeout”: 120000,
“indexer.settings.send_buffer_size”: 1024,
“indexer.settings.sliceBufSize”: 50000,
“indexer.settings.smallSnapshotThreshold”: 30,
“indexer.settings.statsLogDumpInterval”: 60,
“indexer.settings.storage_mode”: “plasma”,
“indexer.settings.storage_mode.disable_upgrade”: false,
“indexer.settings.wal_size”: 4096,
“projector.settings.log_level”: “info”,
“queryport.client.settings.backfillLimit”: 5120,
“queryport.client.settings.minPoolSizeWM”: 1000,
“queryport.client.settings.poolOverflow”: 30,
“queryport.client.settings.poolSize”: 5000,
“queryport.client.settings.relConnBatchSize”: 100
}