MongoDB sort memory limit exceeded when retrieving execution history

SUMMARY

Hi,
After updating Stackstorm from 3.6 to 3.8 I ran into a sort memory limit issue with MongoDB when gathering the execution history.

STACKSTORM VERSION

st2 3.8.0, on Python 3.8.13

OS, environment, install method

Single-boxed: Stackstorm 3.8, MongoDB 5.0.6, and RabbitMQ 3.8.8 are installed on a single RedHat8 server.

Steps to reproduce the problem

You can reproduce the problem by running the 2 following commands : mongosh -> db.adminCommand({setParameter: 1, internalQueryMaxBlockingSortMemoryUsageBytes: 1}).
This limits the allowed memory usage to 1 byte which generates the exact same error I encountered.

Expected Results

I expected to see the history.

Actual Results

Error encountered when accessing the history (see detailed error message section of this issue):


This is probably due to the way Stackstorm gathers the execution history (line 1135, in _get_action_executions -> return super(ActionExecutionsController, self)._get_all() in the actionexecutions.py script).
Could this be fixed/optimized in the next Stackstorm release?
I found a temporary fix by tripling the value of the MongoDB ‘internalQueryMaxBlockingSortMemoryUsageBytes’ parameter, but the history takes more than a minute to load versus a couple seconds before. This error never apeared in Stackstorm 3.6, so it's probably related to an update in how Stackstorm gathers the history.

Detailed error message:

2023-02-23 08:06:22,140 140164375924048 ERROR router [-] Failed to call controller function "get_all" for operation "st2api.controllers.v1.actionexecutions:action_executions_controller.get_all": Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in., full error: {'ok': 0.0, 'errmsg': 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.', 'code': 292, 'codeName': 'QueryExceededMemoryLimitNoDiskUseAllowed'}
Traceback (most recent call last):
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2common/router.py", line 632, in call
resp = func(**kw)
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2api/controllers/v1/actionexecutions.py", line 739, in get_all
return self._get_action_executions(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2api/controllers/v1/actionexecutions.py", line 1135, in _get_action_executions
return super(ActionExecutionsController, self)._get_all(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2api/controllers/resource.py", line 244, in _get_all
result = self.resources_model_filter(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2api/controllers/resource.py", line 570, in resources_model_filter
result = super(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/st2api/controllers/resource.py", line 275, in resources_model_filter
for instance in instances[offset:eop]:
File "/opt/stackstorm/st2/lib/python3.8/site-packages/mongoengine/queryset/queryset.py", line 110, in _iter_results
self._populate_cache()
File "/opt/stackstorm/st2/lib/python3.8/site-packages/mongoengine/queryset/queryset.py", line 129, in _populate_cache
self._result_cache.append(next(self))
File "/opt/stackstorm/st2/lib/python3.8/site-packages/mongoengine/queryset/base.py", line 1574, in next
raw_doc = next(self._cursor)
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/cursor.py", line 1207, in next
if len(self.__data) or self._refresh():
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/cursor.py", line 1124, in _refresh
self.__send_message(q)
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/cursor.py", line 999, in __send_message
response = client._run_operation_with_response(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1368, in _run_operation_with_response
return self._retryable_read(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1471, in _retryable_read
return func(session, server, sock_info, slave_ok)
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/mongo_client.py", line 1360, in _cmd
return server.run_operation_with_response(
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/server.py", line 135, in run_operation_with_response
_check_command_response(first, sock_info.max_wire_version)
File "/opt/stackstorm/st2/lib/python3.8/site-packages/pymongo/helpers.py", line 164, in _check_command_response
raise OperationFailure(errmsg, code, response, max_wire_version)
pymongo.errors.OperationFailure: Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in., full error: {'ok': 0.0, 'errmsg': 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.', 'code': 292, 'codeName': 'QueryExceededMemoryLimitNoDiskUseAllowed'} (_exception_class='OperationFailure',_exception_message="Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in., full error: {'ok': 0.0, 'errmsg': 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.', 'code': 292, 'codeName': 'QueryExceededMemoryLimitNoDiskUseAllowed'}",_exception_data={'_message': "Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in., full error: {'ok': 0.0, 'errmsg': 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.', 'code': 292, 'codeName': 'QueryExceededMemoryLimitNoDiskUseAllowed'}", '_error_labels': set(), '_OperationFailure__code': 292, '_OperationFailure__details': {'ok': 0.0, 'errmsg': 'Executor error during find command :: caused by :: Sort exceeded memory limit of 104857600 bytes, but did not opt in to external sorting. Aborting operation. Pass allowDiskUse:true to opt in.', 'code': 292, 'codeName': 'QueryExceededMemoryLimitNoDiskUseAllowed'}, '_OperationFailure__max_wire_version': 13})

Start by reproducing the failure with MongoDB's internalQueryMaxBlockingSortMemoryUsageBytes set to 1, then inspect st2api/controllers/v1/actionexecutions.py at _get_action_executions and st2api/controllers/resource.py around _get_all and resources_model_filter. Trace how the execution-history query is built and verify that history loads without exceeding the sort limit under the reported setup.