Forum Discussion
Eventhouse - Engine Under Memory Pressure
We're in the midst of building a Fabric solution for a client and we're facing crippling performance issues with Eventhouse in production environment even though usage is still low e.g. we're ingesting data but the usage of reporting is still minimal. When I look at the operations, I can see several "Engine under memory pressure" issues reported with Failure or PartialFailure.
Operation Details (Examples):
An admin command cannot be executed due to a failure in the distributed ingestion: Details=‘Query execution lacks memory resources to complete (xxxx): multiple errors occurred:
Partial query error ({"shard _id":"xxxx"]), source: (hr: ‘xxxx ‘Engine under memory pressure, context: fielddatum materialize’)
We have Eventhouse with 2 KQL databases and two ingestion streams, using F64 capacity. One stream is just configuration data, so it's kinda negligible, the second one is telemetry data from sensors e.g. temperature, humidity, power usage and so on. This stream ingests approximately 250M records per day, and the current KQL db size is 1.1TB. We had a single materialised view for deduplicated and validated data before, and we added two more views for hourly aggregations over the weekend. This was done as an attempt to improve performance but it actually made things worse. For example we need to be able to detect sensor failures and anomalies in real time and for that we need to compare sensor readings to defined min & max values for specific types of sensor and for anomalies we need to compare average readings for two consecutive hours and report anomaly when averages differ above a predefined threshold. We had to disable the materialised views altogether as it made the performance issue even worse. The client wished to be able to see real time reports for the last 7 days for sensor errors and anomalies, but we reduced that down to last 24 hours, and we're still getting errors and timeouts if we reduce it to just the last 3 hours.
Looking at the Fabric Capacity Metrics report I'm not able to see any throttling or memory usage for the Eventhouse. CU % over time sits at about 25% of capacity, there's no throttling and no overages reported and I can't see any other useful info that would allow me to further investigate where these memory issues are coming from and how we can optimise the solution.
I'd welcome guidance on how to approach this problem. Where can I find the details of the allocated vs actual memory usage or see details on what's using up the available memory?
Nah, sorry, the Microsoft Fabric Capacity Metric app is completely useless for troubleshooting this issue, as I mentioned in my original post. Storage and memory usage are two distinct concepts. It would be misleading to accept your reply as a solution.
I raised a support ticket with Microsoft and had a helpful engineer from Microsoft provide some useful scripts that we were able to run to gather some diagnostics on memory usage. The level of detail that's exposed to customers for KQL is apparently quite limited by design for now. I provided this feedback, which will hopefully reach the Microsoft Fabric product team for consideration. Perhaps access to more advanced monitoring & diagnostics for KQL/Evenhouse could be a product roadmap feature.
Until executing .show queries and .show commands to sift through the logs is the way.
.show commands command - Azure Data Explorer & Real-Time Intelligence | Azure Docs
Here are some of the queries that were provided by Microsoft Support team that we found helpful in identifying the root cause of memory issues:
.show queries | where StartedOn > ago(7d) | extend MemoryUsage = todouble(OverallQueryStats.resource_usage.memory.peak_per_node) | summarize totalplanning = sum(MemoryUsage) by Application, User, bin(StartedOn, 1h) | render timechart.show commands | where StartedOn > ago(7d) | extend MemoryUsage = todouble(ResourcesUtilization.MemoryPeak) | summarize totalplanning = sum(MemoryUsage) by Application, User, bin(StartedOn, 1h) | render timechart.show commands-and-queries | where StartedOn > ago(5d) | where State !="Completed".show commands-and-queries | where StartedOn > ago(7d) | where FailureReason has "Engine under memory pressure" or FailureReason has 'Low memory condition' or FailureReason has 'Query execution lacks memory resources to complete' | summarize count() by bin(StartedOn,6h) | render timechart
3 Replies
Hi kalinky ,
Thanks for reaching out to the Microsoft fabric community forum.
The Microsoft Fabric Capacity Metric app allows any capacity administrator to monitor OneLake Storage. Please try to monitor storage to try and check if anything specific is causing the error.
Storage is billed separately from your Fabric or Power BI Premium Capacity units. Data ingested into a KQL database is stored in two tiers of storage: OneLake Cache Storage, and OneLake Standard Storage.If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thank youHi kalinky ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Nah, sorry, the Microsoft Fabric Capacity Metric app is completely useless for troubleshooting this issue, as I mentioned in my original post. Storage and memory usage are two distinct concepts. It would be misleading to accept your reply as a solution.
I raised a support ticket with Microsoft and had a helpful engineer from Microsoft provide some useful scripts that we were able to run to gather some diagnostics on memory usage. The level of detail that's exposed to customers for KQL is apparently quite limited by design for now. I provided this feedback, which will hopefully reach the Microsoft Fabric product team for consideration. Perhaps access to more advanced monitoring & diagnostics for KQL/Evenhouse could be a product roadmap feature.
Until executing .show queries and .show commands to sift through the logs is the way.
.show commands command - Azure Data Explorer & Real-Time Intelligence | Azure Docs
Here are some of the queries that were provided by Microsoft Support team that we found helpful in identifying the root cause of memory issues:
.show queries | where StartedOn > ago(7d) | extend MemoryUsage = todouble(OverallQueryStats.resource_usage.memory.peak_per_node) | summarize totalplanning = sum(MemoryUsage) by Application, User, bin(StartedOn, 1h) | render timechart.show commands | where StartedOn > ago(7d) | extend MemoryUsage = todouble(ResourcesUtilization.MemoryPeak) | summarize totalplanning = sum(MemoryUsage) by Application, User, bin(StartedOn, 1h) | render timechart.show commands-and-queries | where StartedOn > ago(5d) | where State !="Completed".show commands-and-queries | where StartedOn > ago(7d) | where FailureReason has "Engine under memory pressure" or FailureReason has 'Low memory condition' or FailureReason has 'Query execution lacks memory resources to complete' | summarize count() by bin(StartedOn,6h) | render timechart
