>Description:
Database:
total: about 500Mb now
mostly used table: about 300Mb data, about 150Mb index, about 6,7 millions of records,
two indexes(primary key contains 3 fields, and other index contains 2 fields)
other tables is negligible (U see)
>How-To-Repeat:
we repeating crash by make 1000 to 10000 subsequent SQL requests (by
scripts)
>Fix:
<how to correct or work around the problem, if known (multiple lines)>
>Submitter-Id:
[EMAIL PROTECTED]
>Originator:
>Organization:
Rusian Space Research Institute, SMIS Laboratory
>MySQL support:
none
>Synopsis:
Permanent crashes thru signal 11 (without core) or thru signal 10 (with core)
>Severity:
critical
>Priority:
high
>Category:
mysql
>Class:
sw-bug
>Release:
mysql-3.23.32 (Source distribution)
>Environment:
Hardware:
CPU: Dual PIII-933
Mother: SuperMicro SS-LE chipset (so hardware bugs are unlikely)
Memory: 512Mb Registered ECC SDRAM in two modules (so memory rather works properly)
HDD: Adaptec 2100S RAID controller 32Mb cache with 4*18Gb Quantum U160 SCSI HDDs
configured as RAID5
Case: InWin server rackmount with MANY BIG coolers (so overheat is unlikely)
System: FreeBSD fishdb.iki.rssi.ru 4.2-RELEASE FreeBSD 4.2-RELEASE #0: Mon Jan 29
17:47:36 MSK 2001
[EMAIL PROTECTED]:/usr/src/sys/compile/DUAL i386
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='-O6' CXX='gcc' CXXFLAGS='-O6
-felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1169076 Nov 20 14:59 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Jan 29 19:44 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 559196 Nov 20 14:59 /usr/lib/libc.so.4
Configure command: ./configure --with-debug --prefix=/usr/local/mysql-3.23.32debug
Perl: This is perl, version 5.005_03 built for i386-freebsd
(Kernel compiling was made without errors and no other software fails except MySQL -
about system stability)
Kernel configuration:
Generic except SMP and APIC_IO options (dual CPUs)
MySQL variables:
-------------------------------------------------------------------------------------------
mysql> show variables;
+-------------------------+---------------------------------------------------------------------------------------------------------
--------------------------------------------------+
| Variable_name | Value
|
+-------------------------+---------------------------------------------------------------------------------------------------------
--------------------------------------------------+
| ansi_mode | OFF
|
| back_log | 50
|
| basedir | /usr/local/mysql-3.23.32debug/
|
| binlog_cache_size | 32768
|
| character_set | cp1251
|
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7
|cp1251 danish hebrew win1251 estonia hungarian koi8
_ukr win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /db/mysql/
|
| delay_key_write | ON
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| have_bdb | NO
|
| have_gemini | NO
|
| have_innobase | NO
|
| have_isam | YES
|
| have_raid | NO
|
| have_ssl | NO
|
| init_file |
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 268431360
|
| language | /usr/local/mysql-3.23.32debug/share/mysql/english/
|
| large_files_support | ON
|
| log | ON
|
| log_update | OFF
|
| log_bin | OFF
|
| log_slave_updates | OFF
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 1047552
|
| max_binlog_cache_size | 4294967295
|
| max_connections | 100
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_sort_length | 1024
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_recover_options | OFF
|
| myisam_sort_buffer_size | 67108864
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 1000000
|
| net_write_timeout | 60
|
| open_files_limit | 0
|
| pid_file | /db/mysql/mysqld.pid
|
| port | 3306
|
| protocol_version | 10
|
| record_buffer | 1044480
|
| query_buffer_size | 0
|
| safe_show_database | OFF
|
| server_id | 1
|
| skip_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer | 1048568
|
| table_cache | 256
|
| table_type | MYISAM
|
| thread_cache_size | 8
|
| thread_stack | 65536
|
| timezone | MSK
|
| tmp_table_size | 1048576
|
| tmpdir | /db/tmp/
|
| version | 3.23.32-debug-log
|
| wait_timeout | 28800
|
+-------------------------+---------------------------------------------------------------------------------------------------------
--------------------------------------------------+
73 rows in set (0.00 sec)
MySQL was working under user mysql, now works under user root
but crashing thru signal 11 cause NOT core anyway
no other changes occures when user change
-------------------------------------------------------------------------------------------
some core debugging results:
-------------------------------------------------------------------------------------------
fishdb$ gdb /usr/local/mysql/libexec/mysqld mysqld.core
GNU gdb 4.18
Copyright 1998 Free Software Foundation, Inc.
GDB is free software, covered by the GNU General Public License, and you are
welcome to change it and/or distribute copies of it under certain conditions.
Type "show copying" to see the conditions.
There is absolutely no warranty for GDB. Type "show warranty" for details.
This GDB was configured as "i386-unknown-freebsd"...
Core was generated by `mysqld'.
Program terminated with signal 10, Bus error.
Reading symbols from /usr/lib/libc_r.so.4...done.
Reading symbols from /usr/lib/libz.so.2...done.
Reading symbols from /usr/lib/libcrypt.so.2...done.
Reading symbols from /usr/lib/libm.so.2...done.
Reading symbols from /usr/libexec/ld-elf.so.1...done.
#0 0x28221361 in bcopy () from /usr/lib/libc_r.so.4
(gdb) bt
#0 0x28221361 in bcopy () from /usr/lib/libc_r.so.4
#1 0x282330a8 in _thread_autoinit_dummy_decl () from /usr/lib/libc_r.so.4
#2 0x28222cca in __dtoa () from /usr/lib/libc_r.so.4
#3 0x28220b3b in vfprintf () from /usr/lib/libc_r.so.4
#4 0x2821ec56 in vfprintf () from /usr/lib/libc_r.so.4
#5 0x28219397 in snprintf () from /usr/lib/libc_r.so.4
#6 0x80625d5 in Field_double::val_str (this=0x181e2a58, val_buffer=0xbfaed48c,
val_ptr=0xbfaed48c) at field.cc:2079
#7 0x8060a49 in Field::send (this=0x181e2a58, packet=0x81d7420) at field.cc:257
#8 0x813166b in Item_field::send (this=0x181e13f8, str_arg=0x81d7420) at item.h:119
#9 0x806987f in select_send::send_data (this=0x181e1d88, items=@0x81d724c) at
sql_class.cc:236
#10 0x8098a18 in end_send (join=0xbfaed788, join_tab=0x181e2cf8, end_of_records=false)
at sql_select.cc:4521
#11 0x8097fca in sub_select (join=0xbfaed788, join_tab=0x181e2be0,
end_of_records=false) at sql_select.cc:4033
#12 0x8097c63 in do_select (join=0xbfaed788, fields=0x81d724c, table=0x0,
procedure=0x0) at sql_select.cc:3925
#13 0x80901c4 in mysql_select (thd=0x81d7000, tables=0x181e1598, fields=@0x81d724c,
conds=0x181e1c28, ftfuncs=@0x81d7280,
order=0x181e1cf0, group=0x0, having=0x0, proc_param=0x0, select_options=42505218,
result=0x181e1d88) at sql_select.cc:755
#14 0x807776c in mysql_execute_command () at sql_parse.cc:957
#15 0x807a5ea in mysql_parse (thd=0x81d7000,
inBuf=0x181e1010 "\n\t\t\t\tSELECT STRAIGHT_JOIN\n\t\t\t\t\t\tinf.inf, pos.id_ves,
temp.ves_name_tmp.ves,\n\t\t\t\t\t\tpos.date,
pos.datetime,\n\t\t\t\t\t\tpos.latitude, pos.longitude,\n\t\t\t\t\t\tpos.course,
pos.velocity\n\t\t\t\tFROM\n\t\t\t\t\t\t(pos LEFT
JOI"..., length=466) at sql_parse.cc:2085
#16 0x8076985 in do_command (thd=0x81d7000) at sql_parse.cc:668
#17 0x8075d8c in handle_one_connection (arg=0x81cc000) at sql_parse.cc:403
#18 0x281b49a7 in _thread_start () from /usr/lib/libc_r.so.4
#19 0x0 in ?? ()
-------------------------------------------------------------------------------------------
trace file fragment:
-------------------------------------------------------------------------------------------
?func: info: /usr/local/mysql/libexec/mysqld Ver 3.23.32-debug-log for
unknown-freebsdelf4.2 on i386
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
read_charset_file: info: file name:
/usr/local/mysql-3.23.32debug/share/mysql/charsets/cp1251.conf
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
get_charsets_dir: info: charsets
dir='/usr/local/mysql-3.23.32debug/share/mysql/charsets/'
server_init: general: IP Socket is 3306
server_init: general: UNIX Socket is /tmp/mysql.sock
server_init: info: server started
init_io_cache: info: init_io_cache: cachesize = 8192
open_table: info: inserting table 0x81c0c00 into the cache
open_table: info: inserting table 0x81d5800 into the cache
open_table: info: inserting table 0x181d7000 into the cache
mi_get_status: info: key_file: 1024 data_file: 0
mi_get_status: info: key_file: 2048 data_file: 642
mi_get_status: info: key_file: 3072 data_file: 1208
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 16384
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 16384
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 16384
close_thread_tables: info: thd->open_tables=0x181d7000
~THD(): info: freeing host
open_table: info: inserting table 0x81c0c00 into the cache
open_table: info: inserting table 0x181d8000 into the cache
mi_get_status: info: key_file: 1024 data_file: 0
mi_get_status: info: key_file: 1024 data_file: 0
mi_rnext: error: Got error: -1, errno: 137
close_thread_tables: info: thd->open_tables=0x181d8000
~THD(): info: freeing host
open_table: info: inserting table 0x81c0c00 into the cache
init_read_record: info: using rr_sequential
init_io_cache: info: init_io_cache: cachesize = 8192
close_thread_tables: info: thd->open_tables=0x81c0c00
~THD(): info: freeing host
handle_connections_sockets: general: Waiting for connections.
create_new_thread: info: creating thread 1
create_new_thread: info: Thread 1 created
create_new_thread: info: creating thread 2
create_new_thread: info: Thread 2 created
?func: info: handle_one_connection called by thread 1
?func: info: check_connections called by thread 1
?func: general: New connection received on socket (9)
?func: general: Host: localhost
vio_read: error: Got error 35 during read
?func: info: vio_read returned -1, errno: 35
thr_alarm: info: reschedule
process_alarm: info: sig: 14 active alarms: 1
?func: info: handle_one_connection called by thread 2
?func: info: check_connections called by thread 2
?func: general: New connection received on socket (10)
?func: general: Host: localhost
vio_read: error: Got error 35 during read
?func: info: vio_read returned -1, errno: 35
?func: general: Capabilities: 8333 packet_length: 0 Host: 'localhost' User:
'fish_reader' Using password: yes Access: 0 db: '*none*'
mysql_change_db: general: Use database: fishery
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
?func: general: Capabilities: 8333 packet_length: 0 Host: 'localhost' User:
'fish_reader' Using password: yes Access: 0 db: '*none*'
mysql_change_db: general: Use database: fishery
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
do_command: general: Command on socket (9) = 3 (Query)
do_command: query:
CREATE TEMPORARY TABLE IF NOT EXISTS temp.ves_name_tmp
(
id_ves SMALLINT UNSIGNED NOT NULL,
ves CHAR(30) NOT NULL,
PRIMARY KEY( id_ves )
)
my_chsize: info: old_size: 4096
my_chsize: info: old_size: 347
do_command: info: query ready
(skipping)
process_alarm: info: sig: 14 active alarms: 0
create_new_thread: info: Thread 10 created
?func: info: check_connections called by thread 10
?func: general: New connection received on socket (9)
?func: general: Host: localhost
vio_read: error: Got error 35 during read
?func: info: vio_read returned -1, errno: 35
thr_alarm: info: reschedule
process_alarm: info: sig: 14 active alarms: 1
?func: general: Capabilities: 8333 packet_length: 0 Host: 'localhost' User:
'fish_reader' Using password: yes Access: 0 db: '*none*'
mysql_change_db: general: Use database: fishery
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
thr_alarm: info: reschedule
process_alarm: info: sig: 14 active alarms: 1
do_command: general: Command on socket (9) = 3 (Query)
do_command: query:
CREATE TEMPORARY TABLE IF NOT EXISTS temp.ves_name_tmp
(
id_ves SMALLINT UNSIGNED NOT NULL,
ves CHAR(30) NOT NULL,
PRIMARY KEY( id_ves )
)
my_chsize: info: old_size: 4096
my_chsize: info: old_size: 347
do_command: info: query ready
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
thr_alarm: info: reschedule
do_command: general: Command on socket (9) = 3 (Query)
do_command: query:
SELECT
*
FROM
ves
WHERE
id_ves = 15017
AND date_expiration_ves IS NULL
ORDER BY
date_registration_ves DESC
LIMIT
1
mi_get_status: info: key_file: 287744 data_file: 3384858
WHERE:(original) ((ves.id_ves = 15017) and isnull(ves.date_expiration_ves))
WHERE:(after const change) ((ves.id_ves = 15017) and isnull(ves.date_expiration_ves))
WHERE:(after remove) ((ves.id_ves = 15017) and isnull(ves.date_expiration_ves))
test_quick_select: info: Time to scan table: 2213
_mi_search_pos: info: keynr: 31 offset: 1 max_keynr: 112 nod: 0 flag: 0
_mi_search_pos: info: keynr: 41 offset: 0.283186 max_keynr: 61 nod: 3 flag: 1
_mi_search_pos: info: keynr: 32 offset: 1 max_keynr: 112 nod: 0 flag: 1
_mi_search_pos: info: keynr: 41 offset: 0.292035 max_keynr: 61 nod: 3 flag: 1
mi_records_in_range: info: records: 1
_mi_search_pos: info: keynr: 74 offset: 1 max_keynr: 169 nod: 0 flag: 0
_mi_search_pos: info: keynr: 27 offset: 0.441176 max_keynr: 40 nod: 3 flag: 1
_mi_search_pos: info: keynr: 75 offset: 1 max_keynr: 169 nod: 0 flag: 1
_mi_search_pos: info: keynr: 27 offset: 0.447059 max_keynr: 40 nod: 3 flag: 1
mi_records_in_range: info: records: 1
Used quick_range on key: 0 (other_keys: 0):
15017 <= X <= 15017
WHERE:(ves) isnull(ves.date_expiration_ves)
Info about JOIN
ves type: ref q_keys: 3 refs: 1 key: 0 len: 2
select used
refs: 15017
mysql_select: info: Sorting for send_fields
Info about FILESORT
Sortorder: -ves.date_registration_ves
filesort: info: sort_length: 3
find_all_keys: info: using: ranges
do_select: info: 1 records output
do_command: info: query ready
close_thread_tables: info: thd->open_tables=0x181e4000
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
thr_alarm: info: reschedule
process_alarm: info: sig: 14 active alarms: 1
process_alarm: info: sig: 14 active alarms: 1
do_command: general: Command on socket (9) = 3 (Query)
do_command: query:
INSERT IGNORE INTO temp.ves_name_tmp
VALUES( 15017, 'áíáòåìø' )
do_command: info: query ready
(skipping)
-------------------------------------------------------------------------------------------
some records GREPping out of trace for word "err"
mi_rnext: error: Got error: -1, errno: 137
vio_read: error: Got error 35 during read
?func: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
?func: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
do_command: info: vio_read returned -1, errno: 35
vio_read: error: Got error 35 during read
(and so on... server remains live for a while)
-------------------------------------------------------------------------------------------
(as U see, error 35 is the norm of our life...)
Unfortunately, we often have problems with MySQL instability when
serving "large" databases (>500 Meg), on different hardware and with
different versions of FreeBSD and MySQL. May it be so that this
problem is FreeBSD-specific? Analysis of mailing lists and
documentation was not helpful, though it looks like others often have
similar problems. Can anyone point to example of stable operations of
pair FreeBSD+MySQL with complex database under heavy load?
Alexander Degay,
Software engineer,
SMIS Lab, Space Research Institute (Moscow, Russia).
------------------------
[EMAIL PROTECTED]
+7(095)333-5313(w) +7(902)124-7162(mob)
http://smis.iki.rssi.ru, http://meteo.infospace.ru
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
