Over the last year, I have been pursuing a part time hobby project exploring ways to squeeze as much data as possible in MySQL. As you will see, there are quite a few different ways. Of course things like compression ratio matters a lot but, other items like performance of inserts, selects and updates, along with the total amount of bytes written are also important. When you start combining all the possibilities, you end up with a large set of compression options and, of course, I am surely missing a ton. This project has been a great learning opportunity and I hope you’ll enjoy reading about my results. Given the volume of results, I’ll have to write a series of posts. This post is the first of the series. I also have to mention that some of my work overlaps work done by one of my colleague, Yura Sorokin, in a …[Read more]
Some time ago, a customer had a performance issue with an internal process. He was comparing, finding, and reporting the rows that were different between two tables. This is simple if you use a LEFT JOIN and an
comparison over the second table in the WHERE clause, but what if the column could be null? That is why he used UNION, GROUP BY and a HAVING clauses, which resulted in poor performance.
The challenge was to be able to compare each row using a LEFT JOIN over NULL values.
The challenge in more detail
I’m not going to use the customer’s real table. Instead, I will be comparing two sysbench tables with the same structure:
CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned DEFAULT NULL, `c` char(120) DEFAULT NULL, `pad` char(60) DEFAULT NULL, …[Read more]
A few months ago I wrote a blog post on How to Capture Per Process Metrics in PMM. Since that time, Nick Cabatoff has made a lot of improvements to Process Exporter and I’ve improved the Grafana Dashboard to match.[Read more]
In this blog post we shall discuss how you can analyze slow query logs from Amazon Aurora for MySQL, (referred to as Amazon Aurora in the remaining blog). The tools and techniques explained here apply to the other MySQL compatible services available under Amazon Aurora. However, we’ll focus specially on analyzing slow logs from Amazon Aurora version 2 (MySQL 5.7 compatible) using pt-query-digest. We believe there is a bug in Aurora where it logs really big numbers for query execution and lock times for otherwise really fast queries.
So, the main steps we need are:
- Enable slow query logging on your Amazon Aurora DB parameter group, apply the change when appropriate.
- Download the slow log(s) that …
One of the common ways to classify database workloads is whether it is “read intensive” or “write intensive”. In other words, whether the workload is dominated by reads or writes.
Why should you care? Because recognizing if the workload is read intensive or write intensive will impact your hardware choices, database configuration as well as what techniques you can apply for performance optimization and scalability.
This question looks trivial on the surface, but as you go deeper—complexity emerges. There are different “levels” of reads and writes for you to consider. You can also choose to look at event counts or at the time it takes to do operations. These can provide very different responses, especially as the cost difference between a single read and a single write can be an order of magnitude.
Let’s examine the TPC-C Benchmark from this point of view, or more specifically its …[Read more]
MySQL Resource Groups, introduced in MySQL 8, provide the ability to manipulate the assignment of running threads to specific resources, thereby allowing the DBA to manage application priorities. Essentially, you can assign a thread to a specific virtual CPU. In this post, I’m going to take a look at how these might work in practice.
Let us start with a disclaimer.
What I am going to discuss here is NOT common practice. This is advanced load optimization, and you should approach/implement it ONLY if you are 100% sure of what you are doing, and, more importantly, if you know what you are doing, and why you are doing it.
MySQL 8 introduced a feature that is explained only in a single documentation page. This feature can help a lot if used correctly, and hopefully they will not deprecate or remove it after five minutes. It is well hidden in the …[Read more]
Please join Percona’s CEO, Peter Zaitsev as he presents Forking or Branching – Lessons from the MySQL Community on Tuesday, August 28th, 2018 at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).
The MySQL Community offers a great example of various forks and branches, with MariaDB being the most well-known fork, and companies like Percona, Facebook and Alibaba maintaining their own branches.
In this presentation we will look at the history of MySQL, the causes of MySQL forking and branching, and …[Read more]
Please join Percona’s CEO, Peter Zaitsev as he presents Using MySQL for Distributed Database Architectures on Tuesday, July 31st, 2018 at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).
In modern data architectures, we’re increasingly moving from single-node design systems to distributed architectures using multiple nodes – often spread across multiple databases and multiple continents. Such architectures bring many benefits (such as scalability and resiliency), but can also bring a lot of pain if incorrectly designed and …[Read more]
Introduction As previously explained, you can run database integration tests 20 times faster! The trick is to map the data directory in memory, and my previous article showed you what changes you need to do when you have a PostgreSQL or MySQL instance on your machine. In this post, I’m going to expand the original … Continue reading How to run integration tests at warp speed using Docker and tmpfs →
This blog updates Stephane Combaudon’s blog on MySQL performance tuning, and covers MySQL 5.7 performance tuning immediately after installation.
A few years ago, Stephane Combaudon wrote a blog post on Ten MySQL performance tuning settings after installation that covers the (now) older versions of MySQL: 5.1, 5.5 and 5.6. In this post, I will look into what to tune in MySQL 5.7 (with a focus on InnoDB).
The good news is that MySQL 5.7 has significantly better default values. Morgan Tocker created a page with a complete list of features in MySQL 5.7, and is a great reference point. For example, the …[Read more]