Sign inTry Free

Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

Use Case: Memory Usage by a Query

How to find the total typical memory usage of a recent run of a query?

Use the following query to get the total typical memory usage of a recent run of a query for all activities on the aggregator and on all partitions of the leaf nodes:

SELECT
  aggregator_activity_name,
  SUM(memory_bs * 1000) / (SUM((CASE WHEN mvae.activity_name = aggregator_activity_name THEN elapsed_time_ms ELSE 0 END))) AS avg_clusterwide_mem_bytes_per_second,
  ANY_VALUE(query_text)
FROM information_schema.mv_activities_extended mvae
INNER JOIN information_schema.mv_queries mvq ON mvae.aggregator_activity_name = mvq.activity_name
GROUP BY 1
ORDER BY 2 DESC;

This gives us the cluster-wide memory usage of all instances of user queries, divided by the sum of query latency of those instances. Thus it is the average memory usage per time the query was running.The results are arranged in descending order with the greatest memory users at the top. To view only the top five memory-consuming queries, add a LIMIT 5 clause at the end.

Note

The MV_ACTIVITIES_EXTENDED table provides data only as far back as the uptime, as it is cleared out when the aggregator node restarts.

Warning

The measurement AVG_CLUSTERWIDE_MEM_BYTES_PER_SECOND is the total memory usage, averaged over the execution time of the query. This is a good general measurement, but if there is a large spike in memory usage and the query is long running, then the memory usage averaged over time may not be representative of that large, short term memory usage.

Last modified:

Was this article helpful?

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In