Memory Usage in CompiledQueryCache

Memory Usage in CompiledQueryCache

Postby TimBailey » Tue 30 Aug 2011 14:37

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
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Some numbers

Postby TimBailey » Wed 31 Aug 2011 09:38

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

Showing current bytes
Image

Server summary
Image
TimBailey
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Postby StanislavK » Wed 31 Aug 2011 15:31

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

What is stored

Postby TimBailey » Wed 31 Aug 2011 20:41

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

Cheers
Tim
TimBailey
 
Posts: 9
Joined: Tue 24 May 2011 13:13

More data

Postby TimBailey » Thu 01 Sep 2011 14:50

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?

Image

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

Thanks in advance
Tim
TimBailey
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Postby StanislavK » Thu 01 Sep 2011 17:06

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby TimBailey » Fri 02 Sep 2011 06:01

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
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Demo

Postby TimBailey » Fri 02 Sep 2011 10:33

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
TimBailey
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Postby StanislavK » Fri 02 Sep 2011 14:49

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).
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

More Info

Postby TimBailey » Fri 02 Sep 2011 17:25

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.

Image

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
 
Posts: 9
Joined: Tue 24 May 2011 13:13

More information required?

Postby TimBailey » Tue 06 Sep 2011 04:28

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

Cheers
Tim
TimBailey
 
Posts: 9
Joined: Tue 24 May 2011 13:13

Postby StanislavK » Tue 06 Sep 2011 17:13

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to LinqConnect (LINQ to SQL support)