I’m trying to refresh dataset that points to Azure MySQL flexible as a source. The query runs for 5 minutes on the MySQL itself. But when I try to refresh ingestion is getting stuck in Running status and eventually timed out in couple of hours. The query appears at the MySQL processlist but once completed it just gone from MySQL and the ingestion is still running. And there is one dataset that stuck every other time, another did not succeeded at all.
Could someone help me with an idea how to troubleshoot the cause of the issue? Can it be a connection issue between AWS and Amazon? But in that case, I suppose, ingestion should be failed and not stuck and at same time there is a refresh that runs for 8 hours with active connection to MySQL.
Hi @serjdag - Welcome to AWS QuickSight community and thanks for posting this question. Can you please check the below details.
Total number of rows in the table.
Check the total time when you will run via CTE in database … Let’s say you want to see what is the execution time of the sql when running in data base. For example write something like below query , replace table name with your actual table name
WITH temp_table AS ( select * from <<table name>>)
select count(1) from temp_table;
Once we will get the details, we can understand whether the data volume is very high or there is an issue in SPICE refresh.
@serjdag
this can happen for multiple reasons,
Few things to look for:
if your query is expected to return a large amount of data, make sure your rds / MySQL instance support that (InnoDB buffer size, default query timeout, etc setting) mostly with MySQL performance related
Make sure your query is optimized and using expected indexes for better performance (query optimization)
You can also do some basic aggregation on MySQL before bringing it to QS-SPICE to limit the size of the data
We have also checked VPN connection and looks like the issue is with VPN itself or how QuickSight works with VPN.
We were able to connect to Azure MySQL using public connection, and some of the reports succeeded after that. But we still have a list of reports that fail. Same reports work normal when data is located in AWS Aurora and connection is local.
Investigating.
Thanks @serjdag - Looking at your notes, looks like it is a firewall issue. QuickSight can connect relational databases as long as port and connectivity is established… This is more likely a network related problem and possibly the VPN is blocked the request.
Connection itself is Ok, other datasets work using same datasource and few datasets have intermittent refresh issues. But for large datasets (like hundreds of thousands row) we are seeing these issues.
And it works with same amount of data extracted from Aurora.
For datasets that have intermittent issues it looks like a connection issue.
But there are still few large queries that should be optimized. I think that we need to work on the queries first.
Thanks @serjdag - SQL optimization is one of the area for faster performance. Ideally to know the exact sql execution, use CTE and take a count, that will give the exact execution time in DB.