Mysql – Script to show MySQL health status

MySQLPHP

Kev suggested this question ( https://stackoverflow.com/questions/10524981/php-script-to-show-mysql-health-status ) is better fitted here.

I know phpMyAdmin can shows the lists of MySQL vars and stuff like that.

But I would love to have a script that shows me if, for example, there is something wrong with MySQL configs or some graphic tools to see current MySQL health status.

Is there something like that? How can I know, for example, if current settings aren't good for my machine?

Let's say I have innodb buffer pool size set to 64MB while one of my INNODB tables is 1GB. That's not good at all. And that's just an example with innodb pool size. I think there could be many other settings to be aware of that a web based script could help to monitor.

Best Answer

There are a few options in this space.

A favorite one for information like your buffer pool is too small is mysqltuner.pl. The developer did a nice trick where you can download it by running wget mysqltuner.pl. It outputs something similar to this

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 6M (Tables: 30)
[--] Data in InnoDB tables: 359M (Tables: 1206)
[!!] Total fragmented tables: 21

-------- Security Recommendations  -------------------------------------------
[!!] User '@localhost' has no password set.
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@localhost' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 15h 35m 57s (30K q [0.036 qps], 9K conn, TX: 7M, RX: 1M)
[--] Reads / Writes: 50% / 50%
[--] Total buffers: 322.0M global + 5.4M per thread (15 max threads)
[OK] Maximum possible memory usage: 402.6M (6% of installed RAM)
[OK] Slow queries: 0% (0/30K)
[OK] Highest usage of available connections: 13% (2/15)
[OK] Key buffer size / total MyISAM indexes: 2.0M/921.0K
[OK] Key buffer hit rate: 100.0% (19K cached / 8 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 6 total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 34% (10 open / 29 opened)
[OK] Open file limit used: 0% (19/2K)
[OK] Table locks acquired immediately: 99% (10K immediate / 10K locks)
[!!] InnoDB data size / buffer pool: 359.3M/256.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    innodb_buffer_pool_size (>= 359M)

As PythianMoore mentioned, pt-variable-advisor is another option. It checks different things then mysqltuner.pl does. For example, it outputs

# NOTE auto_increment: Are you trying to write to more than one server in a dual-master or ring replication configuration?  This is potentially very dangerous and in most cases is a serious mistake.

# WARN delay_key_write: MyISAM index blocks are never flushed until necessary.

# WARN innodb_additional_mem_pool_size: This variable generally doesn't need to be larger than 20MB.

# WARN innodb_checksums: InnoDB checksums are disabled.

# WARN innodb_doublewrite: InnoDB doublewrite is disabled.

# WARN innodb_flush_log_at_trx_commit-1: InnoDB is not configured in strictly ACID mode.

# NOTE log_warnings-2: Log_warnings must be set greater than 1 to log unusual events such as aborted connections.

# NOTE max_connect_errors: max_connect_errors should probably be set as large as your platform allows.

# NOTE read_buffer_size-1: The read_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# NOTE read_rnd_buffer_size-1: The read_rnd_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# WARN slave_net_timeout: This variable is set too high.

# NOTE sort_buffer_size-1: The sort_buffer_size variable should generally be left at its default unless an expert determines it is necessary to change it.

# WARN sync_frm: It is best to set sync_frm so that .frm files are flushed safely to disk in case of a server crash.

# NOTE innodb_data_file_path: Auto-extending InnoDB files can consume a lot of disk space that is very difficult to reclaim later.

# WARN innodb_support_xa: MySQL's internal XA transaction support between InnoDB and the binary log is disabled.

# WARN log_bin: Binary logging is disabled, so point-in-time recovery and replication are not possible.

# WARN myisam_recover_options: myisam_recover_options should be set to some value such as BACKUP,FORCE to ensure that table corruption is noticed.

# WARN sync_binlog: Binary logging is enabled, but sync_binlog isn't configured so that every transaction is flushed to the binary log for durability.

Those are the main two I've run into people using. G'luck!

Related Question