IIS Aid

Windows has a hard coded file descriptor limit of 2048 when using the C runtime library which limits the number of files that can be opened simultaneously. This can be a significant performance issue when hosting a busy MySQL server on Windows as MySQL uses the C runtime library to access the file system. This limit of 2048 simultaneously opened files can cause performance issues for MySQL when the table cache value needs to be higher than this. To improve the performance of repeatedly opening tables MySQL places these tables in the table cache, however on a busy MySQL server the table cache may need to be set to a value of several thousand for optimal performance. The double whammy here is that the MyISAM database engine in MySQL needs to open two file descriptors for each unique open table. So by the time you account for temporary tables and other files the maximum effective table cache value for MySQL on Windows when using the MyISAM database engine is actually a good deal less than half of the hard coded file descriptor limit of 2048 imposed by Windows (circa 950 in my experience). So what can be done to work around this?

Unfortunately there is nothing can be done about this by you as the System Administrator. Windows has a hard coded limit of 2048 file descriptors for the C runtime library, and as MySQL is coded to use the C runtime library for accessing the Windows file system there isn't a great deal you can do. To find out how many files your MySQL server has open you can use a command such as below using the MySQL command line client;

There is some good news though. A bug report I've been tracking on the MySQL site since 2006 has recently had some significant work done on it. This work includes a total rework of how MySQL accesses the Windows file system by replacing the C runtime library with the native Win32 API calls. This results in the open file limit for MySQL on Windows to be increased to 16384 by default, and can be expanded even further by using the max-open-files directive in your MySQL my.ini configuration file. Unfortunately this work has only been applied to the MySQL 6.0 branch which is still in alpha stage, and from what I can tell it is unlikely that such significant changes will flow down to the 5.x branches.

So there you have it. Not so much a fix, but rather just something to be aware of if you are considering hosting a large scale MySQL environment on Windows. For most of us just running MySQL to drive a few websites this is generally not an issue, even on busy websites as you'd rarely approach this barrier. Please feel free to post comments here, or if you have any specific questions pertaining to your own environment please post in the forums.