Announcement

Announcement Module
Collapse
No announcement yet.

Swapping question

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

  • Swapping question

    Hi,
    This is my first post on the site and I ma new to mysql world , so please forgive me if some thing sounds stupid.

    We have a 16G machine and the memeory caches are using only around 4G. My question is When we run Analyze I start to see the swap space keeps on going up and in order to avoid Server Crash , we have to kill the Analyze process. Can someone please explain why this is happening. I am also listing all important stats (I think), which may give you some insite. The stats are not taken during Analyze , but are taken during regular processing

    FYI : I have a smaller machine with 8G RAM and same OS and Linux version which runs fine.

    uname -a
    Linux rpt01 2.6.17-11-generic #2 SMP Fri May 18 22:25:27 UTC 2007 x86_64 GNU/Linux

    top
    top - 15:01:53 up 70 days, 19:55, 3 users, load average: 1.13, 1.16, 1.29
    Tasks: 103 total, 1 running, 102 sleeping, 0 stopped, 0 zombie
    Cpu(s): 10.9%us, 2.8%sy, 0.0%ni, 78.4%id, 7.8%wa, 0.0%hi, 0.1%si, 0.0%st
    Mem: 16440032k total, 16293812k used, 146220k free, 172728k buffers
    Swap: 31206252k total, 144504k used, 31061748k free, 12386444k cached

    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    2209 mysql 16 0 3431m 3.2g 5976 S 68 20.4 3215:23 mysqld
    1 root 16 0 2860 516 444 S 0 0.0 0:12.65 init
    2 root RT 0 0 0 0 S 0 0.0 0:06.41 migration/0



    mysql> show status ;
    +-----------------------------------+--------------+
    | Variable_name | Value |
    +-----------------------------------+--------------+
    | Aborted_clients | 8 |
    | Aborted_connects | 2 |
    | Binlog_cache_disk_use | 1189 |
    | Binlog_cache_use | 1299458 |
    | Bytes_received | 129 |
    | Bytes_sent | 175 |
    | Com_admin_commands | 0 |
    | Com_alter_db | 0 |
    | Com_alter_table | 0 |
    | Com_analyze | 0 |
    | Com_backup_table | 0 |
    | Com_begin | 0 |
    | Com_change_db | 0 |
    | Com_change_master | 0 |
    | Com_check | 0 |
    | Com_checksum | 0 |
    | Com_commit | 0 |
    | Com_create_db | 0 |
    | Com_create_function | 0 |
    | Com_create_index | 0 |
    | Com_create_table | 0 |
    | Com_create_user | 0 |
    | Com_dealloc_sql | 0 |
    | Com_delete | 0 |
    | Com_delete_multi | 0 |
    | Com_do | 0 |
    | Com_drop_db | 0 |
    | Com_drop_function | 0 |
    | Com_drop_index | 0 |
    | Com_drop_table | 0 |
    | Com_drop_user | 0 |
    | Com_execute_sql | 0 |
    | Com_flush | 0 |
    | Com_grant | 0 |
    | Com_ha_close | 0 |
    | Com_ha_open | 0 |
    | Com_ha_read | 0 |
    | Com_help | 0 |
    | Com_insert | 0 |
    | Com_insert_select | 0 |
    | Com_kill | 0 |
    | Com_load | 0 |
    | Com_load_master_data | 0 |
    | Com_load_master_table | 0 |
    | Com_lock_tables | 0 |
    | Com_optimize | 0 |
    | Com_preload_keys | 0 |
    | Com_prepare_sql | 0 |
    | Com_purge | 0 |
    | Com_purge_before_date | 0 |
    | Com_rename_table | 0 |
    | Com_repair | 0 |
    | Com_replace | 0 |
    | Com_replace_select | 0 |
    | Com_reset | 0 |
    | Com_restore_table | 0 |
    | Com_revoke | 0 |
    | Com_revoke_all | 0 |
    | Com_rollback | 0 |
    | Com_savepoint | 0 |
    | Com_select | 1 |
    | Com_set_option | 0 |
    | Com_show_binlog_events | 0 |
    | Com_show_binlogs | 0 |
    | Com_show_charsets | 0 |
    | Com_show_collations | 0 |
    | Com_show_column_types | 0 |
    | Com_show_create_db | 0 |
    | Com_show_create_table | 0 |
    | Com_show_databases | 0 |
    | Com_show_errors | 0 |
    | Com_show_fields | 0 |
    | Com_show_grants | 0 |
    | Com_show_innodb_status | 0 |
    | Com_show_keys | 0 |
    | Com_show_logs | 0 |
    | Com_show_master_status | 0 |
    | Com_show_ndb_status | 0 |
    | Com_show_new_master | 0 |
    | Com_show_open_tables | 0 |
    | Com_show_privileges | 0 |
    | Com_show_processlist | 0 |
    | Com_show_slave_hosts | 0 |
    | Com_show_slave_status | 0 |
    | Com_show_status | 1 |
    | Com_show_storage_engines | 0 |
    | Com_show_tables | 0 |
    | Com_show_triggers | 0 |
    | Com_show_variables | 0 |
    | Com_show_warnings | 0 |
    | Com_slave_start | 0 |
    | Com_slave_stop | 0 |
    | Com_stmt_close | 0 |
    | Com_stmt_execute | 0 |
    | Com_stmt_fetch | 0 |
    | Com_stmt_prepare | 0 |
    | Com_stmt_reset | 0 |
    | Com_stmt_send_long_data | 0 |
    | Com_truncate | 0 |
    | Com_unlock_tables | 0 |
    | Com_update | 0 |
    | Com_update_multi | 0 |
    | Com_xa_commit | 0 |
    | Com_xa_end | 0 |
    | Com_xa_prepare | 0 |
    | Com_xa_recover | 0 |
    | Com_xa_rollback | 0 |
    | Com_xa_start | 0 |
    | Compression | OFF |
    | Connections | 63171 |
    | Created_tmp_disk_tables | 0 |
    | Created_tmp_files | 12343 |
    | Created_tmp_tables | 1 |
    | Delayed_errors | 0 |
    | Delayed_insert_threads | 0 |
    | Delayed_writes | 0 |
    | Flush_commands | 1 |
    | Handler_commit | 0 |
    | Handler_delete | 0 |
    | Handler_discover | 0 |
    | Handler_prepare | 0 |
    | Handler_read_first | 0 |
    | Handler_read_key | 0 |
    | Handler_read_next | 0 |
    | Handler_read_prev | 0 |
    | Handler_read_rnd | 0 |
    | Handler_read_rnd_next | 0 |
    | Handler_rollback | 0 |
    | Handler_savepoint | 0 |
    | Handler_savepoint_rollback | 0 |
    | Handler_update | 0 |
    | Handler_write | 131 |
    | Innodb_buffer_pool_pages_data | 59125 |
    | Innodb_buffer_pool_pages_dirty | 1590 |
    | Innodb_buffer_pool_pages_flushed | 5986567 |
    | Innodb_buffer_pool_pages_free | 0 |
    | Innodb_buffer_pool_pages_latched | 0 |
    | Innodb_buffer_pool_pages_misc | 6411 |
    | Innodb_buffer_pool_pages_total | 65536 |
    | Innodb_buffer_pool_read_ahead_rnd | 185086 |
    | Innodb_buffer_pool_read_ahead_seq | 208944 |
    | Innodb_buffer_pool_read_requests | 17251942648 |
    | Innodb_buffer_pool_reads | 6146738 |
    | Innodb_buffer_pool_wait_free | 0 |
    | Innodb_buffer_pool_write_requests | 208969869 |
    | Innodb_data_fsyncs | 2038125 |
    | Innodb_data_pending_fsyncs | 0 |
    | Innodb_data_pending_reads | 0 |
    | Innodb_data_pending_writes | 0 |
    | Innodb_data_read | 397679251456 |
    | Innodb_data_reads | 8502461 |
    | Innodb_data_writes | 6663821 |
    | Innodb_data_written | 204389053952 |
    | Innodb_dblwr_pages_written | 5986567 |
    | Innodb_dblwr_writes | 91058 |
    | Innodb_log_waits | 0 |
    | Innodb_log_write_requests | 18510999 |
    | Innodb_log_writes | 1437659 |
    | Innodb_os_log_fsyncs | 1512049 |
    | Innodb_os_log_pending_fsyncs | 0 |
    | Innodb_os_log_pending_writes | 0 |
    | Innodb_os_log_written | 8183153152 |
    | Innodb_page_size | 16384 |
    | Innodb_pages_created | 194714 |
    | Innodb_pages_read | 24272282 |
    | Innodb_pages_written | 5986567 |
    | Innodb_row_lock_current_waits | 0 |
    | Innodb_row_lock_time | 837018 |
    | Innodb_row_lock_time_avg | 2700 |
    | Innodb_row_lock_time_max | 60164 |
    | Innodb_row_lock_waits | 310 |
    | Innodb_rows_deleted | 6615804 |
    | Innodb_rows_inserted | 14959866 |
    | Innodb_rows_read | 9799218471 |
    | Innodb_rows_updated | 7100240 |
    | Key_blocks_not_flushed | 1036 |
    | Key_blocks_unused | 14253 |
    | Key_blocks_used | 1714736 |
    | Key_read_requests | 12893493278 |
    | Key_reads | 75059796 |
    | Key_write_requests | 791029208 |
    | Key_writes | 15160487 |
    | Last_query_cost | 0.000000 |
    | Max_used_connections | 17 |
    | Ndb_cluster_node_id | 0 |
    | Ndb_config_from_host | |
    | Ndb_config_from_port | 0 |
    | Ndb_number_of_data_nodes | 0 |
    | Not_flushed_delayed_rows | 0 |
    | Open_files | 180 |
    | Open_streams | 0 |
    | Open_tables | 240 |
    | Opened_tables | 0 |
    | Prepared_stmt_count | 0 |
    | Qcache_free_blocks | 9 |
    | Qcache_free_memory | 67000096 |
    | Qcache_hits | 2107 |
    | Qcache_inserts | 9610 |
    | Qcache_lowmem_prunes | 0 |
    | Qcache_not_cached | 192993 |
    | Qcache_queries_in_cache | 13 |
    | Qcache_total_blocks | 47 |
    | Questions | 4330588344 |
    | Rpl_status | NULL |
    | Select_full_join | 0 |
    | Select_full_range_join | 0 |
    | Select_range | 0 |
    | Select_range_check | 0 |
    | Select_scan | 1 |
    | Slave_open_temp_tables | 0 |
    | Slave_retried_transactions | 6 |
    | Slave_running | ON |
    | Slow_launch_threads | 0 |
    | Slow_queries | 0 |
    | Sort_merge_passes | 0 |
    | Sort_range | 0 |
    | Sort_rows | 0 |
    | Sort_scan | 0 |
    | Ssl_accept_renegotiates | 0 |
    | Ssl_accepts | 0 |
    | Ssl_callback_cache_hits | 0 |
    | Ssl_cipher | |
    | Ssl_cipher_list | |
    | Ssl_client_connects | 0 |
    | Ssl_connect_renegotiates | 0 |
    | Ssl_ctx_verify_depth | 0 |
    | Ssl_ctx_verify_mode | 0 |
    | Ssl_default_timeout | 0 |
    | Ssl_finished_accepts | 0 |
    | Ssl_finished_connects | 0 |
    | Ssl_session_cache_hits | 0 |
    | Ssl_session_cache_misses | 0 |
    | Ssl_session_cache_mode | NONE |
    | Ssl_session_cache_overflows | 0 |
    | Ssl_session_cache_size | 0 |
    | Ssl_session_cache_timeouts | 0 |
    | Ssl_sessions_reused | 0 |
    | Ssl_used_session_cache_entries | 0 |
    | Ssl_verify_depth | 0 |
    | Ssl_verify_mode | 0 |
    | Ssl_version | |
    | Table_locks_immediate | 32604176 |
    | Table_locks_waited | 26 |
    | Tc_log_max_pages_used | 0 |
    | Tc_log_page_size | 0 |
    | Tc_log_page_waits | 0 |
    | Threads_cached | 5 |
    | Threads_connected | 3 |
    | Threads_created | 17 |
    | Threads_running | 2 |
    | Uptime | 345317 |
    | Uptime_since_flush_status | 345317 |
    +-----------------------------------+--------------+




    mysql> show variables ;
    +---------------------------------+------------------------- -------------------------------------------+
    | Variable_name | Value |
    +---------------------------------+------------------------- -------------------------------------------+
    | auto_increment_increment | 1 |
    | auto_increment_offset | 1 |
    | automatic_sp_privileges | ON |
    | back_log | 50 |
    | basedir | /usr/local/mysql-5.0.37-linux-x86_64-glibc23/ |
    | binlog_cache_size | 32768 |
    | bulk_insert_buffer_size | 8388608 |
    | character_set_client | utf8 |
    | character_set_connection | utf8 |
    | character_set_database | utf8 |
    | character_set_filesystem | binary |
    | character_set_results | utf8 |
    | character_set_server | utf8 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/local/mysql-5.0.37-linux-x86_64-glibc23/share/mysql/cha rsets/ |
    | collation_connection | utf8_general_ci |
    | collation_database | utf8_general_ci |
    | collation_server | utf8_general_ci |
    | completion_type | 0 |
    | concurrent_insert | 1 |
    | 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 |
    | div_precision_increment | 4 |
    | engine_condition_pushdown | OFF |
    | expire_logs_days | 7 |
    | 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 | YES |
    | have_bdb | NO |
    | have_blackhole_engine | YES |
    | have_compress | YES |
    | have_crypt | YES |
    | have_csv | YES |
    | have_dynamic_loading | YES |
    | have_example_engine | YES |
    | have_federated_engine | YES |
    | have_geometry | YES |
    | have_innodb | YES |
    | have_isam | NO |
    | have_merge_engine | YES |
    | have_ndbcluster | DISABLED |
    | have_openssl | DISABLED |
    | have_query_cache | YES |
    | have_raid | NO |
    | have_rtree_keys | YES |
    | have_symlink | YES |
    | init_connect | |
    | init_file | |
    | init_slave | |
    | innodb_additional_mem_pool_size | 15728640 |
    | innodb_autoextend_increment | 8 |
    | innodb_buffer_pool_awe_mem_mb | 0 |
    | innodb_buffer_pool_size | 1073741824 |
    | innodb_checksums | ON |
    | innodb_commit_concurrency | 0 |
    | innodb_concurrency_tickets | 500 |
    | innodb_data_file_path | ibdata1:10M:autoextend |
    | innodb_data_home_dir | /var/lib/mysql/ibdata |
    | innodb_doublewrite | ON |
    | innodb_fast_shutdown | 1 |
    | 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 | 600 |
    | innodb_locks_unsafe_for_binlog | OFF |
    | innodb_log_arch_dir | |
    | innodb_log_archive | OFF |
    | innodb_log_buffer_size | 8388608 |
    | innodb_log_file_size | 262144000 |
    | 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_rollback_on_timeout | ON |
    | innodb_support_xa | OFF |
    | innodb_sync_spin_loops | 20 |
    | innodb_table_locks | ON |
    | innodb_thread_concurrency | 8 |
    | innodb_thread_sleep_delay | 10000 |
    | interactive_timeout | 7200 |
    | join_buffer_size | 131072 |
    | key_buffer_size | 2147483648 |
    | key_cache_age_threshold | 300 |
    | key_cache_block_size | 1024 |
    | key_cache_division_limit | 100 |
    | language | /usr/local/mysql/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 | /var/log/syslog.err |
    | log_queries_not_using_indexes | OFF |
    | log_slave_updates | ON |
    | log_slow_queries | ON |
    | log_warnings | 1 |
    | long_query_time | 10 |
    | low_priority_updates | OFF |
    | lower_case_file_system | OFF |
    | lower_case_table_names | 0 |
    | max_allowed_packet | 1073740800 |
    | max_binlog_cache_size | 18446744073709551615 |
    | max_binlog_size | 1073741824 |
    | max_connect_errors | 10 |
    | max_connections | 24 |
    | max_delayed_threads | 20 |
    | max_error_count | 64 |
    | max_heap_table_size | 16777216 |
    | max_insert_delayed_threads | 20 |
    | max_join_size | 18446744073709551615 |
    | max_length_for_sort_data | 1024 |
    | max_prepared_stmt_count | 16382 |
    | max_relay_log_size | 0 |
    | max_seeks_for_key | 18446744073709551615 |
    | max_sort_length | 1024 |
    | max_sp_recursion_depth | 0 |
    | max_tmp_tables | 32 |
    | max_user_connections | 0 |
    | max_write_lock_count | 18446744073709551615 |
    | multi_range_count | 256 |
    | myisam_data_pointer_size | 6 |
    | myisam_max_sort_file_size | 9223372036854775807 |
    | myisam_recover_options | OFF |
    | myisam_repair_threads | 1 |
    | myisam_sort_buffer_size | 8388608 |
    | myisam_stats_method | nulls_unequal |
    | ndb_autoincrement_prefetch_sz | 32 |
    | ndb_force_send | ON |
    | ndb_use_exact_count | ON |
    | ndb_use_transactions | ON |
    | ndb_cache_check_time | 0 |
    | net_buffer_length | 16384 |
    | net_read_timeout | 30 |
    | net_retry_count | 10 |
    | net_write_timeout | 60 |
    | new | OFF |
    | old_passwords | ON |
    | open_files_limit | 4096 |
    | optimizer_prune_level | 1 |
    | optimizer_search_depth | 62 |
    | pid_file | /var/run/mysqld/mysqld.pid |
    | 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 | 67108864 |
    | query_cache_type | ON |
    | query_cache_wlock_invalidate | OFF |
    | query_prealloc_size | 8192 |
    | range_alloc_block_size | 2048 |
    | read_buffer_size | 12578816 |
    | read_only | OFF |
    | read_rnd_buffer_size | 262144 |
    | relay_log_purge | ON |
    | relay_log_space_limit | 0 |
    | rpl_recovery_rank | 0 |
    | secure_auth | OFF |
    | server_id | 14 |
    | skip_external_locking | ON |
    | skip_networking | OFF |
    | skip_show_database | OFF |
    | slave_compressed_protocol | OFF |
    | slave_load_tmpdir | /var/tmp |
    | slave_net_timeout | 3600 |
    | slave_skip_errors | OFF |
    | slave_transaction_retries | 10 |
    | slow_launch_time | 2 |
    | socket | /var/run/mysqld/mysqld.sock |
    | sort_buffer_size | 12582904 |
    | 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 | 1 |
    | sync_frm | ON |
    | system_time_zone | PDT |
    | table_cache | 512 |
    | table_lock_wait_timeout | 50 |
    | table_type | MyISAM |
    | thread_cache_size | 8 |
    | thread_stack | 262144 |
    | 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 | READ-COMMITTED |
    | updatable_views_with_limit | YES |
    | version | 5.0.37-log ( |
    | version_comment | MySQL Community Server (GPL) |
    | version_compile_machine | x86_64 |
    | version_compile_os | unknown-linux-gnu |
    | wait_timeout | 7200 |
    +---------------------------------+------------------------- -------------------------------------------+



    Any Help/ Tips are appreciated.

    Thanks in advance
Working...
X