How do I know that we are using all the memory allocated and it is time to allocate more?

abhishek_1

[Deleted User]

Vertica keeps some information of the memory usage, cpu, network etc on the data collector tables. You can check the data_collector table to see what is available as per vertica version that can be different. select table_name from data_collector where table_name ilike '%mem%' and node_name ilike '%01'; table_name ---------------------------------------------- dc_memory_info dc_memory_info_by_second dc_memory_info_by_minute dc_memory_info_by_hour dc_memory_info_by_day From there you can query the tables and see what is available. This tables change per version and normally are not documented and supported. When they are supported they are moved to system_tables. here you can keep track of the memory used by vertica, other application, os, etc. Hope this helps. Eugenia

abhishek_1

Thanks Eugenia !!! Is there any automated way where I can stop a query which is utilizing 80-100% CPU as it really impacts other query?

[Deleted User]

You can close_session('') or cancel the statement INTERRUPT_STATEMEN('',statement_id); Both documented https://my.vertica.com/docs/6.1.x/HTML/index.htm#9857.htm Hope this helps, Eugenia

colin_loghin

You could also check query_events system table historically for event_type of ◾MEMORY LIMIT HIT ◾GROUP_BY_SPILLED ◾JOIN_SPILLED which are usually a sign that memory is running low, and the database is resorting to disk spillage in order to complete requests. A high percentage of those events could indicate low memory or unoptimized queries / logical design. colin