Nov
21
2017
--

Percona Toolkit 3.0.5 is Now Available

Percona ToolkitPercona announces the release of Percona Toolkit 3.0.5 on November 21, 2017.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

You download Percona Toolkit packages from the web site or install from official repositories.

This release includes the following changes:

New Features:

  • PT-216: The pt-mongodb-query-digest supports MongoDB versions lower than 3.2; incorrect output was fixed.
  • PT-182: The pt-summary, pt-mysql-summary, pt-mongodb-summary commands provide output in the the JSON format.
  • pt-mysql-summary shows the output of the SHOW SLAVE HOSTS command.
  • pt-table-sync supports replication channels (requires MySQL version 5.7.6 or higher)
  • PMM-1590: MongoDB Profiler for Percona Management and Monitoring and Percona Toolkit has been improved.

Bug fixes:

  • pt-mext would fail if the Rsa_public_key variable was empty.
  • PT-212: pt-mongodb-query-digest --version produced incorrect values.
  • PT-202: pt-online-schema-change incorrectly processed virtual columns.
  • PT-200: pt-online-schema-change command reported an error when the name of an index contained UNIQUE as as the prefix or suffix.
  • pt-table-checksum did not detect differences on a system with the ROW based replication active.
  • PT-196: pt-onine-schema-change --max-load paused if a status variable was passed 0 as the value.
  • PT-193: pt-table-checksum reported a misleading error if a column comment contained an apostrophe. For more information, see #1708749.
  • PT-187: In some cases, pt-table-checksum did not report that the same table contained different values on the master and slave.
  • PT-186: pt-online-schema-change --alter could fail if field names contained upper case characters. For more information, see #1705998.
  • PT-183: In some cases pt-mongodb-query-digest could not connect to a database using authentication.
  • PT-167: In some cases, pt-kill could ignore the value of the --busy-time parameter. For more information, see #1016272.
  • PT-161: When run with the --skip-check-slave-lag, the pt-table-checksum could could fail in some cases.
Sep
18
2017
--

Webinar Tuesday, September 19, 2017: A Percona Support Engineer Walkthrough for pt-stalk

pt-stalkJoin Percona’s, Principal Support Engineer, Markus Albe as he presents A Percona Support Engineer Walkthrough for pt-stalk on Tuesday, September 19, 2017, at 10:00 am PDT / 1:00 pm EDT (UTC-7).

As a support engineer, I get dozens of pt-stalk captures from our customers containing samples of iostat, vmstat, top, ps, SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST and a multitude of other diagnostics outputs.

These are the tools of the trade for performance and troubleshooting, and we must learn to digest these outputs in an effective and systematic way. This allows us to provide high-quality service to a large volume of customers.

In this presentation, I will share the knowledge we’ve gained working with this data, and how to apply it to your database environment. We will learn to setup, capture data, write plugins to trigger collection and to capture custom data, look at our systematic approach and learn what data to read first and how to unwind the tangled threads of pt-stalk.

By the end of this presentation, you will have expert knowledge on how to capture diagnostic metrics at the right time and have a generic approach to digest the captured data. This allows you to diagnose and solve many of problems common to MySQL setups.

Resister for the webinar here.

Marcos AlbeMarcos Albe, Principal Technical Services Engineer

Marcos Albe has been doing web development for over ten years, providing solutions for various media and technology companies of different sizes. He is now a member of the Percona Support Team. Born and raised in the city of Montevideo, Uruguay, he became passionate about computers at the age of 11, when he got a 25Mhz i386-SX. Ten years later, he became one of the pioneers in telecommuting in Uruguay while leading the IT efforts for the second largest newspaper in the country.

Sep
05
2017
--

Webinar Wednesday, September 6, 2017: Percona Roadmap and Software News Update – Q3 2017

Percona Roadmap

Percona RoadmapCome and listen to Percona CEO Peter Zaitsev on Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7) discuss the Percona roadmap, as well as what’s new in Percona open source software.

 

During this webinar Peter will talk about newly released features in Percona software, show a few quick demos and share with you highlights from the Percona open source software roadmap. This discussion will cover Percona Server for MySQL and MongoDB, Percona XtraBackup, Percona Toolkit, Percona XtraDB Cluster and Percona Monitoring and Management.

Peter will also talk about new developments in Percona commercial services and finish with a Q&A.

Register for the webinar before seats fill up for this exciting webinar Wednesday, September 6, 2017 at 10am PT / 1pm ET (UTC-7).

Peter ZaitsevPeter Zaitsev, Percona CEO and Co-Founder

Peter Zaitsev co-founded Percona and assumed the role of CEO in 2006. As one of the foremost experts on MySQL strategy and optimization, Peter leveraged both his technical vision and entrepreneurial skills to grow Percona from a two-person shop to one of the most respected open source companies in the business. With over 140 professionals in 30+ countries, Peter’s venture now serves over 3000 customers – including the “who’s who” of internet giants, large enterprises and many exciting startups. Percona was named to the Inc. 5000 in 2013, 2014, 2015 and 2016. Peter was an early employee at MySQL AB, eventually leading the company’s High Performance Group. A serial entrepreneur, Peter co-founded his first startup while attending Moscow State University, where he majored in Computer Science. Peter is a co-author of High Performance MySQL: Optimization, Backups, and Replication, one of the most popular books on MySQL performance. Peter frequently speaks as an expert lecturer at MySQL and related conferences, and regularly posts on the Percona Database Performance Blog. Fortune and DZone have both tapped Peter as a contributor, and his recent ebook Practical MySQL Performance Optimization is one of percona.com’s most popular downloads.
Aug
02
2017
--

Percona Toolkit 3.0.4 is Now Available

Percona Server for MongoDB

Percona ToolkitPercona announces the release of Percona Toolkit 3.0.4 on August 2, 2017.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

You download Percona Toolkit packages from the web site or install from official repositories.

This release includes the following changes:

New Features

  • PT-90: Added collection of information about prepared statements by pt-stalk when Performance Schema is enabled. For more information, see #1642750.
  • PT-91: Added the --preserve-triggers option for pt-online-schema-change to support AFTER triggers.
  • PT-138: Added --output-format option for pt-mongodb-summary to choose between JSON format and the default plain text.
  • PT-141: Added the --output-format=csv parameter for pt-archiver to archive rows in CSV format.
  • PT-142: Added the --only-same-schema-fks option for pt-online-schema-change to check foreigns keys only on tables with the same schema as the original table. This should speed up the tool’s execution, but keep in mind that if you have foreign keys referencing tables in other schemas, they won’t be detected. For more information, see #1690122.
  • PT-153: Added the --check-unique-key-change option for pt-online-schema-change to abort if the specified statement for --alter is trying to add a unique index. This is supposed to avoid adding duplicate keys that might lead to silently losing data.
  • PT-173: Added the --truncate-replicate-table option for pt-table-checksum to ensure stale data is removed.

Bug fixes

  • PT-136: Fixed pt-table-checksum to support tables that have columns with different collations or charsets. For more information, see #1674266.
  • PT-143: Fixed primary key handling by pt-archiver. For more information, see #1691630.
  • PT-144: Limited constraint name in the new table when running pt-online-schema-change. For more information, see #1491674.
  • PT-146: Fixed the --no-check-binlog-format option for pt-table-checksum to work as expected.
  • PT-148: Fixed the use of uninitialized value in printf() for pt-online-schema-change. For more information, see #1693614.
  • PT-151: Fixed pt-table-sync to prevent field type point to be taken as decimal.
  • PT-154: Reverted PT-116 to remove the --use-insert-ignore option from pt-online-schema-change.
  • PT-161: Fixed the --skip-check-slave-lag feature for pt-table-checksum to safely check for undefined values.
  • PT-178: Fixed regression in --check-slave-lag option for pt-online-schema-change.
  • PT-180: Fixed regression in --skip-check-slave-lag option for pt-online-schema-change.
  • PT-181: Fixed syntax error in pt-online-schema-change.

Other Improvements

  • PT-162: Updated list of tables ignored by pt-table-checksum.

You can find release details in the release notes. Report bugs in Toolkit’s launchpad bug tracker.

May
19
2017
--

Percona Toolkit 3.0.3 is Now Available

Percona Server for MongoDB

Percona ToolkitPercona announces the release of Percona Toolkit 3.0.3 on May 19, 2017.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

You download Percona Toolkit packages from the web site or install from official repositories.

This release includes the following changes:

New Features

  • Added the --skip-check-slave-lag option for pt-table-checksum, pt-online-schema-change, and pt-archiverdp.This option can be used to specify a list of servers where to skip checking for slave lag.
  • 1642754: Added support for collecting replication slave information in pt-stalk.
  • PT-111: Added support for collecting information about variables from Performance Schema in pt-stalk. For more information, see 1642753.
  • PT-116: Added the --[no]use-insert-ignore option for pt-online-schema-change to force or prevent using IGNORE on INSERT statements. For more information, see 1545129.

Bug Fixes

  • PT-115: Fixed OptionParser to accept repeatable DSNs.
  • PT-126: Fixed pt-online-schema-change to correctly parse comments. For more information, see 1592072.
  • PT-128: Fixed pt-stalk to include memory usage information. For more information, see 1510809.
  • PT-130: Fixed pt-mext to work with non-empty RSA public key. For more information, see 1587404.
  • PT-132: Fixed pt-online-schema-change to enable --no-drop-new-table when --no-swap-tables and --no-drop-triggers are used.

You can find release details in the release notes. Report bugs in Toolkit’s launchpad bug tracker.

Apr
17
2017
--

The mysqlpump Utility

mysqlpump

mysqlpumpIn this blog, we’ll look at the

mysqlpump

 utility.

mysqlpump

 is a utility that performs logical backups (which means backing up your data as SQL statements instead of a raw copy of data files). It was added in MySQL Server version 5.7.8, and can be used to dump a database or a set of databases to a file and then loaded on another SQL server (not necessarily a MySQL server).

Its usage is similar to

mysqldump

, but it includes a new set of features. Many of the options are the same, but it was written from scratch to avoid being limited to

mysqldump

 compatibility.

The Main Features Include:

  • To make the dump process faster, it allows parallel processing of databases and objects within databases.
  • There are more options to customize your dumps and choose which databases and objects to dump (tables, stored programs, user accounts), using the
    --include-*

     and 

    --exclude-*

     parameters.

  • User accounts can be dumped now as
    CREATE USER

     and

    GRANT

     statements, instead of inserting directly to the MySQL system database.

  • Information between the client and the server can be compressed using the
    --compress

     option. This feature is very useful for remote backups, as it saves bandwidth and transfer time. You can also compress the output file using

    --compress-output

    , which supports ZLIB and LZ4 compression algorithms.

  • It has an estimated progress indicator. This is really useful to check the current status of the dump process. You can see the total amount of rows dumped and the number of databases completed. It also reports an estimate of the total time to complete the dump.
  • Creation of secondary indexes for InnoDB tables happens after data load for shorter load times.

Exclude/Include:

This feature provides more control over customizing your dumps, and filter the data that you need. Using this feature, you can be more selective with the data you want to dump (databases, tables, triggers, events, routines, users) and save file size, process time and transferring time while copying/moving the file to another host.

Keep in mind that there are some options that are mutually exclusive: e.g., if you use the

--all-databases

 option, the

--exclude-databases

  parameter won’t take effect. By default,

mysqlpump

 will not dump the following databases unless you specify them using the

--include-databases

 option:

INFORMATION_SCHEMA

,

performance_schema

,

ndbinfo

  and

sys

.

Values for these options need to be declared by comma-separated listing. Using a “%” as a value for any of the exclude/include options acts as a wildcard. For example, you can dump all databases starting with “t” and “p” by adding the option

--include-databases=t%,p%

  to the command line.

For users, routines, triggers and events,

mysqlpump

 has

--include-*

 and

--exclude-*

 options with similar usage. Some specific notes:

  • Triggers are dumped by default, but you can also filter them using the
    --include-triggers

    /

    --exclude-triggers

     options

  • Routines and events are not dumped by default, and need to be specified in the command line with
    --routines

     and

    --events

    , or the corresponding

    --include

     and 

    --exclude

     options

  • Keep in mind that if a stored procedure and a function have the same name, then include/exclude applies to both

Parallel Processing:

This feature allows you to process several databases, and tables within the databases, in parallel. By default,

mysqlpump

 uses one processing queue with two threads. You can increase the number of threads for this default queue with

--default-parallelism

. Unless you create additional queues, all the databases and/or tables you elect to dump go through the default queue.

To create additional queues you can use the 

--parallel-schemas

 option, which takes two parameters: the number of threads for the queue and the sub-set of databases this queue processes.  As an example, you could run:

mysqlpump --include-databases=a,b,c,d,e,f,g,h --default-parallelism=3 --parallel-schemas=4:a,b

so that schemas c, d, e, f, g and h are processed by the default queue (which uses three threads), and then tables from schemas a and b are processed by a separate queue (that uses four threads). Database names should be included as a comma-separated list:

$ mysqlpump --parallel-schemas=4:example1,example2,example3 --parallel-schemas=3:example4,example5 > examples.sql
Dump progress: 0/1 tables, 250/261184 rows
Dump progress: 24/30 tables, 1204891/17893833 rows
Dump progress: 29/30 tables, 1755611/17893833 rows
Dump progress: 29/30 tables, 2309111/17893833 rows
...
Dump completed in 42424 milliseconds

User Accounts:

User accounts can be dumped using this tool. Here’s a comparison of our Percona Tool

pt-show-grants

 versus

mysqlpump

 to check their differences.

By default,

mysqlpump

 doesn’t dump user account definitions (even while dumping the MySQL database). To include user accounts on the dump, you must specify the

--users

 option.

Here’s an example on how use

mysqlpump

 to get only user accounts dumped to a file:

$ mysqlpump --exclude-databases=% --exclude-triggers=% --users
-- Dump created by MySQL dump utility, version: 5.7.8-rc, linux-glibc2.5 (x86_64)
-- Dump start time: Thu Aug 27 17:10:10 2015
-- Server version: 5.7.8
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
CREATE USER 'msandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%';
CREATE USER 'msandbox_ro'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'127.%';
CREATE USER 'msandbox_rw'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'127.%';
CREATE USER 'rsandbox'@'127.%' IDENTIFIED WITH 'mysql_native_password' AS '*B07EB15A2E7BD9620DAE47B194D5B9DBA14377AD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%';
CREATE USER 'furrywall'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*AB8D50A9E3B8D1F3ACE85C54736B5BF472B44539' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;
GRANT USAGE ON *.* TO 'furrywall'@'localhost';
CREATE USER 'msandbox'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost';
CREATE USER 'msandbox_ro'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, EXECUTE ON *.* TO 'msandbox_ro'@'localhost';
CREATE USER 'msandbox_rw'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON *.* TO 'msandbox_rw'@'localhost';
CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6C387FC3893DBA1E3BA155E74754DA6682D04747' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
CREATE USER 'testuser'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*6E543F385210D9BD42A4FDB4BB23FD2C31C95462' REQUIRE NONE PASSWORD EXPIRE INTERVAL 30 DAY ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'testuser'@'localhost';
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- Dump end time: Thu Aug 27 17:10:10 2015
Dump completed in 823 milliseconds

As you can see, above the tool makes sure the session uses known values for timezone and character sets. This won’t affect users, it’s part of the dump process to ensure correctness while restoring on the destination.

Comparing it with

pt-show-grants

 from Percona Toolkit, we can see that 

mysqlpump

 dumps the

CREATE USER

  information as well. The statements produced by

mysqlpump

 are the right thing to run to recreate users (and should be the preferred method), especially because of the

sql_mode

 NO_AUTO_CREATE_USERS. If enabled, it renders

pt-show-grants

 useless.

Here’s an example of

pt-show-grants

 usage:

$ pt-show-grants --host 127.0.0.1 --port 5708 --user msandbox --ask-pass
Enter password:
-- Grants dumped by pt-show-grants
-- Dumped from server 127.0.0.1 via TCP/IP, MySQL 5.7.8-rc at 2015-08-27 17:06:52
-- Grants for 'furrywall'@'localhost'
GRANT USAGE ON *.* TO 'furrywall'@'localhost';
-- Grants for 'msandbox'@'127.%'
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'127.%';
-- Grants for 'msandbox'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'msandbox'@'localhost';
-- Grants for 'msandbox_ro'@'127.%'
GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'127.%';
-- Grants for 'msandbox_ro'@'localhost'
GRANT EXECUTE, SELECT ON *.* TO 'msandbox_ro'@'localhost';
-- Grants for 'msandbox_rw'@'127.%'
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'127.%';
-- Grants for 'msandbox_rw'@'localhost'
GRANT ALTER, CREATE, CREATE TEMPORARY TABLES, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, SHOW DATABASES, UPDATE ON *.* TO 'msandbox_rw'@'localhost';
-- Grants for 'root'@'localhost'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;
-- Grants for 'rsandbox'@'127.%'
GRANT REPLICATION SLAVE ON *.* TO 'rsandbox'@'127.%';
-- Grants for 'testuser'@'localhost'
GRANT USAGE ON *.* TO 'testuser'@'localhost';

Some Miscellaneous Notes:

  • One of the differences with
    mysqldump

     is that

    mysqlpump

     adds 

    CREATE DATABASE

     statements to the dump by default, unless specified with the

    --no-create-db

    option.

    • There’s an important difference on the dump process that is closely related: it includes the database name while adding the
      CREATE TABLE

       statement. This causes a problem when trying to use the tool to create a duplicate.

Apr
05
2017
--

Evaluation of PMP Profiling Tools

PMP Profiling Tools

In this blog post, we’ll look at some of the available PMP profiling tools.

While debugging or analyzing issues with Percona Server for MySQL, we often need a quick understanding of what’s happening on the server. Percona experts frequently use the pt-pmp tool from Percona Toolkit (inspired by http://poormansprofiler.org).

The

pt-pmp

 tool collects application stack traces GDB and then post-processes them. From this you get a condensed, ordered list of the stack traces. The list helps you understand where the application spent most of the time: either running something or waiting for something.

Getting a profile with

pt-pmp

 is handy, but it has a cost: it’s quite intrusive. In order to get stack traces, GDB has to attach to each thread of your application, which results in interruptions. Under high loads, these stops can be quite significant (up to 15-30-60 secs). This means that the

pt-pmp

 approach is not really usable in production.

Below I’ll describe how to reduce GDB overhead, and also what other tools can be used instead of GDB to get stack traces.

  • GDB
    By default, the symbol resolution process in GDB is very slow. As a result, getting stack traces with GDB is quite intrusive (especially under high loads).There are two options available that can help notably reduce GDB tracing overhead:

      1. Use readnever patch. RHEL and other distros based on it include GDB with the readnever patch applied. This patch allows you to avoid unnecessary symbol resolving with the 
        --readnever

         option. As a result you get  up to 10 times better speed.

      2. Use gdb_index. This feature was added to address symbol resolving issue by creating and embedding a special index into the binaries. This index is quite compact: I’ve created and embedded gdb_index for Percona server binary (it increases the size around 7-8MB). The addition of the gdb_index speeds up obtaining stack traces/resolving symbols two to three times.
    # to check if index already exists:
      readelf -S  | grep gdb_index
    # to generate index:
      gdb -batch mysqld -ex "save gdb-index /tmp" -ex "quit"
    # to embed index:
      objcopy --add-section .gdb_index=tmp/mysqld.gdb-index --set-section-flags .gdb_index=readonly mysqld mysqld
  • eu-stack (elfutils)
    The eu-stack from the elfutils package prints the stack for each thread in a process or core file.Symbol resolving also is not very optimized in eu-stack. By default, if you run it under load it will take even more time than GDB. But eu-stack allows you to skip resolving completely, so it can get stack frames quickly and then resolve them without any impact on the workload later.
  • Quickstack
    Quickstack is a tool from Facebook that gets stack traces with minimal overheads.

Now let’s compare all the above profilers. We will measure the amount of time it needs to take all the stack traces from Percona Server for MySQL under a high load (sysbench OLTP_RW with 512 threads).

The results show that eu-stack (without resolving) got all stack traces in less than a second, and that Quickstack and GDB (with the readnever patch) got very close results. For other profilers, the time was around two to five times higher. This is quite unacceptable for profiling (especially in production).

There is one more note regarding the

pt-pmp

 tool. The current version only supports GDB as the profiler. However, there is a development version of this tool that supports GDB, Quickstack, eu-stack and eu-stack with offline symbol resolving. It also allows you to look at stack traces for specific threads (tids). So for instance, in the case of Percona Server for MySQL, we can analyze just the purge, cleaner or IO threads.

Below are the command lines used in testing:

# gdb & gdb+gdb_index
  time gdb  -ex "set pagination 0" -ex "thread apply all bt" -batch -p `pidof mysqld` > /dev/null
# gdb+readnever
  time gdb --readnever -ex "set pagination 0" -ex "thread apply all bt" -batch -p `pidof mysqld` > /dev/null
# eu-stack
  time eu-stack -s -m -p `pidof mysqld` > /dev/null
# eu-stack without resolving
  time eu-stack -q -p `pidof mysqld` > /dev/null
# quickstack - 1 sample
  time quickstack  -c 1 -p `pidof mysqld` > /dev/null
# quickstack - 1000 samples
  time quickstack  -c 1000 -p `pidof mysqld` > /dev/null

Mar
27
2017
--

Percona Toolkit 3.0.2 is now available

Percona Server for MongoDB

Percona ToolkitPercona announces the availability of Percona Toolkit 3.0.2 on March 27, 2017.

Percona Toolkit is a collection of advanced command-line tools that perform a variety of MySQL and MongoDB server and system tasks too difficult or complex for DBAs to perform manually. Percona Toolkit, like all Percona software, is free and open source.

This release includes the following changes:

New Features
  • PT-73: Added support for SSL connections to pt-mongodb-summary and pt-mongodb-query-digest
  • 1642751: Enabled gathering of information about locks and transactions by pt-stalk using Performance Schema if it is enabled (Thanks, Agustin Gallego)
Bug Fixes
  • PT-74: Fixed gathering of security settings when running pt-mongodb-summary on a mongod instance that is specified as the host
  • PT-75: Changed the default sort order in pt-mongodb-query-digest output to descending
  • PT-76: Added support of & and # symbols in passwords for pt-mysql-summary
  • PT-77: Updated Makefile to support new MongoDB tools
  • PT-89: Fixed pt-stalk to run top more than once to collect useful CPU usage
  • PT-93: Fixed pt-mongodb-query-digest to make query ID match query key (Thanks, Kamil Dziedzic)
  • PT-94: Fixed pt-online-schema-change to not make duplicate rows in _t_new when updating the primary key. Also, see 1646713.
  • PT-101: Fixed pt-table-checksum to correctly use the –slave-user and –slave-password options. Also, see 1651002.
  • PT-105: Fixed pt-table-checksum to continue running if a database is dropped in the process

You can find release details in the release notes. Bugs can be reported on Toolkit’s launchpad bug tracker.

Mar
02
2017
--

Using Percona Toolkit pt-mongodb-summary

Percona Server for MongoDB

pt-mongodb-summaryIn this blog post, we’ll look at the

pt-mongodb-summary

 tool in Percona Toolkit.

The

pt-mongodb-summary

 tool from Percona Toolkit provides a quick at-a-glance overview of MongoDB and Percona Server for MongoDB instances. It is equivalent to

pt-mysql-summary

 for MySQL. 

pt-mongodb-summary

 also collects information about a MongoDB cluster. It collects information from several sources to provide an overview of the cluster.

How It Works

The usage for the command is as follows:

pt-mongodb-summary [OPTIONS] [HOST[:PORT]]

Options:

  • -a, –auth-db: Specifies the database used to establish credentials and privileges with a MongoDB server. By default, the admin database is used.
  • -p, –password: Specifies the password to use when connecting to a server with authentication enabled. Do not add a space between the option and its value:
    -p<password>

    .
    If you specify the option without any value,

    pt-mongodb-summary

     will ask for the password interactively.

  • -u, –user: Specifies the user name for connecting to a server with authentication enabled.

By default, if you run

pt-mongodb-summary

 without parameters, it tries to connect to the localhost on port 27017. It collects information about the MongoDB instances by running administration commands, and formatting the output.

Sections

Instances

The first thing the tool does is get the list of hosts connected to the specified MongoDB instance by running the

listShards

 command. It also runs

replSetGetStatus

 on every instance to collect the ID, type, and replica set for each instance.

This host

Next, it gathers information about the host it is connected to by grouping information collected from

hostInfo

,

getCmdLineOpts

,

serverStatus

 and the OS process (by process ID). The result provides an overview of the running instance and the underlying OS.

Running ops

This section collects statistics by running the

serverStatus

 command five times at regular intervals (every one second), and provides the minimum, maximum and average operation counters for

insert

,

query

,

update

,

delete

,

getMore

 and

command

 operations.

Security

This collects information about security configurations by parsing the 

getCmdLineOpts

 command and asking the admin.system.users, and admin.system.roles collections.

Oplog

From the MongoDB website:

The oplog (operations log) is a special capped collection that keeps a rolling record of all operations that modify the data stored in your databases. MongoDB applies database operations on the primary and then records the operations on the primary’s oplog. The secondary members then copy and apply these operations in an asynchronous process. All replica set members contain a copy of the oplog, in the local.oplog.rs collection, which allows them to maintain the current state of the database.

How do we get the oplog info? The program collects statistics from the oplog for every host in the cluster, and returns the information on the statistics having the smaller

TimeDiffHours

  value.

Cluster-wide

This section provides information about the number of sharded/unsharded databases, collections and their size.The information is collected by running the

listDatabases

 command, and then running

collStats

 for every collection in every database.

Conditional Sections

You may notice not all sections appear all the time. This is because there are three main patterns:

Sharded Connection to Mongos

  • Instances
  • This host
  • Running ops
  • Security
  • Cluster-wide

ReplicaSet Connection

  • Instances (limited to the current Replica Set)
  • This host
  • Running ops
  • Security
  • Oplog

Standalone Connection

  • Instances (limited to this host)
  • This host
  • Running ops
  • Security

Output Example

The following is an example of the output for

pt-mongodb-summary

:

./pt-mongodb-summary
# Instances ##############################################################################################
  PID    Host                         Type                      ReplSet                   Engine
 11037 localhost:17001                SHARDSVR/PRIMARY          r1                    wiredTiger
 11065 localhost:17002                SHARDSVR/SECONDARY        r1                    wiredTiger
 11136 localhost:17003                SHARDSVR/SECONDARY        r1                    wiredTiger
 11256 localhost:17004                SHARDSVR/ARBITER          r1                    wiredTiger
 11291 localhost:18001                SHARDSVR/PRIMARY          r2                    wiredTiger
 11362 localhost:18002                SHARDSVR/SECONDARY        r2                    wiredTiger
 11435 localhost:18003                SHARDSVR/SECONDARY        r2                    wiredTiger
 11513 localhost:18004                SHARDSVR/ARBITER          r2                    wiredTiger
 11548 localhost:19001                CONFIGSVR                 -                     wiredTiger
 11571 localhost:19002                CONFIGSVR                 -                     wiredTiger
 11592 localhost:19003                CONFIGSVR                 -                     wiredTiger
# This host
# Mongo Executable #######################################################################################
       Path to executable | /home/karl/tmp/MongoDB32Labs/3.2/bin/mongos
# Report On karl-HP-ENVY ########################################
                     User | karl
                PID Owner | mongos
                 Hostname | karl-HP-ENVY
                  Version | 3.2.4
                 Built On | Linux x86_64
                  Started | 2017-02-22 11:39:20 -0300 ART
                Processes | 12
             Process Type | mongos
# Running Ops ############################################################################################
Type         Min        Max        Avg
Insert           0          0          0/5s
Query            0          0          0/5s
Update           0          0          0/5s
Delete           0          0          0/5s
GetMore          0          0          0/5s
Command          1          1          5/5s
# Security ###############################################################################################
Users  : 0
Roles  : 0
Auth   : disabled
SSL    : disabled
Port   : 0
Bind IP:
# Cluster wide ###########################################################################################
            Databases: 4
          Collections: 21
  Sharded Collections: 5
Unsharded Collections: 16
    Sharded Data Size: 134.87 MB
  Unsharded Data Size: 1.44 GB
          ###  Chunks:
                   5 : samples.col2
                 132 : carlos.sample4
                 400 : carlos.sample3
                  50 : carlos.sample2
                 100 : carlos.sample1
# Balancer (per day)
              Success: 18
               Failed: 0
               Splits: 682
                Drops: 0

 The following is an output example when connected to a secondary in the replica set.

./pt-mongodb-summary localhost:17002
# Instances ##############################################################################################
  PID    Host                         Type                      ReplSet                   Engine
  9247 localhost:17001                SHARDSVR/PRIMARY          r1                    wiredTiger
  9318 localhost:17002                SHARDSVR/SECONDARY        r1                    wiredTiger
  9391 localhost:17003                SHARDSVR/SECONDARY        r1                    wiredTiger
  9466 localhost:17004                SHARDSVR/ARBITER          r1                    wiredTiger
# This host
# Mongo Executable #######################################################################################
       Path to executable | /home/karl/tmp/MongoDB32Labs/3.2/bin/mongod
# Report On karl-HP-ENVY:17002 ########################################
                     User | karl
                PID Owner | mongod
                 Hostname | karl-HP-ENVY:17002
                  Version | 3.2.4
                 Built On | Linux x86_64
                  Started | 2017-02-23 10:26:27 -0300 ART
                  Datadir | labs/r1-2
                Processes | 12
             Process Type | replset
# Running Ops ############################################################################################
Type         Min        Max        Avg
Insert           0          0          0/5s
Query            0          0          0/5s
Update           0          0          0/5s
Delete           0          0          0/5s
GetMore          0          1          1/5s
Command          1          3         13/5s
# Security ###############################################################################################
Users  : 0
Roles  : 0
Auth   : disabled
SSL    : disabled
Port   : 17002
Bind IP:

This next example shows when it is connected to a stand alone instance:

/pt-mongodb-summary localhost:27018
# Instances ##############################################################################################
PID Host Type ReplSet Engine
1 localhost:27018 - wiredTiger
# This host
# Report On 2f8862dce6c4 ########################################
PID Owner | mongod
Hostname | 2f8862dce6c4
Version | 3.2.10
Built On | Linux x86_64
Started | 2017-02-23 08:57:36 -0300 ART
Processes | 1
Process Type | mongod
# Running Ops ############################################################################################
Type Min Max Avg
Insert 0 0 0/5s
Query 0 0 0/5s
Update 0 0 0/5s
Delete 0 0 0/5s
GetMore 0 0 0/5s
Command 1 1 5/5s
# Security ###############################################################################################
Users : 0
Roles : 0
Auth : disabled
SSL : disabled
Port : 0
Bind IP:

Conclusion

The tool

pt-mongodb-summary

is new in Percona Toolkit. In the future, we hope we can make this grow to the size of its MySQL big brother!

Mar
01
2017
--

Using Percona Toolkit pt-mongodb-query-digest

Percona Server for MongoDB

pt-mongodb-query-digestIn this blog post, we’ll look at how to use the

pt-mongodb-query-digest

 tool in Percona Toolkit 3.0.

Percona’s

pt-query-digest

 is one of our most popular Percona Toolkit MySQL tools. It is used on a daily basis by DBAs and developers to help identify the queries consuming the most resources. It helps in finding bottlenecks and optimizing database usage. The

pt-mongodb-query-digest

 is a similar tool for MongoDB.

About the Profiler

Before we start, remember that the MongoDB database profiler is disabled by default, and should be enabled. It can be enabled server-wide, but the full mode that logs all queries is not recommended in production unless you are using Percona Server for MongoDB 3.2 or higher. We added a feature to allow the sample rate of non-slow queries (like in MySQL) to limit the overhead this causes. 

Additionally, by default, the profiler is only 1MB per database. You may want to remove/create the profiler to sufficient size to find the results useful. To do this, use:

org_prof_level = db.getProfilingLevel();
//Disable Profiler
db.setProfilingLevel(0);
db.system.profile.drop();
//Setup  a  100M profile  1*Math.pow(1024,2) == 1M
profiler_size = 100 * Math.pow(1024,2);
db.runCommand( { create: "system.profile", capped: true, size: profiler_size } );
db.setProfilingLevel(org_prof_level);

According to the documentation, to check if the profiler is enabled for the samples database, run:

`echo "db.getProfilingStatus();" | mongo localhost:17001/samples`

Remember, you need to connect to a MongoDB instance, not a mongos. The output will be something like this:

MongoDB shell version: 3.2.12
connecting to: localhost:17001/samples
{ "was" : 0, "slowms" : 100 }
bye

The value for the field “was” is 0, which means profiling is disabled. Let’s enable the profiler for the samples database.

You must enable the profiler on all MongoDB instances that could be related to a shard of our database. To check on which instances we should enable the profiler, I am going to use the

pt-mongodb-summary

 tool. It shows us the information we need about our cluster:

./pt-mongodb-summary
./pt-mongodb-summary
# Instances ##############################################################################################
  PID    Host                         Type                      ReplSet                   Engine
 11037 localhost:17001                SHARDSVR/PRIMARY          r1                    wiredTiger
 11065 localhost:17002                SHARDSVR/SECONDARY        r1                    wiredTiger
 11136 localhost:17003                SHARDSVR/SECONDARY        r1                    wiredTiger
 11256 localhost:17004                SHARDSVR/ARBITER          r1                    wiredTiger
 11291 localhost:18001                SHARDSVR/PRIMARY          r2                    wiredTiger
 11362 localhost:18002                SHARDSVR/SECONDARY        r2                    wiredTiger
 11435 localhost:18003                SHARDSVR/SECONDARY        r2                    wiredTiger
 11513 localhost:18004                SHARDSVR/ARBITER          r2                    wiredTiger
 11548 localhost:19001                CONFIGSVR                 -                     wiredTiger
 11571 localhost:19002                CONFIGSVR                 -                     wiredTiger
 11592 localhost:19003                CONFIGSVR                 -                     wiredTiger

We have mongod service running on the localhost on ports 17001~17003 and 18001~18003.

Now, let’s enable the profiler for the samples database on those instances. For this example, I am going to set the profile level to “2”, to collect information about all queries.

for port in 17001 17002 17003 18001 18002 18003; do echo "db.setProfilingLevel(2);" | mongo localhost:${port}/samples; done

Running pt-mongodb-query-profile

Now we are ready to get statistics about our queries. To run

pt-mongodb-query-digest

, we need to specify at least “host: port/database”, like:

./pt-mongodb-query-digest localhost:27017/samples

The output will be something like this (I am showing a section for only one query):

# Query 0:  0.27 QPS, ID 2c0e2f94937d6660f510adeea98618f3
# Ratio    1.00  (docs scanned/returned)
# Time range: 2017-02-22 12:27:21.004 -0300 ART to 2017-02-22 12:28:00.867 -0300 ART
# Attribute            pct     total        min         max        avg         95%        stddev      median
# ==================   ===   ========    ========    ========    ========    ========     =======    ========
# Count (docs)                   845
# Exec Time ms          99      1206           0         697           1           0          29           0
# Docs Scanned           7    594.00        0.00       75.00        0.70        0.00        7.19        0.00
# Docs Returned          7    594.00        0.00       75.00        0.70        0.00        7.19        0.00
# Bytes recv             0      8.60M     215.00        1.06M      10.17K     215.00      101.86K     215.00
# String:
# Namespaces          samples.col1
# Operation           query
# Fingerprint         user_id
# Query               {"user_id":{"$gte":3506196834,"$lt":3206379780}}

From the output, we can see that this query was seen 97 times, and it provides statistics for the number of documents scanned/retrieved by the server, the execution time and size of the results. The tool also provides information regarding the operation type, the fingerprint and a query example to help to identify the source. 

By default, the results are sorted by query count. It can be changed by setting the

--order-by

 parameter to: count, ratio, query-time, docs-scanned or docs-returned.

A “-” in front of the field name denotes the reverse order. Example:

--order-by=-ratio

When considering what ordering to use, you need to know if you are looking for the most common queries (-count), the most cache abusive (-docs-scanned), or the worst ratio of scanned to returned (-ratio)? Please note you may be tempted to use (-query-time), however you will find this almost always ends up being more queries affected by, but not causing, issues.

Conclusion

This is a new tool in the Percona Toolkit. We hope in the future we can make it grow like its big brother for MySQL (

pt-query-digest

). This tool helps DBAs and developers identify and solve bottlenecks, and keep servers running at top performance.

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