Hi Scott, The configuration shows that you are using default mysql configuration with very few enhancement. The enhancement must depends on the size of physical memory available.
innodb_buffer_pool_size 268M It should be between 50% to 70% of your ram. innodb_additional_mem_pool_size 1M innodb_log_file_size 5M You are using default. Increase it based on memory available. query_cache_limit 629M should be between 1M to 3M query_cache_size 629M query_cache_limit is ambiguous. Please visit the link to below to find the optimal parameters. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Krishna On Wed, Jan 14, 2009 at 12:04 PM, Scott Edwards <my...@foss.daxal.com>wrote: > 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 > -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 500003 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: prajapat...@gmail.com
