Nov
27
2019
--

Running PMM1 and PMM2 Clients on the Same Host

Running PMM1 and PMM2 Clients

Running PMM1 and PMM2 ClientsWant to try out Percona Monitoring and Management 2 (PMM 2) but you’re not ready to turn off your PMM 1 environment?  This blog is for you! Keep in mind that the methods described are not intended to be a long-term migration strategy, but rather, simply a way to deploy a few clients in order to sample PMM 2 before you commit to the upgrade. ?

Here are step-by-step instructions for deploying PMM 1 & 2 client functionality i.e. pmm-client and pmm2-client, on the same host.

  1. Deploy PMM 1 on Server1 (you’ve probably already done this)
  2. Install and setup pmm-client for connectivity to Server1
  3. Deploy PMM 2 on Server2
  4. Install and setup pmm2-client for connectivity to Server2
  5. Remove pmm-client and switched completely to pmm2-client

The first few steps are already described in our PMM1 documentation so we are simply providing links to those documents.  Here we’ll focus on steps 4 and 5.

Install and Setup pmm2-client Connectivity to Server2

It’s not possible to install both clients from a repository at the same time. So you’ll need to download a tarball of pmm2-client. Here’s a link to the latest version directly from our site.

Download pmm2-client Tarball

* Note that depending on when you’re seeing this, the commands below may not be for the latest version, so the commands may need to be updated for the version you downloaded.

$ wget https://www.percona.com/downloads/pmm2/2.1.0/binary/tarball/pmm2-client-2.1.0.tar.gz

Extract Files From pmm2-client Tarball

$ tar -zxvf pmm2-client-2.1.0.tar.gz 
$ cd pmm2-client-2.1.0

Register and Generate Configuration File

Now it’s time to set up a PMM 2 client. In our example, the PMM2 server IP is 172.17.0.2 and the monitored host IP is 172.17.0.1.

$ ./bin/pmm-agent setup --config-file=config/pmm-agent.yaml \
--paths-node_exporter="$PWD/pmm2-client-2.1.0/bin/node_exporter" \
--paths-mysqld_exporter="$PWD/pmm2-client-2.1.0/bin/mysqld_exporter" \
--paths-mongodb_exporter="$PWD/pmm2-client-2.1.0/bin/mongodb_exporter" \
--paths-postgres_exporter="$PWD/pmm2-client-2.1.0/bin/postgres_exporter" \
--paths-proxysql_exporter="$PWD/pmm2-client-2.1.0/bin/proxysql_exporter" \
--server-insecure-tls --server-address=172.17.0.2:443 \
--server-username=admin  --server-password="admin" 172.17.0.1 generic node8.ca

Start pmm-agent

Let’s run the pmm-agent using a screen.  There’s no service manager integration when deploying alongside pmm-client, so if your server restarts, pmm-agent won’t automatically resume.

# screen -S pmm-agent

$ ./bin/pmm-agent --config-file="$PWD/config/pmm-agent.yaml"

Check the Current State of the Agent

$ ./bin/pmm-admin list
Service type  Service name         Address and port  Service ID

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/805db700-3607-40a9-a1fa-be61c76fe755  
node_exporter               running    /agent_id/805eb8f6-3514-4c9b-a05e-c5705755a4be

Add MySQL Service

Detach the screen, then add the mysql service:

$ ./bin/pmm-admin add mysql --use-perfschema --username=root mysqltest
MySQL Service added.
Service ID  : /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b
Service name: mysqltest

Here is the state of pmm-agent:

$ ./bin/pmm-admin list
Service type  Service name         Address and port  Service ID
MySQL         mysqltest            127.0.0.1:3306    /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/805db700-3607-40a9-a1fa-be61c76fe755   
node_exporter               running    /agent_id/805eb8f6-3514-4c9b-a05e-c5705755a4be   
mysqld_exporter             running    /agent_id/efb01d86-58a3-401e-ae65-fa8417f9feb2  /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b
qan-mysql-perfschema-agent  running    /agent_id/26836ca9-0fc7-4991-af23-730e6d282d8d  /service_id/28c4a4cd-7f4a-4abd-a999-86528e38992b

Confirm you can see activity in each of the two PMM Servers:

PMM 1 PMM 2

Remove pmm-client and Switch Completely to pmm2-client

Once you’ve decided to move over completely to PMM2, it’s better to make a switch from the tarball version to installation from the repository. It will allow you to perform client updates much easier as well as register the new agent as a service for automatically starting with the server. Also, we will show you how to make a switch without re-adding monitored instances.

Configure Percona Repositories

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm 
$ sudo percona-release disable all 
$ sudo percona-release enable original release 
$ yum list | grep pmm 
pmm-client.x86_64                    1.17.2-1.el6                  percona-release-x86_64
pmm2-client.x86_64                   2.1.0-1.el6                   percona-release-x86_64

Here is a link to the apt variant.

Remove pmm-client

yum remove pmm-client

Install pmm2-client

$ yum install pmm2-client
Loaded plugins: priorities, update-motd, upgrade-helper
4 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package pmm2-client.x86_64 0:2.1.0-5.el6 will be installed
...
Installed:
  pmm2-client.x86_64 0:2.1.0-5.el6                                                                                                                                                           

Complete!

Configure pmm2-client

Let’s copy the currently used pmm2-client configuration file in order to omit re-adding monitored instances.

$ cp pmm2-client-2.1.0/config/pmm-agent.yaml /tmp

It’s required to set the new location of exporters (/usr/local/percona/pmm2/exporters/) in the file.

$ sed -i 's|node_exporter:.*|node_exporter: /usr/local/percona/pmm2/exporters/node_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|mysqld_exporter:.*|mysqld_exporter: /usr/local/percona/pmm2/exporters/mysqld_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|mongodb_exporter:.*|mongodb_exporter: /usr/local/percona/pmm2/exporters/mongodb_exporter|g' /tmp/pmm-agent.yaml 
$ sed -i 's|postgres_exporter:.*|postgres_exporter: /usr/local/percona/pmm2/exporters/postgres_exporter|g' /tmp/pmm-agent.yaml
$ sed -i 's|proxysql_exporter:.*|proxysql_exporter: /usr/local/percona/pmm2/exporters/proxysql_exporter|g' /tmp/pmm-agent.yaml

The default configuration file has to be replaced by our file and the service pmm-agent has to be restarted.

$ cp /tmp/pmm-agent.yaml /usr/local/percona/pmm2/config/
$ systemctl restart pmm-agent

Check Monitored Services

So now we can verify the current state of monitored instances.

$ pmm-admin list

Also, it can be checked on PMM server-side.

Nov
22
2019
--

Tips for Designing Grafana Dashboards

Designing Grafana Dashboards

As Grafana powers our star product – Percona Monitoring and Management (PMM) – we have developed a lot of experience creating Grafana Dashboards over the last few years.   In this article, I will share some of the considerations for designing Grafana Dashboards. As usual, when it comes to questions of design they are quite subjective, and I do not expect you to chose to apply all of them to your dashboards, but I hope they will help you to think through your dashboard design better.

Design Practical Dashboards

Grafana features many panel types, and even more are available as plugins. It may be very attractive to use many of them in your dashboards using many different visualization options. Do not!  Stick to a few data visualization patterns and only add additional visualizations when they provide additional practical value not because they are cool.  Graph and Singlestat panel types probably cover 80% of use cases.

Do Not Place Too Many Graphs Side by Side

This probably will depend a lot on how your dashboards are used.  If your dashboard is designed for large screens placed on the wall you may be able to fit more graphs side by side, if your dashboard needs to scale down to lower resolution small laptop screen I would suggest sticking to 2-3 graphs in a row.

Use Proper Units

Grafana allows you to specify a unit for the data type displayed. Use it! Without type set values will not be properly shortened and very hard to read:

Grafana Dashboards

Compare this to

Grafana Dashboards2

Mind Decimals

You can specify the number of values after decimal points you want to display or leave it default.  I found default picking does not always work very well, for example here:

Grafana Dashboards3

For some reason on the panel Axis, we have way too many values displayed after the decimal point.  Grafana also often picks three values after decimal points as in the table below which I find inconvenient – from the glance view, it is hard to understand if we’re dealing with a decimal point or with “,” as a “thousands” separator, so I may be looking at 2462 GiB there.  While it is not feasible in this case, there are cases such as data rate where a 1000x value difference is quite possible.  Instead, I prefer setting it to one decimal (or one if it is enough) which makes it clear that we’re not looking at thousands.

Label your Axis

You can label your axis (which especially makes sense) if the presentation is something not as obvious as in this example; we’re using a negative value to lot writes to a swap file.

Grafana Dashboards4

Use Shared Crosshair or Tooltip

In Dashboard Settings, you will find “Graph Tooltip” option and set it to “Default”,
“Shared Crosshair” or “Share Tooltip”  This is how these will look:

Grafana Dashboards5

Grafana Dashboards 6

Grafana Dashboards 6

 

Shared crosshair shows the line matching the same time on all dashboards while Tooltip shows the tooltip value on all panels at the same time.  You can pick what makes sense for you; my favorite is using the tooltip setting because it allows me to visually compare the same time without making the dashboard too slow and busy.

Note there is handy shortcut CTRL-O which allows you to cycle between these settings for any dashboard.

Pick Colors

If you’re displaying truly dynamic information you will likely have to rely on Grafana’s automatic color assignment, but if not, you can pick specific colors for all values being plotted.  This will prevent colors from potentially being re-assigned to different values without you planning to do so.

Grafana Dashboards 7

Picking colors you also want to make sure you pick colors that make logical sense. For example, I think for free memory “green” is a better color than “red”.  As you pick the colors, use the same colors for the same type of information when you show it on the different panels if possible, because it makes it easier to understand.

I would even suggest sticking to the same (or similar) color for the Same Kind of Data – if you have many panels which show disk Input and Output using similar colors, this can be a good idea.

Fill Stacking Graphs

Grafana does not require it, but I would suggest you use filling when you display stacking data and don’t use filling when you’re plotting multiple independent values.  Take a look at these graphs:

In the first graph, I need to look at the actual value of the plotted value to understand what I’m looking at. At the same time, in the second graph, that value is meaningless and what is valuable is the filled amount. I can see on the second graph what amount of the Cache, blue value, has shrunk.

I prefer using a fill factor of 6+ so it is easier to match the fill colors with colors in the table.   For the same reason, I prefer not to use the fill gradient on such graphs as it makes it much harder to see the color and the filled volume.

Do Not Abuse Double Axis

Graphs that use double axis are much harder to understand.  I used to use it very often, but now I avoid it when possible, only using it when I absolutely want to limit the number of panels.

Note in this case I think gradient fits OK because there is only one value displayed as the line, so you can’t get confused if you need to look at total value or “filled volume”.

Separate Data of Different Scales on Different Graphs

I used to plot Innodb Rows Read and Written at the same graph. It is quite common to have reads to be 100x higher in volume than writes, crowding them out and making even significant changes in writes very hard to see.  Splitting them to different graphs solved this issue.

Consider Staircase Graphs

In the monitoring applications, we often display average rates computed over a period of time.  If this is the case, we do not know how the rate was changing within that period and it would be misleading to show that. This especially makes sense if you’re displaying only a few data points.

Let’s look at this graph which is being viewed with one-hour resolution:

This visually shows what amount of rows read was falling from 16:00 to 18:00, and if we compare it to the staircase graph:

It simply shows us that the value at 18 am was higher than 17 am, but does not make any claim about the change.

This display, however, has another issue. Let’s look at the same data set with 5min resolution:

We can see the average value from 16:00 to 17:00 was lower than from 17:00 to 18:00, but this is however NOT what the lower resolution staircase graph shows – the value for 17 to 18 is actually lower!

The reason for that is if we compute on Prometheus side rate() for 1 hour at 17:00 it will be returned as a data point for 17:00 where this average rate is really for 16:00 to 17:00, while staircase graph will plot it from 17:00 to 18:00 until a new value is available.  It is off by one hour.

To fix it, you need to shift the data appropriately. In Prometheus, which we use in PMM, I can use an offset operator to shift the data to be displayed correctly:

Provide Multiple Resolutions

I’m a big fan of being able to see the data on the same dashboard with different resolutions, which can be done through a special dashboard variable of type “Interval”.  High-resolution data can provide a great level of detail but can be very volatile.

While lower resolution can hide this level of detail, it does show trends better.

Multiple Aggregates for the Same Metrics

To get even more insights, you can consider plotting the same metrics with different aggregates applied to it:

In this case, we are looking at the same variable – threads_running – but at its average value over a period of time versus max (peak) value. Both of them are meaningful in a different way.

You can also notice here that points are used for the Max value instead of a line. This is in general good practice for highly volatile data, as a plottings line for something which changes wildly is messy and does not provide much value.

Use Help and Panel Links

If you fill out a description for the panel, it will be visible if you place your mouse over the tiny “i” sign. This is very helpful to explain what the panel shows and how to use this data.  You can use Markup for formatting.  You can also provide one or more panel links, that you can use for additional help or drill down.

With newer Grafana versions, you can even define a more advanced drill-down, which can contain different URLs based on the series you are looking at, as well as other templating variables:

Summary

This list of considerations for designing Grafana Dashboards and best practices is by no means complete, but I hope you pick up an idea or two which will allow you to create better dashboards!

Nov
20
2019
--

Profiling Software Using perf and Flame Graphs

Profiling Software Using perf and Flame Graphs

In this blog post, we will see how to use perf (a.k.a.: perf_events) together with Flame Graphs. They are used to generate a graphical representation of what functions are being called within our software of choice. Percona Server for MySQL is used here, but it can be extended to any software you can take a resolved stack trace from.

Before moving forward, a word of caution. As with any profiling tool, DON’T run this in production systems unless you know what you are doing.

Installing Packages Needed

For simplicity, I’ll use commands for CentOS 7, but things should be the same for Debian-based distros (apt-get install linux-tools-$(uname -r) instead of the yum command is the only difference in the steps).

To install perf, simply issue:

SHELL> sudo yum install -y perf

To get Flame Graphs project:

SHELL> mkdir -p ~/src
SHELL> cd ~/src
SHELL> git clone https://github.com/brendangregg/FlameGraph

That’s it! We are good to go.

Capturing Samples

Flame Graphs are a way of visualizing data, so we need to have some samples we can base off of. There are three ways in which we can do this. (Note that we will use the -p flag to only capture data from our process of interest, but we can potentially capture data from all the running processes if needed.)

1- Capture for a set amount of time only (ten seconds here):

SHELL> sudo perf record -a -F 99 -g -p $(pgrep -x mysqld) -- sleep 10

2- Capture until we send the interrupt signal (CTRL-C):

SHELL> sudo perf record -a -F 99 -g -p $(pgrep -x mysqld)

3- Capture for the whole lifetime of the process:

SHELL> sudo perf record -a -F 99 -g -- /sbin/mysqld \
--defaults-file=/etc/percona-server.conf.d/mysqld.cnf --user=mysql

or

SHELL> sudo perf record -a -F 99 -g -p $(pgrep -x mysqld) -- mysql -e "SELECT * FROM db.table"

We are forced to capture data from all processes in the first case of the third variant since it’s impossible to know the process ID (PID) number beforehand (with the command executed, we are actually starting the MySQL service). This type of command comes in handy when you want to have data from the exact beginning of the process, which is not possible otherwise.

In the second variant, we are running a query on an already-running MySQL service, so we can use the -p flag to capture data on the server process. This is handy if you want to capture data at the exact moment a job is running, for instance.

Preparing the Samples

After the initial capture, we will need to make the collected data “readable”. This is needed because it is stored in binary format by perf record. For this we will use:

SHELL> sudo perf script > perf.script

It will read perf.data by default, which is the same default perf record uses for its output file. It can be overridden by using the -i flag and -o flag, respectively.

We will now be able to read the generated text file, as it will be in a human-readable form. However, when doing so, you will quickly realize why we need to aggregate all this data into a more intelligible form.

Generating the Flame Graphs

We can do the following in a one-liner, by piping the output of the first as input to the second. Since we didn’t add the FlameGraph git folder to our path, we will need to use full paths.

SHELL> ~/src/FlameGraph/stackcollapse-perf.pl perf.script | ~/src/FlameGraph/flamegraph.pl > flamegraph.svg

We can now open the .svg file in any browser and start analyzing the information-rich graphs.

How Does it Look?

As an example, I will leave full commands, their outputs, and a screenshot of a flame graph generated by the process using data capture method #2. We will run an INSERT INTO … SELECT query to the database, so we can then analyze its execution.

SHELL> time sudo perf record -a -F 99 -g \
-p $(pgrep -x mysqld) \
-- mysql test -e "INSERT INTO joinit SELECT NULL, uuid(), time(now()),  (FLOOR( 1 + RAND( ) *60 )) FROM joinit;"
Warning:
PID/TID switch overriding SYSTEM
[ perf record: Woken up 7 times to write data ]
[ perf record: Captured and wrote 1.909 MB perf.data (8214 samples) ]

real 1m24.366s
user 0m0.133s
sys 0m0.378s

SHELL> sudo perf script | \ 
~/src/FlameGraph/stackcollapse-perf.pl perf.script | \
~/src/FlameGraph/flamegraph.pl > mysql_select_into_flamegraph.svg

The keen-eyed reader will notice we went one step further here and joined steps #2 and #3 via a pipe (|) to avoid writing to and reading from the perf.script output file. Additionally, there are time outputs so we can get an estimation on the amount of data the tool generates (~2Mb in 1min 25secs); this will, of course, vary depending on many factors, so take it with a pinch of salt, and test in your own environment.

The resulting flame graph is:

perf and Flame Graphs

One clear candidate for optimization is work around write_record: if we can make that function faster, there is a lot of potential for reducing overall execution time (squared in blue in the bottom left corner, we can see a total of ~60% of the samples were taken in this codepath). In the last section below we link to a blog post explaining more on how to interpret a Flame Graph, but for now, know you can mouse-over the function names and it will dynamically change the information shown at the bottom left corner. You may also visualize it better with the following guides in place:

flame graphs

Conclusion

For the Support team, we use this procedure in many cases where we need to have an in-depth view of what MySQL is executing, and for how long. This way, we can have a better insight into what operations are behind a specific workload and act accordingly. This procedure can be used either for optimizing or troubleshooting and is a very powerful tool in our tool belt! It’s known that humans are better at processing images rather than text, and this tool exploits that brilliantly, in my opinion.

Related links

Interpreting Flame Graphs (scroll down to the “Flame Graph Interpretation” section)

Flame Graphs 201, a great webinar by Marcos, if you want to dig deeper into this

Of course, Brendan Gregg (the mastermind behind the Flame Graph project) has even more information on this

Nov
18
2019
--

PMM 2.1, MongoDB Hot Backups, Percona Server Updates: Release Roundup 11/18/2019

Percona Software Release Nov 18

Percona Software Release Nov 18It’s release roundup time here at Percona!

As mentioned a few weeks ago, we are now publishing release roundups comprising all the details and information you need on the previous week (or two)’s releases from Percona. This post will encompass releases from November 4, 2019 – November 18, 2019.

Each roundup will showcase the latest in software updates, tools, and features to help you manage and deploy our software, with highlights and critical information, as well as links to the full release notes and direct links to the software or service itself.

In this edition, we highlight two recent version updates to Percona Server for MySQL, improvements and new features in Percona Monitoring and Management 2.1.0, and some very cool new functions in Percona Server for MongoDB 4.2.1-1, including streaming hot backups in all our active MongoDB releases.

 

Percona Server for MySQL 5.6.46-86.2

On November 6, 2019, we released Percona Server for MySQL version 5.6.46-86.2, the current GA release in the 5.6 series. It includes several bug fixes, including a fix of the Audit log filtering by a user not working and the addition of a package version for the Red Hat Package Manager (rpm). Percona Server for MySQL is an enhanced drop-in replacement for MySQL.

Download Percona Server for MySQL 5.6.46-86.2

 

Percona Monitoring and Management 2.1.0

PMM 2.1.0, a free and open-source platform for managing and monitoring MySQL, MongoDB, and PostgreSQL performance, was released on November 11, 2019. This version has bug fixes and many new features, including a latency detail graph, additional log and config files, and the disabling of heavy-load collectors automatically when there are too many tables.

NOTE: Percona Monitoring and Management (PMM) employs a client/server model. You must download and install both the client and server applications. The directions for doing this are in the documentation.

Download Percona Monitoring and Management 2.1.0

 

Percona Server for MongoDB 4.2.1-1

On November 13, 2019, we released Percona Server for MongoDB version 4.2.1-1, which includes all of the new features of the latest version of MongoDB 4.2 Community Edition, as well as the Percona Memory Engine storage engine, encrypted WiredTiger storage engine, and enhanced query profiling.  Percona Server for MongoDB 4.2.1-1 adds the ability for remote streaming hot backups to Amazon S3 or compatible storage such as MinIO, and is now included in all our active MongoDB releases (3.6, 4.0, and 4.2).

Download Percona Server for MongoDB 4.2.1-1

 

Percona Server for MySQL 5.7.28-31

As of November 13, 2019, Percona Server for MySQL 5.7.28-31 is now the current GA (Generally Available) release in the 5.7 series. It is based on MySQL 5.7.27 and includes all the bug fixes in it. If you’re currently using Percona Server for MySQL 5.7, Percona recommends upgrading to this version of 5.7 prior to upgrading to Percona Server 8.0. Percona Server for MySQL is trusted by thousands of enterprises to provide better performance and concurrency for their most demanding workloads.

Download Server for MySQL 5.7.28-31

That’s it for this roundup, and be sure to follow us on Twitter to stay up-to-date on the most recent releases! Percona is a leader in providing best-of-breed enterprise-class support, consulting, managed services, training and software for MySQL, MariaDB, MongoDB, PostgreSQL, and other open source databases in on-premises and cloud environments.

Nov
11
2019
--

Prepare Your Databases for High Traffic on Black Friday

Prepare Your Databases For High Traffic

Prepare Your Databases For High TrafficIt’s November, so we all know what that means; it’s peak shopping season, and no date is bigger than Black Friday. But how will your database handle all that new, relentless traffic? Not only does your database have to handle traffic without slowing down, but web servers can sometimes see such sudden traffic as an attack; meaning your site(s) could go down completely.

Every year there are news stories about websites that were unresponsive or disappeared completely right at the exact moment when potential shoppers were coming online. Don’t let this be you! To ensure your databases are prepared for a high traffic event, download our Database Disaster Prevention Checklist and read what we advise you do before, during, and after the big day. Let’s get started.

What You Need To Know About High Traffic Events

  1. Your users expect instant response and immediate feedback from your application. If you don’t give it to them, your competition will.
  2. Database slowdowns for you can be perceived as downtime for your customers, and they will lose confidence in your ability.
  3. A few seconds of downtime costs you not only direct revenue but also lost future business. Use our Cost of Database Downtime Calculator to see just how much downtime could cost you.
  4. When you failover a slow system, the slowness follows to the new system. If your slowness alleviates, it may be because your customers went somewhere else when you were down.
  5. Time is finite; you can’t get more of it. You have to make the most of it. It can be much more valuable than money.

Before the Event

  1. Setup monitoring and tooling before the event. If you can’t see what’s going on, how can you measure your success? Percona Monitoring and Management can help with that.
  2. Load test your applications and test how you will scale under normal and peak loads. The best time to find bottlenecks is before the event, as during is costly and impactful.
  3. Test failover and understand how quickly you can recover. The worst time to find out your failover doesn’t work is during the busiest day of the year.
  4. Put a code and configuration freeze in place in advance of the event. It’s really hard to ensure performance if the application is growing and evolving.
  5. Get a second opinion, double-check, and don’t assume. A large portion of your business is tied to this event. Trust but verify things are ready; it’s worth it. Most big outages are caused by easily overlooked things.
  6. Check your backups. Make sure you have reliable and consistent backups of your databases. This will make it easier to restore a crashed database. Percona XtraBackup for MySQL can help.

During the Event

  1. Realize that failing over to another system is the absolute last resort.  Failing over a busy system moves the traffic to a new server. Additionally, most systems are slower when traffic is added as it takes time to warm up the cache.
  2. Have the right people standing by to monitor, tweak, and fix issues before they get out of hand. Too many issues are prolonged by waiting to get the right people in the room to fix issues. Many larger companies make this an all-hands event.
  3. Don’t lose sight of the goal! Getting back up and running and allowing customers access to the site is your goal, not developing a permanent fix in the heat of the moment. Time to make an impact is finite, but equally important people under pressure are more prone to make mistakes.  An easier temporary fix to get you through the day can work in your favor, as long as you don’t forget to make the permanent one eventually.
  4. Don’t make the problem worse. Some activities can cause cascading slowness… know the impact of the changes before you make them. We get a lot of calls from people who had good intentions to try and fix a minor issue but made a much bigger problem. The road to hell is paved with good intentions.
  5. Collect and store the data needed to analyze and improve for the next event. Often when things don’t go right, issues end up being transient and difficult to understand after the fact. Get the data you need, when things are going well or not.

After the Event

  1. Analyze and understand your traffic and usage. Use this data to plan, enhance, and tweak your strategy for future events.
  2. Don’t leave the quick fixes in place and just forget about them, these could escalate at the worst times. Take the time and expense to fix them during slow periods.
  3. Learn from your mistakes and build a plan to mitigate problems and risks in the future.
  4. Update your systems to the latest builds and security fixes. Take advantage of the slower load and catch up after a freeze/blackout period around the event.
  5. Don’t fall into complacency. Congrats that you survived this year, but each application and user base is a living, breathing entity, and what worked last year may not work this year. You have to analyze, plan, and review on a regular basis.

Conclusion

Now you’re better prepared for the database high-traffic days coming your way soon. For more information, learn how to ensure peak database performance for your event and download our Database Disaster Prevention Checklist, or contact us today. Percona’s experts can maximize your application performance with our open source database support, managed services or consulting for MySQL, MariaDB, MongoDB, PostgreSQL in on-premises and cloud environments.

Database Disaster Prevention Checklist

Nov
04
2019
--

Updated PMM and Percona Server for MongoDB, Utility User Feature in Percona Server for MySQL: Release Roundup 11/4/2019

Percona Software Releases

Percona Software ReleasesIt’s release roundup time here at Percona!

We publish quite a few updates to our software and services every week, so we wanted to make it a little easier for you to keep track of our latest releases. So, starting today we will publish release roundups comprising all the details and information you need on the previous week (or two)’s releases from Percona.

Each roundup will showcase the latest in software updates, tools, and features to help you manage and deploy our software, with highlights and critical information, as well as links to the full release notes and direct links to the software or service itself.

In this edition of the release roundup, we highlight the recent updates to Percona Server for Mongo DB and Percona Monitoring and Management, as well as new features in Percona Server for MySQL, and some added experimental features and bug fixes for Percona Server for MongoDB.

 

Percona Server for MongoDB 3.4.23-2.21

On October 24, we released Percona Server for MongoDB 3.4.23-2.21, an enhanced, open source, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 3.4 Community Edition. It supports MongoDB 3.4 protocols and drivers. With over 530,000 downloads, Percona Server for MongoDB delivers high-performance and reliability to enterprises looking to achieve optimum performance, without being tied into an expensive proprietary software vendor relationship.

In this release, the license of RPM and DEB packages has been changed from AGPLv3 to SSPL.

Percona Server for MongoDB offers all the features and benefits of MongoDB Community Edition, plus additional enterprise-grade functionality, and can be used on-premises and in the cloud.

Download Percona Server for MongoDB 3.4.23-2.21.

 

Percona Monitoring and Management 1.17.2

Also on October 24, we released Percona Monitoring and Management 1.17.2,  a free and open-source platform for managing and monitoring MySQLMongoDB, and PostgreSQL performance. It allows deep insight into the performance of applications and databases, both on-premises and in the cloud, and is compatible with major cloud providers such as Amazon Web Services (AWS), Google Cloud, and Microsoft Azure, with specific dashboards for AWS RDS and Amazon Aurora.

PMM 1.17.2 is now based on Grafana 5.4.5. One of the improvements in this release is a substantial size increase for the AMI images, which allows now to reserve space for about 10 instances with 30 days retention. Another improvement is related to the Tooltips for the MySQL InnoDB Compression dashboard, which were updated by describing what the graphs display, along with links to related documentation resources. Besides these improvements, in PMM 1.17.2 we have included 4 improvements and fixed 23 bugs.

Percona Monitoring and Management (PMM) allows deep insight into the performance of applications and databases.

Download Percona Monitoring and Management 1.17.2.

 

Percona Server for MySQL 8.0.17-8

On October 30, Percona Server for MySQL 8.0.17-8 was released. It includes all the features available in MySQL 8.0.17 Community Edition in addition to enterprise-grade features developed by Percona. A new feature is the ability to have a MySQL Utility user who has system access to do administrative tasks but who has limited access to user schemas. This feature is especially useful to those who are operating MySQL as a Service. Percona Server for MySQL 8 has also implemented Data Masking, which provides a set of functions to hide sensitive data with modified content. This release includes fixes to bugs found in previous releases.

Percona Server for MySQL delivers enterprise-grade features for free and is fully compatible with today’s cloud providers such as AWS, Google Cloud, Microsoft Azure, and others, fully deployed in the cloud or as a hybrid solution.

Download Percona Server for MySQL 8.0.17-8.

 

Percona Server for MongoDB 4.0.13-7

Percona announced the release of Percona Server for MongoDB 4.0.13-7 on October 31. It enables uploading hot backups to an Amazon S3 or compatible storage service, such as MinIO. For this release, this feature has the EXPERIMENTAL status. Also, the license for RPM and DEB packages has been changed from AGPLv3 to SSPL. Percona Server for MongoDB is an enhanced, open source, and highly-scalable database that is a fully-compatible, drop-in replacement for MongoDB 4.0 Community Edition.

Download Percona Server for MongoDB 4.0.13-7.

 

Percona XtraBackup 2.4.16

On November 4, Percona XtraBackup 2.4.16 was released.  An improvement was added to this release, where two options (

--backup-lock-timeout

  and

--backup-lock-retry-count

 ) were added to enable the configuring of the timeout for acquiring metadata locks in FLUSH TABLES WITH READ LOCKLOCK TABLE FOR BACKUP, and LOCK BINLOG FOR BACKUP statements. (More information in PXB-1914). In addition, several bugs were fixed, including PXB-1902 where Percona XtraBackup was not able to connect to the database when the password was specified along with the transition-key parameter. For a full list of improvements and bug fixes, please visit the release notes page on our website.  Percona XtraBackup tools provide a method of performing a hot backup of your MySQL data while the system is running. It is a free, online, open source, complete database backups solution for all versions of Percona Server for MySQL and MySQL.

Download Percona XtraBackup 2.4.16.

 

Be sure to follow us on Twitter to stay up-to-date on the most recent releases! Percona is a leader in providing best-of-breed enterprise-class support, consulting, managed services, training and software for MySQL, MariaDB, MongoDB, PostgreSQL and other open source databases in on-premises and cloud environments.

Oct
31
2019
--

How Percona Support Handles Bugs

how percona handles bugs

how percona handles bugsOne of the great things about Percona, and a Percona Support contract, is that we not only guarantee application performance but we also provide bug fixes for covered software—and not just advice on how to use it. This is most likely missing from most customer’s in-house support, as it requires a team with code knowledge to build and test infrastructure, which only a few companies can afford to invest in.

Whether you deploy MySQL®, MariaDB®, MongoDB®, or PostgreSQL—on-premise, in the cloud, in a DBaaS environment, bare metal, virtualized or containerized—Percona Support has you and your database covered.

Now, back to bugs. While there is no such thing as “bug-free” software, there are often some misunderstandings about bugs and how they are handled. What is a bug? What is a feature? What is a repeatable bug? How will Percona troubleshoot the bug? In this post, we’ll answer some of these questions, and detail how Percona Support supports bug reporting.

Features vs. Bugs

Sometimes, software is designed to work a certain way that may not be what some users expect or want. However, that doesn’t mean that it is a “bug” in the true sense—it may just require a change in behavior to use in the correct manner rather than the way it was utilized in the past. These are considered features rather than bugs.

Unfixable Bugs

There are some behaviors that most people would call a bug, but they arise from design limitations or oversight that are impossible to fix in the current GA version without introducing changes that would destabilize the software. These bugs will need to be fixed in future GA releases. Some bugs are not bugs but rather design tradeoffs. These can’t be “fixed” unless tradeoffs are made, and are therefore tied closer to “features” than bugs.

Workaround

There are going to be unexpected behaviors, unfixable bugs, and bugs that take time to fix, so our first practical response to running into this type of bug is finding a workaround that does not expose it. The Percona Support team helps identify these types of bugs and build workarounds that will result in minimal impact on your business. But be prepared: changes to the application, deployed version, schema, or configuration are often required.

Emergencies

Emergencies are just that—emergencies. When you have one, Percona’s first area of focus is to restore your system to working order. Percona offers 24x7x365 support for production outages to all of our support customers, as well as options for real-time electronic and phone access to its expert technical support team, not just asynchronous communications through a ticketing system.

Bug Turnaround Times

We cannot guarantee turnaround time on a bug fix, as all bugs are different. Some are rather trivial for which we can provide a hotfix as soon as 24 hours after we have a repeatable test case. Others are much more complicated and can take weeks of engineering to fix (or be determined non-fixable in the current GA version of the software). The best thing to do is to report a bug and provide any additional information which would be helpful to get it resolved. (Check out our article “How to report bugs, improvements, and new feature requests” for more information.

Verified Bug Fixes

Once you submit a bug, we will first verify if it is actually a bug. As we detailed above, it might be a feature, or intended behavior, or a user mistake. It’s also possible that it only happened one time and it cannot be repeated. Having a repeatable test case that reveals a bug is the best way for it to be fixed quickly. Our support team is often able to help you create a test case if you’re unable to do so on your own.

Sporadic Bugs

Bugs that only show up sporadically are the hardest ones to fix. For example, you might have a system crash once every few months with no way to repeat it. The cause of such bugs can be very complicated; such as a buffer overrun in one piece of code causing corruption and crashes in other places hours later. And while there are a number of diagnostic tools that exist for such bugs, they can still take some time to resolve. Finally, without that repeatable test case, it is often impossible to verify that the proposed fix actually resolves the bug.

Environmental Bugs

Some bugs are caused by what can be called your “environment”, or setup. It could be hardware bugs or incompatibilities, a build not quite compatible with your version of the operating system, etc. In some cases, we can very clearly point to issues in your environment, and in others, we may suspect the environment is an issue and will ask to see if the bug also happens in other environments, such as with different hardware or OS installation.

Hot Fixes

Our default policy is that we fix bugs in the next release of our software so it can go through the full GA cycle and be properly documented. If workaround can be found so that you can wait until the next release for a fix, this is the best choice. If not, with a Percona Support Contract, we can provide you with a hotfix—a special build containing the version of the software you’re running, with the bug fix of interest applied. Hotfixes are especially helpful if you’re not looking to do a full software upgrade—requiring several revisions—but want to validate the fix with the minimum number of changes. Hotfixes might also be different from the final bug fix that goes into the GA release, as our goal is to provide a working solution for you faster. Afterward, we may optimize or re-architect the code, come up with better option names, etc. that will resolve any outstanding bugs.

Bug Diagnostics

Depending on the nature of the bug, there are multiple tools that our support team will use for diagnostics and finding a way to fix the bug. To set expectations, this can be a very involved process requiring that you provide information or try things on your system, such as:

  • If you have a test case that can be repeated by the Percona team to trigger the bug, the diagnostic problem is solved from the customer side. Internal debugging starts at this point.
  • If we have a crash that we can’t repeat on our system we may ask you to enable “core” file or run the program under a debugger so we can get more information when the crash happens.
  • If the problem is related to performance, you should be ready to gather information such as EXPLAIN, status counters, information from performance schema, etc. along with system-level information like pt-pmp output, pt-stalk, oprofile, or perf.
  • If the problem is a “deadlock,” we often need information from gdb about the full state of the system. Information from processlist, performance_schema, and SHOW ENGINE INNODB STATUS can also be helpful.
  • It may also be helpful to have a test system on which you can repeat the problem in your environment and experiment without impacting a production environment. It is not possible in all cases, but it is useful for bug resolution.
  • Sometimes, for hard-to-repeat bugs, we will need to run a special diagnostics build that provides us with additional debug information. Or, we might need to run a debug build or do a run under valgrind or other software designed to catch bugs. This can have a large performance impact, so it is good to see if your workload can be scaled down for this to be feasible.
  • Depending on your environment, we might need to login to troubleshoot your bug or request that you upload the data needed to repeat the bug in our lab (assuming it is not too sensitive). In cases where direct login is not possible, we can help you create a repeatable test case via phone, chat, or email. Using screen sharing can also be very helpful.

Bugs and Non-Percona Software

Percona Support does cover some software not produced by Percona. For open source software, if it is not exempt from bug fix support, we will provide the custom build with a bug fix as well as provide the suggested fix to the software maintainer for its possible inclusion in its next release. For example, if we find a bug in the MySQL Community Edition, we will pass our suggested fix to the MySQL Engineering team at Oracle. For other software that is not open source, such as Amazon RDS, we can help to facilitate creation and submission of a repeatable test case and workaround, but we can’t provide a fix as we do not have access to the source code.

In Conclusion

When we think about software bugs, there are some good parallels with human “bugs”. Some issues are trivial to diagnose and the fix is obvious, while others might be very hard to diagnose, with doctor after doctor still not able to determine the cause of your disease. Then, even when the diagnosis is found, a cure is not always available or feasible, and we have to settle for “managing” a disease—our parallel to implementing changes and settling for a workaround. In the same way as human doctors, we can’t guarantee we will get to the root of every problem, or fix every problem we find. However, as with having good doctors, having us on your team will help maximize your chances of a successful bug resolution.

How Percona Can Help

Percona’s experts can maximize your database performance with our open source database support, managed services or consulting professional services. For more information on our database services, contact us at +1-888-316-9775 (USA), +44 203 608 6727 (Europe), or have us reach out to you directly.

Oct
30
2019
--

Understanding Hash Joins in MySQL 8

hash joins mysql

hash joins mysqlIn MySQL 8.0.18 there is a new feature called Hash Joins, and I wanted to see how it works and in which situations it can help us. Here you can find a nice detailed explanation about how it works under the hood.

The high-level basics are the following: if there is a join, it will create an in-memory hash table based on one of the tables and will read the other table row by row, calculate a hash, and do a lookup on the in-memory hash table.

Great, but does this give us any performance benefits?

First of all, this only works on fields that are not indexed, so that is an immediate table scan and we usually do not recommend doing joins without indexes because it is slow. Here is where Hash Joins in MySQL can help because it will use an in-memory hash table instead of Nested Loop.

Let’s do some tests and see. First I created the following tables:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`c1` int(11) NOT NULL DEFAULT '0',
`c2` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`)
) ENGINE=InnoDB;

I have inserted 131072 random rows into both tables.

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 131072   |
+----------+

First test – Hash Joins

Run a join based on c2 which is not indexed.

mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=1728488704)
-> Table scan on t2 (cost=0.01 rows=131472)
-> Hash
-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

We have to use explain format=tree to see if Hash Join will be used or not, as normal explain still says it is going to be a Nested Loop, which I think it is very misleading. I have already filed a bug report because of this and in the ticket, you can see some comments from developers saying:

The solution is to stop using traditional EXPLAIN (it will eventually go away).

So this is not going to be fixed in traditional explain and we should start using the new way.

Back to the query; we can see it is going to use Hash Join for this query, but how fast is it?

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (0.73 sec)

0.73s for a more than 17m rows join table. Looks promising.

Second Test – Non-Hash Joins

We can disable it with an optimizer switch or optimizer hint.

mysql> select /*+ NO_HASH_JOIN (t1,t2) */ count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (13 min 36.36 sec)

Now the same query takes more than 13 minutes. That is a huge difference and we can see Hash Join helps a lot here.

Third Test – Joins Based on Indexes

Let’s create indexes and see how fast a join based on indexes is.

create index idx_c2 on t1(c2);
create index idx_c2 on t2(c2);

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;
+----------+
| count(*) |
+----------+
| 17172231 |
+----------+
1 row in set (2.63 sec)

2.6s

  Hash Join is even faster than the Index-based join in this case.

However, I was able to force the optimizer to use Hash Joins even if an index is available by using ignore index:

mysql> explain format=tree select count(*) from t1 ignore index (idx_c2) join t2 ignore index (idx_c2) on t1.c2 = t2.c2 where t1.c2=t2.c2\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=17336898)
-> Table scan on t2 (cost=0.00 rows=131472)
-> Hash
-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

I still think it would be nice if I can tell the optimizer with a hint to use Hash Joins even if an index is available, so we do not have to ignore indexes on all the tables. I have created a feature request for this.

However, if you read my first bug report carefully you can see a comment from a MySQL developer which indicates this might not be necessary:

BNL (Block Nested-Loop) will also go away entirely at some point, at which point this hint will be ignored.

That could mean they are planning to remove BNL joins in the future and maybe replace it with Hash join.

Limitations

We can see Hash Join can be powerful, but there are limitations:

  • As I mentioned it only works on columns that do not have indexes (or you have to ignore them).
  • It only works with equi-join conditions.
  • It does not work with LEFT or RIGHT JOIN.

I would like to see a status metric as well to monitor how many times Hash Join was used, and for this, I filled another feature request.

Conclusion

Hash Join seems a very powerful new join option, and we should keep an eye on this because I would not be surprised if we get some other features in the future as well. In theory, it would be able to do Left and Right joins as well and as we can see in the comments on the bug report that Oracle has plans for it in the future.

Oct
29
2019
--

Monitoring PostgreSQL Databases Using PMM

Monitoring PostgreSQL with Percona Monitoring Management

PostgreSQLPostgreSQL is a widely-used Open Source database and has been the DBMS of the year for the past 2 years in DB-Engine rankings. As such, there is always a need for reliable and robust monitoring solutions. While there are some commercial monitoring tools, there is an equally good number of open source tools available for monitoring PostgreSQL. Percona Monitoring and Management (PMM) is one of those open source solutions that have continuous improvements and is maintained forever by Percona. It is simple to set up and easy to use.

PMM can monitor not only PostgreSQL but also MySQL and MongoDB databases, so it is a simple monitoring solution for monitoring multiple types of databases. In this blog post, you will see all the steps involved in monitoring PostgreSQL databases using PMM.

This is what we will be discussing:

  1. Using the PMM docker image to create a PMM server.
  2. Installing PMM client on a Remote PostgreSQL server and connecting the PostgreSQL Client to PMM Server.
  3. Creating required users and permissions on the PostgreSQL server.
  4. Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

If you already know how to create a PMM Server, please skip the PMM server setup and proceed to the PostgreSQL client setup.

Using the PMM docker image to create a PMM server

PMM is a client-server architecture where clients are the PostgreSQL, MySQL, or MongoDB databases and the server is the PMM Server. We see a list of metrics on the Grafana dashboard by connecting to the PMM server on the UI. In order to demonstrate this setup, I have created 2 virtual machines where one of them is the PMM Server and the second server is the PostgreSQL database server.

192.168.80.10 is my PMM-Server
192.168.80.20 is my PG 11 Server

Step 1 : 

On the PMM Server, install and start docker.

# yum install docker -y
# systemctl start docker

Here are the installation instructions of PMM Server.

Step 2 :

Pull the pmm-server docker image. I am using the latest PMM2 docker image for this setup.

$ docker pull percona/pmm-server:2

You see a docker image of size 1.48 GB downloaded after the above step.

$ docker image ls
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/percona/pmm-server 2 cd30e7343bb1 2 weeks ago 1.48 GB

Step 3 :

Create a container for persistent PMM data.

$ docker create \
-v /srv \
--name pmm-data \
percona/pmm-server:2 /bin/true

Step 4 :

Create and launch the PMM Server. In the following step, you can see that we are binding the port 80 of the container to the port 80 of the host machine. Likewise for port 443.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--restart always \
percona/pmm-server:2

At this stage, you can modify certain settings such as the memory you wish to allocate to the container or the CPU share, etc. You can also see more such configurable options using

docker run --help

. The following is just an example of how you can modify the above step with some memory or CPU allocations.

$ docker run -d \
-p 80:80 \
-p 443:443 \
--volumes-from pmm-data \
--name pmm-server \
--cpu-shares 100 \
--memory 1024m \
--restart always \
percona/pmm-server:2

You can list the containers started for validation using

docker ps

.

$ docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
bb6043082d3b percona/pmm-server:2 "/opt/entrypoint.sh" About a minute ago Up About a minute 0.0.0.0:80->80/tcp, 0.0.0.0:443->443/tcp pmm-server

Step 5 : 

You can now see the PMM Server Dashboard in the browser using the Host IP address. For my setup, the PMM Server’s IP Address is

192.168.80.10

. As soon as you put the IP in the browser, you will be asked to enter the credentials as seen in the image below. Default user and password are both:

admin

create a PMM server

And then you will be asked to change the password or skip.

PMM Server setup is completed after this step.

Installing PMM client on a Remote PostgreSQL server

I have a PostgreSQL 11.5 Server running on

192.168.80.20

. The following steps demonstrate how we can install and configure the PMM client to enable monitoring from the PMM server (

192.168.80.10

).

Before you proceed further, you must ensure that ports 80 and 443 are both enabled on the PMM server for the PG 11 Server to connect. In order to test that, I have used telnet to validate whether ports 80 and 443 are open on the PMM Server for the pg11 server.

[root@pg11]$ hostname -I
192.168.80.20

[root@pg11]$ telnet 192.168.80.10 80
Trying 192.168.80.10...
Connected to 192.168.80.10.
Escape character is '^]'.

[root@pg11]$ telnet 192.168.80.10 443
Trying 192.168.80.10...
Connected to 192.168.80.10.
Escape character is '^]'.

Step 6 :

There are very few steps you need to perform on the PostgreSQL server to enable it as a client for PMM server. The first step is to install the PMM Client on the PostgreSQL Database server as follows. Based on the current PMM release, I am installing

pmm2-client

today. But, this may change once we have a new PMM release.

$ sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ sudo yum install pmm2-client -y

Step 7 :

The next step is to connect the client (PostgreSQL server) to the PMM Server. We could use

pmm-admin config

in order to achieve that. Following is a simple syntax that you could use in general.

$ pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

The following are the flags and other options I could use with my setup.

flags        : --server-insecure-tls
               --server-url=https://admin:admin@192.168.80.10:443
               (--server-url should contain the PMM Server Host information)

node-address : 192.168.80.20
               (My PostgreSQL Server)

node-type    : generic
               (As I am running my PostgreSQL database on a Virtual Machine but not on a Container, it is generic.)

node-name    : pg-client
               (Can be any nodename you could use to uniquely identify this database server on your PMM Server Dashboard)

So the final syntax for my setup looks like the below. We can run this command as root or by using the sudo command.

Syntax : 7a

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443 192.168.80.20 generic pg-client

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443 192.168.80.20 generic pg-client
Checking local pmm-agent status...
pmm-agent is running.
Registering pmm-agent on PMM Server...
Registered.
Configuration file /usr/local/percona/pmm2/config/pmm-agent.yaml updated.
Reloading pmm-agent configuration...
Configuration reloaded.
Checking local pmm-agent status...
pmm-agent is running.

Syntax : 7b

You could also use a simple syntax such as following without

node-address, node-type, node-name

 :

$ pmm-admin config --server-insecure-tls --server-url=https://admin:admin@192.168.80.10:443

But when you use such a simple syntax as above,

node-address, node-type, node-name

are defaulted to certain values. If the defaults are incorrect due to your server configuration, you may better pass these details explicitly like I have done in the

syntax : 7a

. In order to validate whether the defaults are correct, you can simply use

# pmm-admin config --help

. In the following log, you see that the

node-address

  defaults to

10.0.2.15

which is incorrect for my setup. It should be

192.168.80.20

.

# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

Flags:
  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
      --version                Show application version
...
...
...
Args:
  [<node-address>]  Node address (autodetected default: 10.0.2.15)

Below is an example where the default settings were perfect because I had configured my database server the right way.

# pmm-admin config --help
usage: pmm-admin config [<flags>] [<node-address>] [<node-type>] [<node-name>]

Configure local pmm-agent

Flags:
  -h, --help                   Show context-sensitive help (also try --help-long and --help-man)
...
...
Args:
  [<node-address>]  Node address (autodetected default: 192.168.80.20)
  [<node-type>]     Node type, one of: generic, container (default: generic)
  [<node-name>]     Node name (autodetected default: pg-client)

Using steps 6 and 7a, I have finished installing the PMM client on the PostgreSQL server and also connected it to the PMM Server. If the above steps are successful, you should see the client listed under Nodes, as seen in the following image. Else, something went wrong.

Creating required users and permissions on the PostgreSQL server

In order to monitor your PostgreSQL server using PMM, you need to create a user *using* which the database stats can be collected by the PMM agent. However, starting from PostgreSQL 10, you do not need to grant SUPERUSER or use SECURITY DEFINER (to avoid granting SUPERUSER). You can simply grant the role

pg_monitor

to a user (monitoring user). In my next blog post, you will see how we could use SECURITY DEFINER to avoid granting SUPERUSER for monitoring PostgreSQL databases with 9.6 or older.

Assuming that your PostgreSQL Version is 10 or higher, you can use the following steps.

Step 1 : 

Create a postgres user that can be used for monitoring. You could choose any username;

pmm_user

in the following command is just an example.

$ psql -c "CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'secret'"

Step 2 : 

Grant

pg_monitor

role to the

pmm_user

.

$ psql -c "GRANT pg_monitor to pmm_user"

Step 3 : 

If you are not using localhost, but using the IP address of the PostgreSQL server while enabling monitoring in the next steps, you should ensure to add appropriate entries to enable connections from the

IP

and the

pmm_user

 in the

pg_hba.conf

file.

$ echo "host    all             pmm_user        192.168.80.20/32        md5" >> $PGDATA/pg_hba.conf
$ psql -c "select pg_reload_conf()"

In the above step, replace

192.168.80.20

with the appropriate PostgreSQL Server’s IP address.

Step 4 : 

Validate whether you are able to connect as

pmm_user

to the postgres database from the postgres server itself.

# psql -h 192.168.80.20 -p 5432 -U pmm_user -d postgres
Password for user pmm_user: 
psql (11.5)
Type "help" for help.

postgres=>

Enabling PostgreSQL Monitoring with and without QAN (Query Analytics)

Using PMM, we can monitor several metrics in PostgreSQL such as database connections, locks, checkpoint stats, transactions, temp usage, etc. However, you could additionally enable Query Analytics to look at the query performance and understand the queries that need some tuning. Let us see how we can simply enable PostgreSQL monitoring with and without QAN.

Without QAN

Step 1 :

In order to start monitoring PostgreSQL, we could simply use

pmm-admin add postgresql

. It accepts additional arguments such as the service name and PostgreSQL address and port. As we are talking about enabling monitoring without QAN, we could use the flag:

--query-source=none

to disable QAN.

# pmm-admin add postgresql --query-source=none --username=pmm_user --password=secret postgres 192.168.80.20:5432
PostgreSQL Service added.
Service ID  : /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea
Service name: postgres

Step 2 :

Once you have enabled monitoring, you could validate the same using

pmm-admin list

.

# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres             192.168.80.20:5432 /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/1d046311-dad7-467e-b024-d2c8cb7f33c2  /service_id/b2ca71cf-a2a4-48e3-9c5b-6ecd1a596aea

You can now access the PostgreSQL Dashboards and see several metrics being monitored.

With QAN

With PMM2, there is an additional step needed to enable QAN. You should create a database with the same name as the monitoring user (

pmm_user

here). And then, you should create the extension:

pg_stat_statements

in that database. This behavior is going to change on the next release so that you can avoid creating the database.

Step 1 : 

Create the database with the same name as the monitoring user. Create the extension:

pg_stat_statements

in the database.

$ psql -c "CREATE DATABASE pmm_user"
$ psql -c -d pmm_user "CREATE EXTENSION pg_stat_statements"

Step 2 : 

If

shared_preload_libraries

has not been set to

pg_stat_statements

, we need to set it and restart PostgreSQL.

$ psql -c "ALTER SYSTEM SET shared_preload_libraries TO 'pg_stat_statements'"
$ pg_ctl -D $PGDATA restart -mf
waiting for server to shut down.... done
server stopped
...
...
 done
server started

Step 3 :

In the previous steps, we used the flag:

--query-source=none

to disable QAN. In order to enable QAN, you could just remove this flag and use

pmm-admin add postgresql

without the flag.

# pmm-admin add postgresql --username=pmm_user --password=secret postgres 192.168.80.20:5432
PostgreSQL Service added.
Service ID  : /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
Service name: postgres

Step 4 : 

Once the above step is completed, you could validate the same again using

pmm-admin list

. But this time, you should see an additional service:

qan-postgresql-pgstatements-agent

.

# pmm-admin list
Service type  Service name         Address and port  Service ID
PostgreSQL    postgres             192.168.80.20:5432 /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

Agent type                  Status     Agent ID                                        Service ID
pmm-agent                   connected  /agent_id/13fd2e0a-a01a-4ac2-909a-cae533eba72e  
node_exporter               running    /agent_id/f6ba099c-b7ba-43dd-a3b3-f9d65394976d  
postgres_exporter           running    /agent_id/7039f7c4-1431-4518-9cbd-880c679513fb  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73
qan-postgresql-pgstatements-agent running    /agent_id/7f0c2a30-6710-4191-9373-fec179726422  /service_id/24efa8b2-02c2-4a39-8543-d5fd54314f73

After this step, you can now see the Queries and their statistics captured on the

Query Analytics Dashboard

.

Meanwhile, have you tried Percona Distribution for PostgreSQL? It is a collection of finely-tested and implemented open source tools and extensions along with PostgreSQL 11, maintained by Percona. PMM works for both Community PostgreSQL and also the Percona Distribution for PostgreSQL. Please subscribe to our blog posts to learn more interesting features in PostgreSQL.

Oct
29
2019
--

Column Histograms on Percona Server and MySQL 8.0

MySQL Column HIstorgrams

MySQL Column HIstorgramsFrom time to time you may have experienced that MySQL was not able to find the best execution plan for a query. You felt the query should have been faster. You felt that something didn’t work, but you didn’t realize exactly what.

Maybe some of you did tests and discovered there was a better execution plan that MySQL wasn’t able to find (forcing the order of the tables with STRAIGHT_JOIN for example).

In this article, we’ll see a new interesting feature available on MySQL 8.0 as well as Percona Server for MySQL 8.0: the histogram-based statistics.

Today, we’ll see what a histogram is, how you can create and manage it, and how MySQL’s optimizer can use it.

Just for completeness, histogram statistics have been available on MariaDB since version 10.0.2, with a slightly different implementation. Anyway, what we’ll see here is related to Percona Server and MySQL 8.0 only.

 

What is a histogram

We can define a histogram as a good approximation of the data distribution of the values in a column.

Histogram-based statistics were introduced to give the optimizer more execution plans to investigate and solve a query. Until then, in some cases, the optimizer was not able to find out the best possible execution plan because non-indexed columns were ignored.

With histogram statistics, now the optimizer may have more options because also non-indexed columns can be considered. In some specific cases, a query can run faster than usual.

Let’s consider the following table to store departing times of the trains:

CREATE TABLE train_schedule(
id INT PRIMARY KEY,
train_code VARCHAR(10),
departure_station VARCHAR(100),
departure_time TIME);

We can assume that during peak hours, from 7 AM until 9 AM, there are more rows, and during the night hours we have very few rows.

Let’s take a look at the following two queries:

SELECT * FROM train_schedule WHERE departure_time BETWEEN '07:30:00' AND '09:15:00';
SELECT * FROM train_schedule WHERE departure_time BETWEEN '01:00:00' AND '03:00:00';

Without any kind of statistics, the optimizer assumes by default that the values in the departure_time column are evenly distributed, but they aren’t. In fact, the first query returns more rows because of this assumption.

Histograms were invented to provide to the optimizer a good estimation of the rows returned. This seems to be trivial for the simple queries we have seen so far. But let’s think now about having the same table involved in JOINs with other tables. In such a case, the number of rows returned can be very important for the optimizer to decide the order to consider the tables in the execution plan.

A good estimation of the rows returned gives the optimizer the capability to open the table in the first stages in case it returns few rows. This minimizes the total amount of rows for the final cartesian product. Then the query can run faster.

MySQL supports two different types of histograms: “singleton” and “equi-height”. Common for all histogram types is that they split the data set into a set of “buckets”, and MySQL automatically divides the values into the buckets and will also automatically decide what type of histogram to create.

Singleton histogram

  • one value per bucket
  • each bucket stores
    • value
    • cumulative frequency
  • well suited for equality and range conditions

Equi-height histogram

  • multiple values per bucket
  • each bucket stores
    • minimum value
    • maximum value
    • cumulative frequency
    • number of distinct values
  • not really equi-height: frequent values are in separated buckets
  • well suited for range conditions

How to use histograms

The histogram feature is available and enabled on the server, but not usable by the optimizer. Without an explicit creation, the optimizer works the same as usual and cannot get any benefit from the histogram-bases statistics.

There is some manual operation to do. Let’s see.

In the next examples, we’ll use the world sample database you can download from here: https://dev.mysql.com/doc/index-other.html

Let’s start executing a query joining two tables to find out all the languages spoken on the largest cities of the world, with more than 10 million people.

mysql> select city.name, countrylanguage.language from city join countrylanguage using(countrycode) where population>10000000; 
+-----------------+-----------+ 
| name            | language  | 
+-----------------+-----------+ 
| Mumbai (Bombay) | Asami     | 
| Mumbai (Bombay) | Bengali   | 
| Mumbai (Bombay) | Gujarati  | 
| Mumbai (Bombay) | Hindi     | 
| Mumbai (Bombay) | Kannada   | 
| Mumbai (Bombay) | Malajalam | 
| Mumbai (Bombay) | Marathi   | 
| Mumbai (Bombay) | Orija     | 
| Mumbai (Bombay) | Punjabi   | 
| Mumbai (Bombay) | Tamil     | 
| Mumbai (Bombay) | Telugu    | 
| Mumbai (Bombay) | Urdu      | 
+-----------------+-----------+ 
12 rows in set (0.04 sec)

The query takes 0.04 seconds. It’s not a lot, but consider that the database is very small. Use the BENCHMARK function to have more relevant response times if you like.

Let’s see the EXPLAIN:

mysql> explain select city.name, countrylanguage.language from city join countrylanguage using(countrycode) where population>10000000; 
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+ 
| id | select_type | table           | partitions | type  | possible_keys       | key         | key_len | ref                               | rows | filtered | Extra       | 
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+ 
| 1  | SIMPLE      | countrylanguage | NULL       | index | PRIMARY,CountryCode | CountryCode | 3       | NULL                              | 984  | 100.00   | Using index | 
| 1  | SIMPLE      | city            | NULL       | ref   | CountryCode         | CountryCode | 3       | world.countrylanguage.CountryCode | 18   | 33.33    | Using where | 
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+-----------------------------------+------+----------+-------------+

Indexes are used for both the tables and the estimated cartesian product has 984 * 18 = 17,712 rows.

Now generate the histogram on the Population column. It’s the only column used for filtering the data and it’s not indexed.

For that, we have to use the ANALYZE command:

mysql> ANALYZE TABLE city UPDATE HISTOGRAM ON population WITH 1024 BUCKETS; 
+------------+-----------+----------+-------------------------------------------------------+ 
| Table      | Op        | Msg_type | Msg_text                                              | 
+------------+-----------+----------+-------------------------------------------------------+ 
| world.city | histogram | status   | Histogram statistics created for column 'Population'. | 
+------------+-----------+----------+-------------------------------------------------------+

We have created a histogram using 1024 buckets. The number of buckets is not mandatory, and it can be any number from 1 to 1024. If omitted, the default value is 100.

The number of chunks affects the reliability of the statistics. The more distinct values you have, the more the chunks you need.

Let’s have a look now at the execution plan and execute the query again.

mysql> explain select city.name, countrylanguage.language from city join countrylanguage using(countrycode) where population>10000000;
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+------------------------+------+----------+-------------+
| id | select_type | table           | partitions | type | possible_keys       | key         | key_len | ref                    | rows | filtered | Extra       |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+------------------------+------+----------+-------------+
|  1 | SIMPLE      | city            | NULL       | ALL  | CountryCode         | NULL        | NULL    | NULL                   | 4188 |     0.06 | Using where |
|  1 | SIMPLE      | countrylanguage | NULL       | ref  | PRIMARY,CountryCode | CountryCode | 3       | world.city.CountryCode |  984 |   100.00 | Using index |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+------------------------+------+----------+-------------+

mysql> select city.name, countrylanguage.language from city join countrylanguage using(countrycode) where population>10000000;
+-----------------+-----------+
| name            | language  |
+-----------------+-----------+
| Mumbai (Bombay) | Asami     |
| Mumbai (Bombay) | Bengali   |
| Mumbai (Bombay) | Gujarati  |
| Mumbai (Bombay) | Hindi     |
| Mumbai (Bombay) | Kannada   |
| Mumbai (Bombay) | Malajalam |
| Mumbai (Bombay) | Marathi   |
| Mumbai (Bombay) | Orija     |
| Mumbai (Bombay) | Punjabi   |
| Mumbai (Bombay) | Tamil     |
| Mumbai (Bombay) | Telugu    |
| Mumbai (Bombay) | Urdu      |
+-----------------+-----------+
12 rows in set (0.00 sec)

The execution plan is different, and the query runs faster.

We can notice that the order of the tables is the opposite as before. Even if it requires a full scan, the city table is in the first stage. It’s because of the filtered value that is only 0.06. It means that only 0.06% of the rows returned by the full scan will be used to be joined with the following table. So, it’s only 4188 * 0.06% = 2.5 rows. In total, the estimated cartesian product is 2.5 * 984 = 2.460 rows. This is significantly lower than the previous execution and explains why the query is faster.

What we have seen sounds a little counterintuitive, doesn’t it? In fact, until MySQL 5.7, we were used to considering full scans as very bad in most cases. In our case, instead, forcing a full scan using a histogram statistic on a non-indexed column lets the query to get optimized. Awesome.

 

Where are the histogram statistics

Histogram statistics are stored in the column_statistics table in the data dictionary and are not directly accessible by the users. Instead the INFORMATION_SCHEMA.COLUMN_STATISTICS table, which is implemented as a view of the data dictionary, can be used for the same purpose.

Let’s see the statistics for our table.

mysql> SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM)  
    -> FROM information_schema.column_statistics  
    -> WHERE COLUMN_NAME = 'population'\G
*************************** 1. row ***************************
           SCHEMA_NAME: world
            TABLE_NAME: city
           COLUMN_NAME: Population
JSON_PRETTY(HISTOGRAM): {
  "buckets": [
    [
      42,
      455,
      0.000980632507967639,
      4
    ],
    [
      503,
      682,
      0.001961265015935278,
      4
    ],
    [
      700,
      1137,
      0.0029418975239029173,
      4
    ],
...
...
    [
      8591309,
      9604900,
      0.9990193674920324,
      4
    ],
    [
      9696300,
      10500000,
      1.0,
      4
    ]
  ],
  "data-type": "int",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2019-10-14 22:24:58.232254",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 1024
}

We can see for any chunk the min and max values, the cumulative frequency, and the number of items. Also, we can see that MySQL decided to use an equi-height histogram.

Let’s try to generate a histogram on another table and column.

mysql> ANALYZE TABLE country UPDATE HISTOGRAM ON Region;
+---------------+-----------+----------+---------------------------------------------------+
| Table         | Op        | Msg_type | Msg_text                                          |
+---------------+-----------+----------+---------------------------------------------------+
| world.country | histogram | status   | Histogram statistics created for column 'Region'. |
+---------------+-----------+----------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM)  FROM information_schema.column_statistics  WHERE COLUMN_NAME = 'Region'\G
*************************** 1. row ***************************
           SCHEMA_NAME: world
            TABLE_NAME: country
           COLUMN_NAME: Region
JSON_PRETTY(HISTOGRAM): {
  "buckets": [
    [
      "base64:type254:QW50YXJjdGljYQ==",
      0.02092050209205021
    ],
    [
      "base64:type254:QXVzdHJhbGlhIGFuZCBOZXcgWmVhbGFuZA==",
      0.04184100418410042
    ],
    [
      "base64:type254:QmFsdGljIENvdW50cmllcw==",
      0.05439330543933054
    ],
    [
      "base64:type254:QnJpdGlzaCBJc2xhbmRz",
      0.06276150627615062
    ],
    [
      "base64:type254:Q2FyaWJiZWFu",
      0.1631799163179916
    ],
    [
      "base64:type254:Q2VudHJhbCBBZnJpY2E=",
      0.20083682008368198
    ],
    [
      "base64:type254:Q2VudHJhbCBBbWVyaWNh",
      0.23430962343096232
    ],
    [
      "base64:type254:RWFzdGVybiBBZnJpY2E=",
      0.3179916317991631
    ],
    [
      "base64:type254:RWFzdGVybiBBc2lh",
      0.35146443514644343
    ],
    [
      "base64:type254:RWFzdGVybiBFdXJvcGU=",
      0.39330543933054385
    ],
    [
      "base64:type254:TWVsYW5lc2lh",
      0.41422594142259406
    ],
    [
      "base64:type254:TWljcm9uZXNpYQ==",
      0.44351464435146437
    ],
    [
      "base64:type254:TWljcm9uZXNpYS9DYXJpYmJlYW4=",
      0.4476987447698744
    ],
    [
      "base64:type254:TWlkZGxlIEVhc3Q=",
      0.5230125523012552
    ],
    [
      "base64:type254:Tm9yZGljIENvdW50cmllcw==",
      0.5523012552301255
    ],
    [
      "base64:type254:Tm9ydGggQW1lcmljYQ==",
      0.5732217573221757
    ],
    [
      "base64:type254:Tm9ydGhlcm4gQWZyaWNh",
      0.602510460251046
    ],
    [
      "base64:type254:UG9seW5lc2lh",
      0.6443514644351465
    ],
    [
      "base64:type254:U291dGggQW1lcmljYQ==",
      0.7029288702928871
    ],
    [
      "base64:type254:U291dGhlYXN0IEFzaWE=",
      0.7489539748953975
    ],
    [
      "base64:type254:U291dGhlcm4gQWZyaWNh",
      0.7698744769874477
    ],
    [
      "base64:type254:U291dGhlcm4gYW5kIENlbnRyYWwgQXNpYQ==",
      0.8284518828451883
    ],
    [
      "base64:type254:U291dGhlcm4gRXVyb3Bl",
      0.891213389121339
    ],
    [
      "base64:type254:V2VzdGVybiBBZnJpY2E=",
      0.9623430962343097
    ],
    [
      "base64:type254:V2VzdGVybiBFdXJvcGU=",
      1.0
    ]
  ],
  "data-type": "string",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2019-10-14 22:29:13.418582",
  "sampling-rate": 1.0,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 100
}

In this case, a singleton histogram was generated.

Using the following query we can see more human-readable statistics.

mysql> SELECT SUBSTRING_INDEX(v, ':', -1) value, concat(round(c*100,1),'%') cumulfreq,         
    -> CONCAT(round((c - LAG(c, 1, 0) over()) * 100,1), '%') freq   
    -> FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets','$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist  
    -> WHERE schema_name  = 'world' and table_name = 'country' and column_name = 'region';
+---------------------------+-----------+-------+
| value                     | cumulfreq | freq  |
+---------------------------+-----------+-------+
| Antarctica                | 2.1%      | 2.1%  |
| Australia and New Zealand | 4.2%      | 2.1%  |
| Baltic Countries          | 5.4%      | 1.3%  |
| British Islands           | 6.3%      | 0.8%  |
| Caribbean                 | 16.3%     | 10.0% |
| Central Africa            | 20.1%     | 3.8%  |
| Central America           | 23.4%     | 3.3%  |
| Eastern Africa            | 31.8%     | 8.4%  |
| Eastern Asia              | 35.1%     | 3.3%  |
| Eastern Europe            | 39.3%     | 4.2%  |
| Melanesia                 | 41.4%     | 2.1%  |
| Micronesia                | 44.4%     | 2.9%  |
| Micronesia/Caribbean      | 44.8%     | 0.4%  |
| Middle East               | 52.3%     | 7.5%  |
| Nordic Countries          | 55.2%     | 2.9%  |
| North America             | 57.3%     | 2.1%  |
| Northern Africa           | 60.3%     | 2.9%  |
| Polynesia                 | 64.4%     | 4.2%  |
| South America             | 70.3%     | 5.9%  |
| Southeast Asia            | 74.9%     | 4.6%  |
| Southern Africa           | 77.0%     | 2.1%  |
| Southern and Central Asia | 82.8%     | 5.9%  |
| Southern Europe           | 89.1%     | 6.3%  |
| Western Africa            | 96.2%     | 7.1%  |
| Western Europe            | 100.0%    | 3.8%  |
+---------------------------+-----------+-------+

 

Histogram maintenance

Histogram statistics are not automatically recalculated. If you have a table that is very frequently updated with a lot of INSERTs, UPDATEs, and DELETEs, the statistics can run out of date very soon. Having unreliable histograms can lead the optimizer to the wrong choice.

When you find a histogram was useful to optimize a query, you need to also have a scheduled plan to refresh the statistics from time to time, in particular after doing massive modifications to the table.

To refresh a histogram you just need to run the same ANALYZE command we have seen before.

To completely drop a histogram you may run the following:

ANALYZE TABLE city DROP HISTOGRAM ON population;

 

Sampling

The histogram_generation_max_mem_size system variable controls the maximum amount of memory available for histogram generation. The global and session values may be set at runtime.

If the estimated amount of data to be read into memory for histogram generation exceeds the limit defined by the variable, MySQL samples the data rather than reading all of it into memory. Sampling is evenly distributed over the entire table.

The default value is 20000000 but you can increase it in the case of a large column if you want more accurate statistics. For very large columns, pay attention not to increase the threshold more than the memory available in order to avoid excessive overhead or outage.

 

Conclusion

Histogram statistics are particularly useful for non-indexed columns, as shown in the example.

Execution plans that can rely on indexes are usually the best, but histograms can help in some edge cases or when creating a new index is a bad idea.

Since this is not an automatic feature, some manual testing is required to investigate if you really can get the benefit of a histogram. Also, the maintenance requires some scheduled and manual activity.

Use histograms if you really need them, but don’t abuse them since histograms on very large tables can consume a lot of memory.

Usually, the best candidates for a histogram are the columns with:

  • values that do not change much over time
  • low cardinality values
  • uneven distribution

Install Percona Server 8.0, test and enjoy the histograms.

 

Further reading on the same topic: Billion Goods in Few Categories – How Histograms Save a Life?

 

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com