Home » Performance » MySQL » ERROR 1041 (HY000) OutOfMemory
ERROR 1041 (HY000) OutOfMemory [message #3635] Thu, 16 October 2008 07:16 Go to next message
sterma  is currently offline sterma
Messages: 8
Registered: September 2008
Junior Member
Hi there,

I tried to replay a MySQL Dump back in Database and got this Error:

mysql -u root_db -p < /tmp/ccl_ccr.dump
ERROR 1041 (HY000) at line 58: Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

So I checked the system behavior:

free -m:
      total       used       free     shared    buffers     cached
Mem:  16052       11770      4281          0         87     10369
-/+ buffers/cache:1313       14738
Swap: 32773          0       32773


Looks fine. Here´s the output from ulimit -a:

ulimit -a:
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
file size               (blocks, -f) unlimited
pending signals                 (-i) 139264
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 139264
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


Here the show status output:

Variable_name	Value
Aborted_clients	563
Aborted_connects	1
Binlog_cache_disk_use	0
Binlog_cache_use	0
Bytes_received	81
Bytes_sent	71
Com_admin_commands	0
Com_alter_db	0
Com_alter_table	0
Com_analyze	0
Com_backup_table	0
Com_begin	0
Com_change_db	0
Com_change_master	0
Com_check	0
Com_checksum	0
Com_commit	0
Com_create_db	0
Com_create_function	0
Com_create_index	0
Com_create_table	0
Com_dealloc_sql	0
Com_delete	0
Com_delete_multi	0
Com_do	0
Com_drop_db	0
Com_drop_function	0
Com_drop_index	0
Com_drop_table	0
Com_drop_user	0
Com_execute_sql	0
Com_flush	0
Com_grant	0
Com_ha_close	0
Com_ha_open	0
Com_ha_read	0
Com_help	0
Com_insert	0
Com_insert_select	0
Com_kill	0
Com_load	0
Com_load_master_data	0
Com_load_master_table	0
Com_lock_tables	0
Com_optimize	0
Com_preload_keys	0
Com_prepare_sql	0
Com_purge	0
Com_purge_before_date	0
Com_rename_table	0
Com_repair	0
Com_replace	0
Com_replace_select	0
Com_reset	0
Com_restore_table	0
Com_revoke	0
Com_revoke_all	0
Com_rollback	0
Com_savepoint	0
Com_select	0
Com_set_option	0
Com_show_binlog_events	0
Com_show_binlogs	0
Com_show_charsets	0
Com_show_collations	0
Com_show_column_types	0
Com_show_create_db	0
Com_show_create_table	0
Com_show_databases	0
Com_show_errors	0
Com_show_fields	0
Com_show_grants	0
Com_show_innodb_status	0
Com_show_keys	0
Com_show_logs	0
Com_show_master_status	0
Com_show_ndb_status	0
Com_show_new_master	0
Com_show_open_tables	0
Com_show_privileges	0
Com_show_processlist	0
Com_show_slave_hosts	0
Com_show_slave_status	0
Com_show_status	1
Com_show_storage_engines	0
Com_show_tables	0
Com_show_triggers	0
Com_show_variables	0
Com_show_warnings	0
Com_slave_start	0
Com_slave_stop	0
Com_stmt_close	0
Com_stmt_execute	0
Com_stmt_fetch	0
Com_stmt_prepare	0
Com_stmt_reset	0
Com_stmt_send_long_data	0
Com_truncate	0
Com_unlock_tables	0
Com_update	0
Com_update_multi	0
Com_xa_commit	0
Com_xa_end	0
Com_xa_prepare	0
Com_xa_recover	0
Com_xa_rollback	0
Com_xa_start	0
Compression	OFF
Connections	321
Created_tmp_disk_tables	0
Created_tmp_files	2095
Created_tmp_tables	1
Delayed_errors	0
Delayed_insert_threads	0
Delayed_writes	0
Flush_commands	1
Handler_commit	0
Handler_delete	0
Handler_discover	0
Handler_prepare	0
Handler_read_first	0
Handler_read_key	0
Handler_read_next	0
Handler_read_prev	0
Handler_read_rnd	0
Handler_read_rnd_next	0
Handler_rollback	0
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0
Handler_write	130
Innodb_buffer_pool_pages_data	3170
Innodb_buffer_pool_pages_dirty	0
Innodb_buffer_pool_pages_flushed	431
Innodb_buffer_pool_pages_free	92760
Innodb_buffer_pool_pages_latched	0
Innodb_buffer_pool_pages_misc	70
Innodb_buffer_pool_pages_total	96000
Innodb_buffer_pool_read_ahead_rnd	6
Innodb_buffer_pool_read_ahead_seq	0
Innodb_buffer_pool_read_requests	46477127
Innodb_buffer_pool_reads	2866
Innodb_buffer_pool_wait_free	0
Innodb_buffer_pool_write_requests	729
Innodb_data_fsyncs	718
Innodb_data_pending_fsyncs	0
Innodb_data_pending_reads	0
Innodb_data_pending_writes	0
Innodb_data_read	54120448
Innodb_data_reads	2956
Innodb_data_writes	859
Innodb_data_written	14304256
Innodb_dblwr_pages_written	431
Innodb_dblwr_writes	143
Innodb_log_waits	0
Innodb_log_write_requests	57
Innodb_log_writes	154
Innodb_os_log_fsyncs	298
Innodb_os_log_pending_fsyncs	0
Innodb_os_log_pending_writes	0
Innodb_os_log_written	107520
Innodb_page_size	16384
Innodb_pages_created	0
Innodb_pages_read	3170
Innodb_pages_written	431
Innodb_row_lock_current_waits	0
Innodb_row_lock_time	0
Innodb_row_lock_time_avg	0
Innodb_row_lock_time_max	0
Innodb_row_lock_waits	0
Innodb_rows_deleted	0
Innodb_rows_inserted	0
Innodb_rows_read	11105283
Innodb_rows_updated	139
Key_blocks_not_flushed	0
Key_blocks_unused	1250530
Key_blocks_used	42906
Key_read_requests	112825972
Key_reads	42998
Key_write_requests	26550
Key_writes	24168
Last_query_cost	0.000000
Max_used_connections	41
Not_flushed_delayed_rows	0
Open_files	105
Open_streams	0
Open_tables	110
Opened_tables	0
Qcache_free_blocks	117
Qcache_free_memory	944267424
Qcache_hits	13637
Qcache_inserts	13722
Qcache_lowmem_prunes	0
Qcache_not_cached	20090
Qcache_queries_in_cache	5681
Qcache_total_blocks	12025
Questions	171225
Rpl_status	NULL
Select_full_join	0
Select_full_range_join	0
Select_range	0
Select_range_check	0
Select_scan	1
Slave_open_temp_tables	0
Slave_retried_transactions	0
Slave_running	OFF
Slow_launch_threads	0
Slow_queries	0
Sort_merge_passes	0
Sort_range	0
Sort_rows	0
Sort_scan	0
Table_locks_immediate	24161
Table_locks_waited	0
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0
Threads_cached	7
Threads_connected	29
Threads_created	128
Threads_running	1
Uptime	14456


and the show variables output:

Variable_name	Value
auto_increment_increment	1
auto_increment_offset	1
automatic_sp_privileges	ON
back_log	50
basedir	/usr/
binlog_cache_size	32768
bulk_insert_buffer_size	8388608
character_set_client	latin1
character_set_connection	latin1
character_set_database	utf8
character_set_filesystem	binary
character_set_results	latin1
character_set_server	utf8
character_set_system	utf8
character_sets_dir	/usr/share/mysql/charsets/
collation_connection	latin1_swedish_ci
collation_database	utf8_general_ci
collation_server	utf8_general_ci
completion_type	0
concurrent_insert	1
connect_timeout	5
datadir	/var/lib/mysql_data/id1/
date_format	%Y-%m-%d
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
delay_key_write	ON
delayed_insert_limit	100
delayed_insert_timeout	300
delayed_queue_size	1000
div_precision_increment	4
engine_condition_pushdown	OFF
expire_logs_days	0
flush	OFF
flush_time	0
ft_boolean_syntax	+ -><()~*:""&|
ft_max_word_len	84
ft_min_word_len	4
ft_query_expansion_limit	20
ft_stopword_file	(built-in)
group_concat_max_len	1024
have_archive	NO
have_bdb	NO
have_blackhole_engine	NO
have_compress	YES
have_crypt	YES
have_csv	NO
have_dynamic_loading	NO
have_example_engine	NO
have_federated_engine	NO
have_geometry	YES
have_innodb	YES
have_isam	NO
have_merge_engine	YES
have_ndbcluster	NO
have_openssl	NO
have_query_cache	YES
have_raid	NO
have_rtree_keys	YES
have_symlink	YES
init_connect	
init_file	
init_slave	
innodb_additional_mem_pool_size	33554432
innodb_autoextend_increment	8
innodb_buffer_pool_awe_mem_mb	0
innodb_buffer_pool_size	1572864000
innodb_checksums	ON
innodb_commit_concurrency	0
innodb_concurrency_tickets	500
innodb_data_file_path	ibdata1:10M:autoextend
innodb_data_home_dir	/var/lib/mysql_data/id1/
innodb_doublewrite	ON
innodb_fast_shutdown	1
innodb_file_io_threads	4
innodb_file_per_table	ON
innodb_flush_log_at_trx_commit	0
innodb_flush_method	
innodb_force_recovery	0
innodb_lock_wait_timeout	50
innodb_locks_unsafe_for_binlog	OFF
innodb_log_arch_dir	/var/lib/mysql/
innodb_log_archive	OFF
innodb_log_buffer_size	8388608
innodb_log_file_size	536870912
innodb_log_files_in_group	2
innodb_log_group_home_dir	/var/lib/mysql/
innodb_max_dirty_pages_pct	90
innodb_max_purge_lag	0
innodb_mirrored_log_groups	1
innodb_open_files	300
innodb_support_xa	ON
innodb_sync_spin_loops	20
innodb_table_locks	ON
innodb_thread_concurrency	8
innodb_thread_sleep_delay	10000
interactive_timeout	28800
join_buffer_size	2093056
key_buffer_size	1610612736
key_cache_age_threshold	300
key_cache_block_size	1024
key_cache_division_limit	100
language	/usr/share/mysql/english/
large_files_support	ON
large_page_size	0
large_pages	OFF
lc_time_names	en_US
license	GPL
local_infile	ON
locked_in_memory	OFF
log	OFF
log_bin	OFF
log_bin_trust_function_creators	OFF
log_error	
log_queries_not_using_indexes	ON
log_slave_updates	OFF
log_slow_queries	ON
log_warnings	1
long_query_time	2
low_priority_updates	ON
lower_case_file_system	OFF
lower_case_table_names	0
max_allowed_packet	268434432
max_binlog_cache_size	18446744073709551615
max_binlog_size	1073741824
max_connect_errors	100
max_connections	100
max_delayed_threads	20
max_error_count	64
max_heap_table_size	2147482624
max_insert_delayed_threads	20
max_join_size	18446744073709551615
max_length_for_sort_data	1024
max_prepared_stmt_count	16382
max_relay_log_size	0
max_seeks_for_key	18446744073709551615
max_sort_length	1024
max_sp_recursion_depth	0
max_tmp_tables	32
max_user_connections	100
max_write_lock_count	18446744073709551615
multi_range_count	256
myisam_data_pointer_size	6
myisam_max_sort_file_size	9223372036854775807
myisam_recover_options	OFF
myisam_repair_threads	1
myisam_sort_buffer_size	536870912
myisam_stats_method	nulls_unequal
net_buffer_length	16384
net_read_timeout	30
net_retry_count	10
net_write_timeout	60
new	OFF
old_passwords	OFF
open_files_limit	1134
optimizer_prune_level	1
optimizer_search_depth	62
pid_file	/var/lib/mysql/mysqld.pid
port	3306
preload_buffer_size	32768
prepared_stmt_count	0
protocol_version	10
query_alloc_block_size	8192
query_cache_limit	4194304
query_cache_min_res_unit	4096
query_cache_size	1073741824
query_cache_type	ON
query_cache_wlock_invalidate	OFF
query_prealloc_size	8192
range_alloc_block_size	2048
read_buffer_size	2093056
read_only	OFF
read_rnd_buffer_size	2093056
relay_log_purge	ON
relay_log_space_limit	0
rpl_recovery_rank	0
secure_auth	OFF
server_id	1
skip_external_locking	ON
skip_networking	OFF
skip_show_database	OFF
slave_compressed_protocol	OFF
slave_load_tmpdir	/var/lib/mysql/.tmp/
slave_net_timeout	3600
slave_skip_errors	OFF
slave_transaction_retries	10
slow_launch_time	2
socket	/var/lib/mysql/mysql.sock
sort_buffer_size	2097144
sql_big_selects	ON
sql_mode	
sql_notes	ON
sql_warnings	OFF
ssl_ca	
ssl_capath	
ssl_cert	
ssl_cipher	
ssl_key	
storage_engine	InnoDB
sync_binlog	0
sync_frm	ON
system_time_zone	CEST
table_cache	512
table_lock_wait_timeout	50
table_type	InnoDB
thread_cache_size	8
thread_stack	262144
time_format	%H:%i:%s
time_zone	SYSTEM
timed_mutexes	OFF
tmp_table_size	268435456
tmpdir	/var/lib/mysql/.tmp/
transaction_alloc_block_size	8192
transaction_prealloc_size	4096
tx_isolation	REPEATABLE-READ
updatable_views_with_limit	YES
version	5.0.26-log
version_comment	SUSE MySQL RPM
version_compile_machine	x86_64
version_compile_os	suse-linux
wait_timeout	28800


Versions are:

OS: SUSE Linux Enterprise Server 10 (x86_64)
MySQL: Ver 14.12 Distrib 5.0.26

So my Questions is, why does this error come up? I cant´t find any setting with reference to this Error. Also very strange: After a reboot of MySQL, everything works fine - until a couple of days..

I would be realy happy if someone could help.. I hope, that I gave all informations you need. thanks for reading!

markus

PS: I surfed hours to find a solution but found nothing which helps..

[Updated on: Thu, 16 October 2008 07:17]

Re: ERROR 1041 (HY000) OutOfMemory [message #3647 is a reply to message #3635 ] Thu, 23 October 2008 01:34 Go to previous messageGo to next message
sterma  is currently offline sterma
Messages: 8
Registered: September 2008
Junior Member
Hi,

here is a log entry I found in MySQL Error Log:

080924 14:29:03 [ERROR] Got error 12 when reading table './sia_ccr/contentattribute'

thx, markus
Re: ERROR 1041 (HY000) OutOfMemory [message #3656 is a reply to message #3647 ] Mon, 27 October 2008 01:37 Go to previous messageGo to next message
debug  is currently offline debug
Messages: 128
Registered: March 2008
Senior Member

Are you sure that there is no some limit really? Or probably you are running a kernel which cannot allocate required amount of memory (32bit kernel on server with a lot of RAM)?


Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Re: ERROR 1041 (HY000) OutOfMemory [message #3659 is a reply to message #3656 ] Mon, 27 October 2008 02:45 Go to previous messageGo to next message
sterma  is currently offline sterma
Messages: 8
Registered: September 2008
Junior Member
Hi, thanks for your reply. i´m very sure that there is no limit. we are runnning Suse and Mysql in 64Bit and have 16GB of RAM...
Re: ERROR 1041 (HY000) OutOfMemory [message #3687 is a reply to message #3635 ] Tue, 04 November 2008 01:16 Go to previous messageGo to next message
sterma  is currently offline sterma
Messages: 8
Registered: September 2008
Junior Member
We can´t locate the error. We will upgrade to version 5.1. Maybe it´s a BUG in 5.0.26
Re: ERROR 1041 (HY000) OutOfMemory [message #3688 is a reply to message #3687 ] Tue, 04 November 2008 01:19 Go to previous messageGo to next message
debug  is currently offline debug
Messages: 128
Registered: March 2008
Senior Member

I believe it makes sense to upgrade to latest stable 5.0 release, not to 5.1.


Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Re: ERROR 1041 (HY000) OutOfMemory [message #3689 is a reply to message #3688 ] Tue, 04 November 2008 01:20 Go to previous messageGo to next message
sterma  is currently offline sterma
Messages: 8
Registered: September 2008
Junior Member
it´s the 5.0.28?
Re: ERROR 1041 (HY000) OutOfMemory [message #3690 is a reply to message #3689 ] Tue, 04 November 2008 01:23 Go to previous message
debug  is currently offline debug
Messages: 128
Registered: March 2008
Senior Member

Please look here: http://dev.mysql.com/downloads/mysql/5.0.html#downloads


Mikhail Solovyev, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
Previous Topic:Optimizing simple order by query
Next Topic:Delete records does not reduce hard disk space
Goto Forum:
  



Current Time: Wed Jan 7 19:29:45 EST 2009

Total time taken to generate the page: 0.03733 seconds
.:: Contact :: Home :: MySQL Support by Percona.com ::.

Powered by: FUDforum 2.7.5.
Copyright ©2001-2006 FUD Forum Bulletin Board Software

MySQL Performance | Forum authority Badge