Page 1 of 1
Memory Usage in CompiledQueryCache
Posted: Tue 30 Aug 2011 14:37
by 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
Some numbers
Posted: Wed 31 Aug 2011 09:38
by TimBailey
Here is some data from one of our webservers.....
Showing current bytes
Server summary
Posted: Wed 31 Aug 2011 15:31
by 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.
What is stored
Posted: Wed 31 Aug 2011 20:41
by TimBailey
Does it store just the compiled query or the results also?
Cheers
Tim
More data
Posted: Thu 01 Sep 2011 14:50
by 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
Posted: Thu 01 Sep 2011 17:06
by 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.
Posted: Fri 02 Sep 2011 06:01
by 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
Demo
Posted: Fri 02 Sep 2011 10:33
by 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
Posted: Fri 02 Sep 2011 14:49
by 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).
More Info
Posted: Fri 02 Sep 2011 17:25
by 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]
More information required?
Posted: Tue 06 Sep 2011 04:28
by TimBailey
Hi desperate to fix this issue, do you need anything else from me to help me fix this?
Cheers
Tim
Posted: Tue 06 Sep 2011 17:13
by 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.