Re: innodb keeps crashing due to out-of-memory errors
Hi Heikki,
Ive followed your advice, (tmp_table_size=10M), which has helped marginally, but the memory consumption continues to be a problem, as it still gradually increases, untill a server crash.
mytop shows:
MySQL on localhost (4.1.9-standard-log) up 0+00:21:26 [17:24:17]
Queries: 18.0k qps: 14 Slow: 0.0 Se/In/Up/De(%): 08/74/00/01
qps now: 8 Slow qps: 0.0 Threads: 9 ( 1/ 2) 12/71/00/00
Cache Hits: 12.0 Hits/s: 0.0 Hits now: 0.0 Ratio: 0.8% Ratio now: 0.0%
Key Efficiency: 93.6% Bps in/out: 10.7k/ 1.0k Now in/out: 6.7k/ 1.4k
Id User Host/IP DB Time Cmd Query or State
-- ---- ------- -- ---- --- ----------
2 root localhost 0 Sleep
3 root localhost 0 Query REPLACE ot_engine
4 root localhost 0 Sleep
5 root localhost 0 Sleep
6 root localhost 0 Sleep
7 root localhost 0 Sleep
8 root localhost 0 Sleep
33 root localhost test 0 Query show full process
35 transfer 70.84.103.66 5 Sleep
Most snapshots are rather empty as the majority of querries are fast.
Underneath all variables, maybe I am missing something.
Thank you,
Eddie
mysql> show variables;
+---------------------------------+--------------------------------------------------
| Variable_name | Value
+---------------------------------+--------------------------------------------------
| back_log | 50
| basedir | /
| binlog_cache_size | 32768
| bulk_insert_buffer_size | 8388608
| character_set_client | latin1
| character_set_connection | latin1
| character_set_database | utf8
| 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
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| 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
| 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_compress | YES
| have_crypt | YES
| have_csv | NO
| have_example_engine | NO
| have_geometry | YES
| have_innodb | YES
| have_isam | NO
| have_ndbcluster | NO
| have_openssl | NO
| have_query_cache | YES
| have_raid | NO
| have_rtree_keys | YES
| have_symlink | YES
| init_connect |
| init_file | /home/websites/s10.opentracker.net/public_html/WE
| init_slave |
| innodb_additional_mem_pool_size | 20971520
| innodb_autoextend_increment | 8
| innodb_buffer_pool_awe_mem_mb | 0
| innodb_buffer_pool_size | 536870912
| innodb_data_file_path | ibdata1:10M:autoextend
| innodb_data_home_dir |
| innodb_fast_shutdown | ON
| innodb_file_io_threads | 4
| innodb_file_per_table | ON
| innodb_flush_log_at_trx_commit | 1
| innodb_flush_method |
| innodb_force_recovery | 0
| innodb_lock_wait_timeout | 50
| innodb_locks_unsafe_for_binlog | OFF
| innodb_log_arch_dir |
| innodb_log_archive | OFF
| innodb_log_buffer_size | 8388608
| innodb_log_file_size | 104857600
| innodb_log_files_in_group | 2
| innodb_log_group_home_dir | ./
| innodb_max_dirty_pages_pct | 90
| innodb_max_purge_lag | 0
| innodb_mirrored_log_groups | 1
| innodb_open_files | 300
| innodb_table_locks | ON
| innodb_thread_concurrency | 8
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 268435456
| key_cache_age_threshold | 300
| key_cache_block_size | 1024
| key_cache_division_limit | 100
| language | /usr/share/mysql/english/
| large_files_support | ON
| license | GPL
| local_infile | ON
| locked_in_memory | OFF
| log | OFF
| log_bin | OFF
| log_error |
| log_slave_updates | OFF
| log_slow_queries | ON
| log_update | OFF
| log_warnings | 1
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_file_system | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1047552
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connect_errors | 10
| max_connections | 100
| max_delayed_threads | 20
| max_error_count | 64
| max_heap_table_size | 16777216
| max_insert_delayed_threads | 20
| max_join_size | 4294967295
| max_length_for_sort_data | 1024
| 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_data_pointer_size | 4
| myisam_max_extra_sort_file_size | 2147483648
| myisam_max_sort_file_size | 2147483647
| myisam_recover_options | BACKUP,FORCE
| myisam_repair_threads | 1
| myisam_sort_buffer_size | 33554432
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| new | OFF
| old_passwords | OFF
| open_files_limit | 65535
| pid_file | /var/lib/mysql/s10.opentracker.net.pid
| port | 3306
| preload_buffer_size | 32768
| protocol_version | 10
| query_alloc_block_size | 8192
| query_cache_limit | 1048576
| query_cache_min_res_unit | 4096
| query_cache_size | 33554432
| 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 | 4190208
| relay_log_purge | ON
| rpl_recovery_rank | 0
| secure_auth | OFF
| server_id | 1
| skip_external_locking | ON
| skip_networking | OFF
| skip_show_database | OFF
| slave_net_timeout | 3600
| slow_launch_time | 2
| socket | /var/lib/mysql/mysql.sock
| sort_buffer_size | 2097144
| sql_mode |
| storage_engine | MyISAM
| sync_binlog | 0
| sync_frm | ON
| system_time_zone | CET
| table_cache | 256
| table_type | MyISAM
| thread_cache_size | 8
| thread_stack | 126976
| time_format | %H:%i:%s
| time_zone | SYSTEM
| tmp_table_size | 10485760
| tmpdir | /var/lib/mysql/tmp
| transaction_alloc_block_size | 8192
| transaction_prealloc_size | 4096
| tx_isolation | REPEATABLE-READ
| version | 4.1.9-standard-log
| version_comment | Official MySQL RPM
| version_compile_machine | i686
| version_compile_os | pc-linux-gnu
| wait_timeout | 28800
+---------------------------------+--------------------------------------------------
</code>
Subject
Views
Written By
Posted
7170
March 03, 2005 06:55PM
2920
March 03, 2005 07:30PM
2955
March 04, 2005 07:16AM
2401
March 04, 2005 09:02AM
2639
March 07, 2005 03:38AM
2475
March 08, 2005 06:35PM
2316
March 09, 2005 08:51AM
2325
March 09, 2005 08:55AM
2173
March 09, 2005 09:04AM
2505
March 09, 2005 09:28AM
2544
March 05, 2005 01:47AM
2676
April 01, 2005 05:13AM
2360
June 13, 2005 06:54AM
2751
March 05, 2005 01:54AM
1912
March 06, 2005 03:38PM
2378
March 07, 2005 01:58AM
Re: innodb keeps crashing due to out-of-memory errors
2477
March 07, 2005 10:36AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.