0

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.

  • 5.1 is antique; plan on upgrading to 5.5, then 5.6, then 5.7. – Rick James Oct 27 '16 at 22:33
0

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)
  • Yes My Mysql version is 32 bit it looks like this : ia32 , any solution of this problem now? Because i cant re-install mysql on my server. – Mahesh Vema Oct 27 '16 at 11:52
  • you can not increase InnoDB memory more than that, you can have multi installation as cluster but the easiest solution is backup your database and move it to 64 bit version – Ahmad Abuhasna Oct 27 '16 at 11:57
  • 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 . – Mahesh Vema Oct 27 '16 at 11:57
  • its around 1.8G, can not recall the exact number – Ahmad Abuhasna Oct 27 '16 at 12:01
  • Can I upgrade my same MYSQL to 64 bit . I means if i upgrade it directly then it may currupt my database. I will take backup first and then upgrade my mysql. Also tell whether i have to again do its replication to other server or will go like same like now ? – Mahesh Vema Oct 27 '16 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.