All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to there problem. Why?  Because their other applications use it, so why not the new application?  Over the past couple of months I have been doing a ton of work for clients who use their database like most people use memcached .  Lookup a row based on a key, update the data in the row, stuff the row back in the database.  Rinse and repeat.  Sure these setups vary sometimes, throwing in a “lookup” via username, or even the rare count.  But for the most part they are designed to be simple.

A classic example is a simple online game.  An online game may only require that an application retrieve a single record from the database.  The record may contain all the vital stats for the game, be updated and stuffed back into the database.  You would be surprised how many people use …

The easiest way to see the differences between two schemas on a non-Windows machine is to run:

mysqldump -h server1 --no-data --all-databases > file1.sql
mysqldump -h server2 --no-data --all-databases > file2.sql
diff file1.sql file2.sql

However, this will show also trivial differences, such as the value of AUTO_INCREMENT. It also does not give you a way to patch one schema to be like another.

We frequently are asked to “do a schema diff and create a script that will ‘patch’ one server.” Usually this is done to take a development or test schema and move it to production for a release.

We like to use the best tool for the job, and while diff is good, I like to use MySQL workbench. The OSS (Community) edition provides all the functionality we need for creating a schema diff and …

We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization ... or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:

SHOW DATABASES;
+----------+
| Database |
+----------+
| db1      |
| db2      |
| db3      |
| db4      |
| mysql    |
+----------+

Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn't allow us to examine statistics on a per-database basis.

Enter Maatkit.

There is an often-ignored gem in Maatkit's mk-query-digest, and that is the --group-by argument. This can …

As you may know, InnoDB has 2 limits for unflushed modified blocks in the buffer pool. The one is from physical size of the buffer pool. And the another one is oldness of the block which is from the capacity of transaction log files.

In the case of heavy updating workload, the modified ages of the many blocks are clustered. And to reduce the maximum of the modified ages InnoDB needs to flush many of the blocks in a short time, if these are not flushed at all. Then the flushing storm affect the performance seriously.

We suggested the "adaptive_checkpoint" option of constant flushing to avoid such a flushing storm. And finally, the newest InnoDB Plugin 1.0.4 has the new similar option "adaptive_flushing" as native.

Let's check the adaptive flushing options at this post.

HOW THEY WORK

< adaptive_checkpoint=reflex (older method)>

I often see people confuse different ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes for query execution, which are not mutually exclusive, in fact some queries will use indexes for all 3 purposes listed here.

Using index to find rows The main purpose of the index is to find rows quickly - without scanning whole data set. This is most typical reason index gets added on the first place. Most popular index type in MySQL - BTREE can speed up equality and prefix range matches. So if you have index on (A,B) This index can be used to lookup rows for WHERE clauses like A=5 ; A BETWEEN 5 AND 10 ; A=5 AND B BETWEEN 5 AND 10 it however will NOT be able to help lookup rows for B BETWEEN 5 AND 10 predicate because it is not index prefix. …

Note: This post is part 2 of 4 on building our training workshop.

Last week I talked about why you don't want to shard. This week I'm following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1.  Building this list was not really a scientific process - It's always difficult to say which feature is better than another, because a lot of it depends on the individual workload.  My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:

1. CPU scalability fixes - XtraDB improves performance on systems with multi-cpus (see docs …

Note: This blog post is part 1 of 4 on building our training workshop.

The Percona training workshop will not cover sharding. If you follow our blog, you'll notice we don't talk much about the subject; in some cases it makes sense, but in many we've seen that it causes architectures to be prematurely complicated.

So let me state it: You don't want to shard.

Optimize everything else first, and then if performance still isn't good enough, it's time to take a very bitter medicine. The reason you need to shard basically comes down to one of …

The feature I announced some time ago http://www.mysqlperformanceblog.com/2009/06/08/impossible-possible-moving-innodb-tables-between-servers/ is now available in our latest releases of XtraBackup 0.8.1 and XtraDB-6.

Now I am going to show how to use it (the video will be also available on percona.tv).
Let's take tpcc schema and running standard MySQL ® 5.0.83, and assume we want to copy order_line table to different server. Note I am going to do it online, no needs to lock or shutdown server.

To export table you need XtraBackup, and you can …

In your recovery practice we often face the problem when data lost by execution DROP TABLE or DROP DATABASE statement. In this case even our InnoDB Data Recovery tool can't help, as table / directory with files was deleted (if you have innodb-file-per-table). And the same for MyISAM, all .MYD / .MYI / .frm - files are deleted in this case.

So first step after DROP is to restore files, and for ext3 file system there are two utilities which can help of you are fast (and lucky) enough.
First one is ext3grep http://code.google.com/p/ext3grep/, with some instruction on this page http://www.xs4all.nl/~carlo17/howto/undelete_ext3.html.
And also there is …

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …