Announcement

Announcement Module
Collapse
No announcement yet.

Mysql never releases memory on a production server with 6 million page views

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql never releases memory on a production server with 6 million page views

    Hello All,

    I have a production server clocking about 4 million page views per month. The server has got 8GB of RAM and mysql acts as a database.

    I am facing problems in handling mysql to take this load. I need to restart mysql twice a day to handle this thing. The problem with mysql is that it starts with some particular memory occupation, the memory consumed by mysql keeps on increasing until it reaches the maximum it can consume and then mysql stops responding slowly or does not respond at all, which freezes the server.

    All my tables are indexed properly and there are no long queries. I need some one to help on how to go about debugging this.

    All my tables are myisam.

    I have tried configuring the parameters key_buffer etc but to no rescue. Any sort of help is greatly appreciated. Here are some parameters which may help:

    mysql --version
    mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (i686) using readline 5.1


    mysql> show variables;
    +---------------------------------+------------------------- -----------------------------------+
    | Variable_name | Value |
    +---------------------------------+------------------------- -----------------------------------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /usr/ |
    | bdb_cache_size | 8384512 |
    | bdb_home | /var/lib/mysql/ |
    | bdb_log_buffer_size | 262144 |
    | bdb_logdir | |
    | bdb_max_lock | 10000 |
    | bdb_shared_data | OFF |
    | bdb_tmpdir | /tmp/ |
    | binlog_cache_size | 32768 |
    | bulk_insert_buffer_size | 8388608 |
    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/share/mysql/charsets/ |
    | collation_connection | latin1_swedish_ci |
    | collation_database | latin1_swedish_ci |
    | collation_server | latin1_swedish_ci |
    | completion_type | 0 |
    | concurrent_insert | 1 |
    | connect_timeout | 10 |
    | 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 |
    | div_precision_increment | 4 |
    | keep_files_on_create | OFF |
    | 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 | YES |
    | have_blackhole_engine | NO |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | NO |
    | have_dynamic_loading | YES |
    | 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 | DISABLED |
    | have_ssl | DISABLED |
    | have_query_cache | YES |
    | have_raid | NO |
    | have_rtree_keys | YES |
    | have_symlink | YES | |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | interactive_timeout | 28800 |
    | join_buffer_size | 131072 |
    | key_buffer_size | 2621440000 |
    | 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 | ON |
    | log_bin_trust_function_creators | OFF |
    | log_error | |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | OFF |
    | log_slow_queries | ON |
    | log_warnings | 1 |
    | long_query_time | 8 |
    | low_priority_updates | OFF |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 8388608 |
    | max_binlog_cache_size | 4294963200 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 400 |
    | 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_prepared_stmt_count | 16382 |
    | max_relay_log_size | 0 |
    | max_seeks_for_key | 4294967295 |
    | max_sort_length | 1024 |
    | max_sp_recursion_depth | 0 |
    | max_tmp_tables | 32 |
    | max_user_connections | 0 |
    | max_write_lock_count | 4294967295 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 2146435072 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 16777216 |
    | 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 | 2000 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /var/run/mysqld/mysqld.pid |
    | plugin_dir | |
    | port | 3306 |
    | preload_buffer_size | 32768 |
    | profiling | OFF |
    | profiling_history_size | 15 |
    | protocol_version | 10 |
    | query_alloc_block_size | 8192 |
    | query_cache_limit | 1048576 |
    | query_cache_min_res_unit | 4096 |
    | query_cache_size | 134217728 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    | range_alloc_block_size | 4096 |
    | read_buffer_size | 2097152 |
    | read_only | OFF |
    | read_rnd_buffer_size | 8388608 |
    | relay_log | |
    | relay_log_index | |
    | relay_log_info_file | relay-log.info |
    | relay_log_purge | ON |
    | relay_log_space_limit | 0 |
    | rpl_recovery_rank | 0 |
    | secure_auth | OFF |
    | secure_file_priv | |
    | server_id | 1 |
    | skip_external_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slave_compressed_protocol | OFF |
    | slave_load_tmpdir | /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 | 2097152 |
    | sql_big_selects | ON |
    | sql_mode | |
    | sql_notes | ON |
    | sql_warnings | OFF |
    | ssl_ca | |
    | ssl_capath | |
    | ssl_cert | |
    | ssl_cipher | |
    | ssl_key | |
    | storage_engine | MyISAM |
    | sync_binlog | 0 |
    | sync_frm | ON |
    | system_time_zone | CST |
    | table_cache | 256 |
    | table_lock_wait_timeout | 50 |
    | table_type | MyISAM |
    | thread_cache_size | 8 |
    | thread_stack | 196608 |
    | time_format | %H:%i:%s |
    | time_zone | SYSTEM |
    | timed_mutexes | OFF |
    | tmp_table_size | 33554432 |
    | tmpdir | /tmp/ |
    | transaction_alloc_block_size | 8192 |
    | transaction_prealloc_size | 4096 |
    | tx_isolation | REPEATABLE-READ |
    | updatable_views_with_limit | YES |
    | version | 5.0.77-log |
    | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 29, 2009) |
    | version_comment | Source distribution |
    | version_compile_machine | i686 |
    | version_compile_os | redhat-linux-gnu |
    | wait_timeout | 28800 |
    +---------------------------------+------------------------- -----------------------------------+

  • #2
    I think you are telling the doctor what the disease is instead of describing the symptoms. Memory usage might be a symptom, not a problem. What other symptoms can you observe during this problem?

    Comment

    Working...
    X