MySQL Bugs: #23834: Mysql crashes a few times a day

Bug #23834	Mysql crashes a few times a day
Submitted:	1 Nov 2006 12:04	Modified:	1 Nov 2006 17:49
Reporter:	Cedric Anes	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.21	OS:	pc-linux-gnu on i686
Assigned to:		CPU Architecture:	Any

Description:
2-3 times a day, mysql crashes. We're using innodb engine in utf8.

Here is the latest backtrace:

0x8162210 JOIN::optimize() + 1552
0xffffe420 _end + -139471184
0x81940ea change_password(THD*, char const*, char const*, char*) + 1658
0x81e2839 show_binlog_info(THD*) + 905
0x81b0450 ha_myisammrg::append_create_info(String*) + 448
0x822774e row_table_add_foreign_constraints + 206
0x81a6f1b filesort_free_buffers(st_table*) + 1723
0x81a5b4a end_read_record(st_read_record*) + 490
0x81a56a7 init_read_record(st_read_record*, THD*, st_table*, SQL_SELECT*, int, bool) + 215
0x819b099 List_iterator<LEX_COLUMN>::operator++(int) + 25
0x82284fd row_discard_tablespace_for_mysql + 1517
0x8227399 row_create_index_for_mysql + 185
0x819811c check_grant(THD*, unsigned long, st_table_list*, unsigned int, unsigned int, bool) + 364
0x8175f61 JOIN::clear() + 2753
0x81b5b77 ha_innobase::check(THD*, st_ha_check_opt*) + 215
0x81b5894 ha_innobase::info(unsigned int) + 964
0x8175823 JOIN::clear() + 899
0x81746e6 copy_funcs(Item**) + 38
0x8173ebf setup_copy_fields(THD*, TMP_TABLE_PARAM*, Item**, List<Item>&, List<Item>&, unsigned int, List<Item>&) + 1215
0x4360e371 _end + 990959105
0x43566ffe _end + 990274190

How to repeat:
No way to repeat. When reexecuting query mentionned in .err file, everything runs smoothly.

Thank you for a problem report. Please, describe your hardware (how much RAM do you have), send your my.cnf content and the results of:

getconf GNU_LIBPTHREAD_VERSION

getconf GNU_LIBPTHREAD_VERSION => NPTL 2.3.4

Hardware: 2 Intel Xeon CPU with 4Gb RAM

/* Begin of my.cnf */

[mysqld]
safe-show-database

ft_min_word_len=2
default-table-type=InnoDB
default-character-set=utf8
default-collation=utf8_unicode_ci

innodb_data_home_dir = /home/innodb/data/
innodb_data_file_path = ibdata0:1024M:autoextend

innodb_log_group_home_dir = /home/innodb/logs/
set-variable = innodb_log_files_in_group=2
set-variable = innodb_log_file_size=128M
set-variable = innodb_log_buffer_size=8M

set-variable = innodb_buffer_pool_size=1640M
set-variable = innodb_additional_mem_pool_size=64M
innodb_flush_log_at_trx_commit=0
set-variable = innodb_lock_wait_timeout=50

set-variable = thread_concurrency=8
set-variable = thread_cache_size=64
set-variable = max_connections=600

set-variable = max_heap_table_size=512M
set-variable = key_buffer_size=96M
set-variable = join_buffer_size=96M
set-variable = sort_buffer_size=96M
set-variable = read_buffer_size=96M
set-variable = table_cache=8192
set-variable = tmp_table_size=512M
set-variable = max_allowed_packet=16M
set-variable = query_cache_size=64M
set-variable = query_cache_type=2

log-bin = /home/innodb/logs/logbin
log-bin-index = /home/innodb/logs/logbin
set-variable = max_binlog_size=128M

log-slow-queries=/var/lib/mysql/slow.log

[myisamchk]
ft_min_word_len=2

/* End of my.cnf */

With these settings:

set-variable = max_connections=600

set-variable = max_heap_table_size=512M
set-variable = key_buffer_size=96M
set-variable = join_buffer_size=96M
set-variable = sort_buffer_size=96M
set-variable = read_buffer_size=96M
set-variable = table_cache=8192
set-variable = tmp_table_size=512M

You ofviously have out of memory issue with several dozens concurrent connections. Set all these buffers properly and check again.

Do you have any recommandation for those settings ? Any help would be greatly appreciated. Thank you very much.

Please, read the manual. Namely:

http://dev.mysql.com/doc/refman/4.1/en/memory-use.html

and

http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html.

http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html (and all the pages about InnoDB) will be, surely, also useful to you.