MySQL Bugs: #52446: MySqlSessionStateStore performance

Bug #52446	MySqlSessionStateStore performance
Submitted:	29 Mar 2010 16:00	Modified:	18 May 2010 9:25
Reporter:	Anatole BAUDOUIN	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S5 (Performance)
Version:	6.2.2	OS:	Windows
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	MySqlSessionStateStore Performance

Description:
Hi,

MySqlSessionStateStore have a problem of performance because it make many and many requests per seconds for each item in the context Session object.

For exemple it make 19 queries per user per request just for 3 string in the session.

And it take many times in a big program with many variables and many users.

Thanx for what you can do ...

How to repeat:
For exemple if I had just 3 string in the session, access them just after, and trace the request :

The queries made by the MySqlSessionStateStore object for 1 request.

- 1 INSERT, 2 UPDATE of my_aspnet_Sessions

- SELECT (NOW() > Expires) as Expired, SessionItems, LockId,  Flags, Timeout,   TIME_TO_SEC(NOW() - LockDate) as lockAge   FROM my_aspnet_Sessions  WHERE SessionId = 'uhf1d455jrui2gmajlf1hgb5' AND ApplicationId = 7
3 times (for each object, but it's the same)

- UPDATE my_aspnet_Sessions SET Expires = NOW() + INTERVAL 120 MINUTE WHERE SessionId = ''uhf1d455jrui2gmajlf1hgb5'' AND ApplicationId = 7
14 times in the same seconds ...

Suggested fix:
Perhaps you can use the InitializeRequest to make a buffer and EndRequest to save it ...

Hi Anatole and thanks for your report.

I'll be checking it together with Bug#52175. If you have a test case ready, you can attach it.

Hi Anatole,
I checked and I cannot get 19 queries on a single page.

Typically it is 
- 1 query per page for when session is initialized with values, 
- 4 queries to read/modify values on the same page, no matter how many items you have. 

I know it sounds much but 3 of them are dedicated to maintaining persistent locks. With stored procedures it could be reduced to 2 instead of 4, but this is really the limit as long as we maintain persistent locks (and persistent locks is the same thing that SQLServer does, so I guess it is fine)
 
When request starts, there is
- attempt to obtain the lock (UPDATE)
- select that reads session items (SELECT)
- update of the lock id if lock was obtained (UPDATE)

When request ends, there is 
- setting new values (optional when they are updated) and unlock (UPDATE)

Actually, all of those queries are point queries, the table should be small, since it is periodically cleaned (expired sessions get deleted). The table likely completely fits into memory. With Innodb, which has fine-grained (row level) locking update queries never collide, so it should not be a big problem on the DB site. 

Communication with database (maybe networking latency) is still an overhead though. 

But back to the original problem, I'm really curious how do you get your 19 queries. I get 4 at most.

Hi Anatole,
I checked and I cannot get 19 queries on a single page.

Typically it is 
- 1 query per page for when session is initialized with values, 
- 4 queries to read/modify values on the same page, no matter how many items you have. 

I know it sounds much but 3 of them are dedicated to maintaining persistent locks. With stored procedures it could be reduced to 2 instead of 4, but this is really the limit as long as we maintain persistent locks (and persistent locks is the same thing that SQLServer does, so I guess it is fine)
 
When request starts, there is
- attempt to obtain the lock (UPDATE)
- select that reads session items (SELECT)
- update of the lock id if lock was obtained (UPDATE)

When request ends, there is 
- setting new values (optional when they are updated) and unlock (UPDATE)

Actually, all of those queries are point queries, the table should be small, since it is periodically cleaned (expired sessions get deleted). The table likely completely fits into memory. With Innodb, which has fine-grained (row level) locking update queries never collide, so it should not be a big problem on the DB site. 

Communication with database (maybe networking latency) is still an overhead though. 

But back to the original problem, I'm really curious how do you get your 19 queries. I get 4 at most.

Hi,

I run my test again and I cound't obtain the first result with 14 update.
In fact, I have this problem on a big website where there are many and many update of the session table on each request.
I have made a small test but I think there were residual data or something like that ...

I will continue my tests but it seems that MySqlSessionStateStore is not responsible.

Sorry for the inconvenience.

Anatole,
after some googling around I found some pointers that describe a similar problem.
See this post for example: http://www.eggheadcafe.com/forumarchives/NETFrameworkASPNET/Jan2006/post26033081.asp

or this one
http://social.msdn.microsoft.com/Forums/en-US/windowsazure/thread/1436b9fe-c899-46c8-813a-...

The essence of the problem seems to be that ASP.NET runtime calls ResetItemTimeout for each loaded resource (pictures, scripts etc). This would cause the update you're talkng about. The first post say something about TreeView control causing ResetItemTimeout to be called. 

The second post tells about scripts and CSS being included. 

However, I'm able to reproduce the situation neither with TreeView control nor with static script page. That is, I can't  get ASP.NET to call this method  (ResetItemTimeout) no matter what I do with the page, which is odd (others seems to get it but nobody post an example:)

Both post tell that SQL Session provider would update the corresponding table multiple times as well. Some  posters seem to imply it is a bug in ASP.NET.

This is what I found so far about it

Closing the bug for now.
@Anatole, if you find something relevant in Connector/NET, please free to reopen the bug.