Azure Data Factory pipeline is failing as i have migrated to Azure MySQL Flexi server from mySQL single server

Question

Azure Data Factory pipeline is failing as i have migrated to Azure MySQL Flexi server from mySQL single server

Manish Kumar 0

I am using azure Data factory which has many pipelines. Pipeline pulls data from Oracle on-Prim data base using integration runtime and puts the data into Azure MySQL database.
Initially the destination was a MySQL single server, now i have migrated to Azure MySQL flexi server.

Now my pipelines are taking longer time in execution and also sometimes it is failing.

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-07-25T16:53:10.1166667+00:00
Hello Manish Kumar,

Can you please check the below and see if it helps.

The performance of the integration runtime can significantly impact the execution time of your pipelines. Ensure that the IR is properly configured and has sufficient resources (CPU, memory) to handle the data load.

Data skew occurs when the data distribution across partitions is not uniform, leading to some partitions processing more data than others. This can cause performance issues as some activities may take longer to complete than others. To address this, you can use partitioning techniques to distribute data evenly across partitions.

As the number of activities in a pipeline increases, the pipeline's complexity also increases, making it harder to manage and troubleshoot. Consider breaking down complex pipelines into smaller, more manageable pipelines.

Ensure that the data is consistent and accurate when copying data from one source to another. This can be challenging when dealing with large volumes of data and having multiple activities in pipelines. Use checkpoints to ensure that the pipeline resumes from the point of failure, rather than starting from the beginning.

Check the resource consumption of your Azure MySQL flexible server. Sometimes, the server might be under-provisioned for the workload, leading to performance degradation. Consider scaling up the server or optimizing the queries to reduce the load.

Enable logging for your pipelines to capture detailed information about the execution.

I hope this helps.
Manish Kumar 0 Reputation points

2024-07-26T05:02:16.9266667+00:00

Hi Bhargava,

I am using AutoresolveIntegrationRuntime to connect to mySQL flexi server.
could you please suggest how can i improve the performance for the same.

I am attaching the screenshot for the same.2.png

1.png
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-07-26T17:03:39.1+00:00

from your screenshot, it seems like you are using Azure IR.

The AutoResolveIntegrationRuntime is a managed integration runtime that automatically scales up or down based on the size and complexity of your data integration workload. This means that the compute size of the integration runtime is dynamically adjusted based on the needs of your workload, and you do not have direct control over the compute size.

Please follow the below document that explains how to choose the right integration runtime for your workload.

https://learn.microsoft.com/en-us/azure/data-factory/choose-the-right-integration-runtime-configuration

I hope this helps.
Manish Kumar 0 Reputation points

2024-07-27T08:02:40.5266667+00:00

Hi Bhargava,
Thanks for the response.

I understand what you just mentioned regarding the "AutoResolveIntegrationRuntime" AutoScale functionality, however when we manually change the configuration from Small to Large, we can see significant improvement in performance.

please suggest.

Your answer

Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-07-25T16:53:10.1166667+00:00

Hello Manish Kumar,

Can you please check the below and see if it helps.

The performance of the integration runtime can significantly impact the execution time of your pipelines. Ensure that the IR is properly configured and has sufficient resources (CPU, memory) to handle the data load.

Data skew occurs when the data distribution across partitions is not uniform, leading to some partitions processing more data than others. This can cause performance issues as some activities may take longer to complete than others. To address this, you can use partitioning techniques to distribute data evenly across partitions.

As the number of activities in a pipeline increases, the pipeline's complexity also increases, making it harder to manage and troubleshoot. Consider breaking down complex pipelines into smaller, more manageable pipelines.

Ensure that the data is consistent and accurate when copying data from one source to another. This can be challenging when dealing with large volumes of data and having multiple activities in pipelines. Use checkpoints to ensure that the pipeline resumes from the point of failure, rather than starting from the beginning.

Check the resource consumption of your Azure MySQL flexible server. Sometimes, the server might be under-provisioned for the workload, leading to performance degradation. Consider scaling up the server or optimizing the queries to reduce the load.

Enable logging for your pipelines to capture detailed information about the execution.

I hope this helps.
Manish Kumar 0 Reputation points

2024-07-26T05:02:16.9266667+00:00

Hi Bhargava,

I am using AutoresolveIntegrationRuntime to connect to mySQL flexi server.
could you please suggest how can i improve the performance for the same.

I am attaching the screenshot for the same.2.png

1.png
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-07-26T17:03:39.1+00:00

from your screenshot, it seems like you are using Azure IR.

The AutoResolveIntegrationRuntime is a managed integration runtime that automatically scales up or down based on the size and complexity of your data integration workload. This means that the compute size of the integration runtime is dynamically adjusted based on the needs of your workload, and you do not have direct control over the compute size.

Please follow the below document that explains how to choose the right integration runtime for your workload.

https://learn.microsoft.com/en-us/azure/data-factory/choose-the-right-integration-runtime-configuration

I hope this helps.
Manish Kumar 0 Reputation points

2024-07-27T08:02:40.5266667+00:00

Hi Bhargava,
Thanks for the response.

I understand what you just mentioned regarding the "AutoResolveIntegrationRuntime" AutoScale functionality, however when we manually change the configuration from Small to Large, we can see significant improvement in performance.

please suggest.

Share via

Azure Data Factory pipeline is failing as i have migrated to Azure MySQL Flexi server from mySQL single server

Your answer