Mydumper 0.2.2 has been released today with a number of fixes and new features.  The one that most people have been asking for is consistent snapshots for non-InnoDB tables (such as MyISAM).  We have been able to achieve this without locking the database for the entire backup using the following method:

1. Flush tables with read lock (and start transaction with consistent snapshot on all threads)
2. Dump non-InnoDB
3. Start InnoDB dump
4. When non-InnoDB dump has finished (whilst InnoDB is dumping) unlock tables
5. …
6. Profit

I have also started work on Drizzle support this week.  The Drizzle support is not entirely complete mostly down to Drizzle’s handling of SHOW TABLE STATUS and …

I have been getting a lot of questions about performance tuning this month. So this will be the first in a series on the basics.

Go get your copies of High Performance MySQL: Optimization, Backups, Replication, and More and MySQL Administrator's Bible1 and read up on benchmarking. Also take a peek at George Trujillo's MOCA. These readings will provide you with a overview of the methods and approaches you will see in later entries. The goal is to get a baseline measurement so that a change can be evaluated …

It seems that everyone loves load testing these days. Problem is that everyone is using their own quick scripts, simple or complex, to drive their tests without the ability for other DBAs to duplicate those tests. Let’s say I write a great test and share my results and graphs on the blog – you want to run the same tests to see how your new DB servers compare in performance: this framework allows you to do that without duplicating any work or writing code. This is a basic release that will get the ball rolling. I’ve included some sample tests in the README file, so give them a try.

This codebase offers a user friendly framework for creating and visualizing MySQL database load test jobs. It is based around Sysbench, which is generally considered the industry standard load test application. The framework allows you to do the following:

• standardize your tests without requiring you to write one-off bash scripts to handle …

Two years ago I published a post entitled “things I wrote down during OSBC”. It tuned out to be surprisingly popular, so I decided to try the same formula again.

Just as was the case two years ago these are presented chronologically. I wrote down a lot more than this, to be clear, but these were the most quote-worthy things. Also, as in 2009, some of them confirm things we already think about commercial open source (or in some cases data management), others were interesting ways of expressing old ideas:

“The next generation of computing is being led by users, rather than vendors.”
Jim Whitehurst

“The value of open source has moved from commoditization to innovation.”
…

There are multiple metrics that are really useful for read workload analysis, that should all be tracked and looked at in performance-critical environments.

The most commonly used is of course Questions (or ‘Queries’, ‘COM_Select’) – this is probably primary finger-pointing metric that can be used in communication with different departments (“why did your qps go up by 30%?”) – it doesn’t always reveal actual cost, it can be increase of actual request rates, it can be new feature, it can be fat fingers error somewhere in the code or improperly handled cache failure.

Another important to note is Connections – MySQL’s costly bottleneck. Though most of users won’t be approaching ~10k/s area – at that point connection pooling starts actually making sense – it is worth to check for other reasons, such as “maybe we connect when we shouldn’t”, or needlessly reconnect, or …

Want to win the title of Developer, DBA or IT Manager of the Year?

Can you demonstrate outstanding achievements using MySQL and/or other Oracle technologies?

Then submit a nomination now!

Formerly Oracle Magazine Editors’ Choice Awards and now part of Oracle’s prestigious Oracle Excellence Awards program, these awards honor Oracle technologists for their cutting-edge solutions using Oracle products and services. Winners are selected based on a compelling story of technology leadership and innovation that differentiates them from others in their roles. The awards will be presented during Oracle OpenWorld 2011 (October 2–6) in San Francisco.

The 10 winners will be selected across 10 award categories.

Read more and submit nominations …

http://Flexvie.ws fully implements a method for creating materialized views for MySQL data sets. The tool is for MySQL, but the methods are database agnostic. A materialized view is an analogue of software transactional memory. You can think of this as database transactional memory, or as database state distributed over time, but in an easy way to manage.

It has been shown that combinatorial algebraics can be applied to all aggregate functions (Flexviews uses only composable operations even for deletes over “non-distributable aggregate functions”), and Flexviews includes this capability today. Because Flexviews can move database …

Since it is possible to turn any “infinite” list into a finite list using weighted sets (there is no such thing as an infinite list unless you can provide me with infinite data), it is no longer possible to consider infinite lists of input into computer programs. A program without an infinite list of instructions will halt unless there is an infinite loop. Improper infinite loops can be detected within software programs using multiple testing methods. With proper error checking and therefore with proper checks, halting state prediction should be possible. If improper state is detected in an application, rollback the data state in the database to prevent corruption.

An “indeterminate” input can be expressed as a NULL in the input. It is not possible to produce or receive an infinite input. All input is read in small chunks, or all the memory in the universe would be needed, which would itself represent universal state.

Here is the problem: http://en.wikipedia.org/wiki/Partition_problem

Any weighted set can be partitioned. A weighted set can be represented in a sparse way as a space and time optimization. Also duplicates are removed, making it easy to at least partially partition any problem by hash.

For example, you can distribute the subset sum problem over multiple machines when you reduce the set to unique values and hash on the value (weighted set). This allows you to distribute checks, reducing complexity.

You can partition the problem into even and odd items, the absolute value of the items, etc, and add many simple checks which can be tested during insertion instead of after a huge list has been created inside the database. Using distributed computation of combinatorial algebra (sum) you can aggregate the data with your load, allowing you to answer questions on the data while it is loaded. The check will fire as soon as an …

This will just be a short reminder. If you're planning to create multiple-column indexes in MySQL, remember that the order of the columns matters. As the MySQL documentation points out, any "leftmost prefix of the index can be used." This means that the index can be used for any number of columns in a where clause as long as those columns are at the beginning of the index column list and in the same order.