Hi there,
I tried to replay a MySQL Dump back in Database and got this Error:
mysql -u root_db -p < /tmp/ccl_ccr.dumpERROR 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 cachedMem: 16052 11770 4281 0 87 10369-/+ buffers/cache:1313 14738Swap: 32773 0 32773
Looks fine. Here´s the output from ulimit -a:
ulimit -a:core file size (blocks, -c) 0data seg size (kbytes, -d) unlimitedfile size (blocks, -f) unlimitedpending signals (-i) 139264max locked memory (kbytes, -l) 32max memory size (kbytes, -m) unlimitedopen files (-n) 1024pipe size (512 bytes, -p) 8POSIX message queues (bytes, -q) 819200stack size (kbytes, -s) 8192cpu time (seconds, -t) unlimitedmax user processes (-u) 139264virtual memory (kbytes, -v) unlimitedfile locks (-x) unlimited
Here the show status output:
Variable_name ValueAborted_clients 563Aborted_connects 1Binlog_cache_disk_use 0Binlog_cache_use 0Bytes_received 81Bytes_sent 71Com_admin_commands 0Com_alter_db 0Com_alter_table 0Com_analyze 0Com_backup_table 0Com_begin 0Com_change_db 0Com_change_master 0Com_check 0Com_checksum 0Com_commit 0Com_create_db 0Com_create_function 0Com_create_index 0Com_create_table 0Com_dealloc_sql 0Com_delete 0Com_delete_multi 0Com_do 0Com_drop_db 0Com_drop_function 0Com_drop_index 0Com_drop_table 0Com_drop_user 0Com_execute_sql 0Com_flush 0Com_grant 0Com_ha_close 0Com_ha_open 0Com_ha_read 0Com_help 0Com_insert 0Com_insert_select 0Com_kill 0Com_load 0Com_load_master_data 0Com_load_master_table 0Com_lock_tables 0Com_optimize 0Com_preload_keys 0Com_prepare_sql 0Com_purge 0Com_purge_before_date 0Com_rename_table 0Com_repair 0Com_replace 0Com_replace_select 0Com_reset 0Com_restore_table 0Com_revoke 0Com_revoke_all 0Com_rollback 0Com_savepoint 0Com_select 0Com_set_option 0Com_show_binlog_events 0Com_show_binlogs 0Com_show_charsets 0Com_show_collations 0Com_show_column_types 0Com_show_create_db 0Com_show_create_table 0Com_show_databases 0Com_show_errors 0Com_show_fields 0Com_show_grants 0Com_show_innodb_status 0Com_show_keys 0Com_show_logs 0Com_show_master_status 0Com_show_ndb_status 0Com_show_new_master 0Com_show_open_tables 0Com_show_privileges 0Com_show_processlist 0Com_show_slave_hosts 0Com_show_slave_status 0Com_show_status 1Com_show_storage_engines 0Com_show_tables 0Com_show_triggers 0Com_show_variables 0Com_show_warnings 0Com_slave_start 0Com_slave_stop 0Com_stmt_close 0Com_stmt_execute 0Com_stmt_fetch 0Com_stmt_prepare 0Com_stmt_reset 0Com_stmt_send_long_data 0Com_truncate 0Com_unlock_tables 0Com_update 0Com_update_multi 0Com_xa_commit 0Com_xa_end 0Com_xa_prepare 0Com_xa_recover 0Com_xa_rollback 0Com_xa_start 0Compression OFFConnections 321Created_tmp_disk_tables 0Created_tmp_files 2095Created_tmp_tables 1Delayed_errors 0Delayed_insert_threads 0Delayed_writes 0Flush_commands 1Handler_commit 0Handler_delete 0Handler_discover 0Handler_prepare 0Handler_read_first 0Handler_read_key 0Handler_read_next 0Handler_read_prev 0Handler_read_rnd 0Handler_read_rnd_next 0Handler_rollback 0Handler_savepoint 0Handler_savepoint_rollback 0Handler_update 0Handler_write 130Innodb_buffer_pool_pages_data 3170Innodb_buffer_pool_pages_dirty 0Innodb_buffer_pool_pages_flushed 431Innodb_buffer_pool_pages_free 92760Innodb_buffer_pool_pages_latched 0Innodb_buffer_pool_pages_misc 70Innodb_buffer_pool_pages_total 96000Innodb_buffer_pool_read_ahead_rnd 6Innodb_buffer_pool_read_ahead_seq 0Innodb_buffer_pool_read_requests 46477127Innodb_buffer_pool_reads 2866Innodb_buffer_pool_wait_free 0Innodb_buffer_pool_write_requests 729Innodb_data_fsyncs 718Innodb_data_pending_fsyncs 0Innodb_data_pending_reads 0Innodb_data_pending_writes 0Innodb_data_read 54120448Innodb_data_reads 2956Innodb_data_writes 859Innodb_data_written 14304256Innodb_dblwr_pages_written 431Innodb_dblwr_writes 143Innodb_log_waits 0Innodb_log_write_requests 57Innodb_log_writes 154Innodb_os_log_fsyncs 298Innodb_os_log_pending_fsyncs 0Innodb_os_log_pending_writes 0Innodb_os_log_written 107520Innodb_page_size 16384Innodb_pages_created 0Innodb_pages_read 3170Innodb_pages_written 431Innodb_row_lock_current_waits 0Innodb_row_lock_time 0Innodb_row_lock_time_avg 0Innodb_row_lock_time_max 0Innodb_row_lock_waits 0Innodb_rows_deleted 0Innodb_rows_inserted 0Innodb_rows_read 11105283Innodb_rows_updated 139Key_blocks_not_flushed 0Key_blocks_unused 1250530Key_blocks_used 42906Key_read_requests 112825972Key_reads 42998Key_write_requests 26550Key_writes 24168Last_query_cost 0.000000Max_used_connections 41Not_flushed_delayed_rows 0Open_files 105Open_streams 0Open_tables 110Opened_tables 0Qcache_free_blocks 117Qcache_free_memory 944267424Qcache_hits 13637Qcache_inserts 13722Qcache_lowmem_prunes 0Qcache_not_cached 20090Qcache_queries_in_cache 5681Qcache_total_blocks 12025Questions 171225Rpl_status NULLSelect_full_join 0Select_full_range_join 0Select_range 0Select_range_check 0Select_scan 1Slave_open_temp_tables 0Slave_retried_transactions 0Slave_running OFFSlow_launch_threads 0Slow_queries 0Sort_merge_passes 0Sort_range 0Sort_rows 0Sort_scan 0Table_locks_immediate 24161Table_locks_waited 0Tc_log_max_pages_used 0Tc_log_page_size 0Tc_log_page_waits 0Threads_cached 7Threads_connected 29Threads_created 128Threads_running 1Uptime 14456
and the show variables output:
Variable_name Valueauto_increment_increment 1auto_increment_offset 1automatic_sp_privileges ONback_log 50basedir /usr/binlog_cache_size 32768bulk_insert_buffer_size 8388608character_set_client latin1character_set_connection latin1character_set_database utf8character_set_filesystem binarycharacter_set_results latin1character_set_server utf8character_set_system utf8character_sets_dir /usr/share/mysql/charsets/collation_connection latin1_swedish_cicollation_database utf8_general_cicollation_server utf8_general_cicompletion_type 0concurrent_insert 1connect_timeout 5datadir /var/lib/mysql_data/id1/date_format %Y-%m-%ddatetime_format %Y-%m-%d %H:%i:%sdefault_week_format 0delay_key_write ONdelayed_insert_limit 100delayed_insert_timeout 300delayed_queue_size 1000div_precision_increment 4engine_condition_pushdown OFFexpire_logs_days 0flush OFFflush_time 0ft_boolean_syntax + -><()~*:“”&|ft_max_word_len 84ft_min_word_len 4ft_query_expansion_limit 20ft_stopword_file (built-in)group_concat_max_len 1024have_archive NOhave_bdb NOhave_blackhole_engine NOhave_compress YEShave_crypt YEShave_csv NOhave_dynamic_loading NOhave_example_engine NOhave_federated_engine NOhave_geometry YEShave_innodb YEShave_isam NOhave_merge_engine YEShave_ndbcluster NOhave_openssl NOhave_query_cache YEShave_raid NOhave_rtree_keys YEShave_symlink YESinit_connect init_file init_slave innodb_additional_mem_pool_size 33554432innodb_autoextend_increment 8innodb_buffer_pool_awe_mem_mb 0innodb_buffer_pool_size 1572864000innodb_checksums ONinnodb_commit_concurrency 0innodb_concurrency_tickets 500innodb_data_file_path ibdata1:10M:autoextendinnodb_data_home_dir /var/lib/mysql_data/id1/innodb_doublewrite ONinnodb_fast_shutdown 1innodb_file_io_threads 4innodb_file_per_table ONinnodb_flush_log_at_trx_commit 0innodb_flush_method innodb_force_recovery 0innodb_lock_wait_timeout 50innodb_locks_unsafe_for_binlog OFFinnodb_log_arch_dir /var/lib/mysql/innodb_log_archive OFFinnodb_log_buffer_size 8388608innodb_log_file_size 536870912innodb_log_files_in_group 2innodb_log_group_home_dir /var/lib/mysql/innodb_max_dirty_pages_pct 90innodb_max_purge_lag 0innodb_mirrored_log_groups 1innodb_open_files 300innodb_support_xa ONinnodb_sync_spin_loops 20innodb_table_locks ONinnodb_thread_concurrency 8innodb_thread_sleep_delay 10000interactive_timeout 28800join_buffer_size 2093056key_buffer_size 1610612736key_cache_age_threshold 300key_cache_block_size 1024key_cache_division_limit 100language /usr/share/mysql/english/large_files_support ONlarge_page_size 0large_pages OFFlc_time_names en_USlicense GPLlocal_infile ONlocked_in_memory OFFlog OFFlog_bin OFFlog_bin_trust_function_creators OFFlog_error log_queries_not_using_indexes ONlog_slave_updates OFFlog_slow_queries ONlog_warnings 1long_query_time 2low_priority_updates ONlower_case_file_system OFFlower_case_table_names 0max_allowed_packet 268434432max_binlog_cache_size 18446744073709551615max_binlog_size 1073741824max_connect_errors 100max_connections 100max_delayed_threads 20max_error_count 64max_heap_table_size 2147482624max_insert_delayed_threads 20max_join_size 18446744073709551615max_length_for_sort_data 1024max_prepared_stmt_count 16382max_relay_log_size 0max_seeks_for_key 18446744073709551615max_sort_length 1024max_sp_recursion_depth 0max_tmp_tables 32max_user_connections 100max_write_lock_count 18446744073709551615multi_range_count 256myisam_data_pointer_size 6myisam_max_sort_file_size 9223372036854775807myisam_recover_options OFFmyisam_repair_threads 1myisam_sort_buffer_size 536870912myisam_stats_method nulls_unequalnet_buffer_length 16384net_read_timeout 30net_retry_count 10net_write_timeout 60new OFFold_passwords OFFopen_files_limit 1134optimizer_prune_level 1optimizer_search_depth 62pid_file /var/lib/mysql/mysqld.pidport 3306preload_buffer_size 32768prepared_stmt_count 0protocol_version 10query_alloc_block_size 8192query_cache_limit 4194304query_cache_min_res_unit 4096query_cache_size 1073741824query_cache_type ONquery_cache_wlock_invalidate OFFquery_prealloc_size 8192range_alloc_block_size 2048read_buffer_size 2093056read_only OFFread_rnd_buffer_size 2093056relay_log_purge ONrelay_log_space_limit 0rpl_recovery_rank 0secure_auth OFFserver_id 1skip_external_locking ONskip_networking OFFskip_show_database OFFslave_compressed_protocol OFFslave_load_tmpdir /var/lib/mysql/.tmp/slave_net_timeout 3600slave_skip_errors OFFslave_transaction_retries 10slow_launch_time 2socket /var/lib/mysql/mysql.socksort_buffer_size 2097144sql_big_selects ONsql_mode sql_notes ONsql_warnings OFFssl_ca ssl_capath ssl_cert ssl_cipher ssl_key storage_engine InnoDBsync_binlog 0sync_frm ONsystem_time_zone CESTtable_cache 512table_lock_wait_timeout 50table_type InnoDBthread_cache_size 8thread_stack 262144time_format %H:%i:%stime_zone SYSTEMtimed_mutexes OFFtmp_table_size 268435456tmpdir /var/lib/mysql/.tmp/transaction_alloc_block_size 8192transaction_prealloc_size 4096tx_isolation REPEATABLE-READupdatable_views_with_limit YESversion 5.0.26-logversion_comment SUSE MySQL RPMversion_compile_machine x86_64version_compile_os suse-linuxwait_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…