The latest from the MySQL community

I worked on an issue last recently where a query was too slow when executed in MySQL Cluster. The issue was that Cluster has some restrictions when it comes to push down conditions.

As an example of this, consider the following query using the employees sample database. The query takes a look at the average salary based on how many years the employee has been with the company. As the latest hire date in the database is in January 2000, the query uses 1 February 2000 as the reference date.

Initially the query performs like (performance is with two data nodes and all nodes in the same virtual machine on a laptop, so the timings are not necessarily representative of a production system, though the improvements should be repeatable):

mysql> SELECT FLOOR(DATEDIFF('2000-02-01', hire_date)/365) AS LengthOfService,
              COUNT(DISTINCT employees.emp_no) AS NoEmployees, AVG(salary) AS AvgSalary
         FROM salaries …

This post discusses how XtraDB Cluster and Ceph are a good match, and how their combination allows for faster SST and a smaller disk footprint.

My last post was an introduction to Red Hat’s Ceph. As interesting and useful as it was, it wasn’t a practical example. Like most of the readers, I learn about and see the possibilities of technologies by burning my fingers on them. This post dives into a real and novel Ceph use case: handling of the Percona XtraDB Cluster SST operation using Ceph snapshots.

If you are familiar with Percona XtraDB Cluster, you know that a full state snapshot transfer (SST) is required to provision a new cluster node. Similarly, SST can also be triggered when a cluster node happens to have a corrupted …

Founded in St. Paul, Minnesota, API Outsourcing is atechnology company and provider of Centreviews Business Intelligence Suite, asimple, scalable, and secure solution for back office departments andprofessionals to automate their accounts payable, accounts receivable anddocument management processes. Manyenterprises adopt finance and accounting technologies not only to driveefficiency, improve business performance, and reduce operating costs, but evenmore importantly, to allow their resources to focus on core competencies inproducts and service offerings.

Centreviews Business Intelligence Suite is available tocustomers or business partners on a SaaS only model or as a BPaaS model whereAPI provides outsourced labor to process accounts payable, accounts receivable,document management on its Centreviews Business Intelligence Suite. Both theSaaS and BPaaS offering enables customers to …

In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.

First, I want to thank everybody for attending the July 14 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: At which isolation level do 

pt-online-schema-change

 and 

pt-archive

  copy data from a table?

A: Both tools do not change the server’s default transaction isolation level. Use either

REPEATABLE READ

 or set …

In the previous blog post we explained how work with the collection CRUD operations. In this blog post we are going to explain other functions that are related to document management.

We already know how to create collections, as well as how to add, delete, update and retrieve documents from them. But, how can we add a new field to a document or documents that are in a collection?

The following code demonstrates how to do it:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var query = "$.name == 'NewField'";
  var newDoc = { name: 'NewField', description: 'a new field', 
                 extra: ['hello', 'world'] …

Today we are announcing the open source release of gh-ost: GitHub’s triggerless online schema migration tool for MySQL.

gh-ost has been developed at GitHub in recent months to answer a problem we faced with ongoing, continuous production changes requiring modifications to MySQL tables. gh-ost changes the existing online table migration paradigm by providing a low impact, controllable, auditable, operations friendly solution.

MySQL table migration is a well known problem, and has been addressed by online schema change tools since 2009. Growing, fast-paced products often require changes to database structure. Adding/changing/removing columns and indexes etc., are blocking operations with the default MySQL behavior. We conduct such schema changes multiple times per day and wish to minimize user facing impact.

Before illustrating …

Is it cheating? Is using MySQL without Structured Query Language (SQL) or putting all your data into one column proper? Impossible a year ago and probably thought as a poor/crazy practice until recently, this is a new type of MySQL usage. NoSQL has had a big impact in the SQL world with several relational products from vendors like MySQL, Microsoft, Postgresql and others offering NoSQL interfaces and JSON data types to their databases.

Several old timers have come to me asking if putting lots of data in a JSON column is cheating? After all data normalization is part of relational databases and the way to efficiency and speed is well organized data. This higgledy–piggledy fashion of putting an entire document in a column without breaking it down to its component sections does violate the first rule of data normalization. And that has …

Decoding binary protocols in python

Decoding binary protocols like the MySQL Client/Server Protocol or MySQL's new X Protocol involves taking a sequence of bytes and turning them into integers.

In python the usually workhorse for this task is struct.unpack()

It takes a sequence of bytes and a format-string and returns a tuple of decoded values.

In the case of the MySQL Client/Server protocol the integers are (mostly) little-endian, unsigned and we can use:

In the previous blog post we explained how to create schemas and collections. In this one we are going to explain how to work with collections: adding, updating and deleting documents.

The following code demonstrates how to add a single document to an existing collection:

var mysqlx = require('mysqlx');
mysqlx.getSession({
  host: 'host',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'my pass'
}).then(function (session) {
  var schema = session.getSchema('mySchema');
  var coll = schema.getCollection('myColl');
  var newDoc = { name: 'Test Name', description: 'Test Description' };

  coll.add(newDoc).execute().then(function (added) {
    console.log('Document(s) added: '
                + added.getAffectedItemsCount());
    session.close();
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack); …

Most people know that you can use SHOW SLAVE STATUS to verify if a slave is running in MySQL. Most people also know that you need to check both Slave_IO_Running and Slave_SQL_Running.

View the code on Gist.

A few years ago, I responded to a question on dba.stackexchange.com on how to verify if a slave is running outside of SHOW SLAVE STATUS.

Prior to MySQL 5.7, you could get this information from SHOW GLOBAL STATUS:

View the code on Gist.

Keep in mind that certain versions of 5.1 …