Showing entries 1 to 10 of 266
10 Older Entries »
Displaying posts with tag: monitoring (reset)
On the Observability of Outliers

At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?”

As a developer, I just want to automatically identify and isolate slow queries!

Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context.

There is also work done by a few developers which instead collects query strings, query execution times and query counts at the application. This has access to the call stack, so it can tell you which code generated the query that was slow.

It …

[Read more]
Adjusting MySQL 8.0 Memory Parameters

So you’ve just added some more memory to your MySQL server; now what? If you’ve been around the MySQL block for a while, you know that nothing is automatically changed to take advantage of this new system RAM. Let’s have a look at a few parameters you would want to adjust.

InnoDB Parameters innodb_buffer_pool_size

The InnoDB buffer pool is “…the memory area that holds cached InnoDB data for both tables and indexes.” This parameter is probably the #1 tuning parameter in MySQL. If your buffer pool is too small, then InnoDB must spend extra CPU/Disk time, loading, and unloading pages in/out of memory. This is time better spent executing your queries.

The default size of this cache is 128MB; woefully small for any serious database. Increasing the size of this cache allows more frequently accessed pages to remain in memory for the fastest access. Obviously, you do not need a buffer pool which is larger than your …

[Read more]
Understanding MySQL Memory Usage with Performance Schema

Understanding how MySQL uses memory is key to tuning it for optimal performance as well as troubleshooting cases of unexpected memory usage, i.e. when you have MySQL Server using a lot more than you would expect based on your configuration settings.

Early in MySQL history, understanding memory usage details was hard and included a lot of guesswork.  Is it possible that some queries running require a large temporary table or allocated a lot of memory for stored user variables?  Are any stored procedures taking an unexpectedly high amount of memory? All could be reasons for excessive MySQL memory usage, but you would not easily see if that is just the case.

All that changed with MySQL 5.7, which added memory instrumentation in Performance Schema, and with MySQL 8.0, this instrumentation is enabled by default, so you can get this data from pretty much any running instance.

If you’re looking for current memory …

[Read more]
How to Find Query Slowdowns Using Percona Monitoring and Management

Visibility is a blessing, and with databases, visibility is a must. That’s true not only for metrics but for the queries themselves. Having info on all the stats around query execution is priceless, and Percona Monitoring and Management (PMM) offers that in the form of the Query Analytics dashboard (QAN).

But where to start? QAN helps you with that by calculating the query profile. What is the profile? It’s a rank of queries, ordered by Load, so it is easy to spot the heaviest queries hitting your database. The Load is defined as the “Average Active Queries” but can also be defined as a mix of Query Execution Time Plus Query count. In other words, all the time the query was alive and kicking.

The Profile in PMM 2.10.0 looks like this:

The purpose of this profile is to facilitate the task of finding the …

[Read more]
MySQL Query Performance Troubleshooting: Resource-Based Approach

When I speak about MySQL performance troubleshooting (or frankly any other database), I tend to speak about four primary resources which typically end up being a bottleneck and limiting system performance: CPU, Memory, Disk, and Network.

It would be great if when seeing what resource is a bottleneck, we could also easily see what queries contribute the most to its usage and optimize or eliminate them. Unfortunately, it is not as easy as it may seem.

First, MySQL does not really provide very good instrumentation in those terms, and it is not easy to get information on how much CPU usage, Disk IO, or Memory a given query caused.  Second, direct attribution is not even possible in a lot of cases. For example, disk writes from flushing data from the InnoDB buffer pool in the …

[Read more]
A Simple MySQL Plugin to Retrieve System Metrics

Ever wanted to extend MySQL and add some feature you think it is missing?  With MySQL plugins, you can do exactly that.  One thing that has bothered me for several years is that you cannot easily retrieve system metrics from within MySQL.  Whether I am connecting via a remote connection or looking to add features to monitoring without the need for another interface with the server, I have wanted to retrieve system metrics without leaving the MySQL interface.

So, I started a Proof of Concept for this.  My goal was to get metrics such as RAM (total, used, free), system load, CPU utilization, disk utilization for the file system containing the datadir, and more.  My objective was to do this as efficiently within MySQL as possible.  For this, I chose to utilize standard C libraries in as few lines of code as possible without having to scrape system files or run commands to get the data.  The …

[Read more]
Preventing MySQL Error 1040: Too Many Connections

One of the most common errors encountered in the MySQL world at large is the infamous Error 1040:

ERROR 1040 (00000): Too many connections

What this means in practical terms is that a MySQL instance has reached its maximum allowable limit for client connections.  Until connections are closed, no new connection will be accepted by the server.

I’d like to discuss some practical advice for preventing this situation, or if you find yourself in it, how to recover.

Accurately Tune the max_connections Parameter

This setting defines the maximum number of connections that a MySQL instance will accept.  Considerations on “why” you would want to even have a max number of connections are based on resources available to the server and application usage patterns.  Allowing uncontrolled connections can crash a server, which may be considered “worse” than preventing further …

[Read more]
MySQL 101: Parameters to Tune for MySQL Performance

While there is no magic bullet for MySQL tuning, there are a few areas that can be focused on upfront that can dramatically improve the performance of your MySQL installation. While much information has been published on this topic over the years, I wanted to break down some of the more critical settings that anyone can implement with no guesswork required.

Depending on the version of MySQL you are running, some of the default values used in this post may differ from your install, but the premise is still largely the same.

Initial MySQL performance tuning can be broken down to the following categories:

  • Tuning for your hardware
  • Tuning for best performance / best practices
  • Tuning for your workload

Tuning MySQL for Your Hardware

Depending on the hardware you have installed MySQL on, some …

[Read more]
Percona Server for MySQL Highlights – Extended Slow Query Logging

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 …

[Read more]
MySQL 101: How to Find and Tune a Slow SQL Query

One of the most common support tickets we get at Percona is the infamous “database is running slower” ticket.  While this can be caused by a multitude of factors, it is more often than not caused by a bad query.  While everyone always hopes to recover through some quick config tuning, the real fix is to identify and fix the problem query.  Sure, we can generally alleviate some pain by throwing more resources at the server.  But this is almost always a short term bandaid and not the proper fix.

With Percona Monitoring and Management

So how do we find the queries causing problems and fix them?  If you have Percona Monitoring and Management (PMM) installed, the identification process is swift.  With the Query Analytics enabled (QAN) in PMM, you can simply look at the table …

[Read more]
Showing entries 1 to 10 of 266
10 Older Entries »