SQL XOP: Memory usage with SQLHighLevelOp

Share icon

Wed, 04/01/2020 - 08:05 am

Hello,

I have noticed a memory problem when I run my Igor experiment that handles measurement data and communicates with a MySQL database. I can pinpoint the problem to the SQLHighLevelOp command. This high level command seems to reserve memory with every execution and the used memory is never freed. The help file talks about improving memory management with SQLHighLevelOp, but that concerns large select statements. I can trigger this problem with a simple test table insert or select statement:

function sql_insert_test(insertions)
    variable insertions
   
    string statement = "INSERT INTO testTable (number) VALUES (?)"
   
    variable i
    for(i = 0; i < insertions; i++)
        SQLHighLevelOp /CSTR={connectionStr,SQL_DRIVER_COMPLETE_REQUIRED} /PVAR={i} statement          
    endfor
end


function sql_select_test(selections)
    variable selections
   
    string statement = "SELECT number FROM testTable WHERE idtestTable = 1"
   
    String numberStr
    variable i
    for(i = 0; i < selections; i++)
        SQLHighLevelOp/CSTR={connectionStr,SQL_DRIVER_COMPLETE_REQUIRED} /VARS={numberStr} statement       
    endfor
end

Where testTable includes only id integer and number integer. I can use the /MFL flag with the SELECT statement, but that does not make any difference. For example after running 10000 iterations of any of the loops above, Igor uses already more than 1GB of memory, and that memory is freed only if I restart Igor.

I have not tried the Igor low level SQL functions, because their use is pretty strongly discouraged if one doesn't have experience with ODBC driver programming. Although only speed improvements are mentioned to their benefit, not memory handling. As far as I understood one should be even more careful with memory handling when using the low level functions.

I am running Igor 7.08 64-bit on Windows 10 with [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.16].

 

So, my question is if what I observe is normal and expected with the SQLHighLevelOp?

Thanks!

Please let me know the following:

1. How are you measuring memory usage?

2. Does not memory usage go down if you do New Experiment?

If it is possible for me to access your database for testing purpose, please send your connection string to WaveMetrics support.

 

April 1, 2020 at 09:00 am - Permalink

1. I am simply monitoring with the Windows builtin tools: physical memory use of Igor64.exe with Resource Monitor and used and available bytes in time with Performance Monitor. No fancier tools.

2. No, not significantly. For example I had an experiment open where I had run 10000 inserts and 10000 selections and memory usage was increased over 2GB. Opening a new experiment dropped the memory usage just below 2GB (I had some procedures and data in the first experiment) but did not clear the majority of the memory used by Igor64.exe. 

Unfortunately, I am not able to give you access to database I am using. I am using my workplace database server and they have strict access restrictions. 

April 1, 2020 at 11:34 pm - Permalink

I can reproduce this problem using Igor 7 and Igor 8 on Windows but not on Macintosh. I'm using MySQL ODBC 8.0 Unicode Driver on Windows and an older version on Macintosh.

I see 142544 bytes leaked on each call to SQLHighLevelOp. 140225 bytes of that is allocated by the SQLDriverConnect function, which is implemented by the ODBC driver. I looked at our XOP code and as far as I can tell, we are making the proper calls to free the memory that's allocated by the call to SQLDriverConnect.

I suspect the problem is the same bug that has already been reported at https://bugs.mysql.com/bug.php?id=93593. That bug reports "about 150 kB" leak per iteration, which is very close to the size of the leak I'm seeing.

Since the bug does not appear to be in the SQL XOP, there is likely nothing we can do to fix it.

The SQL Help file has an example of an alternate approach you can use that should minimize the memory leaked by the driver. For more information, execute the following command on Igor's command line:

DisplayHelpTopic "High-Level Multiple-Commands-Per-Connection Method Example"

That approach has the advantage that it should be faster than re-establishing a connection on every invocation of SQLHighLevelOp.

There is noting wrong with the low level functions, but you need to be very careful when using them because it's easy to leak memory. But in this case, you may find it necessary to use them to avoid a memory leak in the driver itself.

April 2, 2020 at 08:17 am - Permalink

It's OK to use the few low-level functions mentioned in the main SQL help file. These are:

SQLAllocHandle, SQLFreeHandle
SQLConnect, SQLDriverConnect, SQLDisconnect

The reasons for not using the other low-level functions are:

1. They are complicated and require a good understanding of ODBC which is quite complicated.

2. No one has used the low-level functions, other than the few mentioned in the main SQL help file, since shortly after the SQL XOP was created in 2007. Consequently they have not undergone serious vetting.

3. There are too many low-level functions and too many different ways to use them and too many ODBC drivers to thoroughly test them.

4. We will not fix issues in the low-level functions other than the few mentioned in the main SQL help file.

When we started the SQL XOP we took the approach of exposing the ODBC API to Igor users via the low-level functions. For the reasons listed above, it became clear that supporting them would be problematic, so we created SQLHighLevelOp.

 

April 2, 2020 at 08:34 am - Permalink

Thanks for the quick response and investigations!

Good to know that I am not doing anything wrong with the SQL XOP. On the other hand shame that the bug is somewhere in the ODBC driver where we cannot do anything about it. 

I noticed this memory leak while doing 'stress tests' with our measurement system and it is not likely to be a big problem in real situation. I have to consider if it is worth diving into the low level functions or simply making sure that Igor gets rebooted often enough. Definitely not an elegant solution but could be just fine.

April 2, 2020 at 08:55 am - Permalink

>I noticed this memory leak while doing 'stress tests' with our measurement system

You did a good job on the stress testing!

 

April 2, 2020 at 09:12 am - Permalink