We remember when we first started auditing MySQL servers, there were very few tools available.  In one of our early big gigs, we were battling serious performance issues for a client.  At the time, tuning-primer.sh was about the only tool available that could be used to diagnose performance bottlenecks.  Fortunately, with a lot of manual interpolation of the raw data it presented, we were able to find the issue with the server and suggest how to resolve them.  For that we are very thankful.  It was a first step in analyzing MySQL status variables, minimizing the number of formulas to learn and calculate by hand.  Obviously doing it by hand takes forever!

Now fast-forward to today.  Unfortunately, not much has changed.  Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on.  Don’t get the wrong; those tools have …

With Galera (Percona Cluster or MariaDB Cluster), it is sometimes advisable to not route traffic to a node during a backup due to the node already being under a heavier load.  In these situations, it may be wise to not route traffic there until the backup is complete.

Since the default /usr/bin/clustercheck script did not have the option of doing this, we created a modified version.  The below script looks for the presence of the xtrabackup tool running in the process list.  If it is found, the clustercheck script returns the appropriate exit code (502) which signals the load balancer to not route traffic its way.  The script could easily be modified to look for the presence of programs/tools in the process list.

We hope others will find it useful.

#!/bin/bash
# clustercheck.sh
#
# Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly
#
# Modified by Itchy …

Ever get called out for a MySQL issue only to realize that there was no issue?  It was a false alarm from the monitor.  We sure have and it’s frustrating, especially at 3:00 or 4:00 in the morning!

Many DBAs work in an environment where there is some sort of first level support that gets assigned tickets first.  Unfortunately, many of the times these groups are, shall we say, less than skilled in MySQL.  As a result, they quickly escalate the ticket onto the primary on-call DBA, even when there is really nothing wrong.

Much of the time, there are multiple types of MySQL topology in these environments: standalone, galera cluster, replication, etc.  Writing large runbooks with detailed test cases can be a daunting process and one that will cause many first-level support engineers to give up and simply escalate the issue anyway.

In an effort to avoid undue call outs, we developed a simple bash …

Introduction

This document outlines best practices for loading data into MySQL very quickly.  While this is not a comprehensive list of loading methods and configuration, it is a good starting point.

MySQL Configuration

Assuming you are loading into InnoDB tables (and you should probably be doing so), you will want to ensure that MySQL is properly performance tuned for loading large amounts of data.  Out of the box MySQL configuration is rarely sufficient for performance with MySQL.  It is essential that the InnoDB settings, in particular, be set properly.

First of all, consider the InnoDB Buffer Pool.  If you are doing a one-time load, it may be a good idea to configure this as large as possible.  In fact, we sometimes set this to approximately 90% of the available RAM on the system for the load.  This can then be dropped to between 70 and 80% for …

Itchy Ninja Software is pleased to announce the release of Analyst for MySQL v1.1. Revolutionize the way you work and administrate MySQL, MariaDB, Galera, and Percona XtraDB installations.

Make More Efficient Use of Your Time

Gathering all of the metrics to diagnose a database installation is a very time consuming process, and many simply do not have the experience to know where to begin. With Analyst for MySQL, you will be able to get your hands on hundreds of metrics within moments. It really takes all of the guesswork, as well as tedious long sessions of writing queries out of managing a MySQL database server.

Cross-Platform

Not only can you run the program on Windows, Mac, or Linux, you can also generate server reports from each of those platforms as well! No need to install anything on the server at any time. All diagnostics are run from your laptop or desktop machine. The …

On servers that are into the swap file and have multiple processes running, I often wonder if it’s MySQL that is in the swap or some other process.  With Linux this is a fairly easy thing to figure out.  Below is the format of a command to do just that:

echo 0 $(cat /proc/`pidof process`/smaps | grep TYPE | awk '{print $2}' | sed 's#^#+#') | bc

In the above command, TYPE refers to what type of Memory you want to examine.  Below are some of the options for this value:

Sometimes you encounter a server with multiple tables of a particular storage engine which you need to convert to another storage engine.  For us, this often happens when we find systems running MyISAM and we want to get these over to InnoDB.

There are a number of reasons to consider converting a table to another storage engine, such as performance, gaining additional features such as Foreign Keys, and so on.  You should, however, stop to consider that not all storage engines are created the same and do not offer the same features.

If there are hundreds of tables, the process can be very time consuming so we put together a simple bash script to automate this process.

#!/bin/sh

MY_USER="root"
MY_PASSWORD="mypassword"
MY_HOST="127.0.0.1"
MY_PORT=3306
NEW_ENGINE="InnoDB"

TABLES=`mysql -u$MY_USER -p$MY_PASSWORD -h$MY_HOST -P$MY_PORT -e"SELECT CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) AS 'TABLE' FROM …

Have you ever tried to start a server like MySQL and been amazed to see an error that the port is already in use? You rack your brain and try to figure out what it would be to no avail. Sometimes you do a “ps” in Linux and don’t even see anything that you think would be using the port. Well, forutantely, there are some tricks to help you find out without doing a reboot. If it is a production server, a reboot may not be an option anyway!

Below are some methods to help. We will start by looking at the “fuser” utility provided with many Linux distros:

fuser -n tcp 80
80/tcp:               1029  1030  1824  1838  1839  1840  1841 13972 14136 14137 14712

This example shows a simple check of everything using port 80. What you see above is a list of PIDs that are using that port. Now we could probably just do a simple “ps” to figure out what it is. You might also want to get more info by doing something like the …

Some time back, when a client wanted us to setup MySQL Enterprise Monitor, we were surprised to find out that disk monitoring was not available! We worked hard to come up with a solution. Eventually, we decided to setup a custom agent to monitor the disk. Below is the result of that.

While this script may not work as-is for everyone, it should at least provide a basis for such a script. This script has been modified to send an email instead of plug directly into the MySQL Enterprise Monitor. But, it hopefully will get our creative juices flowing…

#!/bin/bash
#
# This script does a very simple test for checking disk space.
#
# Itchy Ninja Software: http://www.itchyninja.com
#

CHECKDISK=`df -h | awk '{print $5}' | grep % | grep -v Use | sort -n | tail -1 | cut -d "%" -f1 -`
ALERT_VALUE="80"
MAIL_USER="root@localhost.com"
MAIL_SUBJECT="Daily Disk Check"

if [ "$CHECKDISK" -ge "$ALERT_VALUE" ]; then
echo "At least one of my disks is nearly …