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]
10 Older Entries »
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]
With the added complexity of growing data volumes and ever changing workloads, database performance tuning is now necessary to maximize resource utilizations and system performance. However, performance tuning is often easier said than done.
Let’s face it, tuning is difficult for a number of reasons. For one thing, it requires a significant amount of expertise in order to understand execution plans, and often update or re-write good SQL. On top of that, tuning is usually very time consuming. There will always be a large volume of SQL statements to sort through, which may lead to uncertainty around which specific statement needs tuning; and given every statement is different, so too is the tuning approach.
As data volumes grow and technology becomes increasingly complex, it is becoming more important to tune databases properly to deliver end-user experience and to lower infrastructure costs. Performance tuning can help database …[Read more]
We remember when we first started auditing MySQL servers, there were very few tools available. In one of our early big gigs, we were battling serious performance issues for a client. At the time, tuning-primer.sh was about the only tool available that could be used to diagnose performance bottlenecks. Fortunately, with a lot of manual interpolation of the raw data it presented, we were able to find the issue with the server and suggest how to resolve them. For that we are very thankful. It was a first step in analyzing MySQL status variables, minimizing the number of formulas to learn and calculate by hand. Obviously doing it by hand takes forever!
Now fast-forward to today. Unfortunately, not much has changed. Many DBAs and developers are still using open source tools such as tuning-primer, mysqltuner.pl, mysqlreport, and so on. Don’t get the wrong; those tools have …[Read more]
10 Older Entries »