[BUG] Memory usage exceed limit and java.lang.IllegalStateException: resource is not enough to run the query, quit.

[microtimber]

Describe the bug
Memory used up after many sql requests was executed.

To Reproduce
Steps to reproduce the behavior:

1. start a new container from the opensearch docker image v1.2.3;
2. use JMeter to perform a lot of sql requests;
3. See error in the container console log: "Memory usage:7301801288 exceed limit:7301444403" and "java.lang.IllegalStateException: resource is not enough to run the query, quit."

Expected behavior
Memory should be GCed.

Plugins

analysis-hanlp
opensearch-asynchronous-search
opensearch-job-scheduler
opensearch-performance-analyzer
opensearch-sql
opensearch-alerting
opensearch-cross-cluster-replication
opensearch-knn
opensearch-reports-scheduler
opensearch-anomaly-detection
opensearch-index-management
opensearch-observability opensearch-security

Host/Environment:

• OS: CentOS 7
• Version: opensearch docker image v1.2.3

Additional context
MAT analyses the memory dump and shows the leak suspect:

The class org.springframework.beans.factory.support.DefaultListableBeanFactory, loaded by java.net.FactoryURLClassLoader @ 0x605cb7520, occupies 5,631,327,632 (91.39%) bytes. The memory is accumulated in one instance of java.util.concurrent.ConcurrentHashMap$Node[], loaded by , which occupies 5,631,322,800 (91.39%) bytes.

And we find that sql legacy plugin creates an AnnotationConfigApplicationContext instance for every sql request in the class file RestSQLQueryAction:

  private SQLService createSQLService(NodeClient client) {
    return doPrivileged(() -> {
      AnnotationConfigApplicationContext context = new AnnotationConfigApplicationContext();
      context.registerBean(ClusterService.class, () -> clusterService);
      context.registerBean(NodeClient.class, () -> client);
      context.registerBean(Settings.class, () -> pluginSettings);
      context.register(ElasticsearchSQLPluginConfig.class);
      context.register(SQLServiceConfig.class);
      context.refresh();
      return context.getBean(SQLService.class);
    });
  }

[dai-chen]

@microtimber Thanks for reporting the issue! Will investigate.
The error message you saw comes from our own circuit breaker as intended. But we may need to double check if any memory leak.

[dai-chen]

This is already fixed in #822. Thanks!