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.

Kev suggested this question ( http://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 table 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

share|improve this question
1  
If you're not familiar with the great work of Percona, please check percona.com/doc/percona-toolkit/2.1/pt-variable-advisor.html for something similar to what you're describing. –  PythianMoore May 10 '12 at 15:49
    
Percona seems to be good. They even got a wizard: tools.percona.com/wizard –  dynamic May 10 '12 at 17:59
    
jetprofiler.com too good –  dynamic May 11 '12 at 14:24

2 Answers 2

up vote 2 down vote accepted

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!

share|improve this answer
    
pt variable advisors seems pretty good –  dynamic May 11 '12 at 14:27

The question asked by yes123 is very relevant in the sense that we all want to know what we can do to improve MySQL's performance without touching the application that's consuming that data. What is that perfect configuration that will just make everything better? Although no one program can just spit out that perfect configuration, there are ways to dramatically improve your server's efficency without touching one piece of code. For starters, I begin with the server's health and what kind of data that server was destined to provide.

Knowing the health of your MySQL database server and how to better configure it must include many factors based on a large number of variables. What is your MySQL server being used for, a blog, e-commerce, reporting? What are the principal engines you are using, InnoDB, MyISAM? What is the OS, Windows, Linux, Unix? Is there replication involved?

This is important to know because the way you configure MySQL depends on all of these factors and also on the health of the machine that MySQL is installed on. All to often, people are quick to blame MySQL for a problem that is system wide. If you setup Magento on a server that only has 1 CPU, 1Gb of ram and and you run out of memory because you have 1000 simultaneous competing on Apache, is it MySQL's fault? Obviously not.

So before you start asking yourself, how is MySQL doing, ask yourself, how is the server its installed on doing?

When dealing with a Linux server for example, here are the steps I follow to perform a general health check

  1. Is there enough RAM? Answer: free -m
  2. Is there enough disk space where MySQL is installed, typically /var/lib/mysql. Answer: df -h
  3. What is the load average, is the CPU or CPUs at 100%. Answer: top or htop
  4. How much traffic is there on the network. Answer: bwm-ng

For a Windows Server, I'm going to use perfmon or process explorer.

If I don't have any disk space left, there's no ram left and/or your network bandwith is saturated, it may not be MySQL's fault. Since MySQL stopped working or is timing out, I may think that MySQL is the cause when its really just the victim of another problem

A very good graphical program for linux that's free is called ksar http://sourceforge.net/projects/ksar/. You can use ksar to produce graphs based on the statistics collected by the sar service. sar can be easily installed on both debian and centos and collects statistics about memory, cpu, disk, network usage. All by itself sar provides you with that information in a numerical report. ksar goes one step further and puts that information into time based graphs that help to visualize the health of your server at any given point in the day or from any given day in the past.

In Windows, you need something that could do the same thing, like Process Explorer http://technet.microsoft.com/en-us/sysinternals/bb896653 or perfmon. ksar and process explorer are both free.

Once I've eliminated the server or other applications such as Apache as possible culprits, that's when I start looking at MySQL.

I'll start with the MySQL's error log if there is one, and I'll look for disk space errors, or table corruption errors or other messages that stand out. If I don't see anything out of the ordinary, I go to my favorite tool Jet Profiler http://www.jetprofiler.com.

Jet Profiler provides you with real time statistics on the state of your MySQL server over a given period of time. This is much more useful than just a simple snapshot of activity at any given moment. Knowing how your server behaves during low and high peak periods of time will ultimately shed light on the weaker areas of your infrasture and that's where Jet Profiler comes in. Its analysis and statistics are based on a server's workload over time.

Find problematic queries

As a first step, I try to identify which the problematic queries are. Check top queries and slow queries, and compare the relative thread load of each of them. Probably, a few of them will stand out clearly from the rest (according to the 90/10 rule or so). These are the queries to focus on, because they will give the most performance improvement per fix.

Load Pattern

The load pattern could also be that the server is doing well for the most time, but with recurring peaks from time to time. It will be very helpful to zoom in on the peaks to discover which queries are heavy during that timeframe. I.e. if you have heavy cron jobs which run once per day, they might not show up too high on the top queries for the last 24 hours so you might not think about them. However, they can still cause so much load and congestion that the server gets really slow precisely at midnight. One database I profiled had this pattern - the developers had added daily cron jobs which did various cleanup, archiving and statistical calculations. The problem was that all of the jobs started at 0 0 * * *. While the server could have handled one or two of these jobs and still serving regular web site visitors, 5+ concurrent jobs was too much. So the site went down for a minute or two every midnight. But those queries probably wouldn't have shown up unless we looked at that specific peak.

Going further, I move on to statistics specific to the engine that my MySQL tables are using MyISAM or InnoDB.

Before though, I ask myself one important question. Is the database mostly InnoDB tables or MyISAM tables?

    use database;
    show table status \G;

*************************** 148. row ***************************
           Name: sometable
         Engine: InnoDB

Many production databases in MySQL become a mixture on InnoDB and MyISAM. This requires an optimization of MySQL for both engines.

InnoDB Specific Information

  1. Buffer Pool Hit Rate : This should typically be at 100%. If it isn't, MySQL is spending a lot of its time reading the disk instead of memory to retrieve data. Increasing the buffer pool, may resolve this problem.
  2. InnoDB disk I/O statistics. Is the server having disk problems, maybe I need to upgrade to something faster, if InnoDB is writing gigabytes of data to the server.

MyISAM specific information

  1. Locked queries: Queries can easily block eachother in MyISAM tables due to locking. Jet Profiler will show you which queries are locked and on what tables. You can then easily resolve this probem by switching the table engine from MyISAM to InnoDB.

  2. MyISAM cache: This should also be as close to 100% as possible. If not, you can increase the key buffer cache to resolve the problem and/or build better indexes.

Next I look at what's going on globally.

Global Factors

  1. States: What is MySQL doing? Is it copying data to temporary tables, is it sending data across the network or maybe its sorting. In other words what processes in MySQL are using up the most CPU, memory and IO.
  2. Slow queries: What queries are taking the most of the CPU's time. You can also get this by activating the slow query log on the server, but this would impact the server, because it would be writing those queries to a disk.
  3. Number of simultaneous queries/users. Perhaps there are just too many users on my little server.

Correlating KSAR and Jet Profiler statistics

With Ksar's and Jet Profiler's graphs, you can actually correlate server performance charts with the MySQL's performance charts. How is the server behaving when MySQL starts running that batch job. If you notice a large number of major page faults in KSAR right when MySQL is processing that huge import that you see running as a Top Query in Jet Profiler right when MySQL is serving 1000 simultaneous users, you could just simply run your import when there aren't so many incoming connections.

Then there are other factors like, is the skip-name-resolve feature turned on. If MySQL has to spend its time resolving domain names, its spending less time retrieving data. I also look at the Query Plan Cache to make sure that's also at 100%

As far as web solutions go. I am beginning to discover the joys of Zabbix http://www.zabbix.com/. It is geared towards monitoring multiple servers simultaneously but is capable of providing very precise detail about MySQL and all types of servers and technologies.

It is possible to have that perfect configuration. With a thourough understanding of your OS, an intelligent analysis of your server's health, the queries its processing and the kind of workload your are asking it to handle you can focus on just those areas that need your attention. Just adding a couple of gigabytes to your buffer pool or getting a faster disk might improve your server's performance, but why spend money on ram and faster disks, when the solution might just be spending a couple of hours on analyzing your server's workload and how its behaving even when your not there to monitor it.

share|improve this answer
    
good answer but I am looking for a script/gui not for an explanation :) –  dynamic May 10 '12 at 17:51
    
Good point, Jet Profiler is a GUI based tool to check on the health of MySQL and KSAR is a GUI based tool to check on the health of your server. The explanation was just to help understand that MySQL's status is only half the picture. –  Craig Efrein May 10 '12 at 19:16
    
+1..Good Explanation!!! –  Abdul Manaf May 11 '12 at 6:40
    
Thanks Abdul. I wish I could have added pictures of ksar. Its a really awesome utility. Personally I don't see how you can configure MySQL without giving the server its installed on just as much attention. –  Craig Efrein May 11 '12 at 6:53

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.