Performance Tuning Primer Script is the shell script which gets performance related information from MySQL engine and produce some recommendations for tuning of MySQL server variables. It’s easy to use and offers valuable output for MySQL performance optimization.
A Bit of Theory
In previous three posts of this MySQL Optimizing tutorial mini series (Configuration Directives, Log Slow Queries and Importance of JOIN Order) we went thru the basics of MySQL performance. Today we’ll move a bit forward and use the knowledge got from previous parts.
Performance Tuning Primer Script as mentioned earlier is the shell script, written by Matthew Montgomery, which can significantly help you optimize MySQL installation. The usage is simple, the only one condition is that MySQL server must run at least 48 hours before the first run of the Performance Tuning Primer Script, to be efficient.
Performance Tuning Primer Script Usage
A Bit of Practice
Let’s try to run the Performance Tuning Primer Script in *NIX like environment:
[root@host /usr/home/webdevstuff.com]# ./tuning-primer.sh
From the stated above shell command you’ll get nice colorfull output in the terminal. But in most cases the output is longer than your screen size and there’s a problem to check all data (except the PuTTY, which allows you to move output up and down by sliding the sidebar). Even more or less output formatting programs don’t help, because you’ll lost the colors and the output is not so neat. I use to redirect the output of the Performance Tuning Primer Script to some file for later reading and analysis. Most often I use the TXT extension of the output file to have a possibility to read it on Windows machine too. So, the run command of the shell script will be as follows:
[root@host /usr/home/webdevstuff.com]# ./tuning-primer.sh all > ./tuning-primer.txt
Now, you can view or parse the output of the Performance Tuning Primer Script on both *NIX like and Windows machines by using known tools (grep, awk, sed, etc.).
Using Output
Well, we have some data from the Performance Tuning Primer Script and what’s next? Let’s take a closer look how we can use this helpfull information. As I mentioned earlier, you’ll probably get colorized output. These colors are very important because they distinguish the information in the output.
Below you can find the first screenshot fo the Performance Tuning Primer Script output which contains general information about MySQL and description of the first checked option – SLOW QUERIES. Blue color means the name of the server parameter checked. White color is common text. Green color means that value/parameter is set OK while red one means there are some deficiencies found and recommendations for better performance are offered.
In this screenshot (below) you can see that max_connections variable is set up properly and thus optimaly.
But following screenshot shows that table_cache sems to be too low and should be increased.
Summary
As you can see, the Performance Tuning Primer Script can be very helpfull in MySQL optimization process. Its output containg a preview of important, performance related variables with their assesments is really neat and handy. Suggestions of the script may lead to better MySQL performance or to thinking about it at least. Tune and enjoy!
On my rackspace ubuntu server, the colorized output looks all garbled with less command and for some strange reason appears all sane with more command. Also to capture the interaction, you can also use $script command.