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
- Is there enough RAM? Answer: free -m
- Is there enough disk space where MySQL is installed, typically /var/lib/mysql. Answer: df -h
- What is the load average, is the CPU or CPUs at 100%. Answer: top or htop
- 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
- 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.
- 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
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.
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
- 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.
- 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.
- 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.
