In my previous post I've presented a list of bugs reported by famous MySQL developers, DBAs, users and support engineers who used oprofile to find the problem or root cause of the problem they studied. I assume that some of my readers would like to follow their steps and try to use this tool while working on MySQL performance problems. Bug reports I mentioned contain outputs and, rarely, just some commands used. That's why I decided to write a kind of a basic "Howto" document. The tool does have a detailed enough manual, but few basic steps for quick use of oprofile to study MySQL-related problems should still help. At least I had always considered …[Read more]
Users had to identify the reasons for MySQL performance problems
somehow well before famous Performance Schema appeared in MySQL 5.5.3,
and even before Domas invented his Poor Man's Profiler. Poor users had
to use some OS-level tools, and among these tools the most
important one was, probably, oprofile.
oprofile is a system-wide statistical profiling tool for Linux available since 2001 and 2.4.x kernels. It was applied to MySQL for many …
Very often MySQL is much less stable than we realize. In this video I explain how to detect and dianose these MySQL stalls that last for 5-10 seconds or more.
The reason nobody knows about these issues is that they’re:
c) monitoring software can’t catch them
You can use pt-stalk to detect and diagnose such issues. All of the tools I have mentioned in this video are part of Percona Toolkit (no wonder they all start with PT), you can find the toolkit here:
Here’s the main commands I have discussed in this video:
Starting pt-stalk in foreground
Starting pt-stalk in background with email notification:
# pt-stalk …[Read more]
After some recent work with systemd I’ve realized it’s power and I can come clean that I am a fan. I realize that there are multitudes of posts out there with arguments both for and against systemd but let’s look at some nice ways to have systemd provide us with (but not limited to) pt-kill-as-a-service.
This brief post introduces you to a systemd unit file and how we can leverage them to enable pt-kill at startup, have it start after mysqld and ensure that MySQL is running by using the mysql service as a dependency of pt-kill. By using systemd to handle this we don’t have to complicate matters by ‘monitoring the monitor’ using hacky shell scripts, cron or utilities like monit.
So then, a quick primer on systemd, because lets face it, we’ve all been avoiding it. Systemd is not new but it made recent headlines in …[Read more]
Percona Toolkit is one of our most mature open source applications. Derived from Maatkit and Aspersa, Percona Toolkit has evolved significantly over the years. The software now contains 32 tools, over 4,000 tests, and has been downloaded over 250,000 times. Anyone who manages a database – from DBAs to system administrators to even software developers – benefits from Percona Toolkit’s ability to perform a variety of MySQL server and system tasks that are too difficult or complex to perform manually.
We continue to make Percona Toolkit better each month. Over the last 9 months alone Percona has had 6 releases and resolved nearly 50 issues.
Percona is pleased to announce the availability of Percona Toolkit 2.2.11. Released on Sept. 25, Percona Toolkit is a collection of advanced command-line tools to perform a variety of MySQL server and system tasks that are too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.
This release contains bug fixes for pt-query-digest, pt-mysql-summary, pt-stalk, as well as other tools and is the current GA (Generally Available) stable release in the 2.2 series. Downloads are available here and from the …[Read more]
Recently we faced an issue when Nagios reported significant
amount of “Locked” queries.
To investigate and debug the issue we needed to get more insight about the state of MySQL and the OS at the time the locks occurred.
This is how we got the much needed information:
wget http://bit.ly/1ltoZtk -O pt-stalk
chmod +x pt-stalk
mkdir -p /tmp/pt-stalk
sudo pt-stalk --daemonize --notify-by-email <EMAIL> --log /tmp/pt-stalk/pt-stalk.log --user root --dest /tmp/pt-stalk --function processlist --variable State --match Locked --threshold 20 --cycles=10 --sleep=15 --run-time=15
In this case we are running pt-stalk utility in background and trying to catch the case when there are 20+ Locked queries in processlist. When a match occurs, pt-stalk will collect a lot of OS and MySQL info and notify us by email.
On April 3 at 10 a.m. PST, I’ll be giving a webinar titled “5 Percona Toolkit Tools for MySQL That Could Save Your Day.” In this presentation you’ll learn how to perform typical but challenging MySQL database administration tasks.
My focus will be on the following tools:
- pt-query-digest, to select the queries you should try to improve to get optimal response times
- pt-archiver, to efficiently purge purge data from huge tables
- pt-table-checksum/pt-table-sync, to check if data on replicas is in sync with data on the master
- pt-stalk, to gather data when performance problems happen randomly or are very short
- pt-online-schema-change, to run …
pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs
It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.
From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):
pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to gather the data on demand without waiting for the trigger to happen. …[Read more]
I’m sure you’ve heard of Box Anemometer from the Box (MySQL) team, an excellent UI tool based on pt-query-digest. Now the guys from the box team offer us another killer UI tool based on pt-stalk, and I’m sure you will really appreciate to use it !
What is it ?
“RainGauge” consists of three parts :
- A set of scripts that collect data about the health of your system and your databases (based on pt-stalk)
- A process that push these data on a centralised place
- A very simple interface to navigate in the collected data
But the collection begins only when specific conditions are triggered, and you choose what these conditions are. Find below the general flowsheet :