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_commands 978,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_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? 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_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! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
