Forum Discussion
Power BI Premium DirectQuery Bottleneck Investigation
I have a large composite dataset making heavy use of Direct Query due to the volume of the data. The underlying data source we are using currently is PostgreSQL (AWS Aurora) behind a Power BI Enterprise Gateway. We are having performance issues with the report and I want to identify the underlying bottleneck.
I thought it might be related to the Direct Query connection limits, but the answer to this question makes me more doubtful:
DirectQuery/Live Connection (per sec) means the max number of data sources connected to DQ or LC per sec (used in memory).Don’t worry about each visual query.
The database generally appears healthy and doesn't appear to be under load, but there are a lot of moving parts in this system that I don't have a lot of insight into, so would like some pointers to either identify or rule out bottlenecks at these components. At a high level, I think of the key components as:
- Web Browser (hosting the Power BI app)
- Power BI service
- Power BI Enterprise Gateway (hosted on AWS)
- PostgreSQL database server (hosted on AWS)
How can I best determine where these bottlenecks might be? Especially if the bottleneck is in our premium capacity.
The Capacity Metrics report shows spikes in wait times for queries, and doesn't show any times where Direct Query/Live Connections exceeded 80% utilitization (Query Duration page) so maybe I can rule out the Power BI service.
So both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Do you have access to the PostGreSQL server query log? Can you monitor that while your users interact with the report?
Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
2 Replies
So both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Do you have access to the PostGreSQL server query log? Can you monitor that while your users interact with the report?
Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
lbendlin wrote:Do you have access to the PostGreSQL server query log?
Somewhat. I have been primarily using the Diagnostics option in Power BI Desktop to capture the logs and then scrape through those to find the underlying queries. Unfortunately it is often difficult to determine which queries are used for which visualisation. One odd thing I have identified is that typically the long queries as reported by Power BI's logs take 4 times the duration in Power BI as running the query independently (and database load has been extremely low).
lbendlin wrote:Can you monitor that while your users interact with the report?
Our DBA has been monitoring the database while a user interacts with the reports and they have not identified any issues on the database end.
lbendlin wrote:Same for the gateway VMs - can you run a performance monitor while users are interacting with the reports? Are the cluster members hitting max memory?
We found that we were hitting high CPU usage on the Gateway and have since added an additional node to the cluster and increased its size. We are currently having issues getting Npgsql working on the new node, so I haven't been able to verify the results of this, but it may be promising.
It sounds like we have the basics covered for now and we are at least looking in the right places. Are there any other techniques we may have missed?
lbendlin wrote:So both your "on-premise database" and "on-premise data gateway" are actually hosted on AWS? Sorry to hear that. This means you have some insane network traffic going back and forth across the VPN.
Fortunately the source of the data loaded into the database is also hosted on AWS.