Help needed in Innodb buffer pool size increase issue

MySQL & MariaDB Other MySQL® Questions
1

Hi,

When I try to increase Innodb buffer pool size to anything higher than 1G in my RHEL3 box running on AMD(4 processor) 16G RAM, am getting the following error…I doubt if the operating system limits this…is there any chance that mysql needs to be recompiled with some configure option for it to take higher buffer memory size?

I have provided the error and some config options for your reference…

060904 9:46:38 InnoDB: Error: cannot allocate 3221241856 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 38243480 bytes. Operating system errno: 12
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
060904 9:47:38 [ERROR] Can’t init databases
060904 9:47:38 [ERROR] Aborting

% limit
cputime unlimited
filesize unlimited
datasize unlimited
stacksize 10MB
coredumpsize 0kB
memoryuse unlimited
maxproc unlimited
descriptors 65535
memorylocked unlimited
addressspace unlimited
maxfilelocks unlimited

% ulimit -a
cpu time (seconds) unlimited
file size (blocks) unlimited
data seg size (kbytes) unlimited
stack size (kbytes) 10240
core file size (blocks) 0
unlimited
processes unlimited
file descriptors 65535
locked-in-memory size (kb) unlimited
memory size (kb) unlimited
file locks unlimited

% free -m
total used free shared buffers cached
Mem: 15922 15888 33 0 73 14324
-/+ buffers/cache: 1490 14431
Swap: 16383 506 15876

% uname -a
Linux mysql-db.rhel3 2.4.21-31.smp #1 SMP Tue Apr 26 16:57:01 PDT 2005 i686 athlon i386 GNU/Linux

#########
Innodb related config in my.cnf

innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 1024M
innodb_data_file_path = ibdata1:30M:autoextend
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 300

innodb_data_home_dir = /var/ibdata
innodb_log_group_home_dir = /var/ibdata

Peter, congrats on setting up this excellent forum and blog…Good luck to your mysql consulting venture )

Regards,
Ram

2

Thank you.

% uname -a
Linux mysql-db.rhel3 2.4.21-31.smp #1 SMP Tue Apr 26 16:57:01 PDT 2005 i686 athlon i386 GNU/Linu

This is the root cause of your problems )

It is 32bit Linux which allows single process to only address 4G of RAM and access even less than that.

Best would be to install 64bit operation system - you would not be able to use 16GB of Ram effectively with Innodb in other case.

3

Thanks Peter,

Theoretically speaking 32-bit OS should allow me to access upto 4G but here I am unable to get 1M more than 1024MB for innodb buffer pool. I am gonna try porting this to RHEL4 and deploy 64-bit version of mysql. I am sure that is gonna do wonders to mysql performance )

Regards,
Ram

4

Different Linux versions (and even Kernels) may have different amount of memory allowed for user process. 4GB of address space can be split in 2:2 1:3 and some other configurations.

Some address space is also taken by the libraries.

Do you have any other variables large like key_buffer or query_cache ?

Normally on 1:3GB split with static binary (having GLIBC 2.2 which limits memory allocation to 2GB) I was able to get innodb buffer pool about 1.7GB or so.

I’m not sure why is it different in your case.