MySQL Bugs: #68470: On 64 bit Windows Server 2012 InnoDB is not allocating more than 32GB Mem

Bug #68470	On 64 bit Windows Server 2012 InnoDB is not allocating more than 32GB Mem
Submitted:	23 Feb 2013 7:07	Modified:	9 Apr 2013 6:30
Reporter:	Salman Akram	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Windows (Server 2012)
Assigned to:		CPU Architecture:	Any
Tags:	64bit, innodb, mem, windows

Description:
I have searched a lot for this bug but seems everyone just had it when they were using 32 bit machine. 

I upgraded to MySQL 5.6 and cannot allocate more than 32GB ram to MySQL/InnoDB. Basically I cannot give 'innodb_buffer_pool_size' a value of more than 28/29GB. It seems around 2/3GB is kept for rest of the buffers/threads so in total it doesn't allocate more than 32GB to MySQL. I can see this on Windows Task Manager (Commit Size). Beyond 32GB it starts behaving weirdly. Like either allocating very less mem (4-8GB) OR allocating just 32GB (this happened once and in this case after sometime it crashed with a mem error and corruped my db too but unfortunately I don't have those logs now) OR MySQL just doesn't start with the below error:

2013-02-23 01:32:23 11224 [Note] InnoDB: The InnoDB memory heap is disabled
2013-02-23 01:32:23 11224 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-02-23 01:32:23 11224 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-02-23 01:32:23 11224 [Note] InnoDB: CPU does not support crc32 instructions
2013-02-23 01:32:23 11224 [Note] InnoDB: Initializing buffer pool, size = 48.0G
InnoDB: VirtualAlloc(0 bytes) failed; Windows error 87
2013-02-23 01:32:23 11224 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2013-02-23 01:32:23 11224 [ERROR] Plugin 'InnoDB' init function returned error.
2013-02-23 01:32:23 11224 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2013-02-23 01:32:23 11224 [ERROR] Unknown/unsupported storage engine: INNODB
2013-02-23 01:32:23 11224 [ERROR] Aborting

System Specs: 
OS: Windows Server 2012
RAM: 128GB
MySQL: 5.6.10

With a 'innodb_buffer_pool_size' value of 24GB below is the log with proper initialization.

2013-01-11 07:10:42 9640 [Note] InnoDB: The InnoDB memory heap is disabled
2013-01-11 07:10:42 9640 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-01-11 07:10:42 9640 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-01-11 07:10:42 9640 [Note] InnoDB: CPU does not support crc32 instructions
2013-01-11 07:10:42 9640 [Note] InnoDB: Initializing buffer pool, size = 24.0G
2013-01-11 07:10:45 9640 [Note] InnoDB: Completed initialization of buffer pool

I thought maybe its due to 'innodb_buffer_pool_instances' variable but that I tried with both 1/8 values and still the error is same.

Note: On same server I was able to allocate 60GB with MySQL 5.0. Couldn't go beyond 64GB there too because of a Windows/MySQL known limitation reported for the way MySQL 5.0 handles thread.

How to repeat:
1- Install MySQL 5.6.10 64 bit on Windows Server 2012 with 128GB RAM
2- Allocate 'innodb_buffer_pool_size' a value more than 32GB
3- Start MySQL

Suggested fix:
I have to keep it below 32GB to get it working. Can't just find any other way out and just wasting all the extra RAM in the server.

http://msdn.microsoft.com/en-us/library/windows/desktop/ms681382(v=vs.85).aspx

ERROR_INVALID_PARAMETER
87 (0x57)
The parameter is incorrect.

The problem is:
mem_size = ut_2pow_round(mem_size, UNIV_PAGE_SIZE);

To repeat, try make a single buffer pool of 4G. It feels.

e.g:
--innodb-buffer-pool-size=4G  --innodb-buffer-pool-instances=1
--innodb-buffer-pool-size=8G  --innodb-buffer-pool-instances=2

So am I supposed to do anything or that's just a comment for MySQL folks? Are you suggesting me to try with 16 buufer pool instances and 64GB RAM, would that work?

Salman, a workaround can be setting a combination of innodb_buffer_pool_size and innodb_buffer_pool_instances so that each instance will be less than 4G in size.

You should be able to set any size (depending on hardware) if you add to my.ini:

innodb_buffer_pool_instances=64

Seems to work! However, with 16 cores and 32 logical processors (virtualization enabled) what would suggest to be the right value for buffer pool instances?

Server default for innodb_buffer_pool_instances is 8 and that is often a good value. To find the best value, experiment between the minimum number needed to get the RAM allocated and 16 to see which is fastest. Then use whatever value is fastest.

The problem you're experiencing is a bug that limits an internal value to 32 bits even with 64 bit Windows builds.

[8 Apr 2013 12:21] Daniel T Price

Added changelog entry for 5.6.12, 5.7.2:

"On 64-bit Windows builds, "INNODB_BUFFER_POOL_SIZE" would not accept an allocation of more than 32GB. This limitation was due to a bug that truncated the
internal value for the InnoDB buffer pool size to 32 bits on 64-bit Windows builds."

I am facing similar issue. MySQL not using more than 32GB.

---------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 32967229440; in additional pool allocated 0
Dictionary memory allocated 11251190
Buffer pool size   1966080
Free buffers       8192
Database pages     1805453
Old database pages 666301
Modified db pages  17019
Pending reads 0
Pending writes: LRU 0, flush list 1 single page 0
Pages made young 8297666, not young 1008507067

Can I assign lets say 10*8G chunks of memory for buffer pool instances.
What should be ideal size for each buffer pool instance??