A customer of ours recently asked us to help them troubleshoot some performance problems they have been having with their ASP/MySQL based solution. They are running the latest version of MySQL 5 under Windows 2003 Server 64-bit edition. Their IIS/ASP based application is using an ODBC connection to connect to MySQL.
They have recently added a partner that is sending them sales leads through an API call. There are occasional peaks in this traffic where the incoming connections into IIS far exceeds what they have been able to handle in the concurrent open connections into MySQL. Tracking this down through the application stack we looked at several low-level file/network system items:
- Ephemeral port exhaustion
- Stale socket disposal
- Max Open Files
#1 and #2 has caused some issues for another customer in the past, but this time it turned out to be #3. Even though they had set an appropriate value for max-connections in their my.ini file:
max-connections = 4000
table-open-cache=512
MySQL was artificially reducing these settings at runtime to much lower than was needed to support this flash traffic. Upon startup, MySQL would emit this into the Application Event Log:
Changed limits: max_open_files: 2048 max_connections: 1910 table_cache: 64
According to this bug report on the MySQL site, the approximate formula is used to determine the maximum open files for MySQL:
table_cache*2+max_connections
This condition has been reported as far back as 2006. A fix that switched MySQL from using the C Runtime Library in the Windows binary for opening files to using the native Win32 API calls was completed in mid June 2008. However, this will only be rolled into the MySQL 6 line of development.
We had looked at seeing about increasing this limit to something higher than 2048, but according to this site, that value is hardcoded. This particular customer has at least one thing going for them, their exclusive use of the InnoDB engine. This engine uses native calls so the table cache value does not need to be anything very high and they can maximize the number of open descriptors for use in incoming connections.
Realistic Options:
- Artificially limit the maximum number of incoming connections to IIS so as to limit the passthrough the MySQL. This leaves serious amounts of processing power of this particular server untapped.
- Move to MySQL running on a non-Windows OS
- Move their backend to MS SQL Server.
MySQL.com documented differences.
Posted by Brian Blood in Database, MySQL, Servers, Web App Development