MySQL keeps going down

[Skeptical]

Once a day now my MySQL database would go down mysteriously. Looking at my /var/log/messages, I see the entry:

May 18 09:01:08 server kernel: Out of Memory: Killed process 571 (mysqld).

Do you guys know what could be causing this besides? I know it says out of memory, but this server isn't exactly filled with that much traffic.

Also, is there a script or a way for me to respawn mysql when it dies?

[eva2000]

not sure but i found this at http://lwn.net/2000/1012/kernel.php3


The Linux kernel, remember, is capable of allocating more virtual memory to processes than it is really able to provide. This is done because processes generally do not use anywhere near all the memory they ask for. To require that the kernel be able to satisfy every last allocation would greatly reduce the total available memory, and would be wasteful. So Linux overcommits it memory.

The consequence of that approach, however, is that the kernel can possibly find itself in a position where it is unable to provide memory to a process that thinks it already has it. It seems to be generally accepted that the only way out of that bind is to start killing processes to recover their memory. Killing processes is not a very satisfactory solution, but it is better than having the whole system lock up.

[eva2000]

what are your

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

setttings looking like ? and you my.cnf settings ?

what OS and kernel version ?

[Skeptical]

My apache settings:

KeepAlive On
MaxKeepAliveRequests 200
KeepAliveTimeout 15
MinSpareServers 30
MaxSpareServers 60
StartServers 30
MaxClients 255

Here is my /etc/my.cnf:

# Example mysql config file for large systems.
#
# This is for large system with memory = 512M where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/mf.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=500
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
log-bin
server-id = 1

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
#set-variable = bdb_max_lock=100000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:1000M
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

As for OS, I'm on Red Hat Linux release 7.1 (Seawolf)
Kernel 2.4.9-12 on an i686.

[eva2000]

you my.cnf memory allocations are too high and you had mysql logging enabled which is very very server intensive

change your my.cnf to below and restart mysql and see how that runs


[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = max_connections=500
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=7200
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash

[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

[Skeptical]

Originally posted by eva2000
you my.cnf memory allocations are too high and you had mysql logging enabled which is very very server intensive

change your my.cnf to below and restart mysql and see how that runs



Excellent! Thanks for your help. I hope this will do the magic. :)

Btw, the logging, where is it being logged?

[eva2000]

Originally posted by Skeptical


Excellent! Thanks for your help. I hope this will do the magic. :)

Btw, the logging, where is it being logged? see the bin files in your mysql data directory ? well that's the logs ;)

[Skeptical]

Originally posted by eva2000
see the bin files in your mysql data directory ? well that's the logs ;)

Hmmm I'm not finding it. I've looked everywhere in /usr/local/mysql/. Is it supposed to be *.bin?

[eva2000]

Originally posted by Skeptical


Hmmm I'm not finding it. I've looked everywhere in /usr/local/mysql/. Is it supposed to be *.bin? yup *.bin files or binary logs