Mysql – multiple requests shut down thesql

MySQLUbuntu

I have some problems with my mysql database.

I configured a server with the database and other services (apache, php) and put run multiple applications.

There are two problems.

Every day at the same time mysql fails, and I must go and restart it.
Every time I reload the website many times continuously mysql falls, and again and need to restart it.

Mysql logs show:

140107 0:09:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use$
140107 0:09:29 [Note] Plugin 'FEDERATED' is disabled.
140107 0:09:29 InnoDB: The InnoDB memory heap is disabled
140107 0:09:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140107 0:09:29 InnoDB: Compressed tables use zlib 1.2.3.4
140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140107 0:09:29 InnoDB: Completed initialization of buffer pool
140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140107 0:09:29 [ERROR] Plugin 'InnoDB' init function returned error.
140107 0:09:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140107 0:09:29 [ERROR] Unknown/unsupported storage engine: InnoDB
140107 0:09:29 [ERROR] Aborting
140107 0:09:29 [Note] /usr/sbin/mysqld: Shutdown complete

What's wrong with my database?

Best Answer

The comment from Barmar is correct.

You do not have enough memory in the server for the InnoDB Buffer Pool.

Since you got the message

140107 0:09:29 InnoDB: Initializing buffer pool, size = 64.0M

followed by

140107 0:09:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool

you should lower innodb_buffer_pool_size below 64M in /etc/my.cnf and restart mysql.

SUGGESTION: If it is withn your budget, you should setup Apache and PHP on a separate server. Connection all webserver requests to the DB thereafter.

Related Solutions

Mysql – Having Problems Installing 64-bit MySQL 5.5.8 x86 on OS X 10.6.6

Fixed it. Listening to /usr/local/mysql/support-files/mysql.server: line 256: my_print_defaults: command not found, I took a peek inside. Turns out this is the script that's called when the startup item or the preference pane want to start or stop MySQL. Unfortunately, it doesn't seem to work out of the box anymore.

The comments near the top of the script seem to know this, and give you a list of options to make the script work. I took the easiest and added the installation path to the basedir variable assignment.

MySQL – How to Safely Change innodb_log_file_size

Yes it is safe to delete the log file once mysqld has been shutdown

In light of this, just perform the following steps:

mysql -uroot -p... -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start

Starting up mysqld will recreate ib_logfile0 and ib_logfile1

Give it a Try !!!

UPDATE 2011-10-20 16:40 EDT

It cleanly page out all data in the InnoDB Buffer Pool prior to redoing the Log Files, you should set this option about 1 hour before shutdown:

SET GLOBAL innodb_max_dirty_pages_pct = 0;

By default, innodb_max_dirty_pages_pct is 75 (MySQL 5.5+) or 90 (prior to MySQL 5.5). Setting this to zero keeps the number of dirty pages under 1% of the InnoDB Buffer Pool. Performing service mysql stop does this anyway. In addition, a shutdown will finish up any remaining items in the redo log. To keep to this option just add it to /etc/my.cnf:

[mysqld]
innodb_max_dirty_pages_pct = 0

UPDATE 2013-04-19 16:16 EDT

I updated my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.