The memcache protocol has an INCR command, which atomically increments a stored value. MySQL Cluster is able to take simple operations (like incrementing a counter) and "push them down" to run on a data node with a lock held. Acquire the lock, increment the value, read it, and release the lock.

Originally in memcached, the incrementing sequence number was local to one memcache server, and was also volatile. Whenever the memcached server restarted, the sequence would be reset. Today, though, we can put cluster and memcached together, and instead of a volatile local sequence number, we get a durable and shared one.

This is a very simple application. Keep reading to see the whole thing, start to finish. In my example I'll use MySQL Cluster 7.2.4 and the memcached ASCII protocol. There is a minor (but annoying) bug, already fixed in 7.2.6, that prevented this from working with the binary protocol. So, keep that in mind, and …

The memcache protocol has an INCR command, which atomically increments a stored value. MySQL Cluster is able to take simple operations (like incrementing a counter) and “push them down” to run on a data node with a lock held. Acquire the lock, increment the value, read it, and release the lock.

Originally in memcached, the incrementing sequence number was local to one memcache server, and was also volatile. Whenever the memcached server restarted, the sequence would be reset. Today, though, we can put cluster and memcached together, and instead of a volatile local sequence number, we get a durable and shared one.

This is a very simple application, and I’m going to walk through the whole thing. Keep reading to see the whole thing, start to finish. In my example I’ll use MySQL Cluster 7.2.4 and the memcached ASCII protocol. There is a minor (but annoying) bug, already fixed in 7.2.6, that prevented this from working with the …

A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

Usage is simple:
garfield ~ # ./extract.sh -f dump.sql -d redmine > redmine.sql
garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows > redmine-workflows.sql

You can grab the script from Downloads page.

extract.sh

#!/bin/bash

#
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
#

function help()
{
  cat <<EOF
  Usage: [-h] -f <dump file> -d <database …

Recent Tips and News on Java, Java EE 6, GlassFish & more :

Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. …

Topics for this podcast:

*OpenStack, Amazon, Eucalyptus and Citrix engage in open cloud warfare
*Microsoft spins off new company for openness
*Updates on automation players Puppet Labs and Opscode with Chef
*Percona turns attention to MySQL high availability
*Open APIs as the fifth pillar of modern IT openness

iTunes or direct download (28:42, 4.9MB)

We are running internally a lot of benchmarks on our recently announced Percona XtraDB Cluster, and I am going to publish these results soon.
But before that I wanted to mention that proper benchmark of distributed system comes with a lot of challenges.
I am saying that not to complain, but to make sure, if you are going to benchmark XtraDB Cluster yourself, there is a lot of things to take into account.

And it seems that one component, which was not much important before, now appears as critical peace, which easily can became bottleneck in the benchmarks – this is network.

In case of simple client-server setup, the network is not fully utilized.

But as …

A customer recently was asking why our MySQL Performance Monitor (MPM) is not working on Windows...? The answer is short: It was developed on Linux and never tested on Windows...

But I was wondering how much effort it would take to make it work on Windows as well.

I was quite surprised how fast it was to make the basic functionality working on Windows. It took me less than one hour to install, configure and patch MPM.

Patch MPM

The file FromDualMySQLagent.pm has to be patched at 2 locations. The lock file name must be something understandable by Windows (for example C:\Temp\FromDualMySQLagent.lock. We will fix that in the next MPM release.

 40   # Should NOT be hard coded, tofix later!!!
 41   # Does not work on Windows!
 42   my $lAgentLockFile = '/tmp/FromDualMySQLagent.lock';
 43   # Check if lock file already exists and complain if yes
...
533   # Does not work on Windows!
534 …

Challenges of Big Databases with MySQL

Many database management tasks become difficult as you move from millions of rows and gigabytes of data to billions of rows and terabytes of data. Such tasks include ingesting data while maintaining indexes; changing schemas without downtime; and supporting connections, replication, and backup. For some scaling problems (connections and replication), MySQL is better than most of the competition. For others, such as indexing, schema changes, and backup, MySQL has typically been harder to use. Fortunately, the tasks MySQL does well are in its core, whereas the tasks that are more difficult can be solved with storage engine plug-ins.

This presentation discusses how MySQL’s storage engines have recently made dramatic progress in large …

This week we talk with Mark Atwood of RedHat about OpenShift, a free, auto-scaling Platform as a Service (PaaS) for applications. Use promotional code "oursql" when signing up!

News/Events/Feedback
We are very thankful for our listeners! Last week we won a MySQL Community Contributor of the Year award (Sarah Novotny, Gerry Narvaja and Sheeri Cabral, who have all been hosts, won the award). Here's a picture of the award we all won.

read more