MySQL is a cross-platform piece of software, with versions available for all major operating systems and even embedded devices. Many enterprise-level servers nowadays are 64-bit hardware and operating system combinations. However, at the time of writing this book, there's still a large number of 32-bit systems in active use.
One of the major limitations of 32-bit systems is their inherent limit of a maximum of 4 GB address space per process, meaning that no process on such a system can ever address more than 4 GB of memory. However, on typical 32-bit operating systems, there is an ever lower limit in place. On Windows, this address space is split in half: 2 GB for the application and 2 GB of reserved addresses for kernel use. In this half of the address space, there are areas reserved for all sorts of hardware interfaces including graphics cards and extension cards—precious addresses that cannot be used to address bytes in RAM chips.
In effect this means that any process—including a MySQL server—can use at most 2 GB of combined RAM on a 32-bit Windows system. This is even true for systems running, for example, Windows Server Enterprise Edition, which supports much more physical memory in a machine using some clever trickery. Even though in total they can support many Gigabytes of memory, the per-process limits still apply.
For a typical InnoDB-centric MySQL configuration, you would usually assign most of the available 2 GB per process to the InnoDB buffer pool (configuration setting innodb_buffer_pool_size). However, you will often notice that even though the total of all buffer sizes is less than this limit, you might still end up with error messages, when trying to start the server, that look like this:
In this example, we tried a 1,500 MB buffer pool size. This kind of problem is regularly caused by memory fragmentation. InnoDB tries to allocate a large, contiguous area of the address space for the buffer pool here. However, even before this takes place, the operating system may have loaded shared libraries (DLLs) into the 2 GB user address space available to the process (0x00000000 - 0x7FFFFFF) effectively splitting the available address range, so that InnoDB cannot get a large enough contiguous chunk for its buffer pool. Such libraries can be part of anti-virus solutions or management suites that need to hook on to all processes in a sys
The freely available Sysinternals ProcessExplorer tool (downloadable from Microsoft's website at http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx) can help you find out details in situations like this:
In the previous screenshot, you can see a MySQL process that has its address space fragmented by two DLLs (see the lower pane)—one part of Sophos Antivirus and the other part of the NetInstall software management suite. Both DLLs take up only a small amount of memory for themselves; however, their position in the address space makes them a problem for InnoDB. The Sophos Buffer Overrun Protection Library is loaded at address 0x552F0000. This is only 1,358 MB from the start address of mysqld-nt.exe (0x40000) and prevents a 1,500 MB block from being assigned. If they were located at the far end of the address range, InnoDB could allocate a block of memory large enough to function. Compare this with the next picture where the anti-virus software has been removed from the system:
In this setup, InnoDB can start just fine because there is a contiguous range free from 0x400000 to 0x641F0000, sized 1,597 MB. See the following screenshot showing the InnoDB status output section (produced by entering SHOW ENGINE INNODB STATUS\G on a MySQL command-line) telling you about the number of 96,000 available 16 KB pages in the buffer pool. This is exactly the 1,500 MB the configuration variable innodb_buffer_pool_size is set to in this server's my.ini file.
Tip
Usually, we would recommend a switch to more capable 64-bit hardware and operating system setups if these memory limits become an issue. However, there might sometimes be external factors preventing that.
If you hit a problem like this, you have to try to decide whether either you can get rid of the problematic libraries completely (by not using the program they are associated with), or you should contact their respective vendors to find out if they can provide a different version of the same DLL that gets loaded into a more convenient address range.
Note
Warning!
Editing the registry can severely damage your system setup, up to the point of not being able to boot or access it at all; so make sure you know what you are doing. Get in contact with any software vendor whose libraries you disabled to get clearance to try this; some libraries might be vital for your system to function!
We strongly recommend making a system backup before you proceed with this!
For testing, you can modify the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Windows\AppInit_DLLs registry key to control which DLLs get loaded when a process starts. After making sure this will not affect the stability of your system, remove the DLLs in question from this key and restart the MySQL server. Verify if you can now use a larger amount of memory.
To get even more memory available to MySQL on 32-bit systems, you might consider using the /3GB boot parameter. This parameter, which can be added to the boot.ini file, will tell Windows that you intend to run programs that need more than 2 GB of address space.
What it will do is present programs that are prepared for it (they need to be compiled with support for this) with a 3 GB address range for application use and reserve only 1 GB for kernel purposes. MySQL versions 5.0.79 and up and 5.1.33 and up are compiled to benefit from this configuration. However, using it can adversely affect your system in other ways. Refer to the Microsoft website or their support for more information.
Also note that this will not enable you to allocate more than 2 GB to the buffer pool alone; however, the MySQL server could use the extra space for other buffers.
The following screenshot was taken on a Windows machine that was booted with the /3GB option in place and shows a MySQL server process using more than 2 GB:
This is the InnoDB status output showing a 1,750 MB buffer pool on a 32-bit system:
