>Description: I'm running MySQL-3.23.51 stable RPMS on the master for a replicated database here. It is processing a large quantity (9,000,000 rows/table/day) of usage records in a pretty much constant flow of inserts. The raw usage tables consume roughly 400MB/table/day in .MYD and about the same in indices. For performance reasons I aggregate the inserts using the bulk "INSERT INTO table VALUES (..), (..), (..)" statements. There are typically between 2 and 3000 records batched together in this manner. For performance reasons I have opted to not write the indices to disk (DELAY_KEY_WRITE=1 on the MyISAM tables). This is a *big* win. Every hour/day the previous hour/days records are "summarised" up a level. (REPLACE INTO summary_table SELECT foo FROM raw_usage_table GROUP BY bar) I'm replicating from a MySQL 3.23 master to a number of 4.01a slaves.
>How-To-Repeat: After the days' usage table has accumulated a large number of records, it seems to crash the index regularly when the summarise batch job runs. i.e. between 10pm and midnight INSERT statements running in parallel to the summarise action crashes the indices with the following error message: "Incorrect key file for table: 'tablename'. Try to repair it" The resolved stack traces from a number of the incidents are attached inline at the bottom of this mail. Interestingly the slave machines have never once crashed, processing exactly the same modify operations ? I am very tempted to switch to 4.01 on the master as it seems more robust. >Fix: No idea how to workaround, I've tried increasing and decreases all manner of memory/cache sizes to no avail. The system is currently doing a REPAIR TABLE foo QUICK every time the application restarts :( The other option was to disable the DELAY_KEY_WRITE feature, but it took a very large performance hit and was less desirable. >Submitter-Id: <submitter ID> >Originator: root >Organization: <organization of PR author (multiple lines)> >MySQL support: none >Synopsis: MySQL 3.23 has index crashes with DELAY_KEY_WRITE=1 >Severity: serious >Priority: medium >Category: mysql >Class: sw-bug >Release: mysql-3.23.51 (Official MySQL RPM) >Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.51, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult 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 3.23.51-Max-log Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 4 hours 58 min 0 sec Threads: 6 Questions: 312397 Slow queries: 95 Opens: 56 Flush tables: 1 Open tables: 23 Queries per second avg: 17.472 >Environment: Intel PIII, 1GB RAM, Redhat-6.2 base, official mysql.com RPMS. System: Linux machine1 2.2.19-6.2.16enterprise #1 SMP Wed Mar 13 13:46:30 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 May 22 2001 /lib/libc.so.6 -> libc-2.1.3.so -rwxr-xr-x 1 root root 4105868 Dec 9 2001 /lib/libc-2.1.3.so -rw-r--r-- 1 root root 20299228 Dec 9 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Dec 9 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc Perl: This is perl, version 5.005_03 built for i386-linux STACK TRACES: 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x82e7777 _end + 482843 0x82e7503 _end + 482215 0x82a6186 _end + 215082 0x834a3e2 _end + 887430 0x8068abf send_data__11select_sendRt4List1Z4Item + 39 0x806eb94 end_thread__FP3THDb + 256 0x8074461 mysql_table_dump__FP3THDPcT1i + 1 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x82e7669 _end + 482573 0x82e7503 _end + 482215 0x82a6186 _end + 215082 0x8294d37 _end + 144347 0x80b8a6a ft_read__9ha_myisamPc + 14 0x808b211 make_new_entry__FiPUcP10st_typelibPCc + 645 0x8088fa0 unlink_open_table__FP3THDP8st_tableT1 + 12 0x82ab645 _end + 236777 0x8085893 close_temporary_tables__FP3THD + 535 0x80c7de2 mysqld_show_keys__FP3THDP13st_table_list + 2174 0x80cab7c proc_analyse_init__FP3THDP8st_orderP13select_resultRt4List1Z4Item + 156 0x8076b2f mysql_execute_command__Fv + 5227 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x806c28b mysql_lock_abort__FP3THDP8st_table + 39 0x80912b5 remove_const__FP4JOINP8st_orderP4ItemPb + 349 0x808d628 make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 96 0x8075d20 mysql_execute_command__Fv + 1628 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x806c28e mysql_lock_abort__FP3THDP8st_table + 42 0x80912b5 remove_const__FP4JOINP8st_orderP4ItemPb + 349 0x808d628 make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 96 0x8077224 mysql_execute_command__Fv + 7008 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x82e740a _end + 481966 0x82e6b61 _end + 479749 0x82a60fe _end + 214946 0x8096299 remove_duplicates__FP4JOINP8st_tableRt4List1Z4ItemP4Item + 61 0x808d858 make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 656 0x8075d20 mysql_execute_command__Fv + 1628 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x82ea6ff _end + 495011 0x8288c57 _end + 94971 0x8291808 _end + 130732 0x82916b4 _end + 130392 0x82921d5 _end + 133241 0x829108f _end + 128819 0x80b8ad5 update_create_info__12ha_myisammrgP24st_ha_create_information + 21 0x809aec5 delayed_get_table__FP3THDP13st_table_list + 817 0x809a7d5 mysql_insert__FP3THDP13st_table_listRt4List1Z4ItemRt4List1Zt4List1Z4Item15enum_duplicates13thr_lock_type + 2013 0x8076f1a mysql_execute_command__Fv + 6230 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 0x806eeb4 init_signals__Fv + 16 0x82be908 _end + 315308 0x82e740a _end + 481966 0x82e6b61 _end + 479749 0x82a60fe _end + 214946 0x82a9d1e _end + 230338 0x80b4cee get_new_handler__FP8st_table7db_type + 366 0x80b4026 make_sortkey__FP13st_sort_paramPUcPc + 806 0x80b3947 find_all_keys__FP13st_sort_paramP10SQL_SELECTPPUcP10st_buffpekPUiP11st_io_cacheT5 + 379 0x8096345 remove_duplicates__FP4JOINP8st_tableRt4List1Z4ItemP4Item + 233 0x808d858 make_join_statistics__FP4JOINP13st_table_listP4ItemP16st_dynamic_array + 656 0x8075d20 mysql_execute_command__Fv + 1628 0x8079dbc reload_acl_and_cache__FP3THDUiP13st_table_list + 216 0x8074ee4 do_command__FP3THD + 2276 0x8074297 handle_bootstrap__FPv + 303 --------------------------------------------------------------------- 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
