In a previous post, titled “Multi-threaded replication with MySQL 5.6: Use GTIDs,” I explained that using GTID replication is almost a requirement when using MySQL 5.6 MTS. Let’s see now how to perform the day-to-day operations when MTS and GTIDs are both enabled. (I’ll also be presenting a related webinar next week titled “Multi-threaded Replication in MySQL 5.6 and 5.7″).

Seeing the execution gaps

If you have a look at SHOW SLAVE STATUS while the slave is running, you may not be expecting such an output:

[...]
Executed_Gtid_Set: …

Carrying on with my MySQL 5.7 Labs Multi Source Replication scenario, I wanted to evaluate performance impact via MySQL Enterprise Monitor.

Whilst I opened my environment, I remember that I had generated lots of different skeleton scripts that allowed me to deploy the 50 servers quickly, and I didn’t want to add each of my targets 1 by 1 in MEM. So, I used one of the many features available, “Add Bulk MySQL Instances”.

So, I’ve got 50 (3001-3050) masters but only 1 slave (3100).

By default, MEM monitors it’s own repository, i.e. the 1/1 server being monitored in the All group.

I want to add my slave in first, because that’s how I’m organizing things, and I’ll take the opportunity to create the monitoring group I want to …

As a MySQL DBA I already know the data changes that happen on my system. I have logs for that.

However, it’s a common problem that several years into the life of an application, the current developers won’t know where in the codebase queries come from. It’s often hard for them to find the location in the code if queries are formed dynamically; the pattern I show them to optimize doesn’t match anything in the code.

I stumbled on a trick a couple years ago that has been invaluable in tracking down these problematic queries: query comments.

Here’s an example:

When a query generally shows up in a slow query log, it might look something like this:

# Time: 150217 10:26:01
# User@Host: comments[comments] @ localhost []  Id:    13
# Query_time: 0.000231  Lock_time: 0.000108 Rows_sent: 3  Rows_examined: 3
SET timestamp=1424186761;
select * from cars;

That logging shows me who executed the query …

There were so many valuable articles already written by others over past years explaining all details about InnoDB transaction isolation modes and how to deal with this. So, I'll avoid to repeat what was already said ;-) -- my attention attracted the performance study made by PeterZ and published in the following article: http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/ -- the article is very good and providing a good analyze of the observed problem which is solved by using READ-COMMITTED transaction isolation instead of REPEATABLE-READ (which is default in InnoDB).. The natural question is coming then: why don't we have then the READ-COMMITTED mode by default?.. Is there any danger?..

Let's then investigate together..

First of all, you should …

MySQL meets NoSQL with JSON UDF

I recently got back from FOSDEM, in Brussels, Belgium. While I was there I got to see a great talk by Sveta Smirnova, about her MySQL 5.7 Labs release JSON UDF functions. It is important to note that while the UDF come in a 5.7 release it is absolutely possible to compile and use the UDF with earlier versions of MySQL because the UDF interface has not changed for a long time. However, the UDF should still be considered alpha/preview level of quality and should not be used in production yet! For this example I am using Percona Server 5.6 with the UDF.

That being said, the proof-of-concept that I’m about to present here uses only one JSON function (JSON_EXTRACT) and it has worked well enough in my testing to present my idea here. The JSON functions will probably be GA sometime soon anyway, and this is a useful test of the JSON_EXTRACT function. …

Within the MySQL data directory, the InnoDB storage engine creates two types of files — the data files and the redo log files. Each data file (or ibd file) belongs to exactly one tablespace. Each tablespace is given a unique identifier called the space_id. One tablespace can have 1 or more data files. If a tablespace has more than one data file, then the data files have a specific order or sequence. The data files can be thought of as being concatenated to each other in that specific order.

The data file is made up of a series of equal sized pages. Each page in the data file is given a unique number identifier called the page number (page_no). The first page of the first ibd file is given the page_no of 0. The page number of the first page of the second ibd file of the tablespace is …

MySQL’s default storage engine as of version 5.5 is InnoDB. InnoDB maintains a storage area called the buffer pool for caching data and indexes in memory. By keeping the frequently-accessed data in memory, related searches are retrieved much faster than reading from disk.

When you stop or restart MySQL, you lose the cached data stored in the buffer pool. There is a feature in MySQL 5.6 which allows you to dump the contents of the buffer pool before you shutdown the mysqld process. Then, when you start mysqld again, you can reload the contents of the buffer pool back into memory. You may also …

Say you have a cluster with 3 nodes using Percona XtraDB Cluster (PXC) 5.6 and one asynchronous replica connected to node1. If asynchronous replication is using GTIDs, moving the replica so that it is connected to node2 is trivial, right? Actually replication can easily break for reasons that may not be obvious at first sight.

Summary

Let’s assume we have the following setup with 3 PXC nodes and one asynchronous replica:

Regarding MySQL GTIDs, a Galera cluster behaves like a distributed master: transactions coming from any node will use the same auto-generated uuid. This auto-generated uuid is related to the Galera uuid, it’s neither ABC, nor DEF, nor GHI.

Transactions executed for …

So, Anton got some useful code working that I could patch into a MySQL server for testing purposes – a POWER8 optimized CRC32 implementation.

I went with a pretty stock MySQL 5.6.22 (one patch) with sysbench preparing a single 2GB table (10,000,000 rows). I then hacked up innochecksum so that it would only do the correct CRC32 (rather than trying each checksum type). Using the standard CRC32 algorithm it took around three seconds to verify all of the checksums. With a POWER8 optimized CRC32: 0.4-0.5 seconds. Useful speed-up!

I then ran sysbench read/write with 16 threads with oltp-table-size=10000 (on the larger table) to see if there would be an improvement in a “real world” workload. I got about 30% better performance on read/write operations!

Using perf to see where CPU was going, CPU time spent doing CRC32 calculations went down from …

Join 29,000 others and follow Sean Hull on twitter @hullsean. Can RDS worsen an outage ?? That’s another way to think about this question. In my experience, it very clearly increases outages, by tying one or both hands behind your back. Believe me when I say, that is terribly frustrating when you’re putting out fires! […]