This Log Buffer Edition spread love of databases just before Valentine’s Day. Lovely blog posts from Oracle, SQL Server and MySQL are here for you to love.

Oracle:

Creating a Mobile-Optimized REST API Using Oracle Service Bus by Steven Davelaar.

GROUP BY – wrong results in 12.1.0.2

Using Edition-Based Redefinition to Bypass Those Pesky Triggers

It’s easy to make mistakes, or overlook defects, when constructing parallel queries – especially …

One of the interesting challenges in writing a system like VividCortex is writing a query digesting algorithm that deals with all of the technology-specific features, bugs, and quirks. When we added PostgreSQL performance monitoring recently, I knew this would be a new challenge because Postgres uses ANSI standard syntax for quoted strings, but MySQL uses different syntax. After checking a bit further, I also found something I didn’t know about: PostgreSQL also supports a nonstandard quoting syntax, which is called “dollar quoting.”

There are other interesting considerations about query digesting, so I thought it was worth a blog post.

Digesting queries is a huge issue. Because VividCortex digests similar SQL statements together, identifying literals accurately is important. If we think something is a literal and it’s an object name, we’ll group …

Up until a short while ago I was still using local.xml for modifications to a project-specific theme, even though 1.9 introduced the theme.xml file. The scenario I encountered which forced me to ditch local.xml is pretty interesting and made me understand the need for the theme.xml layout updates.

Basically, I was working on a website that now wanted to expand in a different country. The new store looked similar, but would have slightly different templates. Usually - in a similar situation - I would use a different theme under the same package (to make use of the “natural” theme fallback to “default”).

I couldn't use a different theme in this case because I was already using themes for certain pages that looked slightly different as a way to easily override template files without having to write any XML. (I was changing the theme in an observer event for specific pages.)

Then I tried using a new package. But that …

Just under a year ago on my old blog I discussed and even demoed the new Linux live kernel patching solutions. I was reviewing these technologies out of my own curiosity as well as HP's Advanced Technology Group having an interest. I think these technologies are great, I am personally more of a fan of the user experience of RedHat's kpatch solution but any solution is a great technical achievement.

Having said this I believe that the use case for this technology is quite narrow. Last time I looked into these technologies only patches that affected the code of functions could be modified. Changing structs and data definitely didn't work and I suspect that changing function declarations was also dangerous. There is also a performance …

[Here is a PDF version for readers whose browser does not understand MathML.]

The Simplex Noise (or Improved Perlin Noise) algorithm uses a somewhat mysterious "skew factor" of 3-12. I did not find any really satisfactory explanation for this factor in the descriptions of Simplex Noise that I read. But I managed to work it out nevertheless, which I thought was a fun exercise and worth a quick write-up.

Simplex noise is constructed by assigning random values to each point in a simplex grid. The simplex grid is a tiling of the plane using rhombuses, each rhombus consisting of two equilateral triangles. See the figure on the right.

Given a point (x,y) (expressed in normal rectangular coordinates), we …

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! […]

Over the last few MySQL releases the size of the MySQL package have increased in size and it looks like the trend is continuing.

• 687M MySQL 5.5.42
• 1,1G  MySQL 5.6.21
• 1,3G  MySQL 5.7.4
• 1,5G         MySQL 5.7.5

This guide is for a Linux installation. I have tested the instructions below to create a minimal installation on my Ubuntu 14.04 laptop. I know you can make it a bit smaller by running mysqld directly and only using one error message file but this would not affect total size much. I also added the mysql client to have some way of logging into MySQL.
MySQL configuration file used to start MySQL: