Mysql – Not Able to Increase InnoDB Buffer Pool Size in Mysql

my.iniMySQLmysql-5.1windows

I am facing a very rare problem with my DB. I have Windows Server with 64 GB RAM but I am not able to increase innodb buffer pool size in mysql.

Current InnoDb buffer pool Size : 1.6G

When I increase it and restart Mysql then it throws Error :

161026  0:52:30 [Note] Plugin 'FEDERATED' is disabled.
161026  0:52:31  InnoDB: Initializing buffer pool, size = 2.6G
161026  0:52:31  InnoDB: Error: cannot allocate 2816491520 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 22604620 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: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
161026  0:53:31InnoDB: Fatal error: cannot allocate the memory for the buffer pool
161026  0:53:31 [ERROR] Plugin 'InnoDB' init function returned error.
161026  0:53:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161026  0:53:31 [ERROR] Unknown/unsupported table type: INNODB
161026  0:53:31 [ERROR] Aborting

I dont have 32 bit OS but my MYSQL is 32 bit . I have 64 bit OS on my Windows server.

Memory space is limited to 2 GB or 4 GB. For 32 bit Mysql but I am not able to make it 2 GB, my Innodb is only 1.6 GB .

Increasing size of Innodb is very necessary because i am getting following error in my error log :

MySql.Data.MySqlClient.MySqlException: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

What I have Already Tried :

  1. I confirmed that My my.ini has no issue .
  2. I restrted My mysql many times but innodb size not increasing.
    At last I got from somewhere that Memory allocation to Mysql in my OS is very less thats why OS is not increasing its innodb size.
  3. I increased virtual memory of windows. It was 8GB before and I increased to to 16 GB. ( Did this change just now and dont know its result because it requires server restart and before 7 hours I cant restart my Server) – Do you think this can solve my problem ?

MySQL Version : 5.1

If anyone know the solution then let me know, i will be very thank ful to you.

Best Answer

Since the OS you are using is 64 bit, you need to make sure MySQL version you are using is 64 bit not 32 bit, how to get those information:

show variables like 'version_compile_machine';

If MySQL version is 32 bit then you will have results like

+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| version_compile_machine | i386  |
+-------------------------+-------+
1 row in set (0.00 sec)

Note MySQL 32 bit has limitation, you can not increase the memory to more than 1.8 to 2G.

If MySQl version is 64 bit then you will have the following results

    +-------------------------+---------+
    | Variable_name           | Value   |
    +-------------------------+---------+
    | version_compile_machine | x86_64  |
    +-------------------------+---------+
    1 row in set (0.00 sec)
Related Question