Showing entries 1 to 10 of 25
10 Older Entries »
Displaying posts with tag: How To (reset)
Part 1: How to Effectively Use a Performance Schema

Performance Schema (PS) has been the subject of many, many recent discussions, presentations, and articles.  After its release in MySQL 5.7, PS has become the main actor for people who want to take the further steps in MySQL monitoring. At the same time, it has become clear that Oracle intends to make PS powerful with so many features and new instrumentation that old-style monitoring will begin to look like obsolete tools from the Stone Age.

This article will explain PS and provide guidance on what needs to be done in order to use it effectively.

What I am not going to do is to dig into specific performance issues or address polemics about what PS is and what, in a Utopian vision, it should be. I have seen too many presentations, articles and comments like this and they are not productive, nor are they in line with my target which is: keep people informed on how to do things EASILY.

For the scope of this …

[Read more]
MySQL Group Replication: A small Corosync guide

MySQL Group Replication is here and with it comes the need to install and configure the underlying group communication toolkit that supports it: Corosync. Corosync is a well known and reliable Group Communication System that is used in such applications as Pacemaker.

In term of support, we develop MySQL Group Replication based on Corosync version 1.4.6, so this tutorial is based on this version. Regardless of this, no known problems are know to exists when using newer versions, but no extensive testing has been done on those.

Along with Corosync we also encourage the use of NSS to better secure your data that is transmitted in the group.


To install Corosync, you can rely on your packet manager for most distributions or compile it from source.

==> From the package manager

  • Debian distributions
$ sudo apt-get install corosync corosync-dev …
[Read more]
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. …

[Read more]
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:


if [[ -e $LOCK_NAME ]] ; then
        echo "re-entry, exiting"
        exit 1

### 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 …

[Read more]
PHP error and MySQL Percona Server 5.5

If you ever get an error with MySQL client library:

php: error while loading shared libraries: cannot open shared object file: No such file or directory

while using Percona MySQL Server 5.5 just go ahead and install Percona-Server-shared-compat package from Percona Repo:

yum install Percona-Server-shared-compat

Percona Tease Kit

Percona you know I love you.  You have the largest brains working with MySQL. Your toolkit (formerly Maatkit and Aspersa) is a real gift to the MySQL community. But stop teasing us with webinars about what it can do and show us how to use it.

The Percona ToolKit needs a cookbook. The documentation is reference only, very thin and total void of useful examples. You’re beginning to frustrate the masses.

Here is what’s happening.  My MySQL server crashes or stalls ever few months.  I searched the Goog and find PT-Stalk :

“watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to gather the data on demand without …

[Read more]
The Full Monty – Version 2

Installing MySQL on CentOS 6.2 with PaceMaker, MHA and more

When I work with High Availability software, I’m reminded of the maze in the original computer adventure game “You are Lost in a maze of twisty-turny passages all alike…”.

If you search the web for HA programs you will find many well maintained projects all related that refer each other. The goal of this document is to give you with a step by step guide to a production worthy MySQL system. It should provide at least 99.999% access to your data and be able to scale read requests as you grow.

I have chosen these programs and utilities because they are free (as in beer) and each has enterprise support available. (When you make the money to pay for it.) If you start with this MySQL platform you will avoid many common problems. Just write your application to read and write data from different servers.

Here is what we’ll be …

[Read more]
Replacing MySQL Full-text search with Sphinx

It’s very handy to have FT search out of the box, but there are several drawbacks attached. Problem is that MyISAM Full-text search is not designed to handle big amounts of text data. If you plan to index more than 1M documents you will probably need to take a look on the external search system like Lucene or Sphinx. For the usual LAMP-based service I personally would prefer to use Sphinx as it provides simple transition from MySQL FT and easy to integrate into any application (Sphinx could be queried via native APIs or via MySQL protocol).

Say we have table called <my_table> with `title` and `content` text fields. In MySQL you have to fire query like this:

SELECT * FROM <my_table> WHERE MATCH(`title`,`content`) AGAINST ('I love Sphinx');

Let’s see how could we do the same …

[Read more]
What can I DROP?

So you have inherited a MySQL server and no one know what applications are use what databases.   Space is every tight and every night transactions logs almost fill your hard disk.  The server was once used as a replication server and it seems every database the company uses is duplicated here.

You could do a full dump, drop everything and wait for the requests to restore. (This might be OK on a test database.)

We could ask MySQL for all the databases that have tables that have been updated in the last 30 days.

$ mysql information_schema -e  \
> Databases

But this only gives a list of databases that have changed.  What about tables that are only read from?

Bin-Logs don’t help because they too only have updates.  What we really need is a long snapshot of …

[Read more]
The Full Monty – Scientfic Linux 6.1, drbd, PaceMaker, MySQL, Tunsten Replication and more

PART 1 – This will be a multi part post.


After years of supporting MySQL, for many different companies, I’ve seen this story played out again and again.
The company:

  • chooses a Database Management System (MySQL)
  • installs the DBMS on a computer with other processes
  • writes many programs to access the data (Without concern on how the queries are written.)
  • moves DBMS to a computer of its own and writes more programs
  • buy bigger computer to run the DBMS and writes more programs
  • tires of DBMS response times and outages caused by developers working on production systems and hires a Database Administrator to fix the mess

This is a step by step description of  how I build a highly available, production MySQL servers. Like most things it life, these problems …

[Read more]
Showing entries 1 to 10 of 25
10 Older Entries »