List:General Discussion« Previous MessageNext Message »
From:Krishna Chandra Prajapati Date:January 15 2009 9:45am
Subject:Re: stuck commits
View as plain text  
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 <mysql@stripped>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: prajapatikc@stripped

Thread
stuck commitsScott Edwards12 Jan
  • Re: stuck commitsBaron Schwartz13 Jan
  • Re: stuck commitsKrishna Chandra Prajapati13 Jan
    • Re: stuck commitsScott Edwards14 Jan
      • Re: stuck commitsKrishna Chandra Prajapati15 Jan