aleborg
asked on
Out of resources when opening file
I get this error every now and then:
mySQL error: Out of resources when opening file 'C:\WINNT\TEMP\#sql_318_0. MYI' (Errcode: 24)
mySQL error code: 23
After restarting the server everything works again
Any ideas?
mySQL error: Out of resources when opening file 'C:\WINNT\TEMP\#sql_318_0.
mySQL error code: 23
After restarting the server everything works again
Any ideas?
ASKER
I have Win2k Server, all SP's and patches, plenty of free GB HD and 1 GB DDR-Ram, Intel 2,4 Ghz CPU
What is your database size (by number of tables) and traffic?
When you do
STATUS;
how many open tables do you have usually?
When you do
STATUS;
how many open tables do you have usually?
ASKER
Aborted_clients52
Aborted_connects10
Bytes_received14908966
Bytes_sent286737430
Com_admin_commands320
Com_alter_table0
Com_analyze0
Com_backup_table0
Com_begin0
Com_change_db13116
Com_change_master0
Com_check2
Com_commit0
Com_create_db0
Com_create_function0
Com_create_index0
Com_create_table1
Com_delete2016
Com_delete_multi0
Com_drop_db0
Com_drop_function0
Com_drop_index0
Com_drop_table2
Com_flush0
Com_grant0
Com_ha_close0
Com_ha_open0
Com_ha_read0
Com_insert2185
Com_insert_select98
Com_kill0
Com_load0
Com_load_master_data0
Com_load_master_table0
Com_lock_tables0
Com_optimize10
Com_purge0
Com_rename_table0
Com_repair2
Com_replace105
Com_replace_select0
Com_reset0
Com_restore_table0
Com_revoke0
Com_rollback0
Com_savepoint0
Com_select51998
Com_set_option0
Com_show_binlog_events0
Com_show_binlogs0
Com_show_create5
Com_show_databases1
Com_show_fields713
Com_show_grants0
Com_show_keys716
Com_show_logs0
Com_show_master_status0
Com_show_new_master0
Com_show_open_tables0
Com_show_processlist3
Com_show_slave_hosts0
Com_show_slave_status0
Com_show_status8
Com_show_innodb_status0
Com_show_tables60
Com_show_variables3
Com_slave_start0
Com_slave_stop0
Com_truncate0
Com_unlock_tables0
Com_update5176
Connections6603
Created_tmp_disk_tables301
Created_tmp_tables4200
Created_tmp_files0
Delayed_insert_threads0
Delayed_writes0
Delayed_errors0
Flush_commands1
Handler_commit0
Handler_delete1600
Handler_read_first462292
Handler_read_key1580684
Handler_read_next4000494
Handler_read_prev13361
Handler_read_rnd206194
Handler_read_rnd_next19652 446
Handler_rollback0
Handler_update279430
Handler_write832988
Key_blocks_used43875
Key_read_requests4383178
Key_reads41653
Key_write_requests168549
Key_writes6999
Max_used_connections28
Not_flushed_key_blocks0
Not_flushed_delayed_rows0
Open_tables216
Open_files332
Open_streams0
Opened_tables1824
Questions82799
Qcache_queries_in_cache0
Qcache_inserts0
Qcache_hits0
Qcache_lowmem_prunes0
Qcache_not_cached51993
Qcache_free_memory67099960
Qcache_free_blocks1
Qcache_total_blocks1
Rpl_statusNULL
Select_full_join1779
Select_full_range_join32
Select_range7897
Select_range_check0
Select_scan19822
Slave_open_temp_tables0
Slave_runningOFF
Slow_launch_threads0
Slow_queries0
Sort_merge_passes0
Sort_range3065
Sort_rows234824
Sort_scan9521
Table_locks_immediate92759
Table_locks_waited2
Threads_cached1
Threads_created77
Threads_connected14
Threads_running1
Uptime7737
Aborted_connects10
Bytes_received14908966
Bytes_sent286737430
Com_admin_commands320
Com_alter_table0
Com_analyze0
Com_backup_table0
Com_begin0
Com_change_db13116
Com_change_master0
Com_check2
Com_commit0
Com_create_db0
Com_create_function0
Com_create_index0
Com_create_table1
Com_delete2016
Com_delete_multi0
Com_drop_db0
Com_drop_function0
Com_drop_index0
Com_drop_table2
Com_flush0
Com_grant0
Com_ha_close0
Com_ha_open0
Com_ha_read0
Com_insert2185
Com_insert_select98
Com_kill0
Com_load0
Com_load_master_data0
Com_load_master_table0
Com_lock_tables0
Com_optimize10
Com_purge0
Com_rename_table0
Com_repair2
Com_replace105
Com_replace_select0
Com_reset0
Com_restore_table0
Com_revoke0
Com_rollback0
Com_savepoint0
Com_select51998
Com_set_option0
Com_show_binlog_events0
Com_show_binlogs0
Com_show_create5
Com_show_databases1
Com_show_fields713
Com_show_grants0
Com_show_keys716
Com_show_logs0
Com_show_master_status0
Com_show_new_master0
Com_show_open_tables0
Com_show_processlist3
Com_show_slave_hosts0
Com_show_slave_status0
Com_show_status8
Com_show_innodb_status0
Com_show_tables60
Com_show_variables3
Com_slave_start0
Com_slave_stop0
Com_truncate0
Com_unlock_tables0
Com_update5176
Connections6603
Created_tmp_disk_tables301
Created_tmp_tables4200
Created_tmp_files0
Delayed_insert_threads0
Delayed_writes0
Delayed_errors0
Flush_commands1
Handler_commit0
Handler_delete1600
Handler_read_first462292
Handler_read_key1580684
Handler_read_next4000494
Handler_read_prev13361
Handler_read_rnd206194
Handler_read_rnd_next19652
Handler_rollback0
Handler_update279430
Handler_write832988
Key_blocks_used43875
Key_read_requests4383178
Key_reads41653
Key_write_requests168549
Key_writes6999
Max_used_connections28
Not_flushed_key_blocks0
Not_flushed_delayed_rows0
Open_tables216
Open_files332
Open_streams0
Opened_tables1824
Questions82799
Qcache_queries_in_cache0
Qcache_inserts0
Qcache_hits0
Qcache_lowmem_prunes0
Qcache_not_cached51993
Qcache_free_memory67099960
Qcache_free_blocks1
Qcache_total_blocks1
Rpl_statusNULL
Select_full_join1779
Select_full_range_join32
Select_range7897
Select_range_check0
Select_scan19822
Slave_open_temp_tables0
Slave_runningOFF
Slow_launch_threads0
Slow_queries0
Sort_merge_passes0
Sort_range3065
Sort_rows234824
Sort_scan9521
Table_locks_immediate92759
Table_locks_waited2
Threads_cached1
Threads_created77
Threads_connected14
Threads_running1
Uptime7737
ASKER
Little easier to read:
Aborted_clients 52
Aborted_connects 10
Bytes_received 14908966
Bytes_sent 286737430
Com_admin_commands 320
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 13116
Com_change_master 0
Com_check 2
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 1
Com_delete 2016
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 2
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 2185
Com_insert_select 98
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 10
Com_purge 0
Com_rename_table 0
Com_repair 2
Com_replace 105
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 51998
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 5
Com_show_databases 1
Com_show_fields 713
Com_show_grants 0
Com_show_keys 716
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 3
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 8
Com_show_innodb_status 0
Com_show_tables 60
Com_show_variables 3
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 5176
Connections 6603
Created_tmp_disk_tables 301
Created_tmp_tables 4200
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 1600
Handler_read_first 462292
Handler_read_key 1580684
Handler_read_next 4000494
Handler_read_prev 13361
Handler_read_rnd 206194
Handler_read_rnd_next 19652446
Handler_rollback 0
Handler_update 279430
Handler_write 832988
Key_blocks_used 43875
Key_read_requests 4383178
Key_reads 41653
Key_write_requests 168549
Key_writes 6999
Max_used_connections 28
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 216
Open_files 332
Open_streams 0
Opened_tables 1824
Questions 82799
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 51993
Qcache_free_memory 67099960
Qcache_free_blocks 1
Qcache_total_blocks 1
Rpl_status NULL
Select_full_join 1779
Select_full_range_join 32
Select_range 7897
Select_range_check 0
Select_scan 19822
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 3065
Sort_rows 234824
Sort_scan 9521
Table_locks_immediate 92759
Table_locks_waited 2
Threads_cached 1
Threads_created 77
Threads_connected 14
Threads_running 1
Uptime 7737
Aborted_clients 52
Aborted_connects 10
Bytes_received 14908966
Bytes_sent 286737430
Com_admin_commands 320
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_change_db 13116
Com_change_master 0
Com_check 2
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 1
Com_delete 2016
Com_delete_multi 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 2
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_insert 2185
Com_insert_select 98
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 10
Com_purge 0
Com_rename_table 0
Com_repair 2
Com_replace 105
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_rollback 0
Com_savepoint 0
Com_select 51998
Com_set_option 0
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_create 5
Com_show_databases 1
Com_show_fields 713
Com_show_grants 0
Com_show_keys 716
Com_show_logs 0
Com_show_master_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_processlist 3
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 8
Com_show_innodb_status 0
Com_show_tables 60
Com_show_variables 3
Com_slave_start 0
Com_slave_stop 0
Com_truncate 0
Com_unlock_tables 0
Com_update 5176
Connections 6603
Created_tmp_disk_tables 301
Created_tmp_tables 4200
Created_tmp_files 0
Delayed_insert_threads 0
Delayed_writes 0
Delayed_errors 0
Flush_commands 1
Handler_commit 0
Handler_delete 1600
Handler_read_first 462292
Handler_read_key 1580684
Handler_read_next 4000494
Handler_read_prev 13361
Handler_read_rnd 206194
Handler_read_rnd_next 19652446
Handler_rollback 0
Handler_update 279430
Handler_write 832988
Key_blocks_used 43875
Key_read_requests 4383178
Key_reads 41653
Key_write_requests 168549
Key_writes 6999
Max_used_connections 28
Not_flushed_key_blocks 0
Not_flushed_delayed_rows 0
Open_tables 216
Open_files 332
Open_streams 0
Opened_tables 1824
Questions 82799
Qcache_queries_in_cache 0
Qcache_inserts 0
Qcache_hits 0
Qcache_lowmem_prunes 0
Qcache_not_cached 51993
Qcache_free_memory 67099960
Qcache_free_blocks 1
Qcache_total_blocks 1
Rpl_status NULL
Select_full_join 1779
Select_full_range_join 32
Select_range 7897
Select_range_check 0
Select_scan 19822
Slave_open_temp_tables 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 3065
Sort_rows 234824
Sort_scan 9521
Table_locks_immediate 92759
Table_locks_waited 2
Threads_cached 1
Threads_created 77
Threads_connected 14
Threads_running 1
Uptime 7737
Ok, can you give the output of SHOW VARIABLES please?
Also, have you tried logging slow queries?
See http://www.mysql.com/doc/en/Slow_query_log.html
Also, have you tried logging slow queries?
See http://www.mysql.com/doc/en/Slow_query_log.html
ASKER
Havn't tried logging slow queries, can that be set in my.ini?
back_log 50
basedir C:\mysql\
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1' big5' czech' euc_kr' gb2312' gbk' latin1_de' sjis' tis620' ujis' 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
convert_character_set
datadir C:\mysql\data\
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 1800
ft_boolean_syntax +' -><()~*:""&|
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_min_word_len 4
ft_stopword_file (built-in)
have_bdb NO
have_crypt NO
have_innodb YES
have_isam YES
have_openssl NO
have_query_cache YES
have_raid NO
have_symlink YES
init_file
innodb_additional_mem_pool _size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_fast_shutdown ON
innodb_file_io_threads 4
innodb_flush_log_at_trx_co mmit 1
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_log_arch_dir .\
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_max_dirty_pages_pct 90
innodb_mirrored_log_groups 1
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 67108864
language C:\mysql\share\english\
large_files_support ON
local_infile ON
log OFF
log_bin ON
log_error .\w2k-4.err
log_slave_updates OFF
log_slow_queries OFF
log_update OFF
log_warnings OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names ON
max_allowed_packet 8387584
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 2000
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
myisam_max_extra_sort_file _size 268435456
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 268435456
named_pipe OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 0
pid_file C:\mysql\data\w2k-4.pid
port 3306
protocol_version 10
query_cache_limit 1048576
query_cache_size 67108864
query_cache_type DEMAND
read_buffer_size 8384512
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 1
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_net_timeout 3600
slow_launch_time 2
sort_buffer_size 8388600
sql_mode 0
table_cache 16384
table_type MYISAM
thread_cache_size 8
thread_stack 196608
timezone W.' Europe' Standard' Time
tmp_table_size 33554432
tmpdir C:\WINNT\TEMP\
tx_isolation REPEATABLE-READ
version 4.0.15-nt-log
wait_timeout 400
back_log 50
basedir C:\mysql\
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1' big5' czech' euc_kr' gb2312' gbk' latin1_de' sjis' tis620' ujis' 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
convert_character_set
datadir C:\mysql\data\
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 1800
ft_boolean_syntax +' -><()~*:""&|
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_min_word_len 4
ft_stopword_file (built-in)
have_bdb NO
have_crypt NO
have_innodb YES
have_isam YES
have_openssl NO
have_query_cache YES
have_raid NO
have_symlink YES
init_file
innodb_additional_mem_pool
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_fast_shutdown ON
innodb_file_io_threads 4
innodb_flush_log_at_trx_co
innodb_flush_method
innodb_force_recovery 0
innodb_lock_wait_timeout 50
innodb_log_arch_dir .\
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_max_dirty_pages_pct
innodb_mirrored_log_groups
innodb_thread_concurrency 8
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 67108864
language C:\mysql\share\english\
large_files_support ON
local_infile ON
log OFF
log_bin ON
log_error .\w2k-4.err
log_slave_updates OFF
log_slow_queries OFF
log_update OFF
log_warnings OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names ON
max_allowed_packet 8387584
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connect_errors 10
max_connections 2000
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_tmp_tables 32
max_user_connections 0
max_write_lock_count 4294967295
myisam_max_extra_sort_file
myisam_max_sort_file_size 2147483647
myisam_recover_options OFF
myisam_repair_threads 1
myisam_sort_buffer_size 268435456
named_pipe OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 0
pid_file C:\mysql\data\w2k-4.pid
port 3306
protocol_version 10
query_cache_limit 1048576
query_cache_size 67108864
query_cache_type DEMAND
read_buffer_size 8384512
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 1
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slave_net_timeout 3600
slow_launch_time 2
sort_buffer_size 8388600
sql_mode 0
table_cache 16384
table_type MYISAM
thread_cache_size 8
thread_stack 196608
timezone W.' Europe' Standard' Time
tmp_table_size 33554432
tmpdir C:\WINNT\TEMP\
tx_isolation REPEATABLE-READ
version 4.0.15-nt-log
wait_timeout 400
I believe you can set it in my.ini, or just use a command line arg. The link covers it pretty well.
I think the table cache may be killing you, 16384 is way too high. The my-huge.cnf file has it set to 512, which is pretty big. Max_connections at 2000 is also super high and is defaulted at 100, best turn it up as you actualy get connection refused problems, maybe 200 to start if you want to be really ambitious.
Anyway, probably a good idea to throttle that query cache back, you probably have too many file descriptors for Windows to handle.
What is your my.cnf/ini based on? What does it look like?
I think the table cache may be killing you, 16384 is way too high. The my-huge.cnf file has it set to 512, which is pretty big. Max_connections at 2000 is also super high and is defaulted at 100, best turn it up as you actualy get connection refused problems, maybe 200 to start if you want to be really ambitious.
Anyway, probably a good idea to throttle that query cache back, you probably have too many file descriptors for Windows to handle.
What is your my.cnf/ini based on? What does it look like?
Oh, and upgrading to 4.0.16 can't hurt.
Oh, and upgrading to 4.0.16 can't hurt.
ASKER
I've upgraded to 4.0.16
I've done some changes in my.ini and used large.cnf as template, and the error still remains, this is what I get on a SHOW VARIABLES:
back_log 50
basedir C:\mysql\
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1' big5' czech' euc_kr' gb2312' gbk' latin1_de' sjis' tis620' ujis' 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
convert_character_set
datadir C:\mysql\data\
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 1800
ft_boolean_syntax +' -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt NO
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool _size 1048576
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_co mmit 1
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir .\
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_mirrored_log_groups 1
innodb_max_dirty_pages_pct 90
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 268435456
language C:\mysql\share\english\
large_files_support ON
local_infile ON
log OFF
log_update OFF
log_bin ON
log_slave_updates OFF
log_slow_queries OFF
log_warnings OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names ON
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 300
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file _size 268435456
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 67108864
named_pipe OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 0
pid_file C:\mysql\data\w2k-4.pid
log_error .\w2k-4.err
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 67108864
query_cache_type DEMAND
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 1
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
sort_buffer_size 1048568
sql_mode 0
table_cache 16384
table_type MYISAM
thread_cache_size 8
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone W.' Europe' Standard' Time
tmp_table_size 33554432
tmpdir C:\WINNT\TEMP\
transaction_alloc_block_si ze 8192
transaction_prealloc_size 4096
version 4.0.16-nt-log
wait_timeout 400
I've done some changes in my.ini and used large.cnf as template, and the error still remains, this is what I get on a SHOW VARIABLES:
back_log 50
basedir C:\mysql\
binlog_cache_size 32768
bulk_insert_buffer_size 8388608
character_set latin1
character_sets latin1' big5' czech' euc_kr' gb2312' gbk' latin1_de' sjis' tis620' ujis' 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
convert_character_set
datadir C:\mysql\data\
default_week_format 0
delay_key_write ON
delayed_insert_limit 100
delayed_insert_timeout 300
delayed_queue_size 1000
flush OFF
flush_time 1800
ft_boolean_syntax +' -><()~*:""&|
ft_min_word_len 4
ft_max_word_len 254
ft_max_word_len_for_sort 20
ft_stopword_file (built-in)
have_bdb NO
have_crypt NO
have_innodb YES
have_isam YES
have_raid NO
have_symlink YES
have_openssl NO
have_query_cache YES
init_file
innodb_additional_mem_pool
innodb_buffer_pool_size 8388608
innodb_data_file_path ibdata1:10M:autoextend
innodb_data_home_dir
innodb_file_io_threads 4
innodb_force_recovery 0
innodb_thread_concurrency 8
innodb_flush_log_at_trx_co
innodb_fast_shutdown ON
innodb_flush_method
innodb_lock_wait_timeout 50
innodb_log_arch_dir .\
innodb_log_archive OFF
innodb_log_buffer_size 1048576
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir .\
innodb_mirrored_log_groups
innodb_max_dirty_pages_pct
interactive_timeout 28800
join_buffer_size 131072
key_buffer_size 268435456
language C:\mysql\share\english\
large_files_support ON
local_infile ON
log OFF
log_update OFF
log_bin ON
log_slave_updates OFF
log_slow_queries OFF
log_warnings OFF
long_query_time 10
low_priority_updates OFF
lower_case_table_names ON
max_allowed_packet 1047552
max_binlog_cache_size 4294967295
max_binlog_size 1073741824
max_connections 300
max_connect_errors 10
max_delayed_threads 20
max_heap_table_size 16777216
max_join_size 4294967295
max_relay_log_size 0
max_seeks_for_key 4294967295
max_sort_length 1024
max_user_connections 0
max_tmp_tables 32
max_write_lock_count 4294967295
myisam_max_extra_sort_file
myisam_max_sort_file_size 2147483647
myisam_repair_threads 1
myisam_recover_options OFF
myisam_sort_buffer_size 67108864
named_pipe OFF
net_buffer_length 16384
net_read_timeout 30
net_retry_count 10
net_write_timeout 60
new OFF
open_files_limit 0
pid_file C:\mysql\data\w2k-4.pid
log_error .\w2k-4.err
port 3306
protocol_version 10
query_alloc_block_size 8192
query_cache_limit 1048576
query_cache_size 67108864
query_cache_type DEMAND
query_prealloc_size 8192
range_alloc_block_size 2048
read_buffer_size 1044480
read_only OFF
read_rnd_buffer_size 262144
rpl_recovery_rank 0
server_id 1
slave_net_timeout 3600
skip_external_locking ON
skip_networking OFF
skip_show_database OFF
slow_launch_time 2
sort_buffer_size 1048568
sql_mode 0
table_cache 16384
table_type MYISAM
thread_cache_size 8
thread_stack 196608
tx_isolation REPEATABLE-READ
timezone W.' Europe' Standard' Time
tmp_table_size 33554432
tmpdir C:\WINNT\TEMP\
transaction_alloc_block_si
transaction_prealloc_size 4096
version 4.0.16-nt-log
wait_timeout 400
ASKER
my.ini:
# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# The MySQL server
[mysqld]
port=3306
safe-show-database
#socket=MySQL
skip-locking
wait_timeout = 400
set-variable = max_connections=300
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256M
query_cache_type = 2
query_cache_size = 64M
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64 M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
log-bin
server-id = 1
#log = c:/mysql/error.log
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout = 300
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld -nt.exe
# Example mysql config file.
# Copy this file to c:\my.cnf to set global options
#
# One can use all long options that the program supports.
# Run the program with --help to get a list of available options
# This will be passed to all mysql clients
[client]
#password=my_password
port=3306
#socket=MySQL
# Here is entries for some specific programs
# The following values assume you have at least 32M ram
# The MySQL server
[mysqld]
port=3306
safe-show-database
#socket=MySQL
skip-locking
wait_timeout = 400
set-variable = max_connections=300
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256M
query_cache_type = 2
query_cache_size = 64M
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
log-bin
server-id = 1
#log = c:/mysql/error.log
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout = 300
[WinMySQLAdmin]
Server=C:/mysql/bin/mysqld
Table cache is a number in units of tables, not bytes. Specifying 256M means you are allowing 16384 table descriptors, change your table cache setting to be 600.
ASKER
My customers keep getting these errors:
MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt-l og]MySQL server has gone away
[MySQL][ODBC 3.51 Driver]MySQL client run out of memory
Some databases work and some don't :(
MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt-l
[MySQL][ODBC 3.51 Driver]MySQL client run out of memory
Some databases work and some don't :(
ASKER
Noticed that the last errors stoped when I made a restart of IIS, seems like MyODBC leaks memory!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What version of Windows are you hosting this on? What kind of RAM/HD capacity do you have?