Sep
03
2015
--

Percona Toolkit and systemd

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 the Linux world due to some of the major distros announcing their intentions to migrate upcoming releases to systemd.

What is it? Well due to it’s depth it is best described as a suite of management daemons, libraries and tools that will replace the traditional init scripts. So essentially remember how you start a service, mount a volume or read the system logs…well start forgetting all of that because systemd is disrupting this space. With systemd comes some really neat tricks for administering your machines and I’m really only beginning to see the tip of this iceberg. There is admittedly a lot to learn with systemd but this should serve as pragmatic entrée.

Systemd what? When did this happen?

Linux distribution Date released as default
Arch Linux 000000002012-10-01-0000October 2012
CoreOS 000000002013-10-01-0000October 2013 (v94.0.0)
Debian 000000002015-04-01-0000April 2015 (v8 aka jessie)
Fedora 000000002011-05-01-0000May 2011 (v15)
Gentoo Linux N/A
Mageia 000000002012-05-01-0000May 2012 (v2.0)
openSUSE 000000002012-09-01-0000September 2012 (v12.2)
Red Hat Enterprise Linux 000000002014-06-01-0000June 2014 (v7.0)
Slackware N/A
SUSE Linux Enterprise Server 000000002014-10-01-0000October 2014 (v12)
Ubuntu 000000002015-04-01-0000April 2015 (v15.04)

Lennart Poettering, the name frequently attached with systemd is seeking to modernize the most fundamental process(es) of the Linux startup system, bringing the paradigms of modern computing; concurrency, parallelism and efficiency. The dependency tree of processes and services is more intuitive and the structure of the underlying startup scripts are unified. I feel that the direction proposed by systemd is an evolutionary one which promotes consistency within the startup scripts enabling conventions that can be easier understood by a broader audience.

Systemd and Percona Toolkit

This post aims to show that we can rely on systemd to handle processes such as pt-kill, pt-stalk, and other daemonized scripts that we like to have running perpetually, are fired at startup and can be reinstated after failure.

The scenario is this; I want pt-kill to drop all sleeping connections from a certain application user, lets call them, ‘caffeinebob’, because they never close connections. Due to various reasons we can’t make changes in the application so we’re employing Percona Toolkit favourite, pt-kill, to do this for us. For convenience we want this result to persist across server restarts. In the olden days we might have some cron job that fires a shell script in combination with a sentinal file to ensure it’s running. I’m pretty sure that this kitty could be skinned many ways.

The systemd Unit File

After some research and testing, the below unit file will play nicely on a Centos 7 node with systemd at it’s core. In this example I am running Percona Server 5.6 installed using Percona’s yum repo with the mysql.service unit file generated at installation. I suspect that there could be some systemd deviation with other MySQL variants however, this configuration is working for me.

[Unit]
Description = pt-kill caffeinebob
After=syslog.target mysql.service
Requires=mysql.service
[Service]
Type = simple
PIDFile = /var/run/ptkill.pid
ExecStart = /usr/bin/pt-kill
--daemonize
--pid=/var/run/ptkill.pid
--interval=5
--defaults-file=/root/.my.cnf
--log=/var/log/ptkill.log
--match-user caffeinebob
--busy-time 10
--kill
--print
Restart=on-abort
[Install]
WantedBy=multi-user.target

Let’s examine the above and see what we’re working with. Systemd unit files have various biologies. The example above is a simple Service unit file. This means we are enacting a process controlled and supervised by systemd. The significance of the After directive is that this service will not attempt startup until after syslog.target and mysql.service have been called. The Required directive is makes ptkill.service dependant on the mysql.service startup being successful.

The next part, the [Service] grouping, details the actions to be taken by the service. The Type can be one of many but as it’s a simple call to a script I’ve used the simple type. We are describing the command and the handling of it. The ExecStart is evidently the pt-kill command that we would usually run from the shell prompt or from within a shell script. This is a very corse example because we can opt to parameterize the command with the assistance of an Environment file. Note the use of the Restart directive, used so that systemd can handle a reaction should a failure occur that interrupts the process.

Finally under the [Install] grouping we’re telling systemd that this service should startup on a multi user system, and could be thought of as runlevel 2 or 3 (Multiuser mode).

So providing that we’ve got all the relevant paths, users and dependencies in place, once you reboot your host, mysql.service should in order, initiate mysqld and when that dependency is met, systemd will initiate pt-kill with our desired parameters to cull connections that meet the criteria stipulated in our configuration. This means you rely on systemd to manage pt-kill for you and you don’t necessarily need to remember to start this or similar processes when you restart you node.

Start up & enable

Now to envoke our service manually and add enable it to work on start up we should run the following systemctl commands;

[moore@localhost ~]$ sudo systemctl start ptkill.service
[moore@localhost ~]$ sudo systemctl enable ptkill.service

No feedback but no errors so we can check the status of the service

[moore@localhost ~]$ sudo systemctl status ptkill -l
ptkill.service - keep pt-kill persistent across restarts
   Loaded: loaded (/etc/systemd/system/ptkill.service; enabled)
   Active: active (running) since Wed 2015-08-12 02:39:13 BST; 1h 19min ago
 Main PID: 2628 (perl)
   CGroup: /system.slice/ptkill.service
           ??2628 perl /usr/bin/pt-kill --daemonize --pid=/var/run/ptkill.pid --interval=5 --defaults-file=/root/.my.cnf --log=/var/log/ptkill.log --match-user caffeinebob --busy-time 10 --kill --print

Perfect we can also instruct systemd to disable this and|or stop our service when the application is changed and caffeinebob close() all those open connections.

[moore@localhost ~]$ sudo systemctl stop ptkill.service
[moore@localhost ~]$ sudo systemctl disable ptkill.service

Now after successful implementation we see that our process is running delightfully;

[moore@localhost ~]$ ps -ef | grep pt-kill
root      2547     1  0 02:37 ?        00:00:00 perl /usr/bin/pt-kill --daemonize --pid=/var/run/ptkill.pid --interval=5 --defaults-file=/root/.my.cnf --log=/var/log/ptkill.log --match-user caffeinebob --busy-time 10 --kill --print

Catch me if I fall

Lets issue a kill signal to the process and observe it’s behaviour using journalctl

[moore@localhost ~]$ sudo kill -SEGV 2547

This will write similar entries into the system log;

[moore@localhost ~]$ sudo journalctl -xn -f
Aug 12 02:39:13 localhost.localdomain sudo[2624]: moore : TTY=pts/1 ; PWD=/home/moore ; USER=root ; COMMAND=/bin/kill -SEGV 2547
Aug 12 02:39:13 localhost.localdomain systemd[1]: ptkill.service: main process exited, code=killed, status=11/SEGV
Aug 12 02:39:13 localhost.localdomain systemd[1]: Unit ptkill.service entered failed state.
Aug 12 02:39:13 localhost.localdomain systemd[1]: ptkill.service holdoff time over, scheduling restart.
Aug 12 02:39:13 localhost.localdomain systemd[1]: Stopping keep pt-kill persistent across restarts...
-- Subject: Unit ptkill.service has begun shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit ptkill.service has begun shutting down.
Aug 12 02:39:13 localhost.localdomain systemd[1]: Starting keep pt-kill persistent across restarts...
-- Subject: Unit ptkill.service has begun with start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit ptkill.service has begun starting up.
Aug 12 02:39:13 localhost.localdomain systemd[1]: Started keep pt-kill persistent across restarts.
-- Subject: Unit ptkill.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
--
-- Unit ptkill.service has finished starting up.
--
-- The start-up result is done.

Pt-kill flaps after the kill signal but systemd has been instructed to restart on failure so we don’t see caffeinebob saturate our processlist with sleeping connections.

Another bonus with this workflow is use within orchestration. Any standardized unit files can be propagated to your fleet of hosts with tools such as Ansible, Chef, Puppet or Saltstack.

Closing note

I’d love to hear from the pragmatists from the systemd world to understand if this approach can be improved or whether there are any flaws in this example unit file that would require addressing. This is very much a new-school of thought for me and feedback is both welcome and encouraged.

Thank you for your time, happy systemd-ing.

The post Percona Toolkit and systemd appeared first on Percona Data Performance Blog.

Mar
23
2015
--

Calling all MySQL DBAs: How do you use Percona Toolkit?

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.

pt2-2-releases-2014-2015

While Percona team members in Support, Consulting, and Managed Services are big drivers of identifying bugs and new features (driven mostly by Percona customer needs), the community of Percona Toolkit users plays a significant role in making the open source software what it is today.

We’d like to learn how we can make Percona Toolkit even better for your needs. Please take a brief survey so we can learn how you actually use the software. As a thank you for taking the survey, we are randomly giving away five $50 Amazon.com gift cards to participants. It’s a small token but one that we hope you’ll appreciate.

Recent additions to Percona Toolkit have included better Percona XtraDB Cluster support as well as multiple fixes and improvements to pt-online-schema-change, pt-kill, pt-query-digest, pt-stalk, and preparation for the MySQL 5.7 GA. Help us continue to improve Percona Toolkit by taking part in our survey. If you use Percona Toolkit and are attending Percona Live next month, please keep a look out for me. I’d like to hear about your experiences.

The post Calling all MySQL DBAs: How do you use Percona Toolkit? appeared first on MySQL Performance Blog.

Sep
30
2014
--

Percona Toolkit 2.2.11 for MySQL is now available

Percona ToolkitPercona 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 Percona Software Repositories.

Bugs Fixed:

  • Fixed bug #1262456: pt-query-digest didn’t report host details when host was using skip-name-resolve option. Fixed by using the IP of the host instead of its name, when the hostname is missing.
  • Fixed bug #1264580: pt-mysql-summary was incorrectly parsing key/value pairs in the wsrep_provider_options option, which resulted in incomplete my.cnf information.
  • Fixed bug #1318985: pt-stalk is now using SQL_NO_CACHE when executing queries for locks and transactions. Previously this could lead to situations where most of the queries that were waiting on query cache mutex were the pt-stalk queries (INNODB_TRX).
  • Fixed bug #1348679: When using -- -p option to enter the password for pt-stalk it would ask user to re-enter the password every time tool connects to the server to retrieve the information. New option --ask-pass has been introduced that can be used to specify the password only once.
  • Fixed bug #1368379: A parsing error caused pt-summary ( specifically the report_system_info module) to choke on the “Memory Device” parameter named “Configured Clock Speed” when using dmidecode to report memory slot information.

Details of the release can be found in the release notes and the 2.2.11 milestone at Launchpad. Bugs can be reported on the Percona Toolkit launchpad bug tracker.

The post Percona Toolkit 2.2.11 for MySQL is now available appeared first on Percona Performance Blog.

Mar
22
2013
--

5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar

Percona ToolkitOn 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 ALTER TABLE statements on large tables without downtime

You can reserve your spot for this free MySQL webinar now. If you can’t attend you’ll receive an link to the recording and the slides after the webinar.

More info on “5 Percona Toolkit Tools for MySQL That Could Save Your Day”Percona Toolkit for MySQL is a must-have set of tools that can help serious MySQL administrators perform tasks that are common but difficult to do manually. Years of deployments by tens of thousands of users including some of the best-known Internet sites, have proven the reliability of the Percona Toolkit tools for MySQL.

In this presentation, you will learn how you can use some of these tools to solve typical, real-world MySQL database administration challenges, such as:

  • Selecting which queries you should try to optimize to get better response times
  • How to efficiently purge data from a huge table without putting too much load on your server
  • How to check which tables are affected when someone accidentally wrote to a replica and fix the problem without rebuilding the tables
  • How to gather data for performance problems that happen randomly and last only a few seconds
  • How to run ALTER TABLE on your largest tables without downtime

REGISTER NOW for this MySQL webinar
DOWNLOAD Percona Toolkit for MySQL

The post 5 Percona Toolkit Tools for MySQL That Could Save Your Day: April 3 Webinar appeared first on MySQL Performance Blog.

Jan
03
2013
--

Percona Toolkit by example – pt-stalk

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.

There are some common options that you can use in all examples, so I recommend you to read the documentation if you have any specific questions.

Be prepared! It’s wise to have pt-stalk running 24/7, because problems such as MySQL lockups or spikes of activity typically leave no evidence to use in root cause analysis. By continuously running pt-stalk, you’ll have the data it gathers when the trouble occurs.

Let’s look at some specific “pt-stalk recipes.”

Just collect the information:

pt-stalk will collect the information and will exit after that.

$ pt-stalk --no-stalk  -- --defaults-file=./my.default.cnf

Every hour for one day:

Collect the information every one hour (–sleep=3600) 24 times (–iterations=24) without wait for any condition (–threshold=0) and run in background (–daemonize).

$ pt-stalk --sleep=3600 --threshold=0 --iterations=24 \
  --daemonize -- --defaults-file=./my.default.cnf

A host has more than 10 connections:

Collect the all information when the server 10.0.0.23 (–match 10.0.0.23) have more than 10 (–threshold 10) connections opened. You can use any variable from the “show processlist” command, in this case, I’m using the “Host” variable.

$ pt-stalk --function processlist --variable Host\
  --match 10.0.0.23 --threshold 10 -- --defaults-file=./my.default.cnf

More than one variable:

In some cases, you want to check more than one variable, in those cases, you will have to write a small scrip to do this.

The script:
The script must contain a shell function called “trg_plugin” and that function must return a number, this number will be the one that pt-stalk will use to match against the –threshold option.

$ cat  pt-stalk-function 
# This function will count the commands that come form localhost ($4 in this case) 
# and with execution time ($7) greater than 5000.
# 
# The  $EXT_ARGV is the connection options that you will send it to the pt-stalk.
#   
#  $2    $3         $4          $5   $6        $7     $8      $9
#+-----+----------+-----------+----+---------+------+-------+------------------+
#| Id  | User     | Host      | db | Command | Time | State | Info             |
#+-----+----------+-----------+----+---------+------+-------+------------------+
#

trg_plugin() {
	mysqladmin $EXT_ARGV processlist |  awk -F\| 'BEGIN{counter=0} 
                     { if ($4 ~ /localhost/ && $7 > 5000 ) counter++ ; } END {print counter}'
}

The pt-stalk command:
Collect all information when the function called trg_plugin inside the script ./pt-stalk-function (–function ../pt-stalk-function) return more than 100 (–threshold 100)

$ pt-stalk --function ./pt-stalk-function \
  --threshold 100  --  --defaults-file=./my.default.cnf

Custom collector with plugins:

Plugins are useful to collect information that it is not included in the pt-stalk by default. For example, if you want to collect pid status information from /proc/[mysqlpid]/status you can use plugins for this.

The plugin:
The script in this case contain a shell function called “before_collect” and pt-stalk will run this function before collect the information (you can collect after, before and after, etc, please check the documentation for more information)

$ cat  pt-stalk-pidplugin
# This plugin will collect the /proc/[mysqlpid]/status from all mysqld pids running in 
# this box and it will store the information in a file in the default directory and 
# with the default prefix and adding the pid number and -status at the end. 
# For example: 2013_01_02_22_58_55-2082-status

before_collect()
{
        for __p in $(pidof mysqld); do 
            cat /proc/${__p}/status > ${OPT_DEST}/${prefix}-${__p}-status ; 
        done
}

The pt-stalk command:
Before collect the information, it will run the plugin ./pt-stalk-pidplugin (–plugin ./pt-stalk-pidplugin)

$ pt-stalk --plugin ./pt-stalk-pidplugin \
  --threshold 10  -- --defaults-file=./my.default.cnf

Have any comments or questions? Just let me know below!

The post Percona Toolkit by example – pt-stalk appeared first on MySQL Performance Blog.

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