SingleStore DB

Understanding Memory and Disk Usage with Studio

The following document demonstrates how to interpret the Total Memory Usage and Total Disk Usage statistics in the SingleStore DB Studio (or simply "Studio") Dashbaord.

Cluster Memory Usage

Under Cluster Usage, Total Memory Usage accounts for cluster activities such as querying and logs, in addition to data storage. When you hover over the Info icon beside the Total Memory Usage statistics, you will see a breakup of the memory usage information, as shown in the following screenshot.

memory-usage.png

The memory values displayed by the Info icon are evaluated using the following logic.

Total Memory Usage =  Memory Used + Reserved Memory

Reserved Memory = Total Memory - Available Memory

The following sections demonstrate the queries that are used to compute the Total Memory Usage. The first query below obtains the Memory Used value.

SELECT
        IFNULL(SUM(mv.VARIABLE_VALUE - mv2.VARIABLE_VALUE), 0) * 1024 * 1024 AS memoryUsageB
    FROM
        INFORMATION_SCHEMA.MV_GLOBAL_STATUS mv
    INNER JOIN
        INFORMATION_SCHEMA.MV_GLOBAL_STATUS mv2
    ON
        mv.NODE_ID = mv2.NODE_ID
    WHERE
        mv.VARIABLE_NAME = 'Total_server_memory' AND
        mv2.VARIABLE_NAME = 'Buffer_manager_cached_memory' AND
        mv.NODE_TYPE = 'LEAF';
****
memoryUsageB 
1929170124.8

The following query retrieves the Total Memory value.

SELECT
        SUM(totalMemoryB) AS totalMemoryB
    FROM (
        SELECT
            MAX(
                IF(
                    CGROUP_TOTAL_B > POW(2, 62) OR CGROUP_TOTAL_B = -1,
                    HOST_TOTAL_B,
                    CGROUP_TOTAL_B
                )
            ) AS totalMemoryB
        FROM
            INFORMATION_SCHEMA.MV_SYSINFO_MEM
        WHERE
            TYPE='LEAF'
        GROUP BY
            IP_ADDR
    );
****
totalMemoryB
101335216128

The following query obtains the Available Memory value.

SELECT
        SUM(MAX_MEMORY_MB) * 1024 * 1024 AS availableMemoryB
    FROM (
        SELECT
            MAX(MAX_MEMORY_MB) AS MAX_MEMORY_MB
        FROM
            INFORMATION_SCHEMA.MV_NODES
        WHERE
            TYPE='LEAF'
        GROUP BY
            IP_ADDR
    ); 
****
availableMemoryB
91200946176 

When the memory values returned by the queries are converted from Bytes to Gigabytes and applied in the Total Memory Usage formula, the following results are obtained (demonstrated in the first screenshot).

Reserved Memory = 94.4 GB - 85 GB = 9.4 GB

Total Memory Used =  1.8 GB + 9.4 GB = 11.3 GB

Note

Unit conversion from Byte to Gigabyte is performed by dividing the byte values by (1024*1024*1024). For example, 1929170124.8 B/(1024*1024*1024) = 1.79667 GB.

Cluster Disk Usage

Under Cluster Usage, you can view the Total Disk Usage of the cluster. When you hover over the Info icon under Total Disk Usage, you can additionally view the Total Disk Space.

disk-usage.png

The first query below computes the Total Disk Usage, whereas the second one determines the Total Disk Space. The queries return the values in bytes, which are converted into Gigabytes and displayed in the Studio Dashboard.

WITH
        mv_query AS (
            SELECT
                MAX(MOUNT_USED_B) AS diskUsageB
            FROM
                INFORMATION_SCHEMA.MV_SYSINFO_DISK
            WHERE
                TYPE = 'LEAF'
            GROUP BY
                IP_ADDR
        )
    SELECT
        SUM(diskUsageB) AS diskUsageB
    FROM
        mv_query;
****
diskUsageB 
350867456
WITH
        mv_query AS (
            SELECT
                MAX(MOUNT_TOTAL_B) AS diskTotalB
            FROM
                INFORMATION_SCHEMA.MV_SYSINFO_DISK
            WHERE
                TYPE = 'LEAF'
            GROUP BY
                IP_ADDR
        )
    SELECT
        SUM(diskTotalB) AS diskTotalB
    FROM
        mv_query;
****
diskTotalB 
350867456

Note

Unit conversion from Byte to Gigabyte is performed by dividing the byte values by (1024*1024*1024). For example, 1929170124.8 B/(1024*1024*1024) = 1.79667 GB.