We are running on premise Singlestore and I’m trying to audit where memory is being consumed by our in memory tables. For most data types the columns_statistics tables contains information about the memory used but for variable string type (E.g. varchar) it just gives null as the string is outside the row. Where is the string stored and how can I see how much memory is being used for it?
There isn’t a direct way to get exactly the number of bytes for a column and all its indexes and overhead.
Memory gets allocated in blocks and the system keeps those blocks around even after rows are deleted.
The cloud portal and Studio will show you the memory use for a table under databases->tables.
One way to get a floor for the minimum amount of bytes used in a variable-length string column is to sum its LENGTH(), which is the actual byte length of the string. Don’t sum CHAR_LENGTH() since that is the length characters, not bytes. E.g.:
create database db1;
use db1;
create rowstore table t(a text collate utf8mb4_general_ci);
insert t values("abc"),("😀😃😄😁😆😅");
select a, length(a), length(a:>longblob), char_length(a)
from t;
select sum(length(a)) from t;
Results:
singlestore> select a, length(a), length(a:>longblob), char_length(a) from t;
+--------------------------+-----------+---------------------+----------------+
| a | length(a) | length(a:>longblob) | char_length(a) |
+--------------------------+-----------+---------------------+----------------+
| abc | 3 | 3 | 3 |
| 😀😃😄😁😆😅 | 24 | 24 | 6 |
+--------------------------+-----------+---------------------+----------------+
2 rows in set (0.01 sec)
singlestore> select sum(length(a)) from t;
+----------------+
| sum(length(a)) |
+----------------+
| 27 |
+----------------+
There will be other space used for indexes and pre-allocated storage blocks that are not full.
Here’s a related post: Memsql Memory - Table Usage vs Total Usage - #3 by lucian.baciu