Solved

Out of resources when opening file

Posted on 2003-11-04
18
6,959 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:aleborg
  • 8
  • 8
18 Comments
 
LVL 17

Expert Comment

by:Squeebee
ID: 9682034
I am going to take a stab in the dark and guess a lack of file handles. If you do FLUSH TABLES instead of a restart it may go away.

What version of Windows are you hosting this on? What kind of RAM/HD capacity do you have?
0
 

Author Comment

by:aleborg
ID: 9682355
I have Win2k Server, all SP's and patches, plenty of free GB HD and 1 GB DDR-Ram, Intel 2,4 Ghz CPU
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9682493
What is your database size (by number of tables) and traffic?

When you do

STATUS;

how many open tables do you have usually?
0
 

Author Comment

by:aleborg
ID: 9682684
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_next19652446
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
0
 

Author Comment

by:aleborg
ID: 9682793
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    
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9682819
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
0
 

Author Comment

by:aleborg
ID: 9682887
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_commit    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    
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9683009
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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17

Expert Comment

by:Squeebee
ID: 9683013
Oh, and upgrading to 4.0.16 can't hurt.
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9683014
Oh, and upgrading to 4.0.16 can't hurt.
0
 

Author Comment

by:aleborg
ID: 9697077
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_commit      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_size      8192      
transaction_prealloc_size      4096      
version      4.0.16-nt-log      
wait_timeout      400      
0
 

Author Comment

by:aleborg
ID: 9697089
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=64M
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
0
 
LVL 17

Expert Comment

by:Squeebee
ID: 9697511
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.
0
 

Author Comment

by:aleborg
ID: 9739979
My customers keep getting these errors:
MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt-log]MySQL server has gone away
[MySQL][ODBC 3.51 Driver]MySQL client run out of memory
Some databases work and some don't :(
0
 

Author Comment

by:aleborg
ID: 9740046
Noticed that the last errors stoped when I made a restart of IIS, seems like MyODBC leaks memory!
0
 
LVL 17

Accepted Solution

by:
Squeebee earned 250 total points
ID: 9740240
I'll see if I can put in a bug report.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now