Re: Timed call by Timer or OnTime, EXCEL keeps using more memory (memory leak?)

[Govert van Drimmelen]

Hi JC,

Thanks for posting an update about this issue.

I'd guess the most likely issues are:
* .NET COM wrappers being slow to get released. (You could call
Marshal.ReleaseComObject for the four intermediate COM objects you
access - ActiveWorkbook, .Worksheet, .Cells Range, .Cells(1,1) Range).
Don't try to release the ExcelDna.Application reference.
* Some internal Excel structures.

Excel has a few surprising behaviours that can look like memory leaks:
* There is some kind of string caching, so if you set a cell to a new
string values, Excel will internally build a table of all the strings
seen so far. I have not idea if there is a limit to the memory used in
this way.
* Excel keeps an undo stack, and some actions will cause the undo
stack to grow, which could look like a memory leak.

Neither of these cases seem to apply to what you are doing, but it is
the kind of thing that can be very difficult to track down.
You can try to see whether the memory is from the managed heap, by
adding some extra GC.Collect() calls, or checking
GC.GetTotalMemory(true). If the problem is with the COM wrappers, I
think you'd see it with this.
The .NET marshaling code allocates memory outside the .NET heap, but
that is not actually involved in your example, and is not likely to
exhibit the periodic release you see.

Another direction you can investigate is to write something similar in
VBA and check the memory.

You can get rid of Application.OnTime by making your own timer and
calling ExcelAsyncUtil.QueueMacro / .QueueuAsMacro. These should be
reliable in the sense that they will wait until Excel is ready to
process the call.

Let us know if you find anything interesting.

Regards,
Govert


* * * * * * * * * * * * * * * * * * * * * * * * * * * *
Ensure that the Excel-DNA project continues by
making your donation - http://excel-dna.net/support/
* * * * * * * * * * * * * * * * * * * * * * * * * * * *


On Apr 27, 4:52 pm, JC <w2jc...@gmail.com> wrote:
> Some updates. I profiled the above code quite a bit and confirmed that the
> memory footprint does keep increasing slowly. The good thing is that its
> memory will be released/freed after certain time.
>
> I also realized that for some reason I was on an old ExcelDna.xll
> (v0.30.3.1?) but the latest stable version is v0.30.22.1. When I switched
> to the latest one, the same thing is still observed. Memory usage increases
> slowly and gets released from time to time.
>
> Thanks,
>
>
>
>
>
>
>
> On Wednesday, April 3, 2013 10:05:49 PM UTC-4, JC wrote:
>
> > Hi Govert,
>
> > This is more or less a follow up for my old post back in 2011 (see link
> > below) when I asked you a solution for timed call.
>
> > On using native .NET System.Timer (
> >https://groups.google.com/d/msg/exceldna/iUE7P1Be3hA/XIOhjQ3CEnEJ)
>
> > I've tried and tested timed call by *Timer *and *OnTime*. Both of them
> > works, however Timer seems more reliable since it will *not *be
> > interrupted by other things. For example with OnTime, the timed call can be
> > easily missed when I try to open or save another spreadsheet. I didn't see
> > OnTime being so unreliable like this in my current VBA implementation.
>
> > I pasted both .dna versions here, one uses Timer, another uses OnTime.
> > Both of them can use C API or COM interface to update a Cell.
>
> > The problem is that I noticed the memory used by EXCEL process will keep
> > increasing.
> > The Timer version uses more memory much faster than OnTime version.
> > i.e. EXCEL started using 40MB, kept increasing memory usage, added 4KB to
> > 12KB every a few seconds, up to 49MB within 4 hours, and keeps growing
> > slowly...
>
> > Can you please shed some light on the root cause of this memory behavior?
> > or Is that normal? Particularly for the Timer version.
> > I also found the memory usage increases more if I use more complex code.
> > That why I had to reduce the code to its simplest form, but this issue
> > seems obvious.
>
> > ---------------------------------------------------------------------
> > -------------- Timer.dna that uses Timer ----------------------------
> > ---------------------------------------------------------------------
> > -- the following version started using 40M for EXCEL prrocess, keep
> > increasing the usage,
> > -- it goes up to 49M in memory after 4 hours, and seems slowly increasing
> > ...
> > ---------------------------------------------------------------------
>
> > <DnaLibrary Name="ExcelDna Test AddIn">
> > <Project Language="VB">
> > <Reference Path="System.Windows.Forms.dll"/>
> > <![CDATA[
> >     Imports System.Windows.Forms
>
> >     Public Module MyTestFunctions
> >         Public aTimer As System.Windows.Forms.Timer = New Timer()
> >         Public i As Integer = 0
>
> >         <ExcelCommand(MenuName:="&Timer", _
> >             MenuText:="Start Timer")> _
> >         Public Sub StartTimer()
> >             'Dim aTimer As New Timer()
> >             AddHandler aTimer.Tick, AddressOf OnTimedEvent
> >             ' Set the Interval to 1 second.
> >             aTimer.Interval = 1000
> >             aTimer.Enabled = True
> >             aTimer.Start()
> >         End Sub
>
> >         <ExcelCommand(MenuName:="&Timer", _
> >             MenuText:="Stop Timer")> _
> >         Public Sub StopTimer()
> >             aTimer.Enabled = False
> >             aTimer.Stop()
> >         End Sub
>
> >         Public Sub OnTimedEvent(source As Object, e As EventArgs)
> >             ' All kind of errors might occur here...
> >             Try
> >                 'ExcelDnaUtil.Application.Run("UpdateCell")
> >                 ExcelDnaUtil.Application.Run("UpdateCellByCOM")
> >             Catch
> >                 'What to do now...?
> >             End Try
> >         End Sub
>
> >         ' Excel-DNA will register this as an Excel macro
> >         Public Sub UpdateCell()
> >             'use module public instead of static
> >             'static i as integer
>
> >             Dim r as New ExcelReference(0,0,0,0,"Sheet1")
> >             r.SetValue(i)
>
> >             i = i + 1
> >             if(i < 3) Then
> >                 MessageBox.Show(i)
> >             End If
> >         End Sub
>
> >         ' Excel-DNA will register this as an Excel macro
> >         Public Sub UpdateCellByCOM()
> >             'use module public instead of static
> >             'static i as integer
>
> > ExcelDnaUtil.Application.ActiveWorkbook.Sheets("Sheet1").Cells(1,1).value =
> > i
>
> >             i = i + 1
> >             if(i < 2) Then
> >                 MessageBox.Show(i)
> >             End If
> >         End Sub
>
> >     End Module
> > ]]>
> > </Project>
> > </DnaLibrary>
>
> > ---------------------------------------------------------------------
> > -------------- OnTime.dna that uses OnTime --------------------------
> > ---------------------------------------------------------------------
>
> > <DnaLibrary Name="ExcelDna Test AddIn">
> > <Project Language="VB">
> > <Reference Path="System.Windows.Forms.dll"/>
> > <![CDATA[
> >     Imports System.Windows.Forms
>
> >     Public Module MyTestFunctions
> >         Public gbRunFlag as Boolean
> >         Public i as integer = 0
>
> >         <ExcelCommand(MenuName:="&Timer", _
> >                     MenuText:="Start Timer")> _
> >         Public Sub StartTimer()
> >             gbRunFlag = True
>
> >             'XlCall.Excel(XlCall.xlcOnTime, DateTime.Now.AddSeconds(5),
> > "UpdateCell")   'works as well
> >             'ExcelDnaUtil.Application.OnTime(DateTime.Now.AddSeconds(5),
> > "UpdateCell")
> >             ExcelDnaUtil.Application.OnTime(DateTime.Now.AddSeconds(5),
> > "UpdateCellByCOM")
> >         End Sub
>
> >         <ExcelCommand(MenuName:="&Timer", _
> >                         MenuText:="Stop Timer")> _
> >         Public Sub StopTimer()
> >             gbRunFlag = False
> >         End Sub
>
> >         ' Excel-DNA will register this as an Excel macro
> >         Public Sub UpdateCell()
> >             'use module public instead of static
> >             'static i as integer
>
> >             Dim r as New ExcelReference(0,0,0,0,"Sheet1")
> >             r.SetValue(i)
>
> >             i = i + 1
> >             if(i < 2) Then
> >                 MessageBox.Show(i)
> >             End If
>
> >             'schedule another call to itself
> >             If gbRunFlag Then
> >                 'XlCall.Excel(XlCall.xlcOnTime,
> > DateTime.Now.AddSeconds(5), "UpdateCell")   'works as well
>
> > ExcelDnaUtil.Application.OnTime(DateTime.Now.AddSeconds(5), "UpdateCell")
> >             End If
>
> >         End Sub
>
> >         ' Excel-DNA will register this as an Excel macro
> >         Public Sub UpdateCellByCOM()
> >             'use module public instead of static
> >             'static i as integer
>
> > ExcelDnaUtil.Application.ActiveWorkbook.Sheets("Sheet1").Cells(1,1).value =
> > i
>
> >             i = i + 1
> >             if(i < 2) Then
> >                 MessageBox.Show(i)
> >             End If
>
> >             'schedule another call to itself
> >             If gbRunFlag Then
> >                 'XlCall.Excel(XlCall.xlcOnTime,
> > DateTime.Now.AddSeconds(5), "UpdateCellByCOM")   'works as well
>
> > ExcelDnaUtil.Application.OnTime(DateTime.Now.AddSeconds(5),
> > "UpdateCellByCOM")
> >             End If
>
> >         End Sub
>
> >     End Module
> > ]]>
> > </Project>
> > </DnaLibrary>

[Elvis Hsu]

Hi, 

I know this is an old thread. Would it be possible relate this this topic on MSDN?

https://msdn.microsoft.com/en-us/library/office/bb687840.aspx

Cheers,

Elvis

[Govert van Drimmelen]

Hi Elvis,

Memory management in the Excel C API (which is discussed in the link you point to) is indeed quite tricky.

But I believe Excel-DNA implements that correctly, and that the C API memory management is not the cause of memory usage pattern reported.

I'd be happy to revisit it if you are running into a specific problem in this regard.

-Govert

[Elvis Hsu]

Hi, Govert,

Thank you for your response. I had a look at your implementation on Git and it is indeed correct however I am suffering memory leak issue on calling ExcelAsyncUtil.Run(functionName, parameter, ExcelFuncAsyncHandle). I have attached a sample VS 2015 solution and a sample workbook if you don't mind to check it out.

When I open the workbook and registered with addin and let it run for a few minutes or hours, the unmanaged memory stacks up and never got released. GC does collect the managed heaps but not unmanaged. 

The following sample code is pretty simple and I have also tried to implement my own ExcelAsyncHandleObservable but the result is the same. I really appreciate if you have alternative solution for the following implementation.

Cheers,

Elvis

public static class Test

    {

        private static readonly Random _random = new Random();

        //private static readonly ObjectCache CachedValues = MemoryCache.Default;

        [ExcelFunction(Name = "MemoryLeakFunction")]

        public static object MemoryLeakFunction(string field, object value, object returnString)

        {

            //var cached = CachedValues[field] as string;

            //if (cached != null)

            //    return cached;

            // field1 should be the key because it never changes

            var asyncResult = ExcelAsyncUtil.Run("MemoryLeakFunction", field, h =>

            {

                ProcessTask(h, value, (bool) returnString);

            });

            return asyncResult.Equals(ExcelError.ExcelErrorNA)

                ? "Processing..."

                : asyncResult;

        }

        private static void ProcessTask(ExcelAsyncHandle handle, object value, bool returnString)

        {

            Task.Run(async () =>

            {

                // mimic some long run task. For example, http request

                await Task.Delay(_random.Next(200, 997));

                if (returnString)

                {

                    var result = $"OK: {DateTime.Now:yyyy-MM-dd HH:mm:ss}";

                    //CachedValues.Add(field, result, DateTimeOffset.Now.AddMilliseconds(997));

                    handle.SetResult(result);

                }

                else

                {

                    handle.SetResult(value);

                }

            });

        }

    }

[Elvis Hsu]

Um, maybe I should create another thread for the case I just posted? But I guess it is related to memory leak though?

Cheers,

Elvis

[Elvis Hsu]

Worth to mention that I am using Excel 2010 v14.0.7166.5000 (32 bit)

and I also use VMMap to check where the leak is and I think it happened on unregister observer to RTD?

Because I see a bunch of rtdsrv.abererrgtsdggsdgf  and following by guid (https://github.com/Excel-DNA/ExcelDna/blob/master/Source/ExcelDna.Integration/ExcelRtd.cs)

https://github.com/Excel-DNA/ExcelDna/blob/master/Source/ExcelDna.Integration/ExcelRtdObserver.cs

[Govert van Drimmelen]

Hi Elvis,

 

I’ve not had a chance to dig in, but did notice something unusual in your sample.

You are using the overload of ExcelAsyncUtil.Run that uses an ExcelAsyncHandle.

 

The ExcelAsyncHandle is normally intended for use with the native Excel async feature, where it would just be the last parameter. But I tried ot make a compatible version of ExcelAscynUtil.Run that also works with it, and the implementation to make that work is a bit tricky, so perhaps there is a problem with how the RTD topics are disconnected.

 

If you run a debug build of Excel-DNA under s debugger, you shouldget a good output of RTD topics as they are connected and disconnected, and should be able to see if there are topics that don’t get disconnected properly.

 

I understood from your previous mail that you think the leak is not in managed memory, but in unmanaged memory. If there were RTD topics not cleaned up, then I’d expect the managed ExcelRtdServer.Topic objects to build up too.

 

You can try things with ExcelAsyncUtil.Run(…) overloads that don’t use the ExcelAsyncHandle. (If you want to use Tasks, there is a helper for doing Task async that you can find in the Registration extension project here: https://github.com/Excel-DNA/Registration/blob/master/Source/ExcelDna.Registration/Utils/AsyncTaskUtil.cs You can just take the AsyncTaskUtil file and add into your project.)

 

It’s also possible that you’ve run into something that Excel is caching internally. To test this, we’d have to make simple RTD server independent of Excel-DNA and check the memory usage as we create and clean up many topics.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Elvis Hsu]

Hi, Govert,

Thank you for your response. I am digging into your code at the moment.

So far I found out an interesting behaviour which when XlCall.TryExcel(XlCall.xlfRtd, out result, parameters) the parameters are (rtcsrv.guid, null, topicid) where the combination stays in the excel (private data blocks) and never get released.

Eventually, Excel slowly builds up these blocks of memory. In our case, when we uses the async way to implement the UDF, and each time the new GUID is created and  XlCall.TryExcel(XlCall.xlfRtd,...) is called if the result is served.

I think this should be the direction for me to investigate?

Cheers,

Elvis

[Govert van Drimmelen]

Hi Elvis,

I understand that you're suggesting that Excel internally keeps track of all the RTD topics that have been created, even after the topic has been disconnected. One question is whether this is true even after the last topic for a particular server has been disconnected, and hence the RTD server terminated.

I would suggest you make an RTD server that does not involve Excel-DNA at all, and check that case. The Kenny Kerr articles would be a good start: http://weblogs.asp.net/kennykerr/Rtd3

-Govert

---

From: exce...@googlegroups.com [exce...@googlegroups.com] on behalf of Elvis Hsu [elvis...@gmail.com]
Sent: 09 June 2016 11:30 AM
To: Excel-DNA
Subject: Re: [ExcelDna] Re: Timed call by Timer or OnTime, EXCEL keeps using more memory (memory leak?)

[Elvis Hsu]

Hi, Govert,

I have taken your suggestion to make a simple RTD server and indeed Excel 2010 produces memory leak for whatever topic id it creates.

At first few minutes, I used "Item 1.123234134" style as topic key then I changed to "Item HH:mm:ss - 1.123154646" style.

And then I monitor the memory blocks, Excel never releases them and the memory committed size keeps increasing.

If I have a large amount of operations during the day, then the Excel instance will eventually run out of virtual memory and crash.

It is not the problem of ExcelDna but Govert, do you have good idea for me to avoid this issue?

Cheers,

Elvis

To post to this group, send email to exc...@googlegroups.com.

[Elvis Hsu]

Forgot to attached a screenshot...

[Elvis Hsu]

Furthermore, looks like this issue has been addressed at https://social.msdn.microsoft.com/Forums/office/en-US/42efbb96-2d97-452d-a148-b6a212bd0f37/excel-rtd-causes-memory-leak?forum=exceldev

[Elvis Hsu]

Another interesting topic from VSOD team: https://blogs.msdn.microsoft.com/vsod/2011/09/30/excel-rtd-cachingmemory-usage/

[Elvis Hsu]

Oh, No... not a good news https://support.microsoft.com/en-us/kb/2596576

[Govert van Drimmelen]

Do you need to make many async calls, or can you restructure this as a set of topics that you just update (i.e. IObservables instead of discrete Tasks?).

I guess we could make some kind of recycling plan for the topics - similar to how Excel-DNA async already abstracts the topic strings.

I suggest you start a new thread with some more details on your use case, or you contact me directly.

-Govert

[Alan Stubbs]

Hi Elvis,

I've not read through all of your posts fully so this may not be relevant but does this help fix the issue - if called at relevant times (timer loop if no obvious place in normal process flow):

GCSettings.LargeObjectHeapCompactionMode = GCLargeObjectHeapCompactionMode.CompactOnce; 

GC.Collect(); // This can be omitted 

from, among other sources:

https://www.simple-talk.com/dotnet/.net-framework/large-object-heap-compaction-should-you-use-it/

It depends what the actual issue is obviously but thought I'd mention it.  It requires .NET 4.5.1+

Alan

[Elvis Hsu]

Hi, Alan,

Thank you for your suggestion. I have tried this tip but it didn't work as the memory issue is not related to .NET LOH. It is related to RTD topics cache in Excel.

The .NET heaps are all ok, in my case, which is around 15-17 MB during the life time of Excel with my current implementation. 

Cheers,

Elvis