The Problem

If you just bought a brand new server with 4 GB of RAM hoping to speed up your MySQL 5.0.27 database you might be disappointed! By default, 32-bit Windows XP Professional and Windows Server 2003 cannot allocate more than 2GB RAM per process. Nor can the 32-bit MySQL/InnoDB binaries distributed by MySQL. Having 4 GB of physical RAM on the box, your MySQL instance can only use around 1.5 GB.

As I have previously mentioned in my article about MySQL SNMP monitoring, MySQL continues to be extremely valuable tool in many e-commerce projects. Not without its quirks, MySQL is capable, reliable, fast and free. The fact that it's also open source and I can recompile it makes me like it even more.

The Solution

In order to get around the Windows 2 GB limitation, modifications have to be made both to Windows' configuration and to InnoDB. To use the memory over 2GB MySQL relies on a special feature of Intel processors (PAE) and Windows operating system (AWE). PAE is an Intel-provided memory address extension that enables support of up to 64 GB of physical memory for applications running on most 32-bit. Support for PAE is provided under Windows 2000 and 32-bit versions of Windows XP and Windows Server 2003. PAE allows the most recent IA-32 processors to expand the number of bits that can be used to address physical memory from 32 bits to 36 bits through support in the host operating system for applications using the Address Windowing Extensions (AWE) application programming interface (API).

Thanks to Heikki Tuuri, the author of InnoDB storage engine, the InnoDB can use all the RAM you can buy. For this to work, MySQL (and InnoDB engine) will have to be recompiled, since MySQL 5.0.x standard distributions don't have this feature enabled. There are several forum posts where Heikki describes briefly how it to be done, but without any details. Recently we had this problem and wrote down more or less exact steps of how to do it.

The whole process took under an hour. It worked without any glitches the first time we tried. Before you begin, make sure that you have:

• to deploy: Microsoft Windows XP Professional or Microsoft Windows Server 2003 (any version except the Web Edition) on a computer with at least 4GB RAM and PAE support (available on Intel)
• to compile: Microsoft Windows XP, Windows 2000, or higher operating system on a computer with at least 3GB of free disk space
• Microsoft Visual Studio 7.1 or higher development environment with support for C++

Recompiling MySQL to support over 2 GB of RAM

Follow these steps:

• download MySQL 5.0.27 source from http://www.mysql.com/
• unpack downloaded source to a working directory (WORKDIR)
• open mysql.sln in a current version of Visual Studio (at least Visual Studio 7.1)
• set build properties to "nt"
• add following conditional define to the innobase project

__WIN2000__

• build entire solution
• if you want to test the new AWE-enabled binaries on a computer which does not have the AWE API, you can compile with UNIV_SIMULATE_AWE defined in this in innobase\os0proc.c.
• create installation directory and copy the MySQL executables to it

d:\MOO_NODE\bin> mkdir mysql-5.0.27-awe
d:\MOO_NODE\bin> cd ..\..\WORKDIR
d:\WORKDIR> copy client_release\*.exe D:\MOO_NODE\bin\mysql-5.0.27-awe\bin
d:\WORKDIR> copy client_debug\mysqld.exe D:\MOO_NODE\bin\mysql-5.0.27-awe\bin\mysqld-debug.exe
d:\WORKDIR> xcopy share\*.* D:\MOO_NODE\bin\mysql-5.0.27-awe\share /E

Configure MySQL to use over 2 GB of RAM

On the computer on which you wish to deploy, modify mysql.ini file. Follow these steps:

• set the size of the InnoDB buffer pool, in megabytes, to be placed in the AWE memory, for e.g.,

set-variable=innodb_buffer_pool_awe_mem_mb=2500
set-variable=innodb_buffer_pool_size=500M

• the maximum possible value for innodb_buffer_pool_awe_mem_mb is 63000 (63 GB)
• for more information, refer to the MySQL 5.x documentation for innodb_buffer_pool_awe_mem_mb

Configure Windows to use over 2 GB of RAM

On the computer on which you wish to deploy, configure Windows to use more than 2GB per process by modifying the boot.ini file by adding the /3GB flag which permits large address aware applications to use 3 GB of virtual address space (see Microsoft article for more information). Follow these steps:

• save a backup copy of the original boot.ini file
• modify the boot.ini file per the following example showing a boot.ini file with the /3GB flag added

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="????" /3GB

• replace "????" with the appropriate operating system version (e.g., "Windows XP Professional")
• you will have to restart Windows for this modification to take effect

Allow locking of memory pages for user under which MySQL will be running. Follow these steps:

• as a user with administrative priviliges, navigate to the Local Security Policy settings (Start-->Programs-->Administrative Tools-->Local Security Policy) and, in the User Rights Assignment folder, open the Lock Pages in Memory policy
• add the user under whom MySQL will be running to the list of users allowed to lock memory pages
• you will have to restart Windows for this policy to take effect

The Final Word

While all appears to work great, the custom MySQL build described here might not be suitable for your needs. It works great for our off-line analytical applications, OLAP, data-mining – all those things that can benefits from a lot of RAM, but it might not work you. We recommend that you use the official MySQL binaries distributed as part of MySQL release, unless you absolutely have to use custom compilation.