I'm investigating windows 32bit memory problems (the 2G limit) and am after a test which will max out MySQL's memory including InnoDB Buffer Pool, per connection memory and/or any other uses of memory.
Perhaps a query I could use for
I thought other people would benefit from a simple way to load test mysql and this was an easy question where lots of people would have "tools". They would help my obscure situation and I could use to investigate our real problem.
We have our configuration tuned, and it was fine for months but recently we eventually get
mysqld: Out of memory (Needed 220428 bytes) and MySQL will crash minutes later.
I'm not researching your usual "whoops I allocated too much to my buffer pool" problem. That memory is allocated at start up and improper setting causes immediate shutdown. I'm really after the other 20%. Just creating the maximum number of connections with a high buffer pool hasn't proven to reproduce the problem so I want to stress everything. I am not ruling out stressing the buffer pool in case we are actually suffering a MySQL bug relating to it.
Here is an information dump for people interested:
/3GB to overcome 2GB for mysql - we haven't set
/LARGEADDRESSAWAREbut we do have
/PAEwith 8gb memory
We are at risk of low
System Page Table Entriesbut last we saw over 18,000 (not a lot but enough, it stayed at this level at last crash)
Mysql keep fixing memory related errors in their change logs
Mysql 5.1 let you put the inndb buffer pool in Address Windowing Extensions (i.e. use PAE) with innodb_buffer_pool_awe_mem_mb but MySQL 5.5 has no such option
I'm no authority on any of the above I just want to be able to produce high memory activity so we can A) reproduce the problem and then B) solve it in the medium term before moving off 32bit, which is the real solution but it won't happen tomorrow.
Turns out Mysql 5.5's mysqld.exe is already built to be
/LARGEADDRESSAWARE but cannot set the innodb engine size equal to or greater than 1600M. Connections do use the extra memory from
/3GB (proven with mysqlslap).
dumpbin.exe /headers mysqld.exe
Microsoft (R) COFF/PE Dumper Version 10.00.40219.01
Copyright (C) Microsoft Corporation. All rights reserved.
Dump of file mysqld.exe
PE signature found
File Type: EXECUTABLE IMAGE
FILE HEADER VALUES
14C machine (x86)
5 number of sections
4E6A3CEF time date stamp Sat Sep 10 04:21:03 2011
0 file pointer to symbol table
0 number of symbols
E0 size of optional header
Application can handle large (>2GB) addresses
32 bit word machine
My conclusion. If you don't use
/3GB or otherwise set connection memory + innodb buffer memory greater than available memory you are bound to eventually get
mysqld: Out of memory... although mysql seems to do it's best to reject connections to avoid this problem.
Take home message, test that you can actually achieve
max_connections with mysqlslap and the rest comes down to maintaining OS memory (and other resources like free system PTE).