MySQL Forums
Forum List  »  InnoDB

Re: innodb keeps crashing due to out-of-memory errors
Posted by: Eddie Moojen
Date: March 07, 2005 10:36AM

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>

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: innodb keeps crashing due to out-of-memory errors
2443
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.