> I'm sure we can put something into place in addition to the existing > monitoring. --aB
In the summer time, Greg Steffensen put together a python script to gather periodic statistics from the MySQL admin tool in order to monitor the DB and troubleshoot issues. This tool provides statistics on the number of threads running in the DB each time it polls. This could be useful for getting more information into the issue that Naganand raised. The tool can be run on a remote server, but it requires that the 'mysqladmin' tool is available on your local machine (this is in the 'bin' directory of your local MySQL server installation). Here are some instructions on how to use the tool: 1. Install python 2.5.1 or greater on the machine you are going to run the script from (note that the tool can be run remotely, so python and the script do not have to be installed on your DB server). 2. Save the mysqlmonitor.py python script (attached) on your machine 3. Create a shortcut link to your mysqladmin.exe file (MySQL Administrator program) in the same location as the python script. This file is usually in the 'bin' directory of your local MySQL Server installation. 4. Run the mysqlmonitor.py tool in the command line as follows: mysqlmonitor.py -s <URL of remote or local MySQL server> -u <mysql username> -p <mysql password> -i <polling interval in seconds> -f <log file name to where results will be written> If you don't specify the server, it will assume localhost. If you don't specify the filename, it will write to 'mysqlstats.log' in the current directory, if you don't specify polling interval, it will poll at 10 second intervals, I think. Running with just the "-h" option will give information on how to use the tool. Note also that the log files get large very fast if you poll frequently so you will only want to run this for short period of time if you need to pull stats frequently. Amy - I had provided GK with this information in the summer and installed on their test machines, so it may still be there to use. Naganand, JS or Praveen may still remember where it is located. Attached to this email is the mysqlmonitor.py script and below is sample output from the log file that it provides so you can get a sense for the information it provides. I think the information should also be up on Mifos.org somewhere (I had created a troubleshooting page with this information). If it's not, I will post it when I next have an internet connection. Thanks, Aliya # mysqlmonitor log started 2007-11-15 16:49:07.656000 # host localhost # dbuser root # mysqlmonitor output 2007-11-15 16:49:08.312000 Aborted_clients 0 Aborted_connects 0 Binlog_cache_disk_use 0 Binlog_cache_use 0 Bytes_received 97 Bytes_sent 80 Com_admin_commands 0 Com_alter_db 0 Com_alter_table 0 Com_analyze 0 Com_backup_table 0 Com_begin 0 Com_call_procedure 0 Com_change_db 0 Com_change_master 0 Com_check 0 Com_checksum 0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index 0 Com_create_table 0 Com_create_user 0 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi 0 Com_do 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_drop_user 0 Com_execute_sql 0 Com_flush 0 Com_grant 0 Com_ha_close 0 Com_ha_open 0 Com_ha_read 0 Com_help 0 Com_insert 0 Com_insert_select 0 Com_kill 0 Com_load 0 Com_load_master_data 0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize 0 Com_preload_keys 0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_rename_table 0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback 0 Com_savepoint 0 Com_select 0 Com_set_option 0 Com_show_binlog_events 0 Com_show_binlogs 0 Com_show_charsets 0 Com_show_collations 0 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 0 Com_show_databases 0 Com_show_errors 0 Com_show_fields 0 Com_show_grants 0 Com_show_innodb_status 0 Com_show_keys 0 Com_show_logs 0 Com_show_master_status 0 Com_show_ndb_status 0 Com_show_new_master 0 Com_show_open_tables 0 Com_show_privileges 0 Com_show_processlist 0 Com_show_slave_hosts 0 Com_show_slave_status 0 Com_show_status 1 Com_show_storage_engines 0 Com_show_tables 0 Com_show_triggers 0 Com_show_variables 0 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_stmt_close 0 Com_stmt_execute 0 Com_stmt_fetch 0 Com_stmt_prepare 0 Com_stmt_reset 0 Com_stmt_send_long_data 0 Com_truncate 0 Com_unlock_tables 0 Com_update 0 Com_update_multi 0 Com_xa_commit 0 Com_xa_end 0 Com_xa_prepare 0 Com_xa_recover 0 Com_xa_rollback 0 Com_xa_start 0 Compression OFF Connections 2 Created_tmp_disk_tables 0 Created_tmp_files 5 Created_tmp_tables 1 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 0 Handler_delete 0 Handler_discover 0 Handler_prepare 0 Handler_read_first 3 Handler_read_key 0 Handler_read_next 0 Handler_read_prev 0 Handler_read_rnd 0 Handler_read_rnd_next 7 Handler_rollback 0 Handler_savepoint 0 Handler_savepoint_rollback 0 Handler_update 0 Handler_write 0 Innodb_buffer_pool_pages_data 101 Innodb_buffer_pool_pages_dirty 0 Innodb_buffer_pool_pages_flushed 0 Innodb_buffer_pool_pages_free 985 Innodb_buffer_pool_pages_latched 0 Innodb_buffer_pool_pages_misc 2 Innodb_buffer_pool_pages_total 1088 Innodb_buffer_pool_read_ahead_rnd 1 Innodb_buffer_pool_read_ahead_seq 0 Innodb_buffer_pool_read_requests 2401 Innodb_buffer_pool_reads 50 Innodb_buffer_pool_wait_free 0 Innodb_buffer_pool_write_requests 0 Innodb_data_fsyncs 3 Innodb_data_pending_fsyncs 0 Innodb_data_pending_reads 0 Innodb_data_pending_writes 0 Innodb_data_read 3837952 Innodb_data_reads 111 Innodb_data_writes 3 Innodb_data_written 1536 Innodb_dblwr_pages_written 0 Innodb_dblwr_writes 0 Innodb_log_waits 0 Innodb_log_write_requests 0 Innodb_log_writes 1 Innodb_os_log_fsyncs 3 Innodb_os_log_pending_fsyncs 0 Innodb_os_log_pending_writes 0 Innodb_os_log_written 512 Innodb_page_size 16384 Innodb_pages_created 0 Innodb_pages_read 101 Innodb_pages_written 0 Innodb_row_lock_current_waits 0 Innodb_row_lock_time 0 Innodb_row_lock_time_avg 0 Innodb_row_lock_time_max 0 Innodb_row_lock_waits 0 Innodb_rows_deleted 0 Innodb_rows_inserted 0 Innodb_rows_read 0 Innodb_rows_updated 0 Key_blocks_not_flushed 0 Key_blocks_unused 8981 Key_blocks_used 0 Key_read_requests 0 Key_reads 0 Key_write_requests 0 Key_writes 0 Last_query_cost 0.000000 Max_used_connections 1 Not_flushed_delayed_rows 0 Open_files 12 Open_streams 0 Open_tables 6 Opened_tables 12 Prepared_stmt_count 0 Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0 Questions 1 Rpl_status NULL Select_full_join 0 Select_full_range_join 0 Select_range 0 Select_range_check 0 Select_scan 1 Slave_open_temp_tables 0 Slave_retried_transactions 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 0 Sort_merge_passes 0 Sort_range 0 Sort_rows 0 Sort_scan 0 Ssl_accept_renegotiates 0 Ssl_accepts 0 Ssl_callback_cache_hits 0 Ssl_cipher Ssl_cipher_list Ssl_client_connects 0 Ssl_connect_renegotiates 0 Ssl_ctx_verify_depth 0 Ssl_ctx_verify_mode 0 Ssl_default_timeout 0 Ssl_finished_accepts 0 Ssl_finished_connects 0 Ssl_session_cache_hits 0 Ssl_session_cache_misses 0 Ssl_session_cache_mode NONE Ssl_session_cache_overflows 0 Ssl_session_cache_size 0 Ssl_session_cache_timeouts 0 Ssl_sessions_reused 0 Ssl_used_session_cache_entries 0 Ssl_verify_depth 0 Ssl_verify_mode 0 Ssl_version Table_locks_immediate 12 Table_locks_waited 0 Tc_log_max_pages_used 0 Tc_log_page_size 0 Tc_log_page_waits 0 Threads_cached 0 Threads_connected 1 Threads_created 1 Threads_running 1 Uptime 23 Uptime_since_flush_status 23 # process list +----+------+----------------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+----------------+----+---------+------+-------+------------------+ | 2 | root | localhost:3561 | | Query | 0 | | show processlist | +----+------+----------------+----+---------+------+-------+------------------+
mysqlmonitor.py
Description: mysqlmonitor.py
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
