I recently had the chance to witness the effects of innodb_adaptive_flushing on the performance of InnoDB Plugin 1.0.5 in the wild, which Yasufumi wrote about previously here and here.

The server in question was Solaris 10 with 8 disk RAID10 and 2 32GB SSDs used for ZIL and L2ARC, 72G RAM and 40G buffer pool. We started it up with innodb_adaptive_flushing=OFF and innodb_doublewrite=OFF, then ramped up traffic and everything looked stable ... but I noticed one troubling thing: ~2GB of uncheckpointed data.

mysql> SHOW INNODB STATUS\G
....
Database pages      2318457
Old database pages  855816
Modified db pages   457902
Log flushed up to   10026890404067
Last checkpoint at

I wrote a post a while back that said why you don't want to shard.  In that post that I tried to explain that hardware advances such as 128G of RAM being so cheap is changing the point at which you need to shard, and that the (often omitted) operational issues created by sharding can be painful.

What I didn't mention was that if you've established that you will need to eventually shard, is it better to just get it out of the way early?  My answer is almost always no. That is to say I disagree with a statement I've been hearing recently; "shard early, shard often".  Here's why:

• There's an order of magnitude better performance that can be gained by focusing on query/index/schema optimization.  The gains from sharding are usually much

This is part 3 of my Tyrant extra's, part 1 focused on durability, part 2 focused on the perceived performance wall.

#3.  Tokyo Cabinet Can have only a single writer thread, bottlenecking performance

When writing an application using Tokyo Cabinet only one connection can be opened as a “writer”  while the rest are readers.  Tyrant allows for multiple “writes”  to be sent in from multiple applications but it still single threads them when writing out to disk.   If you run several threads all just inserting into Tyrant your will see tyrant hit 100% Cpu on 1 core, and your writes will start to peter out quickly.

In my tests when I was not disk bound (FS Cache writes) I was able to complete 4Million

Continuing my look at Tokyo Tyrant/Cabinet and addressing some of the concerns I have seen people have brought up this is post #2.

#2.  As your data grows does  Tokyo Cabinet slow down?

Yes your performance can degrade. One obvious performance decrease with a larger dataset  is you start to increase the likelihood that your data no longer fits into memory.  This decreases the number of memory operations and trades them for more expensive disk based operations.    As fast as any application is, as you read off disk opposed to memory performance is going to drop off substantially.  One of the more difficult things to test with Tyrant is disk bound performance.  The FS Cache can make Tyrant seem like small amounts of memory will still make it scream.  Once your data set is larger then that, people start to claim they hit the

This is part 3 of our series.  In part 1 we talked about boosting performance with memcached on top of MySQL, in Part 2 we talked about running 100% outside the data with memcached, and now in Part 3 we are going to look at a possible solution to free you from the database.  The solution I am going to discuss here is Tokyo Cabinet and Tyrant.

I am not going to give you a primer  or Tutorial on Tyrant and Cabinet, there are plenty of these out there already.  Instead I want to see what sort of performance we can see compared to MySQL and Memcached, and later on other NoSQL solutions.  Tokyo actually allows you to use several types of databases that are supported,

I've seen a few people link to an INFORMATION_SCHEMA query to be able to find any indexes that have low cardinality, in an effort to find out what indexes should be removed.  This method is flawed - here's the first reason why:

Part 1 of our series set-up our "test"  application and looked at boosting performance of the application by buffer MySQL with memcached.  Our test application is simple and requires only 3 basic operations per transaction 2 reads and 1 write.  Using memcached combined with MySQL we ended up nearly getting a 10X performance boost from the application.  Now we are going to look at what we could achieve if we did not have to write to the database at all.  So let's look at what happens if we push everything including writes into memcached.

Wow that's shockingly fast isn't it! I guess being completely in memory helps for this app.  What is

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

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

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

<

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

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:

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

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

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

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

Let me start with example to make it clear. Lets say you're doing some reports from your apache log files - how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility - you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not

Forget tedious hours of searching links to the renamed object and replacing them.

To save your efforts, dbForge Studio for MySQL automates renaming of tables, columns of tables, views, aliases, stored routines, local variables, triggers, events, UDFs, and users and even offers a convenient preview of the changes before applying them.

To rename an object in the script, right-click it and select the Rename option from the menu. The Rename dialog box opens where you can enter a new name and view all references to the renamed object in the expressions, strings, and even comments.

All you have to do is to select required references and press

When I'm looking at a server, I often want to see the /etc/my.cnf file nicely formatted, and with comments stripped. This Perl one-liner will pretty-print the file:

Playing with TokuDB updates I noticed in SHOW PROCESSLIST unsual for MySQL State.

I wanted to share a little rule of thumb I sometimes use to decide which columns should come first in an index. This is not specific to MySQL, it's generally applicable to any database server with b-tree indexes. And there are a bunch of subtleties, but I will also ignore those for the sake of simplicity.

Let's start with this query, which returns zero rows but does a full table scan. EXPLAIN says there are no possible_keys.

Don't try to figure out the meaning of the query, because that'll

This is mainly a cheat sheet for me to remember. Nothing rocket science.

It often makes sense to use netcat/nc to copy MySQL database between hosts in trusted networks. It bypasses encryption overhead of SSH and depending on configuration can be significantly faster.

Also note MySQL should be down when you copy data unless you're copying from snapshot etc.

So to copy go to the mysql data directory on both boxes; such as cd /var/lib/mysql . Make sure target directory is empty. Now on the TARGET server do nc -l 4000 | tar xvf - and on the SOURCE server do tar -cf - . | nc target_ip 4000

Also note - the port you're using should be open in the firewall.

Entry posted by peter |

If you often need to create and modify views in your MySQL development you will like dbForge Studio for MySQL in-place query editing feature. This feature allows integration of powerful Query Builder tool with view editor without annoying copy/paste.

Suppose you have previously created view with some SELECT statement. To view and design this statement with Query Builder you need to perform these steps:

If you need to design new SELECT statement when you are creating view use