In this blog post, I’ll discuss MySQL sharding models, and how they apply to SaaS application environments.

MySQL is one of the most popular database technologies used to build many modern SaaS applications, ranging from simple productivity tools to business-critical applications for the financial and healthcare industries.

Pretty much any large scale SaaS application powered by MySQL uses sharding to scale. In this blog post, we will discuss sharding choices as they apply to these kinds of applications.

In MySQL, unlike in some more modern technologies such as MongoDB, there is no standard sharding implementation that the vast majority of applications …

SQL Injection is a highly feared and often misunderstood problem. The basic phobia is that someone hijacks your SQL request and suddenly has full access to everything in your database. Well, it usually is not that easy and it is actually easy to avoid. Rule 1: Never Trust User SuppliedThe usual example is something like a query SELECT * FROM customer_data WHERE customer_id='$id' and the programmer was expecting an integer for the customer_id. But a dastardly use inserts some horrible SQL code to pirate the information so the query looks like SELECT * FROM customer_data WHERE customer_id=1 OR customer_id > 0 and suddenly all your customer data is out free in the universe waiting for who knows what.

The code could have checked to see if the value of customer_id was truly an integer or returning an error if not. The is_int function was designed to do just this.

if is_int($customer_id)  {
 …

This post follows a previous one, Evolving MySQL Compression.

Pinterest’s main data source–Pin data–is stored as medium-sized (~1.2kb) JSON blobs in our MySQL cluster. These blobs are very compressible, but the existing compression system in MySQL was less than optimal and only resulted in 2:1 compression. In a previous post, we discussed why column compression is a more ideal compression system. In order to use column compression and have significant savings, we need to use a compression and optional predefined compression dictionary (i.e. lookback window). Here we’ll cover how we increased the compression ratio of Pin data from around 3:1 to 3.47:1.  

Background

First, let’s take a look at …

Last month, MariaDB officially released MariaDB ColumnStore, their column store engine for MySQL. This post discusses what it is (and isn’t), why it matters and how you can approach a test of it.

What is ColumnStore?

ColumnStore is a storage engine that turns traditional MySQL storage concepts on their head. Instead of storing the data by row, a column store stores the data by column (obviously). This provides advantages for certain types of data, and certain types of queries run against that data. See my previous post for more details on column-based storage systems.

ColumnStore is a fork of InfiniDB and carries forward many of the concepts behind that product. InfiniDB ceased operations in 2014. With the front end managed through MariaDB, you get access to …

MySQL replication setups are inevitably related to failovers. Unlike multi-master clusters like Galera, there is one single writer in a whole setup - the master. If the master fails, one of the slaves will have to take its role through the process of failover. Such process is tricky and potentially, it may cause data loss. It may happen, for example, if a slave is not up to date while it is promoted. The master may also die before it is able to transfer all binlog events to at least one of its slaves.

Different people have different takes on how to perform failover. It depends on personal preferences but also on requirements of the business. There are two main options - automated failover or manual failover.

Automated failover comes in very handy if you want your environment to run 24x7, and to recover quickly from any failures. Unfortunately, this may come at a cost - in more complex failure scenarios, automated failover may not …

It's not always obvious what makes one database type distinct from another. What are the most significant ways that Amazon Aurora is different from MySQL? Clear separators aren't always featured or widely known, but even slight variables between two databases can prove valuable in choosing which one is right for you and your organization.

In the case of Aurora, there are at least three interesting things that make it unique and that present opportunities for particular uses. (Thanks in advance to @saileshkrish for helping us stay in-the-know on what Aurora can do.)

Adaptive Thread Pool

wow pic.twitter.com/KeVY5VBEMS

— Preetam (@PreetamJinka) December 21, 2016

Aurora's thread pool …

In today’s blog, I will show an issue with seconds_behind_master that one of our clients faced when running slave_parallel_works > 0. We found out that the reported seconds_behind_master from SHOW SLAVE STATUS was lying. To be more specific, I’m talking about bugs #84415 and #1654091.

The Issue

MySQL will not report the correct slave lag if you have slave_parallel_workers> 0. Let’s show it in practice.

I’ll use MySQL Sandbox to speed up one master and two slaves on MySQL version …

When we do Galera Cluster consulting we always discuss with the customer if his software is Galera Cluster ready. This basically means: Can the software cope with the Galera Cluster specifics?

If it is a software product developed outside of the company we recommend to ask the software vendor if the software supports Galera Cluster or not.

We typically see 3 different answers:

• We do not know. Then they are at least honest.
• Yes we do support Galera Cluster. Then they hopefully know what they are talking about but you cannot be sure and should test carefully.
• No we do not. Then they most probably know what they are talking about.

If the software is developed in-house it becomes a bit more tricky because the responsibility for this statement has to be taken by …

Welcome to another post in the series of Percona Live featured tutorial speakers blogs! In these blogs, we’ll highlight some of the tutorial speakers that will be at this year’s Percona Live conference. We’ll also discuss how these tutorials can help you improve your database environment. Make sure to read to the end to get a special Percona Live 2017 registration bonus!

In this Percona Live featured tutorial, we’ll meet Frédéric Descamps, MySQL Community Manager at Oracle. Frédéric is probably better known in the community as “LeFred” (Twitter: @lefred)! His tutorial is …

In this blog post, we’ll look at Performance Schema benchmarks for OLTP Read/Write workloads.

I am in love with Performance Schema and talk a lot about it. Performance Schema is a revolutionary MySQL troubleshooting instrument, but earlier versions had performance issues. Many of these issues are fixed now, and the default options work quickly and …