Last year, I made the first post in a small series, which aimed to highlight unique features of Percona Server for MySQL, by discussing binlog_space_limit option.

Today, I am going to discuss another important type of log available in MySQL that is enhanced in Percona Server for MySQL – the slow query log. The reason why I am doing this is that although this extension has existed since the very early times of versions 5.1 (over 10 years ago!), many people are still unaware of it, which I see from time to time when working with Support customers.

Default Slow Log Inadequacy

How many times have you been wondering why, whilst reviewing slow query logs, the very same query occasionally runs way slower than usual? There may be many reasons for that, but the standard slow …

MySQL Server generates several logs that can help you monitor the activities of the server. However, once these logs are enabled, they can grow in size and start taking up too much disk space. This is why it’s important to have an automated way of archiving and preserving MySQL log files for a certain duration, as well as deleting the old ones. In this blog post, we describe some best practices for setting up and managing MySQL error logs, general logs and slow query logs for your MySQL deployments.

Setting Up MySQL Server Logging

Let’s look at how to setup the following 3 types of logs:

Error Log

Logs all the problems encountered during starting, running, or stopping mysqld. This log can be enabled by having the following option in /etc/my.cnf file:

…

pt-query-digest is one of the most commonly used tool when it comes to query auditing in MySQL®. By default, pt-query-digest reports the top ten queries consuming the most amount of time inside MySQL. A query that takes more time than the set threshold for completion is considered slow but it’s not always true that tuning such queries makes them faster. Sometimes, when resources on server are busy, it will impact every other operation on the server, and so will impact queries too. In such cases, you will see the proportion of slow queries goes up. That can also include queries that work fine in general.

This article explains a small trick to identify such spots using pt-query-digest and the slow query log. pt-query-digest is a component of …

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:

1. Enable slow query logging on your Amazon Aurora DB parameter group, apply the change when appropriate.
2. Download the slow log(s) that …

Whenever we do upgrades for our clients from one major version of MySQL to another we strongly recommend to test in two forms.

First, it would be a performance test between the old version and the new version to make sure there aren’t going to be any unexpected issues with the query processing rates. Secondly, do a functional test to ensure all queries that are running on the old version will not have syntactic errors or problems with reserved words in the new version that we’re upgrading to.

If a client doesn’t have an appropriate testing platform to perform these types of tests, we will leverage available tools to test to the best of our ability. More often than not this includes using pt-upgrade after capturing slow logs with …

When your car doesn’t start, you don’t just blindly change the battery, starter, fuel pump, spark plugs or all of the above. Instead, you go to your mechanic and ask him to check what is wrong (or you check it yourself if you are the mechanic) and then fix whatever is broken.

Yet very often I see DBAs doing exactly the opposite with their MySQL servers. Rather than assessing what is the server so busy with, they keep changing configuration options until the problem “goes away”. Alternatively, they add more RAM, more CPUs or faster disks, depending on which resources seems to be the most busy at a time. Or they switch to a new server altogether.

MySQL (with a help of some tools) has a really convenient way to analyse the workload and see clearly what exactly is MySQL so busy doing. And even how much improvement you can expect by, say, fixing a specific MySQL query.

…

Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization.

In part 2 here, we will go over the pt-query-digest report, that we have prepared in part 1.

Here’s links to the other two parts:

• Advanced MySQL Slow Query Logging Part 1: the essentials
• Advanced MySQL Slow Query Logging Part 3: fine-tuning the logging process

The post Advanced MySQL Slow Query Logging Part 2: …

Proper MySQL Query Optimization starts with a proper Slow Query Logging session. And MySQL Query Optimization is where I spend 70-80% of my time when doing MySQL performance optimization. In this short series I will show you how to do Slow Query Logging the right way.

Here’s links to the other two parts:

• Advanced MySQL Slow Query Logging Part 2: pt-query-digest report
• Advanced MySQL Slow Query Logging Part 3: fine-tuning the logging process

The post Advanced MySQL Slow Query Logging appeared first on Speedemy.

Some time ago, Peter Boros at Percona wrote this post: Rotating MySQL slow logs safely. It contains good info, such as that one should use the rename method for rotation (rather than copytruncate), and then connect to mysqld and issue a FLUSH LOGS (rather than send a SIGHUP signal).

So far so good. What I do not agree with is the additional construct to prevent slow queries from being written during log rotation. The author’s rationale is that if too many items get written while the rotation is in process, this can block threads. I understand this, but let’s review what actually happens.

Indeed, if one were to do lots of writes to the slow query log in a short space of time, a write could block while waiting.

Is the risk of this occurring greater during a logrotate operation? I doubt it. A FLUSH LOGS has to …

If you’ve used relational databases for more than ten minutes, I hope you’ve heard of slow queries. Those are those pesky little gremlins that are slowing down your startup, and preventing scalability you so desperately need. Luckily there’s a solution. What I’ve found is if I send a report to developers every week, it keeps […]