current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I need a MySQL Monitoring Script in Bash or Perl for Linux.

I found some but they are quite big with a lots of checks that are not useful at all for me and in this case I should customise the script a lot.

Any idea for most important parameters of MySQL monitoring in a Shell or Perl script?

Following parameters should be monitored: 

qcache_hits                                                     
SHOW GLOBAL STATUS;                             
SHOW SESSION STATUS;                                
slow_queries                                                    
max_connections                                         
key_buffer_size
mysql availability  STATUS
Error log
SHOW ENGINE INNODB STATUS;
max_used_connections
Handler_read%
share|improve this question

3 Answers 3

up vote 0 down vote accepted

Here are the things I look at when reviewing a server:

(Innodb_buffer_pool_reads) / Uptime   
(Innodb_buffer_pool_pages_flushed) / Uptime   
(Opened_tables) / Uptime 
(Opened_files) / Uptime  
(Opened_table_definitions) / Uptime   
(Qcache_lowmem_prunes) / Uptime  
(Qcache_not_cached) / Uptime 
(Questions) / Uptime 
(Queries) / Uptime   
(Created_tmp_disk_tables) / Uptime    
(Created_tmp_tables) / Uptime
(Handler_read_rnd) / Uptime  
(Com_commit) / Uptime    
(Select_full_join) / Uptime  
(Select_range) / Uptime  
(Select_range_check) / Uptime
(Select_scan) / Uptime   
(Sort_merge_passes) / Uptime 
(Slow_queries) / Uptime  
(Aborted_clients) / Uptime   
(Aborted_connects) / Uptime  
(Connections) / Uptime   
(Threads_created) / Uptime   
(Slow_launch_threads) / Uptime    
(Slave_retried_transactions) / Uptime
(Com_lock_tables) / Uptime   
(Com_rollback) / Uptime  
(Innodb_rows_inserted) / Uptime  
(Innodb_row_lock_waits) / Uptime 
(Innodb_dblwr_writes) / Uptime    
(Innodb_deadlocks) / Uptime  
(Binlog_cache_use) / Uptime  
(Binlog_cache_disk_use) / Uptime 
(Innodb_buffer_pool_reads) / Uptime   
(Innodb_buffer_pool_pages_flushed) / Uptime   
(Innodb_data_fsyncs) / Uptime
(Innodb_os_log_fsyncs) / Uptime  
(Innodb_log_waits) / Uptime  
(Key_reads * 1024 / key_buffer_size) / Uptime 
(Key_reads) / Uptime 
(Key_writes) / Uptime    
(Key_reads + Key_writes) / Uptime
(Table_locks_waited) / Uptime
(Subquery_cache_miss) / Uptime    
(Subquery_cache_hit) / Uptime
(Innodb_buffer_pool_wait_free) / Uptime   
(Qcache_inserts - Qcache_queries_in_cache) / Uptime   
(Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi) / Uptime 
(Com_insert_select + Com_replace_select) / Uptime 
((Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) / Uptime  
(Innodb_buffer_pool_read_ahead_evicted) / Uptime  
(Table_open_cache_overflows) / Uptime
(Table_open_cache_misses) / Uptime    
(Table_open_cache_hits) / Uptime 
(binlog_commits) / Uptime
(binlog_group_commits) / Uptime  
(Binlog_stmt_cache_disk_use) / Uptime
(Slow_launch_threads) / Uptime    
key_buffer_size / _ram    
innodb_buffer_pool_size / _ram    
(key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram   
table_cache  
table_open_cache 
Open_tables / table_cache
Open_tables / table_open_cache    
open_files_limit / table_cache    
open_files_limit / table_open_cache   
open_files_limit 
innodb_open_files
query_cache_type 
query_cache_size 
Qcache_free_memory / query_cache_size 
Qcache_free_blocks * 4096 / query_cache_size  
Qcache_hits / Qcache_inserts 
Qcache_hits / (Qcache_hits + Com_select)  
Qcache_total_blocks * query_cache_min_res_unit / Qcache_queries_in_cache    
Qcache_queries_in_cache  
Table_locks_waited / (Table_locks_immediate + Table_locks_waited)  
Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables)    
min(max_heap_table_size, tmp_table_size)  
max_heap_table_size  
tmp_table_size   
tmp_table_size * max_connections  
Handler_read_next / Handler_read_key  
Handler_read_rnd_next / Handler_read_rnd  
(Com_select + Qcache_hits) / (Com_insert + Com_update + Com_delete + Com_replace)   
Com_rollback / Com_commit
(Com_insert + Com_update + Com_delete + Com_replace) / Com_commit  
Select_full_join / Com_select
Select_range / Com_select
Select_range_check / Com_select   
Select_scan / Com_select 
sort_buffer_size 
log_slow_queries 
slow_query_log   
slow_query_log_file  
long_query_time  
Slow_queries / Questions 
wait_timeout 
max_connections  
Max_used_connections / max_connections    
Questions / Connections  
Com_change_db / Connections   
Aborted_clients / Connections
Aborted_connects / Connections    
max_connect_errors   
thread_cache_size
Threads_cached   
Threads_connected / max_connections   
Threads_running - 1  
Threads_created / Connections
Threads_running / thread_cache_size   
slow_launch_time 
connect_timeout  
Slave_IO_Running 
Slave_SQL_Running
Seconds_Behind_Master
Last_error   
log_slave_updates
log_slow_slave_statements
binlog_format    
expire_logs_days 
Seconds_behind_master
read_only    
max_allowed_packet   
slave_max_allowed_packet 
net_buffer_length
join_buffer_size 
read_buffer_size 
read_rnd_buffer_size 
tmp_table_size   
tmp_table_size * max_connections  
myisam_sort_buffer_size  
sort_buffer_size 
read_buffer_size 
max_length_for_sort_data 
bulk_insert_buffer_size  
log_bin  
innodb_buffer_pool_size  
innodb_thread_concurrency
innodb_flush_method  
innodb_file_per_table
innodb_file_format   
Innodb_row_lock_time_avg 
innodb_lock_wait_timeout 
Innodb_have_atomic_builtins   
Innodb_dblwr_pages_written / Innodb_dblwr_writes  
innodb_checksums 
tx_isolation 
innodb_additional_mem_pool_size   
innodb_force_recovery
innodb_commit_concurrency
innodb_locks_unsafe_for_binlog    
binlog_cache_size
Binlog_cache_disk_use / Binlog_cache_use  
(binlog_cache_size * max_connections) / _ram  
innodb_buffer_pool_size  
innodb_buffer_pool_size / _ram    
(key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram   
innodb_buffer_pool_instances 
Innodb_buffer_pool_pages_free * 16384 / innodb_buffer_pool_size    
innodb_old_blocks_pct
innodb_old_blocks_time    
(Innodb_pages_written + Innodb_pages_read) / ((Innodb_pages_written + Innodb_pages_read) + (Key_writes + Key_reads)) 
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests    
Innodb_pages_read / Innodb_buffer_pool_read_requests   
Innodb_pages_written / Innodb_buffer_pool_write_requests   
1 - Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead  
Innodb_buffer_pool_wait_free / Innodb_buffer_pool_write_requests   
innodb_log_buffer_size    
Innodb_log_waits / Innodb_log_writes  
Innodb_log_writes / Innodb_log_write_requests 
innodb_log_files_in_group
innodb_log_file_size 
innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size 
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group
Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size  
Uptime / 60 * innodb_log_file_size / Innodb_os_log_written
innodb_fast_shutdown 
innodb_adaptive_checkpoint    
innodb_flush_log_at_trx_commit    
innodb_doublewrite   
sync_binlog  
innodb_fast_shutdown 
innodb_adaptive_hash_index    
innodb_thread_concurrency
innodb_io_capacity   
innodb_max_dirty_pages_pct    
innodb_read_io_threads    
innodb_write_io_threads  
innodb_flush_log_at_trx_commit_session    
key_buffer_size / _ram    
Key_blocks_used * 1024 / key_buffer_size  
(Key_writes + Key_reads) / ((Innodb_pages_written + Innodb_pages_read) + (Key_writes + Key_reads))  
Key_reads / Key_read_requests
Key_writes / Key_write_requests   
Table_locks_waited / (Table_locks_immediate + Table_locks_waited)  
table_lock_wait_timeout  
table_lock_wait_timeout  
min(max_heap_table_size, tmp_table_size)  
myisam_max_sort_file_size
read_buffer_size 
read_rnd_buffer_size 
myisam_sort_buffer_size  
myisam_recover_options    
bulk_insert_buffer_size  
character_set_client 
character_set_connection 
character_set_results
character_set_server 
default_character_set
Ssl_finished_accepts 
performance_schema   
ft_min_word_len  
init_connect 
init_file    
init_slave   
large_pages  
locked_in_memory 
log_warnings 
old_passwords    
optimizer_prune_level
optimizer_search_depth    
optimizer_switch 
(Com_show_create_table + Com_show_fields) / Questions 
skip_name_resolve
have_symlink 
local_infile 
log_warnings 
optimizer_search_depth    
innodb_open_files
innodb_adaptive_flushing 
tmpdir   
Uptime   
Uptime_since_flush_status
fppbar   
innodb_autoinc_lock_mode 
innodb_flush_neighbors    
innodb_io_capacity   
Key_reads + Key_writes    
Innodb_pages_read + Innodb_pages_written  
Key_reads + Key_writes    
Innodb_pages_read + Innodb_pages_written  
max_heap_table_size  
Qcache_not_cached / (Qcache_hits + Com_select + Qcache_not_cached) 
table_open_cache_instances    
version  
Qcache_free_blocks / Qcache_total_blocks  
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache 
query_alloc_block_size    
query_prealloc_size  
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size 
innodb_stats_auto_update 
query_prealloc_size  
query_alloc_block_size    
expand_fast_index_creation    
innodb_use_sys_malloc
innodb_lazy_drop_table    
innodb_concurrency_tickets    
fast_index_creation  
innodb_rollback_on_timeout    
thread_handling  
thread_pool_size 
innodb_recovery_update_relay_log  
eq_range_index_dive_limit
optimizer_trace_max_mem_size 
binlog_commits   
binlog_group_commits 
read_buffer_size / max_allowed_packet 
innodb_ft_total_cache_size    
record_buffer    
innodb_random_read_ahead 
Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead  
innodb_buffer_pool_populate   
flush_caches 
numa_interleave  
innodb_strict_mode   
(Queries-Questions)/Queries   
Subquery_cache_hit / ( Subquery_cache_hit + Subquery_cache_miss )  
table_definition_cache    
host_cache_size  
open_files_limit 
table_definition_cache    
back_log
innodb_page_size 
log_slow_admin_statements
log_slow_slave_statements
innodb_log_files_in_group
innodb_flush_log_at_trx_commit    
binlog_format    
auto_increment_increment 
simplified-binlog-gtid-recovery   
wsrep_sync_wait  
wsrep_provider_options    
wsrep_slave_threads  
wsrep_auto_increment_control 
wsrep_retry_autocommit    
wsrep_causal_reads   
wsrep_on
Uptime - Uptime_since_flush_status    
max_connections  
read_buffer_size 
10 * read_buffer_size
innodb_expand_import 
innodb_import_table_from_xtrabackup   
binlog_error_action  
autocommit   
version  
version_compile_machine  
version_compile_os   
max_heap_table_size / _ram    
join_buffer_size / _ram  
sort_buffer_size / _ram  
Binlog_stmt_cache_disk_use / Binlog_stmt_cache_use
(binlog_stmt_cache_size * max_connections) / _ram 
binlog_stmt_cache_size    
Com...   
net_buffer_length / max_allowed_packet    
slow_launch_time 
default_storage_engine    
default_tmp_storage_engine    
storage_engine   
transaction_isolation
flush_time   
innodb_stats_auto_recalc 
innodb_stats_on_metadata 
innodb_stats_persistent  
innodb_stats_persistent_sample_pages  
innodb_checksum_algorithm
binlog_checksum  
read_only    
master_info_repository    
enforce_gtid_consistency 
gtid_mode    
SQL_delay    
slave_parallel_workers    
slave_compressed_protocol
Innodb_deadlocks / Com_commit
innodb_lock_wait_timeout / Queries    
Innodb_history_list_length
share|improve this answer
    
Thanks, how It would be in Perl as a script ! –  Navid Ostovar May 13 at 6:54
up vote 0 down vote

For mysql server monitoring you should concern these- Mysql server is running or not by command (service mysql status) and by telnet the server that is running or not. You should consider these parameters.

ibdata size
no of connections or Threads
reads per second
write per second
cpu load
memory utilization
no of open tables
query per second
insert/update per second
mysql-server uptime
slow queries
flush tables
no of questions
share|improve this answer
up vote 0 down vote

I recommend you use Nagios and Cacti plugins from Percona. Show engine innodb status spits out lots of data, not always easy to parse. Percona has a tool for that i believe.

share|improve this answer
    
I know Nagios but the place I worked we are not able to use this. The manual script should be created. My latest question is : dba.stackexchange.com/questions/114339/… –  Navid Ostovar 2 days ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.