MySQL Bugs: #13370: Cannot start with innodb_buffer_pool

Bug #13370	Cannot start with innodb_buffer_pool_size > 924M
Submitted:	21 Sep 2005 8:09	Modified:	21 Sep 2005 8:53
Reporter:	Spyros Papantoniou	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.14	OS:	Windows (Winodws 2003 server SP1)
Assigned to:		CPU Architecture:	Any

Description:
Cannot use the full 4GB RAM of the server. Cannot setup the my.ini to use more that 924M for the innodb_buffer_pool_size
The error is:
050908 12:51:05  InnoDB: Error: cannot allocate 2147500032 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 23267012 bytes. Operating system errno: 8
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
050908 12:52:05 [ERROR] Can't init databases
050908 12:52:05 [ERROR] Aborting

swap space is 2x 4094M (on each disk) total 8192.

How to repeat:
use a 4GB RAM server, presently we use ML380p (3.0 GHz Xeon, one isntalled) with 4GB RAM. Dedicated database server ( only MySQL runs here) few connections, heavy database operations.

The server runs prensntly with 1.8GB RAM, cannot increase the MySQL parameters to use the full RAM.

my.ini:

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
4.1\my.ini"
[client]

port=3306

default-character-set=utf8

# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/MySQL Server 4.1/"

#Path to the database root
datadir="C:/MySQL/MySQL Server 4.1/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
default-character-set=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

max_connections=100

query_cache_size=484M

# section [mysqld_safe]
table_cache=1520

tmp_table_size=59M

thread_cache_size=38

#*** MyISAM Specific options
myisam_max_sort_file_size=100G

myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=80M

key_buffer_size=684M

read_buffer_size=64K
read_rnd_buffer_size=256K

sort_buffer_size=684k

#*** INNODB Specific options ***
innodb_data_home_dir="D:/MySQL Datafiles/"
#skip-innodb
innodb_additional_mem_pool_size=80M

innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=924M

innodb_log_file_size=190M
innodb_thread_concurrency=8

Hi!

I guess the maximum process size in Win 2000 / Xeon is somewhat less than 2 GB. In 32-bit computers, 2 GB is the normal maximum process size.

You have allocated 480 MB for the query cache. Make that smaller.

This is probably not a bug. You are just running out of the process size.

Regards,

Heikki

So if we were to use Linux, we could use the full 4GB?
would the server be faster?
many thanks for the prompt reply!
Spyros

On a system with 64-bit address space (such as AMD64, Intel EM64T, and the 64-bit systems by Sun, HP and SGI), InnoDB can use more than 4 gigabytes.

On many 32-bit GNU/Linux systems, a process cannot allocate more than 2 gigabytes of memory, because parts of the address space are reserved for other purposes, such as program text. On some 32-bit GNU/Linux systems (most notably Red Hat Linux), the limit has been raised to 3 gigabytes, if I remember correctly.