Hi I am a new MySQL dba tuning a busy Apache/MySQL installation. I could do with some advice from the gurus! Here are some of the statistics for the last 24 hours:
Com_admin_commands 978,760 What "Admin_Commands" does this include - at 11.3 per sec this is a lot! Com_analyze 0 The indexes have not been analyzed at all: so the optimizer assumes no data skew. How good is the optimizer: will having accurate stats cause if to scan too much, or is it safe to assume that any stats are good stats? Does everyone out there analyze their tables? Connections 536,998 So it is processing about 6.2 connections per second. Lots. It is configured for a max of 700 connections, and generally has about 300 connection processes. My question is if it has a connection pool (from apache) are these really new connections (i.e. start a new connection process, allocate memory etc) - or is it efficiently re-using existing connections from the pool and just incrementing the counter? Sort_merge_passes 25 Sort_range 57,128 Sort_rows 11,368,012 Sort_scan 126,770 The machine has a 1MB sort_buffer_size - which for 300-700 connections is 300-700MB that I would prefer to use for the key_buffer. Is there any wany I can tell from these stats that it is safe to reduce the sort_buffer_size? Ive got as much info as I can from newsgroups and the manual: but tuning MySQL seems more like magic than science. Any comments much appreciated!! Mark (p.s. heres the full list of changes for 24 hours: Aborted_clients 11 Aborted_connects 0 Bytes_received 726,146,766 Bytes_sent 3,574,227,784 Com_admin_commands 978,760 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_change_db 1,510,345 Com_change_master 0 Com_check 0 Com_commit 1,787 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_delete 33,768 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_flush 0 Com_grant 0 Com_insert 164,361 Com_insert_select 12 Com_kill 0 Com_load 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_purge 0 Com_rename_table 0 Com_repair Com_replace 20,631 Com_replace_select 1 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_rollback 0 Com_select 5,780,420 Com_set_option 103 Com_show_binlogs 0 Com_show_create 103 Com_show_databases 0 Com_show_fields 103 Com_show_grants 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_status 0 Com_show_open_tables 0 Com_show_processlist 0 Com_show_slave_status 0 Com_show_status 4 Com_show_innodb_status 0 Com_show_tables 3 Com_show_variables 0 Com_slave_start 0 Com_slave_stop 0 Com_truncate 0 Com_unlock_tables 0 Com_update 895,584 Connections 536,998 Created_tmp_disk_tables 7,928 Created_tmp_tables 57,552 Created_tmp_files 50 Delayed_insert_threads 0 Delayed_errors 0 Flush_commands 0 Handler_delete 157,555 Handler_read_first 19,825 Handler_read_key 31,733,152 Handler_read_next 1,109,099,218 Handler_read_prev 0 Handler_read_rnd 9,864,713 Handler_read_rnd_next -102,644,059 Handler_update 3,657,710 Handler_write 9,068,562 Key_blocks_used 0 Key_read_requests 123,092,508 Key_reads 1,090 Key_write_requests 1,221,214 Key_writes 470,310 Max_used_connections 0 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables 0 Open_files -1 Open_streams 0 Opened_tables 256 Questions 8,944,173 Select_full_join 10,210 Select_full_range_join 0 Select_range 4,898 Select_range_check 2 Select_scan 1,629,106 Slave_running 0 Slave_open_temp_tables 0 Slow_launch_threads 0 Slow_queries 1 Sort_merge_passes 25 Sort_range 57,128 Sort_rows 11,368,012 Sort_scan 126,770 Table_locks_immediate 7,149,531 Table_locks_waited 136,276 Threads_cached 0 Threads_created 8,738 Threads_connected 48 Threads_running 1 Uptime 86,400 Many Thanks! ---------------------------- ERG Group -------------------------- The contents of this email and any attachments are confidential and may only be read by the intended recipient. ----------------------------------------------------------------- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
