Wednesday, February 18, 2009

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.

10 comments:

  1. Hi!

    interesting post. So let me get this straight: this is a tool from Microsoft that directly *alters a binary executable*?

    It sounds pretty scary. How does this tool know what bytes to tweak? I read the technet post and it says something about the "image header". Does this only work with executables compiled by Visual C++? Or is it something about Windows binary executables in general?

    TIA,

    Roland

    ReplyDelete
  2. Correct. It modifies some of the capability flags of the executable in its header.

    From what I understand, the flag is part of the standard windows binary format of the executable, so it should work regardless of where the binary was created.

    This format is pretty well documented, some good locations about it:

    http://en.wikipedia.org/wiki/Portable_Executable
    http://support.microsoft.com/kb/65122

    ReplyDelete
  3. I do not think a signed executable is easily modifyable this way. There is a link option /LARGEADDRESSAWARE we could use. Yet, I do not see a compelling reason to do it. For serious work with mysql where buffers get gigabytes large, 64 bit Windows and 64 bit mysql should be used. Both available and do really well.

    /3GB boot option makes Windows non-pages pool small and takes away space from sockets (among other things). And socket calls can relatively easily start to fail with WSAENOBUFS. So even if /3GB would reduce address space pressure for the application, it increases this pressure on kernel. All in all, the option can be seen as a workaround before 64 bit Windows became common. I'm quite sure it is now common in all shops that do serious work with Windows.

    ReplyDelete
  4. wlad: I agree that it would be great for everyone to use 64-bit MySQL on Windows if they need > 2G of memory for MySQL use. It is certainly a much better solution that using this flag.

    However, it is a question that I have seen asked repeatedly to MySQL support, particularly since 4GB of RAM on a 32-bit system is extremely common now. Buying new hardware isn't always an option and you have to make due with what you have.

    ReplyDelete
  5. hmm. if requests are so common, I wonder if it should be filed as a bug, as

    - editbin'ing signed executables is not cosher
    (I have not tried it, but would be interested in results, what happens to signature if file is tampered)
    - support would not need to explain the structure of PE executables to customers
    - the fix is just a single compile flag

    ReplyDelete
  6. Thanks so much for this information because I have MySQL system but I didn't know some characteristics about it, now I can use the application in the correct way.Besides my friend Sildenafil Citrate told me you going to release the new one MySTLF Is that true?

    ReplyDelete
  7. Wow, nice post,there are many person searching about that now they will find enough resources by your post.Thank you for sharing to us.Please one more post about that..Janet

    ReplyDelete
  8. The MySQL development project has made its source code available under the terms of the GNU General Public License, as well as under a variety of proprietary agreements. MySQL was owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now owned by Oracle Corporation.

    For more information click here

    ReplyDelete