Vlad Fedorkov

Performance consulting for MySQL and Sphinx

Full table scans and MySQL performance

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

Select_scan from SHOW GLOBAL STATUS indicates how many full table scans were done since last MySQL restart. Scanning the entire table is a resource intensive operation. It also forces MySQL to store unnecessary data in the buffer pool, wasting memory and IO resources. Continue reading

Tips and tricks while working with Production DBs

From time to time we have to work with live environments and production databases. For some of us this is day-to-day job. And most of the time cost of a mistake is way higher than expected improvement especially on the databases. Because issue on the database side will affect everything else.

I heard enough war stories about ruined productions and can imagine well enough speed of DROP DATABASE command replicating across the cluster. So I’m scared to make changes in production. The more loss expected if things go wrong the more I’m going to be scared planning every change. But I still love to make improvements so the only question is how to make them safer.

This post is not intended to be a guide or best practices on how to avoid issues at all, it’s more invitation to discussion that started between me and @randomsurfer in twitter on how to avoid production failures. It was hard for me to fit to 150 characters so I’m switching to more comfortable environment. Continue reading

How to avoid two backups running at the same time

When your backup script is running for too long it sometimes causes the second backup script starting at the time when previous backup is still running. This increasing pressure on the database, makes server slower, could start chain of backup processes and in some cases may break backup integrity.

Simplest solution is to avoid this undesired situation by adding locking to your backup script and prevent script to start second time when it’s already running.

Here is working sample. You will need to replace “sleep 10″ string with actual backup script call:

#!/bin/bash

LOCK_NAME="/tmp/my.lock"
if [[ -e $LOCK_NAME ]] ; then
        echo "re-entry, exiting"
        exit 1
fi

### Placing lock file
touch $LOCK_NAME
echo -n "Started..."

### Performing required work
sleep 10

### Removing lock
rm -f $LOCK_NAME

echo "Done."

It works perfectly most of the times. Problem is that you could still theoretically run two scripts at the same time so both will pass lock file checks and will be running together. To avoid that you would need to place unique lock file just before check and make sure no other processes did the same.

Here is improved version:

#!/bin/bash

UNIQSTR=$$
LOCK_PREFIX="/tmp/my.lock."
LOCK_NAME="$LOCK_PREFIX$UNIQSTR"

### Placing lock file
touch $LOCK_NAME
if [[ -e $LOCK_NAME && `ls -la $LOCK_PREFIX* | wc -l` == 1 ]] ; then
        echo -n "Started..."
        ### Performing required work
        sleep 10
        ### Removing lock
        rm -f $LOCK_NAME
        echo "Done."
else

### another process is running, removing lock
        echo "re-entry, exiting"
        rm -f $LOCK_NAME
        exit 1
fi

Now even if you managed to run two scripts at the same time only one script could actually start backup. In very rare situation both scripts will refuse to start (because of two lock files existing at the same time) but you could catch this issue by simply monitoring script exit code. Anyway – as soon you receive backup exit code different than zero it’s time to review your backup structure and make sure it works as desired.

Please note – when you terminate this script manually you will also need to remove lock file as well so script will pass check on startup. You could also use this script for any periodic tasks you have like Sphinx indexing, merging or index consistency checking.

For your convenience this script is available for download directly or using wget:

wget http://astellar.com/downloads/backup-wrapper.sh

You could also find more about MySQL backup solutions here.

Keep your data safe and have a nice day!

Difference between myisam_sort_buffer_size and sort_buffer_size

MySQL has two confusingly identical by the first look variables myisam_sort_buffer_size and sort_buffer_size. Thing is that those two confusingly similar variables has absolutely different meanings.

sort_buffer_size is a per-connection variable and do not belongs to any specific storage engine. It doesn’t matter do you use MyISAM or InnoDB – MySQL will allocate sort_buffer_size for every sort (required most of the times for ORDER BY and GROUP BY queries) so increasing it’s value might help speeding up those queries however I would not recommend to change it from the default value unless you are absolutely sure about all the drawbacks. Value for out-of-the-box MySQL-5.1.41 installation on Ubuntu is 2Mb and it’s recommended to keep it that way.

On the other side myisam_sort_buffer_size used by MyISAM to perform index sorting on relatively rare table-wide modifications like ALTER/REPAIR TABLE. Stock value is 8Mb so if you are using MyISAM tables intensively (please refer to my other post to see how to know your tables type) I would recommend to set it to some higher value close or even more than key_buffer_size but still small enough to keep it in memory and prevent MySQL from swapping.

Why is stock MySQL slow?

“I’ve installed MySQL and it doesn’t work fast enough for me”. MySQL server is heart of database driven application (if it uses MySQL as database of course!) and any slowness related to running queries is affecting all application layers.

MySQL server tuning and query slowness hunting are always step by step process and without knowing all the data (SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS, SHOW TABLE STATUS LIKE ‘tablename’, EXPLAIN details for slow query is just some of the required information) it would be generally a blind guess. But there are still few things which is related to newly installed MySQL server.

If you are using stock MySQL you might need to check memory pool size which MySQL used to load index data to avoid slow IO requests and increase queries speed. Connect to MySQL and fire two queries: Continue reading