Displaying Overhead, Memory Distribution and Total Memory Footprint

You can query the SYS.MEMORYANALYTICS virtual table to estiamte the memory usage of the the tables in your data stores. You can also query the table using an optimizer hint to obtain a more accurate view of the memory footprint of tables and indexes.

Sample sys.memoryanalytics Queries

This section includes some example queries that can provide useful information about memory usage in your GemFire XD system.

Entry Overhead for Partioned Tables

To find the total per entry overhead for partitioned tables across all members:
gfxd> SELECT table_name, SUM(constant_overhead+entry_size) / CASE WHEN SUM(num_rows) > 0 THEN SUM(num_rows) ELSE 1 END FROM sys.memoryanalytics WHERE index_name IS null GROUP BY table_name;
To find the total per entry overhead for partitioned tables and all the indexes defined across all members:
gfxd> SELECT table_name, SUM(constant_overhead+entry_size) / CASE WHEN sum(num_rows) > 0 THEN SUM(num_rows) ELSE 1 END FROM sys.memoryanalytics GROUP BY table_name;

Index Memory Usage

To find the total memory usage of a particular index across all members:
gfxd> SELECT index_name, SUM(total_size) FROM sys.memoryanalytics WHERE index_name LIKE 'IDX%' GROUP BY index_name;

Total Memory Usage

To see how much memory is consumed by all tables per host (in the scenario where there are multiple members on each host):
gfxd> SELECT host, sum(total_size) FROM sys.memoryanalytics GROUP BY host;

Average Primary Key Size

To see average primary key size in memory when the value is overflowed to the disk:
gfxd> SELECT table_name, index_name, key_size/ CASE WHEN num_keys_in_memory > 0 THEN num_keys_in_memory ELSE 1 END FROM sys.memoryanalytics;

Distribution of Off-Heap to Heap Size

To view the average distribution of the size of off-heap memory used in comparison with the heap size of a table:
gfxd> SELECT table_name, sum(value_size)/ CASE WHEN sum(num_values_in_memory) > 0 THEN sum(num_values_in_memory) ELSE 1 END, SUM(value_size_offheap)/ CASE WHEN sum(num_values_in_offheap) > 0 THEN SUM(num_values_in_offheap) ELSE 1 END FROM sys.memoryanalytics WHERE index_name IS null GROUP BY table_name;

Range of Table Size

To find the largest (maximum) and smallest (minimum) table sizes in your distributed system:
gfxd> SELECT table_name, index_name, MAX(total_size) AS max_, min(total_size) AS min_ FROM sys.memoryanalytics GROUP BY table_name, index_name;	
			

Using sizerHints When Querying sys.memoryanalytics

If you use the sizerHints=withMemoryFootPrint option with your query, you can obtain a more accurate estimate of the total memory footprint of your table.

Note: Use sizerHints=withMemoryFootPrint with caution because it uses the Java agent and requires lots of memory to process. It should be applied on to very small dataset (typically couple of hundred rows) and should never be used in a production system. When you query sys.memoryanalytics using sizerHints=withMemoryFootPrint GemFire XD walks through the heap and uses reflection to provide estiamtes thart are closer to what visualVM or other tools might display. The displayed value can be 40-50% different from the value displayed without the optimizer hint, or from values shown in the Pulse monitoring tool.
Follow these steps to start a GemFire XD member with the Java agent to use sizerHints when querying memory analytics:
  1. If you use a locator for member discovery, specify the complete path and filename of gemfirexd.jar in the -javaagent:jar_path system property when starting the locator. For example:
    gfxd locator start -dir=locator -peer-discovery-port=10101 -client-port=1527 -J-javaagent:/gfxd-dir/lib/gemfirexd.jar 
  2. Use the -javaagent:jar_path Java system property to specify the gemfirexd.jar file in your installation when you start each GemFire XD member. For example, if you use gfxd to start a GemFire XD server:
    gfxd server start -client-port=1528 -J-javaagent:/gfxd-dir/lib/gemfirexd.jar 
    Make sure you specify the complete path and filename of gemfirexd.jar for your system.
  3. Start a gfxd prompt and connect to the distributed system.
  4. Use the sizerHints=withMemoryFootPrint hint with your query to display the memory footprint:
    gfxd> SELECT * FROM sys.memoryAnalytics -- GEMFIREXD-PROPERTIES sizerHints=withMemoryFootPrint \n ;
    Note: When processing a query hint, GemFire XD does not recognize the terminating semicolon character if it appears at the end of a SQL comment line. For this reason, add a new line character (\n) before the terminating semicolon if the optimizer hint extends to the end of the statement.