0

I'm on a 512MB Debian 7.5 server. I woke up to find mysqld not running, and the syslog reveals lines like:

Aug  9 09:00:58 web kernel: [1086155.903755] Out of memory: Kill process 2669 (m
ysqld) score 139 or sacrifice child
Aug  9 09:00:58 web kernel: [1086155.903920] Killed process 2669 (mysqld) total-
vm:448964kB, anon-rss:70396kB, file-rss:0kB
Aug  9 09:00:59 web mysqld_safe: Number of processes running now: 0
Aug  9 09:00:59 web mysqld_safe: mysqld restarted
[...]
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: The InnoDB memory heap is disabled
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Compressed tables use zlib 1.2.7
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Using Linux native AIO
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Initializing buffer pool, size = 128.0M
Aug  9 09:01:01 web mysqld: InnoDB: mmap(137363456 bytes) failed; errno 12
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Completed initialization of buffer pool
Aug  9 09:01:01 web mysqld: 140809  9:01:01 InnoDB: Fatal error: cannot allocate memory for the buffer pool
Aug  9 09:01:01 web mysqld: 140809  9:01:01 [ERROR] Plugin 'InnoDB' init function returned error.
Aug  9 09:01:01 web mysqld: 140809  9:01:01 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
Aug  9 09:01:01 web mysqld: 140809  9:01:01 [ERROR] Unknown/unsupported storage engine: InnoDB
Aug  9 09:01:01 web mysqld: 140809  9:01:01 [ERROR] Aborting
Aug  9 09:01:01 web mysqld:
Aug  9 09:01:01 web mysqld: 140809  9:01:01 [Note] /usr/sbin/mysqld: Shutdown complete
Aug  9 09:01:01 web mysqld:
Aug  9 09:01:02 web mysqld_safe: mysqld from pid file /var/run/mysqld/mysqld.pid ended
Aug  9 11:08:44 web mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql
[...]
Aug  9 11:08:44 web mysqld: 140809 11:08:44  InnoDB: Database was not shut down normally!
Aug  9 11:08:44 web mysqld: InnoDB: Starting crash recovery.
Aug  9 11:08:44 web mysqld: InnoDB: Reading tablespace information from the .ibd files...
Aug  9 11:08:44 web mysqld: InnoDB: Restoring possible half-written data pages from the doublewrite
Aug  9 11:08:44 web mysqld: InnoDB: buffer...

And then a bunch of warnings saying various tables were marked as crashed. Yet when I go into mysql and run check table <table name>;, it says it's ok.

It looks like something is misconfigured with regards to memory. Here's my /etc/mysql/my.cnf file which is pretty much out of the box:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer_size     = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

Any help would be hugely appreciated! This is a production server and I can't afford for the mysql server to just up and die randomly. Thanks!

4
  • More RAM is the obvious solution. Aug 9, 2014 at 19:09
  • No can do, it's a VPS. Aug 10, 2014 at 1:17
  • No can do? What do you mean? The fact that it's a VPS makes this really easy. Aug 10, 2014 at 1:23
  • Well, I'll rephrase: no can do without shelling out more cash. :) Which it looks like I might have to do.. Aug 10, 2014 at 1:39

1 Answer 1

1

Here is the [mysqld] section of your my.cnf without comments and blank lines

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address        = 127.0.0.1
key_buffer_size     = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
query_cache_limit   = 1M
query_cache_size        = 16M
expire_logs_days    = 10
max_binlog_size         = 100M

You are already running mysqld with the bare essentials for connections, the InnoDB Storage Engine, and per-connection memory settings (sort_buffer_size, join_buffer_size, read_buffer_size, read_rnd_buffer_size) because none of those options are set. Thus, they are running with default values.

What you posted from the syslog cannot point definitively to something else (some other process) in the OS is responsible. Notwithstanding, you simply need more RAM. I would confident rule out mysqld as the cause. It is just a victim of the Out-of-Memory scenario.

If you feel something deeper is involved for mysqld, look at the error log. It should have dumped a memory trace if a memory leak is part of the problem. Since you did not set the error log file in my.cnf, you should be able to find the error log in /var/log/mysql with the hostname as the prefix of the error log file.

4
  • I second that analysis... MySQL is almost certainly the victim, not the perpetrator, of extreme memory pressure, particularly if you're running Apache. The warnings of tables marked as crashed when MySQL is unceremoniously killed, and they are printed as MySQL attempts to clean up the crashed status (it only means that the tables weren't properly closed, not that they are necessarily corrupt), so checking them would reveal "OK" after that. Aug 9, 2014 at 19:46
  • Someone recommended using maria instead of mysql. Would that be more RAM-friendly? Aug 10, 2014 at 1:40
  • It makes no difference. A better MySQL (such as MarisDB) squeezed into 512M cannot escape this issue. Your current settings are not tuned to use more memory, so MySQL is as memory friendly as it is going get. You could get Percona Server (which has 30,000 lines of additional code for performance enhancements) and it will be just as choked for memory. Aug 10, 2014 at 2:55
  • I have another server (just for email) that is running mysql (along with SpamAssassin, amavis, Apache) and it runs perfectly under 512MB. I wonder what's different about the web server vs my mail server that's making mysql struggle. It is hosting a couple WordPress sites, maybe that's it? Aug 10, 2014 at 7:00

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .