GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Can someone please check to see if we're overloading MySql?

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

  • Can someone please check to see if we're overloading MySql?

    Hi,

    We are running a fairly busy social networking website which is suffering from intermittent performance problems. This usually manifests itself as MySql entering a deadlock for no apparent reason and/or apache failing to load libraries and crashing. We are considering upgrading to a larger server but in the meantime I wanted to check that our MySql config is optimal. I have already run tuning-primer but I'm concerned that we are not using the query cache effectively and that our Questions figure is very high.

    Thanks for any help you can give!

    Our server is a VPS with 2GB guaranteed RAM (4GB burst) running Redhat 9 (details at http://www.servint.net/supervps/details.php#supervps)

    SHOW GLOBAL STATUS

    Aborted_clients|9114
    Aborted_connects|8
    Binlog_cache_disk_use|0
    Binlog_cache_use|0
    Bytes_received|1124171962
    Bytes_sent|3544123476
    Com_admin_commands|2
    Com_alter_db|0
    Com_alter_table|2
    Com_analyze|0
    Com_backup_table|0
    Com_begin|0
    Com_call_procedure|0
    Com_change_db|332313
    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|44570
    Com_create_user|0
    Com_dealloc_sql|0
    Com_delete|83912
    Com_delete_multi|1
    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|6
    Com_grant|12
    Com_ha_close|0
    Com_ha_open|0
    Com_ha_read|0
    Com_help|0
    Com_insert|718842
    Com_insert_select|322
    Com_kill|180
    Com_load|0
    Com_load_master_data|0
    Com_load_master_table|0
    Com_lock_tables|730611
    Com_optimize|260
    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|2
    Com_replace_select|0
    Com_reset|0
    Com_restore_table|0
    Com_revoke|0
    Com_revoke_all|0
    Com_rollback|7
    Com_savepoint|0
    Com_select|2031103
    Com_set_option|681931
    Com_show_binlog_events|0
    Com_show_binlogs|2
    Com_show_charsets|56
    Com_show_collations|56
    Com_show_column_types|0
    Com_show_create_db|0
    Com_show_create_table|280
    Com_show_databases|64
    Com_show_errors|0
    Com_show_fields|319
    Com_show_grants|26
    Com_show_innodb_status|2
    Com_show_keys|37
    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|1508
    Com_show_slave_hosts|0
    Com_show_slave_status|0
    Com_show_status|111
    Com_show_storage_engines|0
    Com_show_tables|83
    Com_show_triggers|1
    Com_show_variables|323
    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|367672
    Com_update|375570
    Com_update_multi|3240
    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|703306
    Created_tmp_disk_tables|45430
    Created_tmp_files|17
    Created_tmp_tables|188381
    Delayed_errors|0
    Delayed_insert_threads|0
    Delayed_writes|0
    Flush_commands|1
    Handler_commit|0
    Handler_delete|364924
    Handler_discover|0
    Handler_prepare|0
    Handler_read_first|731862
    Handler_read_key|202353922
    Handler_read_next|1352980025
    Handler_read_prev|19295695
    Handler_read_rnd|37431567
    Handler_read_rnd_next|2495242772
    Handler_rollback|0
    Handler_savepoint|0
    Handler_savepoint_rollback|0
    Handler_update|47142466
    Handler_write|40706380
    Innodb_buffer_pool_pages_data|0
    Innodb_buffer_pool_pages_dirty|0
    Innodb_buffer_pool_pages_flushed|0
    Innodb_buffer_pool_pages_free|0
    Innodb_buffer_pool_pages_latched|0
    Innodb_buffer_pool_pages_misc|0
    Innodb_buffer_pool_pages_total|0
    Innodb_buffer_pool_read_ahead_rnd|0
    Innodb_buffer_pool_read_ahead_seq|0
    Innodb_buffer_pool_read_requests|0
    Innodb_buffer_pool_reads|0
    Innodb_buffer_pool_wait_free|0
    Innodb_buffer_pool_write_requests|0
    Innodb_data_fsyncs|0
    Innodb_data_pending_fsyncs|0
    Innodb_data_pending_reads|0
    Innodb_data_pending_writes|0
    Innodb_data_read|0
    Innodb_data_reads|0
    Innodb_data_writes|0
    Innodb_data_written|0
    Innodb_dblwr_pages_written|0
    Innodb_dblwr_writes|0
    Innodb_log_waits|0
    Innodb_log_write_requests|0
    Innodb_log_writes|0
    Innodb_os_log_fsyncs|0
    Innodb_os_log_pending_fsyncs|0
    Innodb_os_log_pending_writes|0
    Innodb_os_log_written|0
    Innodb_page_size|0
    Innodb_pages_created|0
    Innodb_pages_read|0
    Innodb_pages_written|0
    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|0
    Innodb_rows_inserted|0
    Innodb_rows_read|0
    Innodb_rows_updated|0
    Key_blocks_not_flushed|0
    Key_blocks_unused|71314
    Key_blocks_used|44674
    Key_read_requests|545151772
    Key_reads|283540
    Key_write_requests|23830203
    Key_writes|1950286
    Last_query_cost|0.000000
    Max_used_connections|178
    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|1243
    Open_streams|0
    Open_tables|881
    Opened_tables|1189
    Prepared_stmt_count|0
    Qcache_free_blocks|73
    Qcache_free_memory|25082376
    Qcache_hits|3767678
    Qcache_inserts|1475995
    Qcache_lowmem_prunes|254523
    Qcache_not_cached|556189
    Qcache_queries_in_cache|26260
    Qcache_total_blocks|59156
    Questions|9839944
    Rpl_status|NULL
    Select_full_join|204
    Select_full_range_join|0
    Select_range|112668
    Select_range_check|0
    Select_scan|120492
    Slave_open_temp_tables|0
    Slave_retried_transactions|0
    Slave_running|OFF
    Slow_launch_threads|3
    Slow_queries|1602
    Sort_merge_passes|8
    Sort_range|319481
    Sort_rows|310033602
    Sort_scan|144045
    Table_locks_immediate|4185928
    Table_locks_waited|40180
    Tc_log_max_pages_used|0
    Tc_log_page_size|0
    Tc_log_page_waits|0
    Threads_cached|99
    Threads_connected|1
    Threads_created|178
    Threads_running|1
    Uptime|439639
    Uptime_since_flush_status|439639

    SHOW GLOBAL VARIABLES

    auto_increment_increment|1
    auto_increment_offset|1
    automatic_sp_privileges|ON
    back_log|50
    basedir|/
    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/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|0
    flush|OFF
    flush_time|0
    ft_boolean_syntax|+ -><()~*:""&\|
    ft_max_word_len|84
    ft_min_word_len|3
    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|DISABLED
    have_isam|NO
    have_merge_engine|YES
    have_ndbcluster|DISABLED
    have_openssl|NO
    have_ssl|NO
    have_query_cache|YES
    have_raid|NO
    have_rtree_keys|YES
    have_symlink|YES
    hostname|XXX.XXX.XXX
    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|8388608
    innodb_checksums|ON
    innodb_commit_concurrency|0
    innodb_concurrency_tickets|500
    innodb_data_file_path|
    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|1044480
    key_buffer_size|134217728
    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|OFF
    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|4
    low_priority_updates|OFF
    lower_case_file_system|OFF
    lower_case_table_names|0
    max_allowed_packet|1048576
    max_binlog_cache_size|4294967295
    max_binlog_size|1073741824
    max_connect_errors|10
    max_connections|500
    max_delayed_threads|5
    max_error_count|64
    max_heap_table_size|33554432
    max_insert_delayed_threads|5
    max_join_size|18446744073709551615
    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|4
    max_user_connections|0
    max_write_lock_count|4294967295
    multi_range_count|256
    myisam_data_pointer_size|6
    myisam_max_sort_file_size|2147483647
    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
    ndb_connectstring|
    net_buffer_length|16384
    net_read_timeout|30
    net_retry_count|10
    net_write_timeout|60
    new|OFF
    old_passwords|OFF
    open_files_limit|10000
    optimizer_prune_level|1
    optimizer_search_depth|62
    pid_file|/var/lib/mysql/XXX.XXX.XXX.pid
    port|3306
    preload_buffer_size|32768
    profiling|OFF
    profiling_history_size|15
    protocol_version|10
    query_alloc_block_size|8192
    query_cache_limit|3145728
    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|2048
    read_buffer_size|3141632
    read_only|OFF
    read_rnd_buffer_size|3141632
    relay_log_purge|ON
    relay_log_space_limit|0
    rpl_recovery_rank|0
    secure_auth|OFF
    secure_file_priv|
    server_id|0
    skip_external_locking|ON
    skip_networking|OFF
    skip_show_database|OFF
    slave_compressed_protocol|OFF
    slave_load_tmpdir|/home/mysql/
    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|3145720
    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|MST
    table_cache|2048
    table_lock_wait_timeout|50
    table_type|MyISAM
    thread_cache_size|100
    thread_stack|196608
    time_format|%H:%i:%s
    time_zone|SYSTEM
    timed_mutexes|OFF
    tmp_table_size|134217728
    tmpdir|/home/mysql
    transaction_alloc_block_size|8192
    transaction_prealloc_size|4096
    tx_isolation|REPEATABLE-READ
    updatable_views_with_limit|YES
    version|5.0.45-community-log
    version_comment|MySQL Community Edition (GPL)
    version_compile_machine|i686
    version_compile_os|pc-linux-gnu
    wait_timeout|15
Working...
X