On Tuesday 13 January 2009 07:23:52 am Krishna Chandra Prajapati wrote: > Hi Scott, > > I believe something wrong with innodb parameters. It should be optimum. In > your case it might be too high or too low. Take a look at log file size. > Please send your show variables and show status data to reach at > conclusion. >
Okay. This is not during a time of incident. Another server is handling the load from the queries and database. So far we don't notice any issues with stuck commits on the new server, but it's only handling minimal load outside of amavis queries. We would like to run this on the original system, because it has raid1+drbd+heartbeat (2nodes) where as the temporary solution only uses raid1. Variable_name Value auto_increment_increment 1 auto_increment_offset 1 automatic_sp_privileges ON back_log 50 basedir /usr/ 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 5 datadir /var/www/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 10 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 NO have_compress YES have_crypt YES have_csv YES have_dynamic_loading YES have_example_engine NO 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 1048576 innodb_autoextend_increment 8 innodb_buffer_pool_awe_mem_mb 0 innodb_buffer_pool_size 268435456 innodb_checksums ON innodb_commit_concurrency 0 innodb_concurrency_tickets 500 innodb_data_file_path ibdata1:10M:autoextend innodb_data_home_dir innodb_doublewrite ON innodb_fast_shutdown 1 innodb_file_io_threads 4 innodb_file_per_table OFF 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 1048576 innodb_log_file_size 5242880 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 OFF innodb_support_xa ON innodb_sync_spin_loops 20 innodb_table_locks ON innodb_thread_concurrency 8 innodb_thread_sleep_delay 10000 interactive_timeout 28800 join_buffer_size 3141632 key_buffer_size 50331648 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 3 low_priority_updates OFF lower_case_file_system OFF lower_case_table_names 0 max_allowed_packet 16776192 max_binlog_cache_size 18446744073709551615 max_binlog_size 104857600 max_connect_errors 10000 max_connections 250 max_delayed_threads 20 max_error_count 64 max_heap_table_size 209715200 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 OFF open_files_limit 20260 optimizer_prune_level 1 optimizer_search_depth 62 pid_file /var/run/mysqld/mysqld.pid port 3306 preload_buffer_size 32768 protocol_version 10 query_alloc_block_size 8192 query_cache_limit 629145600 query_cache_min_res_unit 4096 query_cache_size 629145600 query_cache_type ON query_cache_wlock_invalidate OFF query_prealloc_size 8192 range_alloc_block_size 2048 read_buffer_size 8384512 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 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/run/mysqld/mysqld.sock sort_buffer_size 2097144 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 EST table_cache 10000 table_lock_wait_timeout 50 table_type MyISAM thread_cache_size 32 thread_stack 131072 time_format %H:%i:%s time_zone SYSTEM timed_mutexes OFF tmp_table_size 209715200 tmpdir /tmp transaction_alloc_block_size 8192 transaction_prealloc_size 4096 tx_isolation REPEATABLE-READ updatable_views_with_limit YES version 5.0.32-Debian_7etch8-log version_comment Debian etch distribution version_compile_machine x86_64 version_compile_os pc-linux-gnu wait_timeout 28800 Variable_name Value Aborted_clients 204 Aborted_connects 387 Binlog_cache_disk_use 0 Binlog_cache_use 33 Bytes_received 134 Bytes_sent 6593 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 1 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 601230 Created_tmp_disk_tables 0 Created_tmp_files 327 Created_tmp_tables 2 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 226 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 356 Innodb_buffer_pool_pages_data 3905 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 4415 Innodb_buffer_pool_pages_free 12167 Innodb_buffer_pool_pages_latched 0 Innodb_buffer_pool_pages_misc 312 Innodb_buffer_pool_pages_total 16384 Innodb_buffer_pool_read_ahead_rnd 5 Innodb_buffer_pool_read_ahead_seq 7 Innodb_buffer_pool_read_requests 7534206 Innodb_buffer_pool_reads 3282 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 155309 Innodb_data_fsyncs 1953 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 65097728 Innodb_data_reads 3341 Innodb_data_writes 4301 Innodb_data_written 151555072 Innodb_dblwr_pages_written 4415 Innodb_dblwr_writes 443 Innodb_log_waits 0 Innodb_log_write_requests 15936 Innodb_log_writes 799 Innodb_os_log_fsyncs 1039 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 6754816 Innodb_page_size 16384 Innodb_pages_created 65 Innodb_pages_read 3840 Innodb_pages_written 4415 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 16653 Innodb_rows_inserted 208 Innodb_rows_read 4233109 Innodb_rows_updated 938 Key_blocks_not_flushed 0 Key_blocks_unused 14973 Key_blocks_used 40419 Key_read_requests 439723847 Key_reads 1971527 Key_write_requests 95867296 Key_writes 211907 Last_query_cost 10.499000 Max_used_connections 55 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 10492 Open_streams 0 Open_tables 7292 Opened_tables 0 Prepared_stmt_count 0 Qcache_free_blocks 15542 Qcache_free_memory 340462088 Qcache_hits 2465266 Qcache_inserts 487249 Qcache_lowmem_prunes 0 Qcache_not_cached 41610 Qcache_queries_in_cache 81098 Qcache_total_blocks 178983 Questions 5356332 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 2 Slave_open_temp_tables 0 Slave_retried_transactions 0 Select_range_check 0 Select_scan 2 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF 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 1665939 Table_locks_waited 12008 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 29 Threads_connected 11 Threads_created 105 Threads_running 1 Uptime 97438 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
