Memory Usage in CompiledQueryCache

[TimBailey]

Hi,

Devart.Data.MySql.dll 6.30.185.0
Devart.Data.MySql.Linq.dll 2.50.27.0

We are seeing large amount of memory used by objects in the CompiledQueryCache.
How can I control what is in the cache?
If I disable it as suggested in another post, then performance of the site is reduced. I can see that I can set the MaxSize property but I want to remove the large objects.

We are disposing of the datacontext after each request.

Do you have any advice for debugging/controlling the cache.

Thanks
Tim Bailey

[TimBailey]

Here is some data from one of our webservers.....

Showing current bytes


Server summary

[StanislavK]

After a query is executed, it is added to the compiled query cache. To avoid storing some of your queries in the cache, you can remove them right after execution:

Code: Select all

var query = myDataContext.Depts.Where(...);
var result = query.ToList();
if (...)
  MyDataContext.compiledQueryCache.Remove(query);

Otherwise, you can clear the cache based on some conditions (e.g., after its size exceeds some value).

Also, please try updating to the latest 6.30.202 version of dotConnect for MySQL: there were several fixes related to the compiled query cache since the 6.30.185 version.

[TimBailey]

Does it store just the compiled query or the results also?

Cheers
Tim

[TimBailey]

This is the data from another run. This time with the latest dlls as suggested.
The biggest object here is the CompiledQueryCache showing 25mb (81% of retained memory) it contains a bunch (40-50) Mapping.an objects.

The bottom panel shows GC roots to the CompiledQueryCache, maybe this helps?



If you could help me shed some more light on this as this is affecting us in production very badly.

Thanks in advance
Tim

[StanislavK]

The compiled query cache stores queries only, not the objects being retrieved.

The 'an' objects contain mapping metadata for your model; they should be disposed when the DataContext instance is disposed.

Were you able to identify the query causing the memory consumption specified? If yes, please describe it and the way it is executed. If possible, send us a test project with which the issue can be reproduced.

[TimBailey]

So the CompiledQueryCache is supposed to hang around, but the "an" objects are not getting disposed.

I have double checked that our DataContext is being disposed.

We suspect one query as exhibits the issue. Can't post the contents here, but it is made up of a few "compounding" calls...psudocode below...

Queryable contacts = this.db.Contacts;
if(showonlyactive)
{
contacts = contacts.Where(contact => contact.Status == 1);
}

a bit more filtering

Then the final "contacts" is joined onto anther table then returned.

I'll see if I can create a standalone project that exhibits the issue.

Hope to solve this soon
Tim

[TimBailey]

I've emailed a demo app as requested. Please let me know if you got the file.

There is a sql dump file for the database.

Please investigate urgently

cheers
Tim

[StanislavK]

Thank you for your assistance, we have received the test project. However, we couldn't reproduce the problem: objects from the Devart.Data.Linq.Mapping namespace are successfully disposed in our environment.

Please specify whether the issue occurs with any 'options' parameter passed to the .GetContacts method, or 'options' should have some specific value. Also, please specify the SQL generated in this case (you can send it via email in case table names etc. are not open information).

[TimBailey]

The project causes the issue as is, no changes are required and no options are set other than the ones set in the code.

If I run this on my system I see the following memory use as the end of the run.



Here is the sql generated for each query.

Code: Select all

SELECT t1.id AS ContactId, t1.client_code AS ContactCode, t1.client_name AS ContactName, CONCAT(CONCAT(t3.firstname, ' '), t3.lastname) AS C1, CONCAT(CONCAT(t2.firstname, ' '), t2.lastname) AS C2, t4.ao_clientcode IS NOT NULL AS C3, t1.work_phone_1 AS WorkPhone1, t1.email_address_1 AS EmailAddress1, t4.ao_clientcode AS MyobAoClientId
FROM clients t1
LEFT OUTER JOIN users t2 ON t1.client_of = t2.id
LEFT OUTER JOIN users t3 ON t1.manager_id = t3.id
LEFT OUTER JOIN aoclients t4 ON t1.id = t4.client_id
ORDER BY t1.client_name DESC LIMIT 0, 50

[/img]

[TimBailey]

Hi desperate to fix this issue, do you need anything else from me to help me fix this?

Cheers
Tim

[StanislavK]

CompiledQueryCache stores the metadata for all entity types related to stored queries. The 'an' objects represent these type metadata (if the latest version is used - for the 2.50.27 version, 'an' objects store model metadata). Hence, this seems to be the expected behaviour.

If you consider memory consumption for a particular compiled query to be excessive, you can remove this query from the cache right after execution, so that the metadata objects are disposed after disposing the context.

However, we will analyze the possible ways of reducing the memory taken by CompiledQueryCache. We will post here about the results.