Ever had the following error?
110307 12:01:31 mysqld_safe mysqld from pid file /data/mysql/sinscope-vm11.pid ended 110307 12:02:00 mysqld_safe Starting mysqld daemon with databases from /data/mysql 110307 12:02:00 InnoDB: Error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 35493848 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: 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... InnoDB: Fatal error: cannot allocate the memory for the buffer pool 110307 12:03:00 [ERROR] Plugin 'InnoDB' init function returned error. 110307 12:03:00 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed. 110307 12:03:00 [Note] Event Scheduler: Loaded 0 events 110307 12:03:00 [Note] /usr/local/mysql/libexec/mysqld: ready for connections. Version: '5.1.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Source distribution
This is typically cause by trying to allocate more memory to the InnoDB buffer cache mechanism than there is memory available in the machine.
The fix? Edit the /etc/my.cnf file and change the following value to 80% (or less) of total physical memory:
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size = 1G
The description in the file itself is pretty good, so I won’t try and re-invent the wheel and also explain it here.