Posted by kyle_mathews on February 11, 2010 at 6:16pm
This has happened twice now. I awake to find my site down (Drupal returns the "can't connect to database" message).
Running /etc/init.d/mysql status returns:
/usr/bin/mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on x86_64
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a-24+lenny2+spu1-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 30 min 32 sec
Threads: 61 Questions: 1576 Slow queries: 1 Opens: 675 Flush tables: 1 Open tables: 669 Queries per second avg: 0.860.The best I can guess is that somehow the tri-nightly backup-migrate export of my database is causing problems -- but if so, how do I fix it?
Thanks in advance!
Comments
did you open the Mysql error
did you open the Mysql error logs?
/var/log/mysql.err is empty
/var/log/mysql.err is empty as is /var/log/mysql.log
I noticed the log-bin is on. Could that cause problems under the stress of exporting the database? Should I turn it off (as a general rule)?
Kyle Mathews
Shouldn't hurt to have
Shouldn't hurt to have log-bin on. I'm curious why it doesn't report anything when the mysql server process dies. Have you considered upgrading to the latest Mysql 5.1?
With 61 threads open it
With 61 threads open it certainly looks like your backup process is holding things up. Under high load I don't see more than 10 threads open.
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his
I would be interested to see
I would be interested to see the options you are using for the backup script. If you are using mysqldump then you can run into some pretty serious table locks (with myisam) and they can cascade out of control until the db is unusable. That would be my first guess. mysql.err and mysql.log are probably turned off in the my.cnf, they are IO hogs and are usually disabled by default on Debian.
--
www.neospire.net
I'm just using the backup
I'm just using the backup migrate module so I'm not sure what options it has set there. I upgraded to mysql 5.1 per the advice above but MySQL froze up again just this morning. This is quite frustrating / mysterious to me.
Kyle Mathews
Here's my my.cnf if that's of
Here's my my.cnf if that's of any help:
#
# 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 = 32M
key_buffer_size = 96M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 286
max_connections = 60
max_user_connections = 120
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
#thread_concurrency = 10
# Temp Table Limits
max_heap_table_size = 32M
tmp_table_size = 64M
table_definition_cache = 700
#
# * Query Cache Configuration
#
query_cache_limit = 2M
query_cache_size = 12M
query_cache_min_res_unit = 2K
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
#log = /var/log/mysql/mysql.log
#
# 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 = /var/log/mysql/mysql-not-using-indexes.log
#
# 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
#
# * BerkeleyDB
#
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.
# skip-bdb
#
# * 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!
# You might want to disable InnoDB to shrink the mysqld process by circa 100MB.
#skip-innodb
#
# * 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/
Kyle Mathews
When you start getting the
When you start getting the "can't connect to database" error open up mytop to see what threads are holding things up. One possibility is that you have several sleeping threads (threads that didn't get closed correctly). To clean these up periodically you can set wait_timeout in my.cnf to something like 90 seconds. Are you using MyISAM or InnoDB? If InnoDB the biggest problem is likely that you are using all the defaults for InnoDB and have not tuned anything. You might try MySQL Tuner to see what suggestions it has for you:
https://launchpad.net/mysqltuner/
--
Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his