Bug #37676 Allow Lock in Memory on Windows
Submitted: 26 Jun 2008 20:11
Reporter: Harrison Fisk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S4 (Feature request)
Version:5.0.60 OS:Microsoft Windows (2003 64-bit)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[26 Jun 2008 20:11] Harrison Fisk
Description:
On Windows, it is possible to lock processes into memory that use normal memory allocations.

This is similar to the mlockall support that MySQL has on Linux and Unix.

Without this functionality, you can get behavior similar to the following articles:

http://support.microsoft.com/kb/918483
http://weblogs.asp.net/omarzabir/archive/2007/10/19/a-significant-part-of-sql-server-proce...

The problem is that MySQL buffer pools can get swapped out to make room for more disk cache.  This is quite repeatable on Windows 2003 64-bit with 8GB of RAM.

SQL Server 2005 Enterprise Edition supports functionality similar to this, which can resolve the problem.

How to repeat:
Run many random data queries against a very large MyISAM table on Windows 2003 64-bit for a day or two.  mysqld will get swapped out and performance will decrease dramatically.

Suggested fix:
I believe the functions we would need to use are SetSystemFileCacheSize and VirtualLock on Windows.