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
7430
March 03, 2005 06:55PM
3015
March 03, 2005 07:30PM
3034
March 04, 2005 07:16AM
2478
March 04, 2005 09:02AM
2711
March 07, 2005 03:38AM
2547
March 08, 2005 06:35PM
2381
March 09, 2005 08:51AM
2392
March 09, 2005 08:55AM
2249
March 09, 2005 09:04AM
2553
March 09, 2005 09:28AM
2655
March 05, 2005 01:47AM
2804
April 01, 2005 05:13AM
2428
June 13, 2005 06:54AM
2815
March 05, 2005 01:54AM
2028
March 06, 2005 03:38PM
2451
March 07, 2005 01:58AM
Re: innodb keeps crashing due to out-of-memory errors
2554
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.