1

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.

2
  • What do you mean by "restarting with rsync"?
    – lorenzog
    Sep 14, 2010 at 14:55
  • sorry, i meant rsyslog - www.rsyslog.com . But what i mean is that it is a software reset, not a hardware one. We don't have physical access to the server. It is 1000KM away in a data center.
    – maximus
    Sep 14, 2010 at 15:00

3 Answers 3

0

Do you have heavy load on the server?

Have you checked if you running out of any system resources? The package sysstat is an awesome way to monitor your system resources.

Does the server happen to "unfreeze" after a while, or is it locked down completely until you restart it?

I am currently seeing a similar behavior on my own server at times, therefore I would like to know more how your problem plays out, right now it has many similarities.

6
  • Hi, we are using munin for monitoring. It doesn't seem to unfreeze. The server is a MySQL server only, there is a high load on the server about 2K queries per second, but the freezing doesn't happen at high load. Except mysql the only thing i changed is to do an ip-tables script at startup for firewall. I will post those changes in a new post and provide the link here, maybe there is a problem ...
    – maximus
    Sep 15, 2010 at 8:08
  • I created a new question, the problem might be related to our firewall rules, however i am not sure, the new question is at serverfault.com/questions/181293/…
    – maximus
    Sep 15, 2010 at 8:21
  • Hello Maximus, im no expert on the subject but I doubt that the ip tables has anything to do with it. They would either drop the connections from the start or not at all. The issue I experience on my server resolvs itself after a new minutes, and the computer comes back into play. So its a little bit different. Our team will investigate the matter next week, I will get back to you if we find anything that could be traced back to the mysql database. Sep 17, 2010 at 8:09
  • Hi, i used sysstat and captured the log (5 min before and 5 min after the crash). The log's are at box.net/shared/xc6rh7qqob Do you see something strange ?
    – maximus
    Sep 22, 2010 at 6:24
  • Hi, i can unfortunately not see anything wierd in your log. I would however recommend you to plot the data as it is much easier to find any trends/unusual behaviors that way. Myself I use kSar (sourceforge.net/projects/ksar) for this. Our own server seems to have an incompability issue between Ubuntu 8.04 LTS and our raid controller (PERC 6/i). So our problems does not seem to be related to mysql at all. Sep 22, 2010 at 6:59
0

Sounds like a network connectivity issue. You've eliminated hardware from scope and the entire server is inaccessible, which is unlikely to be a system issue without reflecting on some level in the logs. If the server is not rebooting or does not have an issue in the logs that would impact everything, focus on network.

1
0

Local disk or SAN/NAS for the MySQL data folders? I've seen similar, inexplicable, behavior when /var/lib/mysql is mounted on an iSCSI target and you overrun the TOE buffer during big index recalcs.

1
  • We only have 2 x 750 GB SATA-II HDD's no SAN/NAS. Do you know any solution to this ?
    – maximus
    Sep 22, 2010 at 6:22

You must log in to answer this question.

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