Hi all,
our mysql system is "freezing" every two days.
By "freezing" i mean the following:
it doesn't respond to ping
we can't login with SSH
we don't get any answer from MySQL
there is no entry in the error logs! neither from linux neither from MySQL.
we have already changed to a completely new hardware, we have the same problem, so it's definitely not a hardware problem.
we do not have any other software installed except a firewall (iptables rule)
we can restart the server from another server using rsyslog (www.rsyslog.com)(software reset)
Could someone help me, by giving me some pointers what could i do to figure out the problem? I have included every detail about our settings.
Thank you in advance for your help.
Max.
Our system parameters and settings:
System-Memory: 12GB
Processor: Intel 7-920 Quadcore
Operating system: Debian 5 (lenny) 64bit
MySQL 5.1.49
Databases: (a) a small phpbb forum (b) a 6GB database 3 tables with about 15 million rows
my.cnf
#
# 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 = our-ip-address
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 256K
thread_cache_size = 32
max_connections = 300
table_cache = 2048
#thread_concurrency = 4
# Used for InnoDB tables recommended to 50%-80% available memory
innodb_buffer_pool_size = 6G
# 20MB sometimes larger
innodb_additional_mem_pool_size = 20M
# 8M-16M is good for most situations
innodb_log_buffer_size = 8M
# Disable XA support because we do not use it
innodb-support-xa = 0
# 1 is default wich is 100% secure but 2 offers better performance
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
#innodb_thread_concurency = 8
# Recommended 64M - 512M depending on server size
innodb_log_file_size = 512M
# One file per table
innodb_file_per_table
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 16M
#query_cache_type = 1
#query_cache_min_res_unit= 2K
#join_buffer_size = 1M
#
# * 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. This is a Debian improvement :)
#
# 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.
#server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# * InnoDB plugin
# As of MySQL 5.1.38, the InnoDB plugin from Oracle is included in the MySQL source code.
# It has many improvements and better performances than the built-in InnoDB storage engine.
# Please read http://www.innodb.com/products/innodb_plugin/ for more information.
# Uncommenting the two following lines to use the InnoDB plugin.
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so
#
# * 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
#
# * NDB Cluster
#
# See /usr/share/doc/mysql-server-*/README.Debian for more information.
#
# The following configuration is read by the NDB Data Nodes (ndbd processes)
# not from the NDB Management Nodes (ndb_mgmd processes).
#
# [MYSQL_CLUSTER]
# ndb-connectstring=127.0.0.1
#
# * IMPORTANT: Additional settings that can override those from this file!
#
!includedir /etc/mysql/conf.d/
UPDATE
After installing sysstat and configuring it to collect data after every minute i have the following datas.
I used sar to generate the following output:
The log-file is too big so coudn't enter it here but uploaded to box.net.
The link is http://www.box.net/shared/xc6rh7qqob
SECOND UPDATE
We started a ping command in the background, and that solved the problem. Now the server does work since more then a week. We still don't know what's the problem.
