I have so many sindex in my aerospike, and I think I have unused sindex how to check the unused sindex from aerospike using aql or asadm
Hey there, I’d recommend looking over this article about managing the secondary indexes Managing Indexes | Aerospike Documentation
i saw this article but not exists that i want it this article have only create / list / delete / statistic
example on mysql i can find the unused index from this table https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema-table-io-waits-summary-by-index-usage-table.html
I don’t think there is anything out of the box to show the specific usage on a ‘per sindex’ basis. Depending on the volume of transactions, the query tracker may help (only for long queries, though), see the example output below using the sandbox and the occurred_idx index that is used in the example:
Admin> show jobs queries
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Query Jobs (2023-12-27 23:02:00 UTC)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Node |jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dluzcmek5:3000|jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dluzcmek5:3000|jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dluzcmek5:3000|jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dluzcmek5:3000
Namespace |sandbox |sandbox |sandbox |sandbox
Type |basic |basic |basic |basic
Progress% |100.0 % |100.0 % |100.0 % |100.0 %
Transaction ID |3769593577748395036 |14117743315848890809 |11831435512723716324 |12464121192419507964
Time Since Done |00:11:13 |00:11:15 |00:11:19 |00:16:04
active-threads |0 |0 |0 |0
from |127.0.0.1+57552 |127.0.0.1+57552 |127.0.0.1+57552 |127.0.0.1+57764
n-keyds-requested |0 |0 |0 |0
n-pids-requested |4.096 K |4.096 K |4.096 K |4.096 K
net-io-bytes |5.032 MB |30.000 B |30.000 B |5.056 MB
net-io-time |00:00:00 |00:00:00 |00:00:00 |00:00:00
recs-failed |0.000 |0.000 |0.000 |0.000
recs-filtered-bins|0.000 |0.000 |0.000 |0.000
recs-filtered-meta|0.000 |0.000 |0.000 |0.000
recs-succeeded |4.940 K |0.000 |0.000 |5.000 K
recs-throttled |0.000 |0.000 |0.000 |0.000
rps |0.000 |0.000 |0.000 |0.000
run-time |00:00:00 |00:00:00 |00:00:00 |00:00:00
set |ufodata |ufodata |ufodata |--
sindex-name |occurred_idx |occurred_idx |occurred_idx |--
socket-timeout |00:00:30 |00:00:30 |00:00:30 |00:00:30
status |done(ok) |done(ok) |done(ok) |done(ok)
Number of rows: 23
Admin>
The asinfo command would give an easier to read/parse format (specifically the sindex-name for your purpose):
Admin+> asinfo -v "query-show:" -l
jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dluzcmek5:3000 (10.56.7.159) returned:
trid=3769593577748395036:ns=sandbox:set=ufodata:sindex-name=occurred_idx:n-pids-requested=4096:n-keyds-requested=0:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=41:time-since-done=894125:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=4940:recs-failed=0:from=127.0.0.1+57552:job-type=basic:net-io-bytes=5276501:net-io-time=4:socket-timeout=30000
trid=14117743315848890809:ns=sandbox:set=ufodata:sindex-name=occurred_idx:n-pids-requested=4096:n-keyds-requested=0:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=20:time-since-done=896328:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=0:recs-failed=0:from=127.0.0.1+57552:job-type=basic:net-io-bytes=30:net-io-time=0:socket-timeout=30000
trid=11831435512723716324:ns=sandbox:set=ufodata:sindex-name=occurred_idx:n-pids-requested=4096:n-keyds-requested=0:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=21:time-since-done=899723:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=0:recs-failed=0:from=127.0.0.1+57552:job-type=basic:net-io-bytes=30:net-io-time=0:socket-timeout=30000
trid=12464121192419507964:ns=sandbox:n-pids-requested=4096:n-keyds-requested=0:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=88:time-since-done=1184983:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=5000:recs-failed=0:from=127.0.0.1+57764:job-type=basic:net-io-bytes=5301194:net-io-time=2:socket-timeout=30000
This would of course not be practical if too many queries… one other way would be to set the query context to DEBUG level and then track lines as the following one (from the same example against the sandbox on developer.aerospike.com):
Dec 27 2023 22:50:46 GMT: DEBUG (query): (query.c:1469) starting basic query job 3769593577748395036 {sandbox:ufodata:occurred_idx} n-pids-requested (4096,0) rps 0 sample-max 0 socket-timeout 30000 from 127.0.0.1:57552
Notice the sindex name after the namespace and set (sandbox:udodata:occurred_idx}.
1 Like