What will happen if the sql cursor not be closed

It wrote in Elasticsearch Reference that

Unlike scroll, receiving the last page is enough to guarantee that the Elasticsearch state is cleared.

I wonder that:

  • Is there any memory leak or bad effection will happen if the sql cursor aways not be closed
  • Is there any way to find how many cursors in use

Thank you

@gwtony the way this works in ES SQL is that when the user asks for the next page in the cursor, we look at the results size of this page and if there are no documents in it we, on purpose, call clear scroll before returning the response to the user.

If the last page is not reached, the cursor (or scroll context) will remain active for 45 seconds by default (page_timeout parameter from https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-rest-fields.html#sql-rest-fields). There is a memory footprint for each scroll context kept alive in memory before the timeout runs out or before it's explicitly cleared, as explained in https://www.elastic.co/guide/en/elasticsearch/reference/7.x/search-request-body.html#scroll-search-context. If you are using cursors and you want to manage the memory usage properly (recommended), you can clear the cursor with /_sql/close as explained here.

If you want to find out the number of scroll contexts alive (a cursor is basically a scroll context), you can use: GET /_nodes/stats/indices/search. In the results, search for scroll.

Thank you for the answer.
I have another question: it seems that the length of cursor id has relationship with field number, not a fixed length like scroll id. Is that a bug or designed for some reason

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.