MySQL pt-summaryIn part 1 of this series, we introduced you to the approach used by the Percona Support team to inspect MySQL servers, which is based on a triad of tools from the Percona Toolkit used to gather diagnostic information. In this article, we will be focusing on the first of these tools, the pt-summary.

pt-summary is, in fact, not database-related and all about the server that is hosting the service. The suffix “summary” derives from the fact the execution of the tool generates a “single-page” report containing information about the hardware specifications and operating system settings. It aims at providing a starting point for the server assessment, as noted in the introduction post:

To start with, this box the service is running on, what kind of server is this? How much memory is available? How many cores? What about storage, is it fast, is there enough disk space? Is it running on CentOS, Ubuntu, or something else? Is it a VM?! Which kernel version? Is it swapping … is there any swap space configured at all?

To refresh your memory, if you have the Percona Toolkit installed on the server, you can run pt-summary as simply as:

But it is usually better to redirect the report to a file you can save for later, which also allows for better searching:

The next sections break down the generated report in various parts to facilitate its assessment but mostly follow the order in which they are presented. Note that the data presented in each section is used for illustration purposes and does not represent a single server; it is a mix-and-match taken from various different reports, with sensitive data as hostname and IP addresses altered for obvious reasons when necessary.

The Heart and Soul of the Server

The header of the report provides information about the system platform:

A trained eye will scan through these lines pulling information and starting the work of picturing the server as if rendering the image from a series of pixels collected here and there.

When I receive a pt-summary report from a customer, I will check the Date just to be sure it is recent enough and double-check the hostname – this is important in order to match it with the data provided by the other tools. Uptime will tell us if the server has been recently restarted, or maybe it is a new one, and that may impact other metrics and statistics in the report, such as memory caches. The load averages provide a glimpse of the recent load of the server when the report was taken, a metric that should be coupled with the processors’ information for better interpretation.

The Linux distribution and release is a piece of information I will save for later, in case I have to download the specific database packages to check on something or to get the mysqld symbols, for instance. Likewise, knowing this is a bare metal server or a Virtualized one, particularly in the case of a virtual machine, influences my assessment; there are certain scenarios and situations known to affect the performance of a server in a virtualized environment, such as the famous noisy neighbors. The section above may also contain the cloud provider details, if applicable.

Processing Power

The next section covers the processors in the server, which provides a good idea of its processing power and parallelization. There are a number of MySQL settings that should be tuned according to these, which we will be discussing in the next two posts. For now, take note of the number of available cores. Here is an example of how this section may look like:

Note that not all cores in the example above are running at the same speed; why is that, and is this a problem? 

The default power schema in most Linux distributions operates on an “on-demand” basis, which scales down CPU frequencies while they are idle in order to save energy; the name of this scaling governor mode is powersave. Having such a setting in place in a dedicated database server may or may not make sense – it depends on your workload and what your priorities are.

In many cases, the expectation is to have the server running at full capacity; that can be accomplished with the performance governor. How to change the power mode varies between Linux distributions, and even among different versions of the same one, but in most cases, the adjustment can be done with a single command run as root:

Check the exact details for your Linux system as well as how to make the change persist a server restart.

Memory

Another big piece of the puzzle we are trying to assemble (or make sense of) is obviously memory: not only how much is available, but also how it is being used and managed. Here’s the memory section excerpt from the pt-summary report of yet a different server:

There are basically three pieces of information we obtain from this section:

  • How much memory is there available on this server
  • How much memory remains available
  • If there is any swap space allocated and what is the server inclination to make use of it

The Total memory is only the starting point. Besides the amount of memory reported as being Free, note how much memory is being used for the OS cache: this is not typical for an InnoDB-based MySQL server. Normally, we would dedicate the majority of the available memory to the InnoDB Buffer Pool: more on this in the next post.

As for swap space: I feel this has become a controversial subject in the last few years, with the rise of the cloud and disposable servers. If you route your read traffic among a pool of servers, it might be that you would prefer to have one of these face an immediate termination by the kernel’s Out Of Memory (OOM) monitor rather than tap onto swap space and perform slower. But that would be an edge case, and certainly restricted to replicas – the cost and risk of losing a master like this is rather high. In general, a better practice is to have swap space in place, for safeguard, but instruct the OS to only make use of it when absolutely necessary. There is a setting to tune this: swappiness controls the kernel’s tendency to swap memory pages. Higher values for this setting increase the aggressiveness of the kernel to swap whereas lower values decrease the likelihood of this happening. For dedicated database servers, we used to advise setting swappiness to zero, but that proved to be too much for more recent kernels; nowadays, setting it to 1 is a safer approach. You can make the change very easily as root:

THP and a Missing Point

The very last sections of a pt-summary report cover an important complementary point – while leaving another important one behind – whether Transparent Huge Pages (THP) is enabled on the server:

The most succinct explanation I read on why you should have it disabled comes from the MongoDB manual: 

(…) database workloads often perform poorly with THP enabled, because they tend to have sparse rather than contiguous memory access patterns. When running MongoDB on Linux, THP should be disabled for best performance.

And the same is true for MySQL. I will provide a bit more information on this topic in the next post. For now, know you can disable THP in most systems with the following commands:

Ideally, however, you should start the server with them already disabled. You can create a systemd service file for this, as explained in the link above, or alter the kernel boot options in grub.conf to include transparent_hugepage=never .

A notable absence from this section is NUMA-related information, which is essential to analyze memory unbalance on NUMA systems equipped with lots of it. I kindly invite you to support this request.

Storage

This is the longest part of a pt-summary report and includes details about the disk partitioning and arrangement (LVM), IO schedulers, RAID controllers, inodes, and mount points. The latter is the first of the sections presented, and we can usually infer where is MySQL data stored; here is a simplified example:

The topic of which filesystem to use for a database such as MySQL is a popular one but there is no need to lose your sleep over it; between EXT4 and XFS, you will be fine. NFS should be only considered for storing backup files. You should pay attention to the mount options you employ though, as proper ones are determinants for improved performance. In particular, we must avoid having the kernel update timestamps on database files every time they are accessed (atime), hence we should mount the partition containing the data with noatime. The same can be done in regards to directories with nodiratime, though the impact is smaller. If the drives are behind a RAID controller equipped with a battery-backed unit (BBU) for caching data, then you don’t need to “enforce proper on-disk ordering of journal commits” to avoid risking “filesystem corruption in case of power failure” (from the mount manual page), thus you can also mount the data partition with the nobarrier option for improved performance.

Here’s the section of the pt-summary report showing RAID Controller details (make sure you have the controller management software installed on the server, or this section will be empty); note the presence of a BBU cache:

Speaking of RAIDs, we used to advise against RAID-5 (as all the checksum required to ensure data integrity would imply an excessive write penalty) and in favor of RAID-10 volumes spanned across multiple disks and supported by a controller with a BBU, but things are changing with the advent of modern storage technology (SSD and beyond). It also affects the choice for the I/O scheduler:

Back in the day, cfq used to be the default I/O scheduler in most Linux distributions but it has been shown to be a bad choice for database workloads. deadline remains the most indicated for RAID setup with spindle disks but noop is better for SSDs. Changing the scheduler is easy to do on the fly:

Changing the default scheduler in a persistent way requires editing the GRUB_CMDLINE_LINUX_DEFAULT line in grub.conf to include option elevator=noop .

Network

I reckon this is the section I least pay attention to in a pt-summary report. It provides basic details about the controllers and devices, so we get to see how the server is configured, but, to look beyond this, we need more detailed and numerous samples than what is provided here. We do get these from pt-stalk though, which we will be covering in a future post in this blog series.

A Quick Look Into What is Running

The list with the Top Processes comes from a single top sample and the intent of it is simply to provide an insight into what is running at the time:

In a dedicated database server, we expect mysqld to be the top process and this sample does not disappoint in this regard. When looking at this section of the report my eyes will automatically focus on the VIRT(ual) and the RES(idual) memory columns: I’ll compare the values between the two of them but also in view of the total memory in the server (which in this case was 78G – this excerpt comes from a different pt-summary report to the one I used to illustrate the Memory section). Together, these three pieces of information (total memory plus RES and VIR for mysqld) are useful to create a first impression of how well balanced is memory usage in this server. I can almost guess the size of the Buffer Pool, but then I’m often wrong; I tend to underestimate how much memory is being used by temporary tables …

In the Next Post …

What MySQL is running on this server and how it is configured in view of the available resources in its host? pt-mysql-summary is the perfect match for pt-summary and provides great insight into MySQL configuration.

Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments