I am running some experiments with MemSQL databases and in one of them an error occurs regarding the amount of memory of one of the leaves. The cluster memory capacity is:
1 master - 8GB
1 aggegator - 16GB
2 leaves - 16GB (each)
Cluster settings
+----+--------------+------+------+--------+--------------------+----------+---------------+---------------------+----------------+----------------------+--------------------+------------------------+--------+
| ID | IP_ADDR | PORT | TYPE | STATE | AVAILABILITY_GROUP | NUM_CPUS | MAX_MEMORY_MB | MAX_TABLE_MEMORY_MB | MEMORY_USED_MB | TABLE_MEMORY_USED_MB | TOTAL_DATA_DISK_MB | AVAILABLE_DATA_DISK_MB | UPTIME |
+----+--------------+------+------+--------+--------------------+----------+---------------+---------------------+----------------+----------------------+--------------------+------------------------+--------+
| 4 | 172.21.6.154 | 3306 | LEAF | online | 1 | 4 | 15360 | 13824 | 2573 | 97 | 461354 | 458530 | 1383 |
| 3 | 172.21.6.153 | 3306 | LEAF | online | 1 | 8 | 15360 | 13824 | 4969 | 96 | 213515 | 210501 | 1383 |
| 2 | 172.21.6.152 | 3306 | CA | online | NULL | 8 | 13928 | 12535 | 505 | 34 | 213519 | 210787 | 1378 |
| 1 | 172.21.6.151 | 3306 | MA | online | NULL | 8 | 6670 | 5646 | 643 | 33 | 944686 | 939192 | 1374 |
+----+--------------+------+------+--------+--------------------+----------+---------------+---------------------+----------------+----------------------+--------------------+------------------------+--------+
Query
select d_year, c_nation, sum(lo_revenue - lo_supplycost) as profit
from lineorder join dim_date on lo_orderdatekey = d_datekey join
customer on lo_custkey = c_customerkey join
supplier on lo_suppkey = s_suppkey join
part on lo_partkey = p_partkey
where c_region = 'AMERICA' and s_region = 'AMERICA' and (p_mfgr = 'MFGR#1' or p_mfgr = 'MFGR#2')
group by d_year, c_nation
order by d_year, c_nation;
The explain
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| WARNING: Statistics have not been collected on the following tables and columns. Consider running the following commands to collect them: |
| ANALYZE TABLE ssb03.`customer` COLUMNS `C_Region` ENABLE; |
| ANALYZE TABLE ssb03.`part` COLUMNS `P_MFGR` ENABLE; |
| ANALYZE TABLE ssb03.`supplier` COLUMNS `S_Region` ENABLE; |
| |
| See https://docs.memsql.com/docs/analyze for more information on statistics collection. |
| |
| Project [dim_date.D_Year AS d_year, lineorder.C_Nation AS c_nation, profit] |
| Sort [dim_date.D_Year, lineorder.C_Nation] |
| HashGroupBy [SUM(lineorder.LO_Revenue - lineorder.LO_SupplyCost) AS profit] groups:[dim_date.D_Year, lineorder.C_Nation] |
| HashJoin [lineorder.LO_OrderDateKey = dim_date.D_DateKey] |
| |---HashTableBuild alias:dim_date |
| | Project [dim_date_0.D_Year, dim_date_0.D_DateKey] est_rows:2,556 |
| | ColumnStoreScan ssb03.dim_date AS dim_date_0, KEY D_Year (D_Year) USING CLUSTERED COLUMNSTORE est_table_rows:2,556 est_filtered:2,556 |
| HashJoin [part.P_PartKey = lineorder.LO_PartKey] |
| |---HashTableBuild alias:part |
| | Project [part_0.P_PartKey] est_rows:38,000 |
| | Filter [part_0.P_MFGR = 'MFGR#1' OR part_0.P_MFGR = 'MFGR#2'] |
| | ColumnStoreScan ssb03.part AS part_0, KEY P_Type (P_Type) USING CLUSTERED COLUMNSTORE est_table_rows:200,000 est_filtered:38,000 |
| TableScan 1tmp AS lineorder storage:list stream:yes est_table_rows:108,240 |
| Project [remote_0.LO_Revenue, remote_0.LO_SupplyCost, remote_0.LO_PartKey, remote_0.LO_OrderDateKey, remote_0.C_Nation] est_rows:108,240 |
| Gather partitions:all est_rows:108,240 alias:remote_0 |
| Project [lineorder_0.LO_Revenue, lineorder_0.LO_SupplyCost, lineorder_0.LO_PartKey, lineorder_0.LO_OrderDateKey, customer.C_Nation] est_rows:108,240 est_select_cost:64,000 |
| HashJoin [lineorder_0.LO_CustKey = customer.C_CustomerKey] |
| |---HashTableBuild alias:customer |
| | Project [customer_0.C_Nation, customer_0.C_CustomerKey] est_rows:3,000 |
| | Filter [customer_0.C_Region = 'AMERICA'] |
| | ColumnStoreScan ssb03.customer AS customer_0, KEY C_Nation (C_Nation) USING CLUSTERED COLUMNSTORE est_table_rows:30,000 est_filtered:3,000 |
| HashJoin [supplier.S_SuppKey = lineorder_0.LO_SuppKey] |
| |---HashTableBuild alias:supplier |
| | Project [supplier_0.S_SuppKey] est_rows:1,000 |
| | Filter [supplier_0.S_Region = 'AMERICA'] |
| | ColumnStoreScan ssb03.supplier AS supplier_0, KEY S_Nation (S_Nation) USING CLUSTERED COLUMNSTORE est_table_rows:10,000 est_filtered:1,000 |
| BloomFilter table:customer fields:lineorder_0.LO_CustKey |
| BloomFilter table:supplier fields:lineorder_0.LO_SuppKey |
| ColumnStoreScan ssb03.lineorder AS lineorder_0, KEY LO_OrderDateKey (LO_OrderDateKey) USING CLUSTERED COLUMNSTORE est_table_rows:6,001,171 est_filtered:6,001,171 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
The error that appears.
(1712, Leaf Error (172.21.6.153:3306): Leaf Error (172.21.6.153:3306): Memory used by MemSQL (13727.50 Mb) has reached the 'maximum_memory' setting (13928 Mb) on this node.
Possible causes include (1) available query execution memory has been used up for table memory (in use table memory: 164.50 Mb) and
(2) the query is large and complex and requires more query execution memory than is available (in use query execution memory 12517.11 Mb).
See https://docs.memsql.com/troubleshooting/latest/memory-errors for addit)
Any help outside increasing the amount of memory physically?