Description:
I have a MySQL Server version: 5.0.77-log
Which seems to be crashing constantly every few mins,
The reason for crashing is any thing from Signal 11, Signal 6, Assertion Failure, to
memory corruption eg Malloc failed or Free ()…
This server is on Dual Qaud Core, with 8 GB Ram
Running on CentOS 5.x
FYI A lot of innodb tables are used.
Overall DB size is 6-7 GB
Not really sure whats causing these constant crashes.
-------------------------
Extract of mysqld.log
-------------------------
101101 12:20:01 - mysqld got signal 11 ;
....
....
101101 12:29:01 - mysqld got signal 11 ;
..... //May be this was because i set force recovery to 1
InnoDB: A new raw disk partition was initialized or
InnoDB: innodb_force_recovery is on: we do not allow
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
..................
InnoDB: database modifications by the user. Shut down
InnoDB: mysqld and edit my.cnf so that newraw is replaced
InnoDB: with raw, and innodb_force_... is removed.
101101 14:50:11 [Note] /usr/libexec/mysqld: Normal shutdown
...........
....
101101 15:24:04 InnoDB: Error: MySQL is freeing a thd
InnoDB: though trx->n_mysql_tables_in_use is 1
InnoDB: and trx->mysql_n_tables_locked is 124.
TRANSACTION 0 598822756, not started, process no 28938, OS thread id 1629404048
mysql tables in use 1, locked 124
MySQL thread id 987, query id 43042622 localhost mv_php_usr
len 860; hex 065... Thank you!te is to cater to the ne;101102 9:13:47 - mysqld got
signal 11 ;
......
....
101102 9:46:29 - mysqld got signal 11 ;
...
101102 10:08:01InnoDB: Assertion failure in thread 1629285264 in file row0mysql.c line
145
InnoDB: Failing assertion: lenlen == 1
.....
.....
*** glibc detected *** /usr/libexec/mysqld: free(): invalid pointer: 0x6080eb50 ***
======= Backtrace: =========
/lib/libc.so.6[0x28e0f1]
/lib/libc.so.6(cfree+0x90)[0x291bc0]
/usr/libexec/mysqld(delete_dynamic+0x18)[0x848fe08]
/usr/libexec/mysqld(_ZN3THD7cleanupEv+0x8d)[0x817e04d]
/usr/libexec/mysqld(_Z10end_threadP3THDb+0x16)[0x818bc86]
/usr/libexec/mysqld(handle_one_connection+0x403)[0x81ad1b3]
/lib/libpthread.so.0[0x39f49b]
/lib/libc.so.6(clone+0x5e)[0x2f642e]
======= Memory map: ========
00169000-0016a000 r-xp 00169000 00:00 0 [vdso]
00179000-00182000 r-xp 00000000 fd:00 53478649 /lib/libcrypt-2.5.so
00182000-00183000 r--p 00008000 fd:00 53478649 /lib/libcrypt-2.5.so
00183000-00184000 rw-p 00009000 fd:00 53478649 /lib/libcrypt-2.5.so
00184000-001ab000 rw-p 00184000 00:00 0
...
...
..
101102 12:03:44 - mysqld got signal 11 ;
....
...
*** glibc detected *** /usr/libexec/mysqld: malloc(): memory corruption: 0x60d23ec8 ***
======= Backtrace: =========
/lib/libc.so.6[0x28e883]
/lib/libc.so.6(__libc_malloc+0x7b)[0x2903ab]
/usr/libexec/mysqld(my_malloc+0x25)[0x8489355]
/usr/libexec/mysqld(init_alloc_root+0x73)[0x8489ce3]
/usr/libexec/mysqld(_Z14init_sql_allocP11st_mem_rootjj+0x20)[0x8158520]
.....
....
*** glibc detected *** /usr/libexec/mysqld: free(): invalid next size (fast): 0x0b2eea20
***
======= Backtrace: =========
/lib/libc.so.6[0x28e0f1]
/lib/libc.so.6(cfree+0x90)[0x291bc0]
/usr/libexec/mysqld(_Z19close_thread_tablesP3THDbb+0x55)[0x81cee95]
/usr/libexec/mysqld(_ZN18Prepared_statement12cleanup_stmtEv+0x49)[0x8202c39]
/usr/libexec/mysqld(_ZN18Prepared_statement7prepareEPKcj+0x1c8)[0x8202e98]
/usr/libexec/mysqld(_Z18mysql_stmt_prepareP3THDPKcj+0xcb)[0x8204e2b]
/usr/libexec/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1069)[0x81ac439]
/usr/libexec/mysqld(handle_one_connection+0xa78)[0x81ad828]
/lib/libpthread.so.0[0x39f49b]
/lib/libc.so.6(clone+0x5e)[0x2f642e]
======= Memory map: ========
00179000-00182000 r-xp 00000000 fd:00 53478649 /lib/libcrypt-2.5.so
00182000-00183000 r--p 00008000 fd:00 53478649 /lib/libcrypt-2.5.so
00183000-00184000 rw-p 00009000 fd:00 53478649 /lib/libcrypt-2.5.so
00184000-001ab000 rw-p 00184000 00:00 0
....
...
101102 13:17:11 - mysqld got signal 11 ;
....
....
...
101102 15:11:33 InnoDB: Error: MySQL is freeing a thd
InnoDB: though trx->n_mysql_tables_in_use is 1
InnoDB: and trx->mysql_n_tables_locked is 7670.
TRANSACTION 0 602727896, not started, process no 2261, OS thread id 1629776784
mysql tables in use 1, locked 7670
MySQL thread id 5309, query id 113616026 localhost mv_php_usr
len 860; hex ....
*** glibc detected *** /usr/libexec/mysqld: free(): invalid pointer: 0x60d1cc18 ***
======= Backtrace: =========
/lib/libc.so.6[0x28e0f1]
/lib/libc.so.6(cfree+0x90)[0x291bc0]
/usr/libexec/mysqld[0x8212d86]
/usr/libexec/mysqld(_Z19ha_close_connectionP3THD+0x2b)[0x824b87b]
/usr/libexec/mysqld(_ZN3THDD0Ev+0x80)[0x81812f0]
/usr/libexec/mysqld(_Z10end_threadP3THDb+0x3e)[0x818bcae]
/usr/libexec/mysqld(handle_one_connection+0x403)[0x81ad1b3]
/lib/libpthread.so.0[0x39f49b]
/lib/libc.so.6(clone+0x5e)[0x2f642e]
======= Memory map: ========
00110000-00119000 r-xp 00000000 fd:00 53477416 /lib/libnss_files-2.5.so
00119000-0011a000 r--p 00008000 fd:00 53477416 /lib/libnss_files-2.5.so
0011a000-0011b000 rw-p 00009000 fd:00 53477416 /lib/libnss_files-2.5.so
00179000-00182000 r-xp 00000000 fd:00 53478649 /lib/libcrypt-2.5.so
00182000-00183000 r--p 00008000 fd:00 53478649 /lib/libcrypt-2.5.so
00183000-00184000 rw-p 00009000 fd:00 53478649 /lib/libcrypt-2.5.so
00184000-001ab000 rw-p 00184000 00:00 0
-----------------------
my.cnf
-----------------------
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip_name_resolve
#bind-address=192.168.1.3
#General Querieis
log=/var/log/mysql_log_queries.log
#Slow Queries
log_slow_queries=/var/log/mysql_slow_queries.log
long_query_time=2
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
#MyIsam Variables
myisam_sort_buffer_size=64M
#MySql Variables
key_buffer_size=128M
max_allowed_packet=1M
table_cache=1024
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=4M
thread_cache=16
flush
delay_key_write=Off
#thread_concuency=4
#InnoDB Settings
innodb_buffer_pool_size = 1G
innodb_additional_mem_pool_size = 64M
innodb_log_file_size=256M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency=8
innodb_file_per_table
#innodb_force_recovery=1
#Query Cache
query_cache_size=128M
query_cache_type=1
#query_cache_size=0
#query_cache_type=0
max_connections=300
#Replication
log-bin=/var/log/mysql/mysql-bin.log
binlog-do-db=mv_sys_data
server-id=1
sync_binlog=1
[mysqldump]
quick
max_allowed_packet=16M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
How to repeat:
May be its specific to my hardware or config or something..
Just start the server and let it work for some time and it will restart.
Best Answer
Run a memory test on the server (usually you can do the from grub). Try stress testing by compiling a kernel, or in fact compiling several kernels in parallel.
Are you ECC ram? If your not, you SHOULD BE! :-)
Also check the mysql known bugs for incompatibly with your hardware.
This sounds like a hardware fault rather than a software fault, however it may be that you come up against some limit with mysql, make sure your running the latest version with any patches that you need related to your hardware.
http://www.linuxquestions.org/questions/slackware-14/stress-testing-my-cpu-237025/
has some more information.
Again, if you try and kernel compile for stress testing, then make a copy of /usr/src/linux.x.x.x to another directory and run another compile in parallel.
On my quad core system, i can run 32 compiles. Alternatively you can increase the -j parameter to increase the parrleism of make, but i find running completely separate compiles finds problems quicker.