MySQL, Windows 32-bit, and /3GB

One question that comes up relatively commonly in support is how much memory can MySQL use on my system. As you may know, MySQL runs in a single process with many threads. So the memory limit it has is based upon the operating system limits for a single process. Different operating systems have different limits, but the one I want to discuss is Windows 32-bit (such as 2000, XP, 2003, vista).

The short answer is that your mysqld-nt.exe can use up to ~1.8G of RAM. The question is why is that so. Since we have a 32-bit address space, in theory there is 2^32 mapping for the address space (4G). However, Windows uses what is called a 2G/2G split in that addressable space. 2G are allowed for the actual application to use and 2G is for the kernel mappings.

This 2G is then the address space of points, which normally ends up with around 1.8G of memory really being usable. If you try to use more than this, then you end up getting Out of Memory errors from MySQL.

Now Windows 2000 and above has a way you can adjust this split. Not all versions of Windows within these families support this, so it depends upon what version you are using. The specific feature is called '4 GT RAM Tuning' by Microsoft.

To adjust this, you will need to do a few steps, to enable it on Windows and then within MySQL.

First, you need to boot Windows with the /3GB flag given to it. You will need to edit the boot.ini, find the instance you are booting off of, and add that flag to it.

Second, you will need to enable for any program that wants to use this split. MySQL does not ship with this done by default (it is normally a compile time option), so we will need to manually set it (or compile your own mysqld-nt.exe on Windows).

There is a program that comes with Visual Studio C++ called 'editbin'. I use the express version which is free. There is a /LARGEADDRESSAWARE flag you can pass to it to enable the option for your server. I am running this via the Visual Studio Command Prompt.

C:\Program Files\MySQL\MySQL Server 5.0\bin>editbin /LARGEADDRESSAWARE mysqld-nt.exe
Microsoft (R) COFF/PE Editor Version 8.00.50727.42
Copyright (C) Microsoft Corporation.  All rights reserved.

To confirm that this works, you can use dumpbin /HEADERS and there is an option in the very top which says if it is enabled or not.

C:\Program Files\MySQL\MySQL Server 5.0\bin>dumpbin /headers mysqld-nt.exe
Microsoft (R) COFF/PE Dumper Version 8.00.50727.42
Copyright (C) Microsoft Corporation.  All rights reserved.


Dump of file mysqld-nt.exe

PE signature found

File Type: EXECUTABLE IMAGE

FILE HEADER VALUES
             14C machine (x86)
               6 number of sections
        487FA5AC time date stamp Thu Jul 17 16:03:56 2008
               0 file pointer to symbol table
               0 number of symbols
              E0 size of optional header
             12F characteristics
                   Relocations stripped
                   Executable
                   Line numbers stripped
                   Symbols stripped
                   Application can handle large (>2GB) addresses
                   32 bit word machine

Notice the second to last line, "Application can handle large (>2GB) addresses". This means it is enabled for the binary, if it is missing, then the binary does not have it enabled.

Once these two things are done you will now have a 3G address space for MySQL, and you should be able to go up to ~2.6G or so. You are still limited to smaller allocations (ie, you can't set innodb_buffer_pool_size=2.5G), but it can give you more memory for handling connections and per-connection buffers which can effectively allow you to increase the large global buffers.

For more details, I would like to recommend you read the technet blog about the topic, it is quite a good explanation.