PHP Developer News

Finding Differences Between MySQL Servers

When one is responsible for promoting application development from Dev through the various environments such as QA, UAT, etc., through Production, it is often useful to ensure that configurations in test environments are comparable to the final production environment.  This is especially true with systems where a team of DBAs manage the servers.
Obviously, the difference in performance could be due to differences in hardware, storage, networking, software configuration, etc.  The question is how does one quickly and efficiently find the differences without having to run a lot of different commands and compare the output.  Fortunately, our Percona Toolkit has a couple of utilities that can make this much easier.  When you are tasked with supporting large numbers of servers, efficiency is paramount and this is where the toolkit can really help you!
You can find more information on the Percona Toolkit here: https://www.percona.com/software/database-tools/percona-toolkit
Let’s look at a few utilities that can help you spot differences in servers.  The first tool we will look at is pt-summary (https://www.percona.com/doc/percona-toolkit/LATEST/pt-summary.html).
The pt-summary tool will give you a summarized view of the server hardware.  It runs a series of Linux commands and formats the output into an easy-to-consume format.  You will get details on the following:
Operating System & Linux kernel
Virtualization
CPU (number of cores, speed, models, caching)
Memory consumption, swappiness, etc.
Mounted filesystems (mount points, size, usage, format, etc.)
Disk schedulers
Disk partitioning
Logical Volumes
RAID
Network configuration
Network traffic statistics
Network connections
Top processes
CPU utilization
By running the same report on the servers, you can spot-check differences in the hardware and Operating System.
When you want to find out differences in MySQL configuration, the toolkit has the perfect tool for this.  It is called pt-config-diff (https://www.percona.com/doc/percona-toolkit/LATEST/pt-config-diff.html).
This utility can connect to different servers and look at their configuration via MySQL Server Variables and then perform a “diff” on the values.  The tool creates a simple and concise output showing any differences between the servers.  Any differences will be immediately obvious.  Below is an example of the output:2 config differences
Variable my.primary.cnf my.replica.cnf
========================= =============== ===============
datadir /tmp/12345/data /tmp/12346/data
port 12345 12346Usage of these two utilities will allow you to quickly and easily find differences in either hardware or MySQL configuration between two or more servers.  These are the two tools that I always go to when I need to understand why two servers that are supposedly the same do not perform similarly.

Most Popular in Database