1

I've been having an issue with MySQL crashing randomly, apparently as a result of not having enough available memory (as discussed here: https://www.digitalocean.com/community/questions/mysql-on-ubuntu-keeps-crashing).

I increased my server from 4GB to 8GB of RAM (and have also added a 512MB swap), which seems to have helped with the crashing, but when I look at the server, MySQL is still consuming an absurd amount of memory. Is this normal? How can I prevent this from happening?

Here is a screenshot from htop: https://nick.do/images/htop.png

Why would there be so many instances of MySQL taking up 246M each?

Here's what my my.conf file looks like:

#
# 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            = *
#
# * Fine Tuning
#
key_buffer              = 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         = 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 log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# 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
secure-file-priv = /var/tmp



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

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

[isamchk]
key_buffer              = 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/

Thanks!

  • The multiple mysqld entries you see in the htop output are probably threads that reference the same memory, so MySQL seems to be using about 1.8 G. Apache entires, on the other hand, are likely regular processes, there are 18 of them using upwards of 350 M each, to the total of over 6 G. Generally it's not very wise to run your web server(s) and the database server on the same machine. – mustaccio Mar 17 '16 at 16:15
1

Some of the "top" numbers are misleading. That is the same 246M shared by all of the instances of mysqld.

SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; -- that is the main thing that should be occupying memory.

What is in mysqld.err?

What evidence do you have that it is a memory problem? And what says that mysqld is at fault?

Does this version of unix have a swappiness setting? If so, I think "1" is the optimal value.

  • When I do this: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; I get this result: innodb_buffer_pool_size | 134217728 Am I not interpreting top vs htop correctly? When I look at "top," it looks like this (says almost all of my memory is being used) When I look at "htop," it looks like this. Am I just being confused by the "buffers" in the "top" screenshot? When I restart MySQL "top" shows much less memory being consumed, then it eventually builds back up. – Nick Apr 28 '15 at 4:06
  • This is what my MySQL error log looks like in /var/log/mysql/error.log – Nick Apr 28 '15 at 4:13
  • I am unfamiliar with htop, but note how both say 247M, once in top; repeatedly in htop. Swapping would be bad for mysql; I see essentially none. (The 23M of swap is probably not of concern.) 134M for buffer_pool says it is not a big consumer. It is probably included in the 247M. Get OS help for understanding top/htop. Sort the top by RES. Extra RAM is usually used for I/O buffering. – Rick James Apr 28 '15 at 16:17
  • The error.log says nothing exciting. The IP problem is apparently non-fatal. It says "Normal shutdown". Check for cron jobs firing about 17:23. – Rick James Apr 28 '15 at 16:19
0

Your memory is cached. System is consuming it, not MySQL. On your top image:

Mem: 8177776k total, 8039108k used, 138668k free, 600888k buffers, 5138768k cached

This may be caused by intensive I/O, for example. See this answer for details and some explanation.

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.