Results 1 to 3 of 3
  1. #1

    Mysql status inspection

    Could someone please have a look at my mysql status? My php forum gets quite slow at times and I don't really have a clue about the reason why. The server runs on a 3GHz Pentium 4 with 2Gb. The load is strangely not excessive.
    I have already made an effort at optimising queries; a week ago, everything seemed fine. I'm worried though about the value of zero for handler_key_reads. Any ideas?

    Here's the mysql status output:

    Variable_name Value
    Aborted_clients 13089
    Aborted_connects 1249
    Binlog_cache_disk_use 0
    Binlog_cache_use 0
    Bytes_received 104
    Bytes_sent 72
    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_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 0
    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 166941
    Created_tmp_disk_tables 0
    Created_tmp_files 130
    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 130
    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 4294967292
    Key_blocks_unused 348573
    Key_blocks_used 175202
    Key_read_requests 4141902032
    Key_reads 2874208805
    Key_write_requests 1100497
    Key_writes 872098
    Last_query_cost 0.000000
    Max_used_connections 101
    Not_flushed_delayed_rows 0
    Open_files 133
    Open_streams 0
    Open_tables 68
    Opened_tables 0
    Qcache_free_blocks 3485
    Qcache_free_memory 37994328
    Qcache_hits 30569769
    Qcache_inserts 22725990
    Qcache_lowmem_prunes 0
    Qcache_not_cached 444894
    Qcache_queries_in_cache 16068
    Qcache_total_blocks 36103
    Questions 70647864
    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 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 43744388
    Table_locks_waited 34126
    Tc_log_max_pages_used 0
    Tc_log_page_size 0
    Tc_log_page_waits 0
    Threads_cached 6
    Threads_connected 2
    Threads_created 3187
    Threads_running 1
    Uptime 221773

  2. #2
    Join Date
    Dec 2006
    Posts
    477
    Your ratio of key reads : key read requests is about 2:3 - on a good system it should be about 1:10000. This suggests you key buffer is way too small. Download http://hackmysql.com/mysqlreport then look at the guide on that page about how to interpret the results.

  3. #3
    The key buffer size is 384Mb. I'll experiment with it. Thanks for the suggestion.

  4. Newsletters

    Subscribe Now & Get The WHT Quick Start Guide!

Related Posts from theWHIR.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •