ready-to-use resources, tutorials, tips and many other web development related stuff
WARNING! You probably have JavaScript disabled. Some features of this site will not work properly unless JavaScript enabled.
www.webdevelopmentstuff.com changed to www.webdevstuff.com

Optimizing MySQL: Performance Tuning Script

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

  • Download the shell script here.
  • Upload the shell script to your server (if not already downloaded directly by the server:
    wget http://day32.com/MySQL/tuning-primer.sh)
  • Place the script almost wherever you want (one of the best locations is your home directory)
  • Set proper permissions for the shell script to be able to run (chmod 755 should be enough)
  • Run Performance Tuning Primer Script
  • Apply the suggestions of the shell script output

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.

Primer Script Output 1

In this screenshot (below) you can see that max_connections variable is set up properly and thus optimaly.

Primer Script Output 2

But following screenshot shows that table_cache sems to be too low and should be increased.

Primer Script Output 3

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!

One Response to “Optimizing MySQL: Performance Tuning Script”

  1. Rajeev Jha says:

    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.

© 2008 - 2018, webdevstuff.com | Powered by Wordpress | Theme by Elegant Themes | Valid XHTML and CSS | Subscribe to RSS