In 2011, I’ve launched the “The 3-Minute Test: What do you know about SQL performance.” It consists of five questions that follow a simple pattern: each question shows a query/index pair and asks if it demonstrates proper indexing or not. Till today, this test has become one of the most popular features on Use The Index, Luke and has been completed more than 28,000 times.

Note

Just in case you got curious, please be aware that this article is a spoiler. You might want to do the quiz yourself before continuing.

Although the quiz was created for educational purposes, I was wondering if I could get some interesting figures out of these 28,000 results. And I think I could. However, there are several things to …

Lets start by considering a scenario where records are being inserted in a single auto-increment table via different nodes of a multi-master cluster. One issue that might arise is ‘collision’ of generated auto-increment values on different nodes, which is precisely the subject of this article.

As the cluster is multi-master, it allows writes on all master nodes. As a result of which a table might get same auto-incremented values on different nodes on INSERTs. This issue is discovered only after the writeset is replicated and that’s a problem!

Galera cluster suffers with the similar problem.

Lets try to emulate this on a 2-node Galera cluster :

1) On node #1:

MariaDB [test]> CREATE TABLE t1(c1 INT AUTO_INCREMENT PRIMARY KEY, c2 INT)ENGINE=InnoDB;
Query OK, 0 rows affected (0.07 sec)

MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO t1(c2) VALUES (1);
Query …

The Percona Cloud Tools beta service continues to grow its query analysis capabilities with new features for reviewing queries. These enhancements will:

• Provide better visibility into the query performance of your next code deployment
• Introduce query organization tools for efficiency
• Foster better collaboration in teams

Join the hundreds of other users who are using Percona’s MySQL expertise and Percona Cloud Tools to improve the performance of their applications. Of course, it’s free during the beta.

What’s newQuery Review

Query Review gives you the right resources to efficiently review your application’s most important database activities. New tools enable you to assess, categorize, and comment on each of your application’s queries.

…

Migrating from MySQL to MariaDB is generally a straightforward procedure: you can shut down MySQL, install MariaDB, and start it up with a very good chance of success. When upgrading/crossgrading/migrating from one release of MySQL or MariaDB to another, it is important to run mysql_upgrade. The same is true when migrating from MySQL to MariaDB, in part because MariaDB has some different definitions for certain privilege tables. This means that the structures of the MySQL tables are not valid for MariaDB, a problem easily fixed by mysql_upgrade, unless you are trying to import an SQL dump from MySQL that creates tables using the old-style structures and tries to interact with them before it is possible to run mysql_upgrade. Let's take a look at why this is a problem and how to solve it.

…

I've seen some confusion about the difference between user-defined functions (UDFs) and stored functions in MySQL. In my last post I talked about UDFs, so I figured now is a good time to write about the difference between the two types of functions you can create in MySQL. Both are created using CREATE FUNCTION, and they share the same namespace, but they are implemented completely differently.

Stored functions

Stored functions are simply stored routines that return a value. Stored functions are easy to create if you know SQL, and they can be invoked just like built-in functions, but they are much slower than built-in …

Zyme, based in Redwood Shores, California, is the global leading provider of Channel Data Management (CDM) solutions to companies selling through indirect channels. For high tech and consumer electronic products alone, over $1 trillion USD worth of goods are flowing through those indirect sales channels every year. However, when companies sell products through multi-tier channel partners and retailers around the world, it has proven to be challenging in acquiring global, standardized channel inventory and sales data cost-effectively. As a result, companies lacking of such critical information often miss the opportunities to make timely and accurate business decisions either to increase revenue, reduce costs or to prevent losses.

Having a vision to solve such channel visibility problems for customers including Symantec, Logitech, Seagate and Xerox, Zyme built its channel data …

Today’s blog post starts with a question:

“In my continuous integration environment, running tests currently takes 30 minutes. I
believe that a large percentage of time is spent in MySQL, and I want to know if putting MySQL
on tmpfs will cut that time down?”.

I have previously written about how to configure MySQL to be less durable in testing environments, but today I wanted to write about how we can really test the assumption that the question is based around. That is to say:

• We know that total time is 30 minutes.
• We assume MySQL is a large percentage of this.
• We know that this optimization will potentially allow us to avoid IO.
• We then want to know what amount of time MySQL spends waiting on IO during our test suite?

And the answer to …

Greetings from Tokyo where MariaDB's Chief Evangelist, Colin Charles, is spreading the good news at DeNA's offices in downtown Tokyo.

Colin speaking on Wikipedia's performance improvements

A fully packed event

The view from the DeNA offices

read more

Today’s post features an interview with Morgan Tocker, MySQL community manager at Oracle. Morgan is an old friend of Percona, having worked here as director of MySQL training from 2009 to 2011. He’s also done stints at MySQL, Sun Microsystems and InPowered. You can follow his adventures at his blog, “Master MySQL.”  You can also connect with him face-to-face at the Percona Live MySQL Conference and Expo this April 1-4 in Santa Clara, Calif.  Use the promotional code “SeeMeSpeak” on the registration page and save 10 percent.

…

February 18, 2014 By Severalnines

Virtual Machines are great, and very useful when trying out new software. However, they might be an unnecessarily heavyweight solution when testing clusters, especially if these consist of multiple nodes running exactly the same software. Each VM runs a full-blown OS image. On the other hand, Linux Containers (LXC) are an efficient alternative to OS-level virtualization and can run multiple isolated systems on a single host. Docker is a wrapper around LXC, it automates the deployment of applications inside containers. 

A notable advantage if you run with Docker + LXC is you can run many containers on a single host. They all share the same OS as the host, and when possible, the same binaries. Deployment can be extremely fast. Using Docker could be a good method if you want to spin multiple Galera nodes within a single host. 

In this post, we will create MySQL Galera Cluster …