By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,843 Members | 864 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,843 IT Pros & Developers. It's quick & easy.

Extended Stats: how to interpret?

P: n/a
dba
Hi
I am a new MySQL dba tuning a busy Apache/MySQL installation. 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
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.