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_commands978,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_range57,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_received726,146,766
Bytes_sent3,574,227,784
Com_admin_commands978,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_commit1,787
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_delete33,768
Com_drop_db 0
Com_drop_function 0
Com_drop_index0
Com_drop_table0
Com_flush 0
Com_grant 0
Com_insert164,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_select1
Com_reset 0
Com_restore_table 0
Com_revoke0
Com_rollback 0
Com_select5,780,420
Com_set_option103
Com_show_binlogs 0
Com_show_create 103
Com_show_databases0
Com_show_fields 103
Com_show_grants 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status0
Com_show_open_tables 0
Com_show_processlist 0
Com_show_slave_status 0
Com_show_status 4
Com_show_innodb_status0
Com_show_tables 3
Com_show_variables0
Com_slave_start 0
Com_slave_stop0
Com_truncate 0
Com_unlock_tables 0
Com_update895,584
Connections 536,998
Created_tmp_disk_tables 7,928
Created_tmp_tables57,552
Created_tmp_files 50
Delayed_insert_threads0
Delayed_errors0
Flush_commands0
Handler_delete157,555
Handler_read_first19,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_update3,657,710
Handler_write 9,068,562
Key_blocks_used 0
Key_read_requests 123,092,508
Key_reads 1,090
Key_write_requests1,221,214
Key_writes470,310
Max_used_connections 0
Not_flushed_key_blocks0
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_join0
Select_range 4,898
Select_range_check2
Select_scan 1,629,106
Slave_running 0
Slave_open_temp_tables0
Slow_launch_threads 0
Slow_queries 1
Sort_merge_passes 25
Sort_range57,128
Sort_rows 11,368,012
Sort_scan 126,770
Table_locks_immediate 7,149,531
Table_locks_waited136,276
Threads_cached0
Threads_created 8,738
Threads_connected 48
