Hi,
I'm in desperate need of help.
MySQL keeps dying and I just can't figure out why...
Here goes:
Clients connect from Windows machines via VB and Delphi apps
through myodbc doing queries on databases with *big* tables containing over
1.6 million records
Often they're losing connection because mysql dies, queries have to be done
again.
Error log says that mysqld restarted, i.e. safe_mysqld immediately restarts
the server.
Only mysql is running on the machine.
Queries may look like this:
CREATE TEMPORARY TABLE tmpTable(FRM varchar(5),ARKL varchar(5),KNR
varchar(10),ZWT float(11,5),VPN float(11,5),GRS varchar(32),MNG
float(11,5),BRW float(11,5), NTW float(11,5));
INSERT INTO tmpTable(FRM,ARKL,KNR,ZWT,VPN,GRS,MNG,BRW, NTW)SELECT
left(MNR,5),right(MNR,5),KNR,ZWT/MNG,VPN,substring_index(GRS,'X',1)*
right(substring_index(GRS,'X',2),length(substring_index(GRS,'X',2))-length(s
ubstring_index(GRS,'X',1))-1)*
right(GRS,length(GRS)-length(substring_index(GRS,'X',2))-1)/1000,MNG,BRW,NTW
FROM MASTER WHERE (BLDAT >= 20010901 AND BLT <= 20011231 AND SRT = '04' AND
SRT = '06')
MASTER is one *big* table containing 1.6 Million records...
Info on the system:
netbsd 1.5.2, 512 mb RAM
mysql --version
mysql Ver 11.13 Distrib 3.23.35, for -netbsd (i386)
mysqladmin variables:
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+
| Variable_name | Value
|
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+
| ansi_mode | OFF
|
| back_log | 50
|
| basedir | /usr/pkg/
|
| binlog_cache_size | 32768
|
| character_set | latin1
|
| 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 | 10
|
| datadir | /usr/var/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 | 16773120
|
| language | /usr/pkg/share/mysql/english/
|
| large_files_support | ON
|
| locked_in_memory | OFF
|
| 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_binlog_size | 1073741824
|
| 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_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_recover_options | OFF
|
| myisam_sort_buffer_size | 8388608
|
| net_buffer_length | 7168
|
| net_read_timeout | 120
|
| net_retry_count | 40
|
| net_write_timeout | 240
|
| open_files_limit | 0
|
| pid_file | /usr/var/mysql/unixtest.pid
|
| port | 3306
|
| protocol_version | 10
|
| record_buffer | 131072
|
| query_buffer_size | 1044480
|
| 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 | 524280
|
| table_cache | 64
|
| table_type | MYISAM
|
| thread_cache_size | 0
|
| thread_stack | 65536
|
| timezone | CET
|
| tmp_table_size | 1048576
|
| tmpdir | /var/tmp/
|
| version | 3.23.35-log
|
| wait_timeout | 115200
|
+-------------------------+-------------------------------------------------
----------------------------------------------------------------------------
------------------------------+
|
core dumps available...
(they're BIG -21M)
Help is very much appreciated...
Marc
---------------------------------------------------------------------
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
