interpreting extended statistics
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
Re: interpreting extended statistics
Hi Mark, I say PHP below because I'm just assuming that's what's connecting to MySQL. :-) - Original Message - From: Mark Teehan Sent: Monday, October 27, 2003 2:42 AM Subject: interpreting extended statistics 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! I didn't know what admin_commands were either. :-) But I just took a quick look at the source to see where this variable is incremented. There's a couple things but it looks like it's in a change user function. It's at 0 on my system, however. BUT, I don't use persistent connections. I enabled them, and sure enough, this variable started increasing. :-) So, it looks like on the initial connection, there's no change user because the user is sent in mysql_connect(). But when there's an existing persistent connection for the current Apache process, PHP uses it and must do a mysql_change_user() using the user/pass in mysql_pconnect() even if it's the same user -- although I thought persistent connections were just for the same user according to the PHP manual... Aha, I see why this must happen in PHP! From the MySQL manual for mysql_change_user(): Starting from MySQL 4.0.6 this command will always ROLLBACK any active transactions, close all temporary tables, unlock all locked tables and reset the state as if one had done a new connect. This will happen even if the user didn't change. PHP must do that to fix all those nasty problems I had with persistent connections! Maybe too much info for you, but I learned something from this. :-) 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? Com_analyze being 0 doesn't indicate that the tables aren't analyzed. Just that no ANALYZE command has been run since the server started. 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? No, those are actually new, fresh connections. PHP's persistent connections aren't pooled unfortunately. :-( They're only usable in the Apache process that created the connection. This leaves idle MySQL connections even when that Apache process is serving a non-PHP request, among other things. These new connections won't start a new connection thread (most of the time) since it looks like you have thread_cache_size set to a non-zero value (because Threads_created Connections). 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? Sort_merge_passes is 25. I think this means that 25 times sort_buffer_size wasn't large enough to do the sort completely in memory. I don't know if sort_buffer_size is allocated all at once or as needed up to the set size. If the latter, lowering it probably wouldn't save much memory. If the former, try setting it to 512K or something. If Sort_merge_passes doesn't go up by a lot, it should be fine. What is the size of your key_buffer? It doesn't look like you need it very big, because from the status below, Key_reads is only 1090 (~1MB ;-)). Are you using only MyISAM tables? 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 I would also suggest upgrading to MySQL 4 for speed improvements. And don't forget to set query_cache_size to 32M or so! :-) Hope that helps. Matt (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
