This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 39%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKP%3C/text%3E%3C/svg%3E)
Hi Team,
We have a task to process 10 lakhs of records from Mysql DB, decrypt sensitive data of some fields and push it to an excel. This process is giving heap memory issue on App service and sometimes does not respond. We are using S3 pricing tier with 7 GB Ram.
The memory issue happens when we complete reading the records and start the loop to decrypt each of the record for some columns and push to excel in a container.
Any pointers on how to avoid these memory issues. Not sure if upgrading the tier for this task alone is an ideal recommendation.
Thanks,
Kotesh
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(275.2, 41%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi @Koteswara Pentakota ,
Thank you for reaching out to Microsoft Q&A for this question.
I understand that you are trying to read large amount of data from MySql DB --> process it in your application --> store it in Excel. Below are some suggestions to investigate this issue. I am assuming that you are using .NET for this application, therefore some of the suggestions are specific to it. However, the underlying concept would work for any other Language/Framework
1. Instead of reading the entire data from MySql DB in application memory, read and process them in batch. For example, you may use reader related class or use LIMIT clause with SELECT statement to read rows in batch of 100/1000/10000 etc.. Refer to this link for details. This would avoid the huge memory requirement only for storing data read from MySql DB. The rows can be processed in smaller batch and writted to Excel sheet.
2. Ensure that Dispose() and using{} are used wherever applicable. This would avoid having the objects rooted, even when they are not required and thus Garbage collection would be able to clean them up from memory. ref: Memory release
3. If the steps above do not help, use diagnostic tools like perfview to analyze memory leak. The following post has a great guideline to get started with diagnosing memory leak in Azure App Service - Diagnosing Memory Leaks in Azure App Services with PerfView
4. You might also consider running this application on your VM/on-premise machine with smaller volume of test dataset to observe behavior closely, diagnose and troubleshoot the memory leak issue. There are a number of tools that can help you with it like -
Perfview
Visual Studio
dotnet-dump (dotnet cli based)
Increasing the tier of the application could help with an easy/quick fix, but you might again start facing such issues in future when data volume increases in DB or after adding additinal features. The memory leak should be investigated and memory usage should be optimized for this application. You may also connect with Microsoft Support to seek help in investigating this issue with this application.
Please let me know if you have any questions.
---
Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.
Thanks for the quick reply @AnuragSingh-MSFT
We are using MEAN Stack and app service connecting to MYSQL DB. We did try with a VM and the issue of heap comes on local and VM also. So we thought to check if there is a better approach to handle such volumes of data. We can try limit as one option and see where else leaks are happening. As you rightly said the data will keep growing and we need to handle this seamlessly without increasing the tier.
Thanks,
Kotesh
@Koteswara Pentakota , thank you for the reply.
Troubleshooting AppService in Azure is kind of similar to how you'd troubleshoot it on your on-premise VMs.
I would also advise to look at the application architecture and see if other Azure Services can help you optimize it. For example,
1. you may consider using Azure's messaging services, which can act as a buffer storage between data read/process/out.
2. If it suits, you can also utilize Azure Data Factory to perform the ETL type operation.
Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.
We could not use the Data Factory as the sensitive Data is encrypted and needs to be decrypted out of the Database so this was causing an issue to use Data Factory. We wrote an Azure Function to decrypt but unable to link it in Data Pipeline for each of the record.
@Koteswara Pentakota - You mean you are unable to execute Azure function inside ADF? There is a Azure Function activity which we can leverage inside ADF pipeline to run Azure Function. Click here to know more about same.
If your Azure Function is HTTP trigger type then you can consider using web activity also to run it from ADF pipeline.
Usually when we handle huge amounts of the Spark is good option. Spark notebooks we can write either in Azure Databricks or in Azure Synapse Analytics. Kindly check if you can consider using them here.
Last time when we used Azure DataFactory we created a azure function for decryption but we failed to use it as part of reading data from mysql data and applying it to each record.
Is there a way to know what to consider for our use case? Azure Databricks, Azure Spark and Synapse. A bit consuming which one to use for our case and which is best suited.
You can consider using Azure Synapse as there we can write code in .NET as well. But since you already created Azure Function better go with it. make sure you make call using Azure function activity or Web activity(if its HTTP trigger type).
When I say to use for huge data its subjective to various considerations depending on need and requirements and data. Since already things are there kindly try to leverage existing resources and make it work. Thank you.