[SOLVED] MySQL 5.1.66 crashes because Out Of Memory

dhuyvetter · 06-25-2013, 09:27 AM

The MySQL on my VPS crashes every day since a couple of days, looking at my syslog, it seems that the reason is an Out Of Memory error. Should I look at finetuning MySQL or does my server need more memory? I currently have 512MB on my VPS.

Here is what free -m says:

Code:

             total       used       free     shared    buffers     cached
Mem:           512        360        151          0          0         42
-/+ buffers/cache:        317        194
Swap:            0          0          0

And top:

Code:

top - 14:17:47 up 15 days,  9:41,  2 users,  load average: 0.13, 0.30, 0.42
Tasks:  58 total,   1 running,  57 sleeping,   0 stopped,   0 zombie
Cpu(s): 19.7%us,  2.7%sy,  0.0%ni, 77.6%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:    524508k total,   415200k used,   109308k free,        0k buffers
Swap:        0k total,        0k used,        0k free,    72404k cached

Here's the relevant part from my syslog:

Code:

Jun 23 21:19:01 vps528 kernel: [1182395.730776] mysqld invoked oom-killer: gfp_mask=0x1201d2, order=0, oomkilladj=0
Jun 23 21:19:01 vps528 kernel: [1182395.730789] Pid: 13220, comm: mysqld Not tainted 2.6.24-19-xen #1
Jun 23 21:19:01 vps528 kernel: [1182395.730791] 
Jun 23 21:19:01 vps528 kernel: [1182395.730792] Call Trace:
Jun 23 21:19:01 vps528 kernel: [1182395.730802]  [<ffffffff80275e26>] oom_kill_process+0xf6/0x110
Jun 23 21:19:01 vps528 kernel: [1182395.730806]  [<ffffffff802762ae>] out_of_memory+0x19e/0x1e0
Jun 23 21:19:01 vps528 kernel: [1182395.730810]  [<ffffffff80278bd9>] __alloc_pages+0x389/0x3c0
Jun 23 21:19:01 vps528 kernel: [1182395.730815]  [<ffffffff8027af54>] __do_page_cache_readahead+0x104/0x260
Jun 23 21:19:01 vps528 kernel: [1182395.730818]  [<ffffffff8027520e>] filemap_fault+0x2de/0x3e0
Jun 23 21:19:01 vps528 kernel: [1182395.730822]  [<ffffffff80281f3a>] __do_fault+0x6a/0x5d0
Jun 23 21:19:01 vps528 kernel: [1182395.730824]  [<ffffffff80287551>] handle_mm_fault+0x1d1/0xd60
Jun 23 21:19:01 vps528 kernel: [1182395.730829]  [<ffffffff80229f63>] set_next_entity+0x23/0x50
Jun 23 21:19:01 vps528 kernel: [1182395.730835]  [<ffffffff80473553>] do_page_fault+0x1f3/0x11e0
Jun 23 21:19:01 vps528 kernel: [1182395.730840]  [<ffffffff8046f7e9>] thread_return+0x3a/0x481
Jun 23 21:19:01 vps528 kernel: [1182395.730845]  [<ffffffff80471bf7>] error_exit+0x0/0x79
Jun 23 21:19:01 vps528 kernel: [1182395.730848] 
Jun 23 21:19:01 vps528 kernel: [1182395.730850] Mem-info:
Jun 23 21:19:01 vps528 kernel: [1182395.730851] DMA per-cpu:
Jun 23 21:19:01 vps528 kernel: [1182395.730853] CPU    0: Hot: hi:    0, btch:   1 usd:   0   Cold: hi:    0, btch:   1 usd:   0
Jun 23 21:19:01 vps528 kernel: [1182395.730855] DMA32 per-cpu:
Jun 23 21:19:01 vps528 kernel: [1182395.730857] CPU    0: Hot: hi:  186, btch:  31 usd: 172   Cold: hi:   62, btch:  15 usd:  14
Jun 23 21:19:01 vps528 kernel: [1182395.730861] Active:94084 inactive:618 dirty:0 writeback:0 unstable:0
Jun 23 21:19:01 vps528 kernel: [1182395.730861]  free:4898 slab:3594 mapped:0 pagetables:3925 bounce:0
Jun 23 21:19:01 vps528 kernel: [1182395.730866] DMA free:11540kB min:28kB low:32kB high:40kB active:0kB inactive:0kB present:16160kB pages_scanned:0 all_unreclaimable? yes
Jun 23 21:19:01 vps528 kernel: [1182395.730869] lowmem_reserve[]: 0 4024 4024 4024
Jun 23 21:19:01 vps528 kernel: [1182395.730875] DMA32 free:8052kB min:8100kB low:10124kB high:12148kB active:376336kB inactive:2472kB present:4120800kB pages_scanned:767723 all_unreclaimable? yes
Jun 23 21:19:01 vps528 kernel: [1182395.730878] lowmem_reserve[]: 0 0 0 0
Jun 23 21:19:01 vps528 kernel: [1182395.730881] DMA: 3*4kB 3*8kB 3*16kB 4*32kB 3*64kB 1*128kB 1*256kB 1*512kB 2*1024kB 0*2048kB 2*4096kB = 11540kB
Jun 23 21:19:01 vps528 kernel: [1182395.730900] DMA32: 62*4kB 4*8kB 3*16kB 4*32kB 2*64kB 0*128kB 1*256kB 0*512kB 1*1024kB 1*2048kB 1*4096kB = 8008kB
Jun 23 21:19:01 vps528 kernel: [1182395.730908] Swap cache: add 0, delete 0, find 0/0, race 0+0
Jun 23 21:19:01 vps528 kernel: [1182395.730910] Free swap  = 0kB
Jun 23 21:19:01 vps528 kernel: [1182395.730911] Total swap = 0kB
Jun 23 21:19:01 vps528 kernel: [1182395.730912] Free swap:            0kB
Jun 23 21:19:01 vps528 kernel: [1182395.740886] 1048576 pages of RAM
Jun 23 21:19:01 vps528 kernel: [1182395.740893] 21255 reserved pages
Jun 23 21:19:01 vps528 kernel: [1182395.740894] 39897 pages shared
Jun 23 21:19:01 vps528 kernel: [1182395.740895] 0 pages swap cached
Jun 23 21:19:01 vps528 kernel: [1182395.740898] Out of memory: kill process 25246 (apache2) score 73450 or a child
Jun 23 21:19:01 vps528 kernel: [1182395.740913] Killed process 25246 (apache2)

Here's the content of my /etc/mysql/my.cnf

Code:

#
# 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
language	= /usr/share/mysql/english
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		= 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 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		= 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/

I'm using mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 on Debian GNU/Linux 6.0.7 (squeeze)

TB0ne · 06-25-2013, 10:02 AM

Quote:

Originally Posted by dhuyvetter

The MySQL on my VPS crashes every day since a couple of days, looking at my syslog, it seems that the reason is an Out Of Memory error. Should I look at finetuning MySQL or does my server need more memory? I currently have 512MB on my VPS.

I'm using mysql Ver 14.14 Distrib 5.1.66, for debian-linux-gnu (x86_64) using readline 6.1 on Debian GNU/Linux 6.0.7 (squeeze)

Well, 512 MB on an X64 system with Debian 6 is a bit light. More importantly, how big is your database, and how many users does it have?? What other services do you have running on that server? Because if you're running a web server, with a decent number of users, attached to the database, you probably ARE running out of memory. The MySQL is employing OOM to try to save the server.

dhuyvetter · 06-25-2013, 10:22 AM

Quote:

Originally Posted by TB0ne

How big is your database, and how many users does it have?? What other services do you have running on that server?

I'm running about 10 websites, most Drupal a few Wordpress and a few custom made php websites. They are all very low traffic.

dhuyvetter · 06-25-2013, 10:28 AM

I guess I'll upgrade my VPS to 1GB ram.

chrism01 · 06-25-2013, 09:01 PM

That's still a bit low.
Those CMSes take a fair bit of RAM as well.
Even for low usage I'd go 2GB, maybe even 4GB.