mySQL5 - ein Crash nach dem anderen

ragey · Dec 31, 2008

Hallo,
aus heiterem Himmel stürzt mein mySQL Server seit 2 Tagen regelmässig ab.
Der Load geht dann in die Höhe und auch der Apache bricht zusammen.

Manuelle stoppen des mySQL Servers funktioniert nicht.
Nach einem Reboot des Servers läuft zunächst wieder alles normal
bis dann wieder dieser Fehler auftritt.

Nach längerem suchen habe ich folgende Meldungen in der Daemon.log gefunden:

Code:

....
Dec 31 13:54:31 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
Dec 31 13:54:43 p15201960 ntpd[1798]: sendto(212.227.123.64) (fd=-1): Bad file descriptor
Dec 31 13:55:38 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
Dec 31 13:55:52 p15201960 ntpd[1798]: sendto(212.227.123.64) (fd=-1): Bad file descriptor
Dec 31 13:56:46 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
Dec 31 13:56:54 p15201960 ntpd[1798]: sendto(212.227.123.64) (fd=-1): Bad file descriptor
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_7.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_26.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_45.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_46.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_53.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_22.MYI'; try to repair it
Dec 31 13:57:24 p15201960 mysqld[1624]: 081231 13:57:24 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_13.MYI'; try to repair it
Dec 31 13:57:26 p15201960 mysqld[1624]: 081231 13:57:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_38.MYI'; try to repair it
Dec 31 13:57:27 p15201960 mysqld[1624]: 081231 13:57:26 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_36.MYI'; try to repair it
Dec 31 13:57:27 p15201960 mysqld[1624]: 081231 13:57:27 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_81.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:27 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_10.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_47.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_37.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_27.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_9.MYI'; try to repair it
Dec 31 13:57:28 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_35.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:28 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_40.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_43.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_16.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_31.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_41.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_4.MYI'; try to repair it
Dec 31 13:57:29 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_15.MYI'; try to repair it
Dec 31 13:57:31 p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_60.MYI'; try to repair it
Dec 31 13:57:31 p15201960 mysqld[1624]: 081231 13:57:31 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_20.MYI'; try to repair it
Dec 31 13:57:31 p15201960 mysqld[1624]: 081231 13:57:31 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_91.MYI'; try to repair it
Dec 31 13:57:31 p15201960 mysqld[1624]: 081231 13:57:31 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_6.MYI'; try to repair it
Dec 31 13:57:31 p15201960 mysqld[1624]: 081231 13:57:31 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_12.MYI'; try to repair it
Dec 31 13:57:33 p15201960 mysqld[1624]: 081231 13:57:31 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_17.MYI'; try to repair it
Dec 31 13:57:35 p15201960 mysqld[1624]: 081231 13:57:33 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_18.MYI'; try to repair it
Dec 31 13:57:50 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
Dec 31 13:57:57 p15201960 ntpd[1798]: sendto(212.227.123.64) (fd=-1): Bad file descriptor
Dec 31 13:58:53 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
Dec 31 13:59:02 p15201960 ntpd[1798]: sendto(212.227.123.64) (fd=-1): Bad file descriptor
Dec 31 13:59:56 p15201960 mysqld[1624]: *** glibc detected *** double free or corruption (out): 0x00007fc01c461700 ***
Dec 31 13:59:56 p15201960 mysqld[1624]: mysqld got signal 6;
Dec 31 13:59:56 p15201960 mysqld[1624]: This could be because you hit a bug. It is also possible that this binary
Dec 31 13:59:56 p15201960 mysqld[1624]: or one of the libraries it was linked against is corrupt, improperly built,
Dec 31 13:59:56 p15201960 mysqld[1624]: or misconfigured. This error can also be caused by malfunctioning hardware.
Dec 31 13:59:56 p15201960 mysqld[1624]: We will try our best to scrape up some info that will hopefully help diagnose
Dec 31 13:59:56 p15201960 mysqld[1624]: the problem, but since we have already crashed, something is definitely wrong
Dec 31 13:59:56 p15201960 mysqld[1624]: and this may fail.
Dec 31 13:59:56 p15201960 mysqld[1624]: 
Dec 31 13:59:56 p15201960 mysqld[1624]: key_buffer_size=67108864
Dec 31 13:59:56 p15201960 mysqld[1624]: read_buffer_size=131072
Dec 31 13:59:56 p15201960 mysqld[1624]: max_used_connections=157
Dec 31 13:59:56 p15201960 mysqld[1624]: max_connections=1000
Dec 31 13:59:56 p15201960 mysqld[1624]: threads_connected=151
Dec 31 13:59:56 p15201960 mysqld[1624]: It is possible that mysqld could use up to 
Dec 31 13:59:56 p15201960 mysqld[1624]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2241528 K
Dec 31 13:59:56 p15201960 mysqld[1624]: bytes of memory
Dec 31 13:59:56 p15201960 mysqld[1624]: Hope that's ok; if not, decrease some variables in the equation.
Dec 31 13:59:56 p15201960 mysqld[1624]: 
Dec 31 13:59:56 p15201960 mysqld[1624]: thd=0x7fc01c2fa5b0
Dec 31 13:59:56 p15201960 mysqld[1624]: Attempting backtrace. You can use the following information to find out
Dec 31 13:59:56 p15201960 mysqld[1624]: where mysqld died. If you see no messages after this, something went
Dec 31 13:59:56 p15201960 mysqld[1624]: terribly wrong...
Dec 31 13:59:56 p15201960 mysqld[1624]: Cannot determine thread, fp=0x40650190, backtrace may not be correct.
Dec 31 13:59:56 p15201960 mysqld[1624]: Bogus stack limit or frame pointer, fp=0x40650190, stack_bottom=0x40650000, thread_stack=131072, aborting backtrace.
Dec 31 13:59:56 p15201960 mysqld[1624]: Trying to get some variables.
Dec 31 13:59:56 p15201960 mysqld[1624]: Some pointers may be invalid and cause the dump to abort...
Dec 31 13:59:56 p15201960 mysqld[1624]: thd->query at 0x8ba5270 = SELECT * FROM bilder WHERE active = 1 AND link = '' ORDER BY RAND() LIMIT 5
Dec 31 13:59:56 p15201960 mysqld[1624]: thd->thread_id=314
Dec 31 13:59:56 p15201960 mysqld[1624]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Dec 31 13:59:56 p15201960 mysqld[1624]: information that should help you find out what is causing the crash.
Dec 31 13:59:56 p15201960 ntpd[1798]: sendto(195.20.224.142) (fd=-1): Bad file descriptor
....

Hier noch meine 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		= 64M
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
max_connections         = 1000
table_cache             = 256
#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.
#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
#
# 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/

Folgendes System wird eingesetzt:
Debian 4.0 etch
MySQL-Version: 5.0.32
PHP-Version: 5.2.0-8

Der Server ist mit 1GB Speicher ausgestattet.

Gruß Ragey

TamCore · Dec 31, 2008

Code:

p15201960 mysqld[1624]: 081231 13:57:29 [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_657_31.MYI'; try to repair it

http://huschi.net/12_295_de.html said:
/etc/init.d/mysql stop
myisamchk --fast --force --update-state /var/lib/mysql/*/*.MYI
/etc/init.d/mysql start

ragey · Dec 31, 2008

Super vielen Dank,
werde ich mal testen.

Ich bin auch noch hierdrüber gestolpert:
https://mindtouch.comune.at/Opensource/Linux/Max_open_Files

what(): FileSystem Error : Bad file descriptor
mysqld got signal 6;

To fix this error, increase ulimit (see above).

Diesen Fehler bekomme ich ja auch,
ich habe das ulimit mal erhöht.

dexter · Dec 31, 2008

Hallo Ragey,

klingt nach Speicherüberlastung, bspw. durch einen Angriffsversuch.

Du lässt 1000 gleichzeitige Verbindungen zu. Diese bedeuten bei Auslastung einen Speicherbedarf von...

ragey said:
Dec 31 13:59:56 p15201960 mysqld[1624]: It is possible that mysqld could use up to
Dec 31 13:59:56 p15201960 mysqld[1624]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2241528 K
Dec 31 13:59:56 p15201960 mysqld[1624]: bytes of memory
Dec 31 13:59:56 p15201960 mysqld[1624]: Hope that's ok; if not, decrease some variables in the equation.

... 2,2 GB. Da Dein Server nur 1 GB reellen Speicher (+ vermutlich nochmal 1 GB Swap) hat würde eine Situation mit vielen gleichzeitigen Clients (wie bspw. ein Break-In-Attempt) den Server praktisch lahm legen -- so wie Du das Problem beschrieben hast. Die Indexprobleme bei den temporären Tabellen entstehen dann bspw. dadurch, daß MySQL für irgendeine Indexaufgabe keinen Speicher mehr hatte.

Es muss übrigens kein Angriff sein, eine Rush-Hour genügt bei dieser Config. Normale User denken, daß mehrfaches Klicken / Reload den Ladeprozess beschleunigt, so werden aus 100 echten Usern ganz schnell mal 500 Connections.

Noch eine Möglichkeit: Batchprozesse, die nicht korrekt terminieren und Connections blockieren.

Jedenfalls solltest Du die Apache-Logs zu den fraglichen Zeiten mal genau untersuchen. Und natürlich als erstes max_connections auf einen normalen Wert (bspw. 50 / 100) reduzieren, damit verhinderst Du die Überlastsituation.

Guten Rutsch!

ragey · Jan 1, 2009

Super Dexter,
klasse Erklärung ich werde es mal Ändern und schauen wie sich die Kiste verhält.
Auf dem Server liegt in der tat eine Seite wo schnell 200-300 Beuscher gleichzeitig sind. Allerdings gab es auch schon Tage wo es doppelt so viel war und da gab es dieses Problem nicht.

Gruß und frohes neues,
Ragey

dexter · Jan 1, 2009

Frohes Neues auch

Wenn das Problem auftritt obwohl sich an der Nutzung der Kiste nicht viel geändert hat prüf mal ob sich was am DB-Umfang getan hat:

Dec 31 13:59:56 p15201960 mysqld[1624]: thd->query at 0x8ba5270 = SELECT * FROM bilder WHERE active = 1 AND link = '' ORDER BY RAND() LIMIT 5

Dieser Abfragetyp generiert bspw. eine hohe Last für den SQL-Server, da wegen "ORDER BY RAND()" erst das gesamte Resultset (per Tablescan) aufgebaut und sortiert werden muss. Es gibt ein paar Alternativen, s.a. MySQL-Manual.

Hm... dazu fällt mir noch was an Deiner Config auf:

tmpdir = /tmp

Wenn Dein /tmp eine Ramdisk (tmpfs) ist solltest Du das ändern. Das MySQL-Tmpdir sollte laut Manual immer auf einem persistenten Speicher liegen, außerdem rauben Dir alle temporären Tabellen sonst ebenfalls den wertvollen Hauptspeicher.

vurt_runner · Feb 16, 2009

hallo,

sorry das ich mich einmische, aber seit ihr bei dem Problem weitergekommen?
mich plagen naemlich seit kurzem aehnliche, wenn nicht sogar die gleichen Probleme. Bei mir ist die Basis allerdings nen 1und1 Server mit
OpenSuse 10.3 und plesk 8.6
PHP 5.2.5
mysql Ver 14.12 Distrib 5.0.45

ansonsten mach ich nochmal nen neuen Thread auf fuer mein "Problemchen"

danke und gruss
thomas

mySQL5 - ein Crash nach dem anderen

ragey

Registered User

TamCore

Guest

ragey

Registered User

dexter

New Member

ragey

Registered User

dexter

New Member

vurt_runner

Registered User

We value your privacy