Columnstore is consuming memory constantly

SingleStore Tools MemSQL Ops
1

We are constantly reaching a "has reached the ‘maximum_memory’ " error when we run queries.
In order to save memory we moved all the tables into columnstore (not ideal, but we don’t know what else to do)

I cannot detect what’s using all the memory, but I’m seeing in the ui that this table is using 1.4 GB of memory and we don’t know the reason. The value is still going up. We have 4 leafs of 32 gb

TABLE INFO

Memory Usage

1.4 GB

Disk Usage

658.4 GB

Row Count

1.23B rows

Table Type

Columnstore table

Table Compression

77.03%

Do you know how can we detect memory usage and free memory? I already read your documentation about it. Nothing works.

2

Hello @moralez.rodrigo , I’m having the same issue and still waiting for an answer here: Error ER_INDEX_CORRUPT

3

Columnstore tables have an in memory segment that buffers data from small write queries, you can see this here:

select sum(memory_use), sum(rows_in_memory), database_name, table_name 
from information_schema.table_statistics group by 3,4 order by 1;

This memory will be automatically released when the data is flushed to disk but you can force that via optimize table <table_name> flush.

In your case, this is taking very little memory relative to the total amount of memory available (1.4 gb out of 128 gb) so I would focus elsewhere. If you share a query profile then we can look at what is consuming the memory there.

4

hey @rob Thank you for your help, I’m lost, I can’t run even simple queries.

profile
select player_id,
sum(duration) duration ,
count(*) sessions,
null active_days
from events
where name=‘event_name’
and environment =‘production’
group by player_id

Project [remote_0.player_id, remote_0.duration, remote_0.sessions, ? AS active_days] est_rows:10 actual_rows: 300 exec_time: 0ms start_time: 00:00:18.368 network_traffic: 5.441000 KB network_time: 0ms
Top limit:[@@SESSION.sql_select_limit] actual_rows: 300 exec_time: 0ms
Gather partitions:all est_rows:10 alias:remote_0 actual_rows: 300 exec_time: 0ms start_time: 00:00:18.368
Project [r0.player_id, duration, CAST(COALESCE($0,0) AS SIGNED) AS sessions] est_rows:10 actual_rows: 2,986 exec_time: 0ms start_time: [00:00:18.367, 00:00:18.372] network_traffic: 72.495003 KB network_time: 0ms
Top limit:[?] actual_rows: 2,986 exec_time: 0ms
HashGroupBy [SUM(r0.duration) AS duration, SUM(r0.sessions) AS $0] groups:[r0.player_id] actual_rows: 9,405,237 exec_time: 5,918ms start_time: [00:00:04.120, 00:00:04.133] memory_usage: 1,811,677.750000 KB
TableScan r0 storage:list stream:yes table_type:sharded est_table_rows:342,682 est_filtered:342,682 exec_time: 0ms start_time: [00:00:04.120, 00:00:04.133] end_time: [00:00:18.367, 00:00:18.372]
Repartition [events.player_id, duration, sessions] AS r0 shard_key:[player_id] est_rows:342,682 est_select_cost:181,358,352 actual_rows: 14,243,582 exec_time: 596ms start_time: [00:00:04.099, 00:00:14.915] network_traffic: 382,275.406250 KB network_time: 583ms
ShuffleGroupBy [SUM(events.duration) AS duration, COUNT(*) AS sessions] groups:[events.player_id] actual_rows: 14,243,582 exec_time: 8,236ms start_time: [00:00:00.118, 00:00:00.361] memory_usage: 1,728,713.250000 KB
ColumnStoreFilter [events.environment = ? AND events.name = ?] actual_rows: 31,975,541 exec_time: 62ms start_time: [00:00:00.121, 00:00:00.368] total_rows_in: 1,191,622,339 average_filters_per_row: 1.043303 average_index_filters_per_row: 0.000000 average_bloom_filters_per_row: 0.000000
ColumnStoreScan twilightdaycare_landing.events, KEY events (environment, date_utc, name, player_id) USING CLUSTERED COLUMNSTORE table_type:sharded_columnstore est_table_rows:1,344,822,673 est_filtered:22,669,794 actual_rows: 1,195,697,360 exec_time: 2,720ms start_time: [00:00:00.077, 00:00:00.110] memory_usage: 4,194.304199 KB segments_scanned: 1,988 segments_skipped: 243 segments_fully_contained: 0
Compile Total Time: 96ms

image
image2008×546 99 KB

This is the PROFILE. It’s just a group by… any idea?

This is the error I get
Memory used by MemSQL (28425.00 Mb) has reached the ‘maximum_memory’ setting (28665 Mb) on this node. Possible causes include (1) available query execution memory has been used up for table memory (in use table memory: 15852.75 Mb) and (2) the query is large and complex and requires

5

I still have not idea what’s going on with this db. I run optimize flush and moved all the rowstore tables to columnas and I’m still getting this error “in use table memory: 17179.75 Mb”

@rob could you please help me. The memory issues with this database are making all the queries to fail.

6

Hey, thanks for sharing this but i am facing some issues can anyone explain me in details please share.

7

@rob any idea? For a long running query I expect to take time, but almost all the queries are failing because oom instead. A Count distinct or approx_count_distinct is enogth to crash the query. Is this a bug in the database?