May
22
2017
--

ICP Counters in information_schema.INNODB_METRICS

ICP Counters

ICP CountersIn this blog, we’ll look at ICP counters in the information_schema.INNODB_METRICS. This is part two of the Index Condition Pushdown (ICP) counters blog post series. 

As mentioned in the previous post, in this blog we will look at how to check on ICP counters on MySQL and Percona Server for MySQL. This also applies to MariaDB, since the INNODB_METRICS table is also available for MariaDB (as opposed to the Handler_icp_% counters being MariaDB-specific). We will use the same table and data set as in the previous post.

For simplicity we’ll show the examples on MySQL 5.7.18, but they also apply to the latest Percona Server for MySQL (5.7.18) and MariaDB Server (10.2.5):

mysql [localhost] {msandbox} (test) > SELECT @@version, @@version_comment;
+-----------+------------------------------+
| @@version | @@version_comment            |
+-----------+------------------------------+
| 5.7.18    | MySQL Community Server (GPL) |
+-----------+------------------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t1G
*************************** 1. row ***************************
      Table: t1
Create Table: CREATE TABLE `t1` (
 `f1` int(11) DEFAULT NULL,
 `f2` int(11) DEFAULT NULL,
 `f3` int(11) DEFAULT NULL,
 KEY `idx_f1_f2` (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  3999996 |
+----------+
1 row in set (3.98 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 LIMIT 12;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
|    3 |    1 |    1 |
|    3 |    2 |    1 |
|    3 |    3 |    1 |
|    3 |    4 |    1 |
+------+------+------+
12 rows in set (0.00 sec)

Before proceeding with the examples, let’s see what counters we have available and how to enable and query them. The documentation page is at the following link: https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-metrics-table.html.

The first thing to notice is that we are advised to check the validity of the counters for each version where we want to use them. The counters represented in the INNODB_METRICS table are subject to change, so for the most up-to-date list it’s best to query the running MySQL server:

mysql [localhost] {msandbox} (test) > SELECT NAME, SUBSYSTEM, STATUS FROM information_schema.INNODB_METRICS WHERE NAME LIKE '%icp%';
+------------------+-----------+----------+
| NAME             | SUBSYSTEM | STATUS   |
+------------------+-----------+----------+
| icp_attempts     | icp       | disabled |
| icp_no_match     | icp       | disabled |
| icp_out_of_range | icp       | disabled |
| icp_match        | icp       | disabled |
+------------------+-----------+----------+
4 rows in set (0.00 sec)

Looking good! We have all the counters we expected, which are:

  • icp_attempts: the number of rows where ICP was evaluated
  • icp_no_match: the number of rows that did not completely match the pushed WHERE conditions
  • icp_out_of_range: the number of rows that were checked that were not in a valid scanning range
  • icp_match: the number of rows that completely matched the pushed WHERE conditions

This link to the code can be used for reference: https://github.com/mysql/mysql-server/blob/5.7/include/my_icp.h.

After checking which counters we have at our disposal, you need to enable them (they are not enabled by default). For this, we can use the “modules” provided by MySQL to group similar counters for ease of use. This is also explained in detail in the documentation link above, under the “Counter Modules” section. INNODB_METRICS counters are quite inexpensive to maintain, as you can see in this post by Peter Z.

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_enable = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, SUBSYSTEM, STATUS FROM information_schema.INNODB_METRICS WHERE NAME LIKE '%icp%';
+------------------+-----------+---------+
| NAME             | SUBSYSTEM | STATUS  |
+------------------+-----------+---------+
| icp_attempts     | icp       | enabled |
| icp_no_match     | icp       | enabled |
| icp_out_of_range | icp       | enabled |
| icp_match        | icp       | enabled |
+------------------+-----------+---------+
4 rows in set (0.00 sec)

Perfect, we now know what counters we need, and how to enable them. We just need to know how to query them, and we can move on to the examples. However, before rushing into saying that a simple SELECT against the INNODB_METRICS table will do, let’s step back a bit and see what columns we have available that can be of use:

mysql [localhost] {msandbox} (test) > DESCRIBE information_schema.INNODB_METRICS;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| NAME            | varchar(193) | NO   |     |         |       |
| SUBSYSTEM       | varchar(193) | NO   |     |         |       |
| COUNT           | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT       | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT       | double       | YES  |     | NULL    |       |
| COUNT_RESET     | bigint(21)   | NO   |     | 0       |       |
| MAX_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| MIN_COUNT_RESET | bigint(21)   | YES  |     | NULL    |       |
| AVG_COUNT_RESET | double       | YES  |     | NULL    |       |
| TIME_ENABLED    | datetime     | YES  |     | NULL    |       |
| TIME_DISABLED   | datetime     | YES  |     | NULL    |       |
| TIME_ELAPSED    | bigint(21)   | YES  |     | NULL    |       |
| TIME_RESET      | datetime     | YES  |     | NULL    |       |
| STATUS          | varchar(193) | NO   |     |         |       |
| TYPE            | varchar(193) | NO   |     |         |       |
| COMMENT         | varchar(193) | NO   |     |         |       |
+-----------------+--------------+------+-----+---------+-------+
17 rows in set (0.00 sec)

There are two types: %COUNT and %COUNT_RESET. The former counts since the corresponding counters were enabled, and the latter since they were last reset (we have the TIME_% columns to check when any of these were done). This is why in our examples we are going to check the %COUNT_RESET counters, so we can reset them before running each query (as we did with FLUSH STATUS in the previous post).

Without further ado, let’s check how this all works together:

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 AND (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    1 |
+------+------+------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%';
+------------------+-------------+
| NAME             | COUNT_RESET |
+------------------+-------------+
| icp_attempts     |           9 |
| icp_no_match     |           6 |
| icp_out_of_range |           1
| icp_match        |           2 |
+------------------+-------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND (f2 % 4) = 1;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    8 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

If you checked the GitHub link above, you might have noted that the header file only contains three of the counters. This is because icp_attempts is computed as the sum of the rest. As expected, icp_match equals the number of returned rows, which makes sense. icp_no_match should also make sense if we check the amount of rows present without the WHERE conditions on f2.

mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
+------+------+------+
8 rows in set (0.00 sec)

So, 8 – 2 = 6, which is exactly icp_no_match‘s value. Finally, we are left with icp_out_of_range. For each end of range the ICP scan detects, this counter is incremented by one. We only scanned one range in the previous query, so let’s try something more interesting (scanning three ranges):

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE ((f1 < 2) OR (f1 > 4 AND f1 < 6) OR (f1 > 8 AND f1 < 12)) AND (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    5 |    1 |    1 |
|    9 |    1 |    1 |
|   10 |    1 |    1 |
|   11 |    1 |    1 |
+------+------+------+
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%';
+------------------+-------------+
| NAME             | COUNT_RESET |
+------------------+-------------+
| icp_attempts     |          23 |
| icp_no_match     |          15 |
| icp_out_of_range |           3 |
| icp_match        |           5 |
+------------------+-------------+
4 rows in set (0.01 sec)

We have now scanned three ranges on f1, namely: (f1 < 2), (4 < f1 < 6) and (8 < f1 < 12). This is correctly reflected in the corresponding counter. Remember that the MariaDB Handler_icp_attempts status counter we looked at in the previous post does not take into account the out-of-range counts. This means the two “attempts” counters will not be the same!

mysql [localhost] {msandbox} (test) > SET GLOBAL innodb_monitor_reset = module_icp; SET GLOBAL innodb_monitor_reset = dml_reads; FLUSH STATUS;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE ((f1 < 2) OR (f1 > 4 AND f1 < 6) OR (f1 > 8 AND f1 < 12)) AND (f2 % 4) = 1;
...
5 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp_attempts';
+--------------+-------------+
| NAME         | COUNT_RESET |
+--------------+-------------+
| icp_attempts |          23 |
+--------------+-------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW STATUS LIKE 'Handler_icp_attempts';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Handler_icp_attempts | 20    |
+----------------------+-------+
1 row in set (0.00 sec)

It can be a bit confusing to have two counters that supposedly measure the same counts yielding different values, so watch this if you use MariaDB.

ICP Counters in PMM

Today you can find an ICP counters graph for MariaDB (Handler_icp_attempts) in PMM 1.1.3.

Additionally, in release 1.1.4 you’ll find graphs for ICP metrics from information_schema.INNODB_METRICS: just look for the INNODB_METRICS-based graph on the InnoDB Metrics dashboard!

I hope you found this blog post series useful! Let me know if you have any questions or comments below.

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.

May
16
2017
--

Percona Live Open Source Database Conference 2017 Slides and Videos Available

Percona Live

Percona LiveThe slides and videos from the Percona Live Open Source Database Conference 2017 are available for viewing and download. The videos and slides cover the keynotes, breakout sessions and MySQL and MongoDB 101 sessions.

To view slides, go to the Percona Live agenda, and select the talk you want slides for from the schedule, and click through to the talk web page. The slides are available below the talk description. There is also a page with all the slides that is searchable by topic, talk title, speaker, company or keywords.

To view videos, go to the Percona Live 2017 video page. The available videos are searchable by topic, talk title, speaker, company or keywords.

There are a few slides and videos outstanding due to unforeseen circumstances. However, we will upload those as they become available.

Some examples of videos and slide decks from the Percona Live conference:

MongoDB 101: Efficient CRUD Queries in MongoDB
Adamo Tonete, Senior Technical Engineer, Percona
Video: https://www.percona.com/live/17/content/efficient-crud-queries-mongodb
Slides: https://www.percona.com/live/17/sessions/efficient-crud-queries-mongodb

MySQL 101: Choosing a MySQL High Availability Solution
Marcos Albe, Principal Technical Services Engineer, Percona
Video: https://www.percona.com/live/17/content/choosing-mysql-high-availability-solution
Slides: https://www.percona.com/live/17/sessions/choosing-mysql-high-availability-solution

Breakout Session: Using the MySQL Document Store
Mike Zinner, Sr. Software Development Director and Alfredo Kojima, Sr. Software Development Manager, Oracle
Video: https://www.percona.com/live/17/content/using-mysql-document-store
Slides: https://www.percona.com/live/17/sessions/using-mysql-document-store

Keynote: Continuent is Back! But What Does Continuent Do Anyway?
Eero Teerikorpi, Founder and CEO and MC Brown, VP Products, Continuent
Video: https://www.percona.com/live/17/content/continuent-back-what-does-continuent-do-anyway
Slides: https://www.percona.com/live/17/sessions/continuent-back-what-does-continuent-do-anyway

Please let us know if you have any issues. Enjoy the videos!

Percona Live Europe 2017
Percona Live Europe 2017: Dublin, Ireland!

This year’s Percona Live Europe will take place September 25th-27th, 2017, in Dublin, Ireland. Put it on your calendar now! Information on speakers, talks, sponsorship and registration will be available in the coming months.

We have developed multiple sponsorship options to allow participation at a level that best meets your partnering needs. Our goal is to create a significant opportunity for our partners to interact with Percona customers, other partners and community members. Sponsorship opportunities are available for Percona Live Europe 2017.

Download a prospectus here.

We look forward to seeing you there!

May
12
2017
--

Percona Server for MySQL 5.7.18-14 is Now Available

Percona Server for MySQL 5.7.18-14

Percona Server for MySQL 5.7.18-14Percona announces the GA release of Percona Server for MySQL 5.7.18-14 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.7.18, including all the bug fixes in it, Percona Server for MySQL 5.7.18-14 is the current GA release in the Percona Server for MySQL 5.7 series. Percona’s provides completely open-source and free software. Find release details in the 5.7.18-14 milestone at Launchpad.

New Features:
Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • In-place upgrade from Percona Server 5.6 to 5.7 by using standalone packages would fail if /var/lib/mysql wasn’t defined as the datadir. Bug fixed #1687276.
  • Combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Processing GTIDs in the relay log that were already been executed were causing write/fsync amplification. Bug fixed #1669928 (upstream #85141).
  • Text/BLOB fields were not handling sorting of the empty string consistently between InnoDB and filesort. Bug fixed #1674867 (upstream #81810) by porting a Facebook patch for MySQL.
  • InnoDB adaptive hash index was using a partitioning algorithm which would produce uneven distribution when the server contained many tables with an identical schema. Bug fixed #1679155 (upstream #81814).
  • For plugin variables that are signed numbers, doing a SHOW VARIABLES would always show an unsigned number. Fixed by porting a Facebook patch for MySQL.

Other bugs fixed: #1629250 (upstream #83245), #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1670588 (upstream #84173), #1672389, #1674507, #1675623, #1650294, #1659224, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1677943, #1678692, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, #1687386, #1687432, #1687600, and #1674281.

The release notes for Percona Server for MySQL 5.7.18-14 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

May
12
2017
--

Percona Server for MySQL 5.6.36-82.0 is Now Available

Percona Server for MySQL 5.7.18-14

Percona Server for MySQL 5.6.36-82.0Percona announces the release of Percona Server for MySQL 5.6.36-82.0 on May 12, 2017. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.6.36, and including all the bug fixes in it, Percona Server for MySQL 5.6.36-82.0 is the current GA release in the Percona Server for MySQL 5.6 series. Percona Server for MySQL is open-source and free – this is the latest release of our enhanced, drop-in replacement for MySQL. Complete details of this release are available in the 5.6.36-82.0 milestone on Launchpad.

New Features:
Bugs Fixed:
  • Deadlock could occur in I/O-bound workloads when server was using several small buffer pool instances in combination with small redo log files and variable innodb_empty_free_list_algorithm set to backoff algorithm. Bug fixed #1651657.
  • Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992.
  • tokubackup_slave_info file was created for a master server after taking the backup with Percona TokuBackup. Bug fixed #135.
  • Fixed a memory leak in Percona TokuBackup. Bug fixed #1669005.
  • Compressed columns with dictionaries could not be added to a partitioned table by using ALTER TABLE. Bug fixed #1671492.
  • Fixed a memory leak that happened in case of failure to create a multi-threaded slave worker thread. Bug fixed #1675716.
  • The combination of using any audit API-using plugin, like Audit Log Plugin and Response Time Distribution, with multi-byte collation connection and PREPARE statement with a parse error could lead to a server crash. Bug fixed #1688698 (upstream #86209).
  • Fix for a #1433432 bug in Percona Server 5.6.28-76.1 caused a performance regression due to suboptimal LRU manager thread flushing heuristics. Bug fixed #1631309.
  • Creating Compressed columns with dictionaries in MyISAM tables by specifying partition engines would not result in error. Bug fixed #1631954.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.
  • Replication slave did not report Seconds_Behind_Master correctly when running in multi-threaded slave mode. Bug fixed #1654091 (upstream #84415).
  • DROP TEMPORARY TABLE would create a transaction in binary log on a read-only server. Bug fixed #1668602 (upstream #85258).
  • Creating a compression dictionary with innodb_fake_changes enabled could lead to a server crash. Bug fixed #1629257.

Other bugs fixed: #1660828 (upstream #84786), #1664519 (upstream #84940), #1674299, #1683456, #1670588 (upstream #84173), #1672389, #1674507, #1674867, #1675623, #1650294, #1659224, #1660565, #1662908, #1669002, #1671473, #1673800, #1674284, #1676441, #1676705, #1676847 (upstream #85671), #1677130 (upstream #85678), #1677162, #1678692, #1678792, #1680510 (upstream #85838), #1683993, #1684012, #1684078, #1684264, and #1674281.

Release notes for Percona Server for MySQL 5.6.36-82.0 are available in the online documentation. Please report any bugs on the launchpad bug tracker.

May
11
2017
--

MyRocks and LOCK IN SHARE MODE

LOCK IN SHARE MODE

LOCK IN SHARE MODEIn this blog post, we’ll look at MyRocks and the

LOCK IN SHARE MODE

.

Those who attended the March 30th webinar “MyRocks Troubleshooting” might remember our discussion with Yoshinori on 

LOCK IN SHARE MODE

.

I did more tests, and I can confirm that his words are true:

LOCK IN SHARE MODE

 works in MyRocks.

This quick example demonstrates this. The initial setup:

CREATE TABLE t (
id int(11) NOT NULL,
f varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=ROCKSDB;
insert into t values(12345, 'value1'), (54321, 'value2');

In session 1:

session 1> begin;
Query OK, 0 rows affected (0.00 sec)
session 1> select * from t where id=12345 lock in share mode;
+-------+--------+
| id | f |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.01 sec)

In session 2:

session 2> begin;
Query OK, 0 rows affected (0.00 sec)
session 2> update t set f='value3' where id=12345;
ERROR HY000: Lock wait timeout exceeded; try restarting transaction

However, in the webinar I wanted to remind everyone about the differences between

LOCK IN SHARE MODE

  and

FOR UPDATE

. To do so, I added the former to my “session 2” test for the webinar. Once I did, it ignores the lock set in “session 1”. I can update a row and commit:

session 2> select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)
session 2> update t set f='value3' where id=12345;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
session 2> commit;
Query OK, 0 rows affected (0.02 sec)

I reported this behavior here, and also at Percona Jira bugs database: MYR-107. In Facebook, this bug is already fixed.

This test clearly demonstrates that it is fixed in Facebook. In “session 1”:

session1> CREATE TABLE `t` (
    -> `id` int(11) NOT NULL,
    -> `f` varchar(100) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=ROCKSDB;
Query OK, 0 rows affected (0.00 sec)
session1> insert into t values(12345, 'value1'), (54321, 'value2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
session1> begin;
Query OK, 0 rows affected (0.00 sec)
session1>  select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)

And now in another session:

session2> begin;
Query OK, 0 rows affected (0.00 sec)
session2> select * from t where id=12345 lock in share mode;
+-------+--------+
| id    | f      |
+-------+--------+
| 12345 | value1 |
+-------+--------+
1 row in set (0.00 sec)
session2> update t set f='value3' where id=12345;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: Timeout on index: test.t.PRIMARY

If you want to test the fix with the Facebook MySQL build, you need to update submodules to download the patch:

git submodule update

.

May
10
2017
--

Percona Server for MySQL 5.5.55-38.8 is Now Available

Percona Server for MySQL 5.5

Percona Server for MySQL 5.5.55-38.8Percona announces the release of Percona Server for MySQL 5.5.55-38.8 on May 10, 2017. Based on MySQL 5.5.55, including all the bug fixes in it, Percona Server for MySQL 5.5.55-38.8 is now the current stable release in the 5.5 series.

Percona Server for MySQL is open-source and free. You can find release details in the 5.5.55-38.8 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.

New Features:
  • Percona Server 5.5 packages are now available for Ubuntu 17.04 (Zesty Zapus).
Bugs Fixed:
  • If a bitmap write I/O errors happened in the background log tracking thread while a FLUSH CHANGED_PAGE_BITMAPS is executing concurrently it could cause a server crash. Bug fixed #1651656.
  • Querying TABLE_STATISTICS in combination with a stored function could lead to a server crash. Bug fixed #1659992.
  • Queries from the INNODB_CHANGED_PAGES table would needlessly read potentially incomplete bitmap data past the needed LSN range. Bug fixed #1625466.
  • It was not possible to configure basedir as a symlink. Bug fixed #1639735.

Other bugs fixed: #1688161, #1683456, #1670588 (upstream #84173), #1672389, #1675623, #1660243, #1677156, #1680061, #1680510 (upstream #85838), #1683993, #1684012, #1684025, and #1674281.

Find the release notes for Percona Server for MySQL 5.5.55-38.8 in our online documentation. Report bugs on the launchpad bug tracker.

May
09
2017
--

MariaDB Handler_icp_% Counters: What They Are, and How To Use Them

Handler_icp_% counters

Handler_icp_% countersIn this post we’ll see how MariaDB’s Handler_icp_% counters status counters (Handler_icp_attempts and Handler_icp_matches) measure ICP-related work done by the server and storage engine layers, and how to see if our queries are getting any gains by using them.

These counters (as seen in SHOW STATUS output) are MariaDB-specific. In a later post, we will see how we can get this information in MySQL and Percona Server. This investigation spun off from comments in Michael’s post about the new MariaDB dashboard in PMM. Comments are very useful, so keep them coming! ?

We can start by checking the corresponding documentation pages:

https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_attempts

Description: Number of times pushed index condition was checked. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See Index Condition Pushdown.

https://mariadb.com/kb/en/mariadb/server-status-variables/#handler_icp_match

Description: Number of times pushed index condition was matched. The smaller the ratio of Handler_icp_attempts to Handler_icp_match the better the filtering. See See Index Condition Pushdown.

As we’ll see below, “attempts” counts the number of times the server layer sent a WHERE clause down to the storage engine layer to check if it can be filtered out. “Match”, on the other hand, counts whether an attempt ended up in the row being returned (i.e., if the pushed WHERE clause was a complete match).

Now that we understand what they measure, let’s check how to use them for reviewing our queries. Before moving forward with the examples, here are a couple of points to keey in mind:

  • Even if the attempt was not successful, it doesn’t mean that it is bad. However, a high (attempts – match) number is good in this context, since this is a measure of the rows that were “saved” from being checked in the server layer after getting the complete row from the storage engine. On the other hand, a low number is not bad — it just means that most (or all) attempts ended up being a match.
  • From the documentation links above, it is stated that “the smaller the ratio between attempts to match, the better the filtering.”, which I believe is the contrary.

Back to our examples, then. First, let’s review version, table structure and data set.

mysql [localhost] {msandbox} (test) > SELECT @@version, @@version_comment;
+-----------------+-------------------+
| @@version       | @@version_comment |
+-----------------+-------------------+
| 10.1.20-MariaDB | MariaDB Server    |
+-----------------+-------------------+
1 row in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SHOW CREATE TABLE t1G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  `f2` int(11) DEFAULT NULL,
  `f3` int(11) DEFAULT NULL,
  KEY `idx_f1_f2` (`f1`,`f2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
mysql [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|  3999996 |
+----------+
1 row in set (1.75 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 LIMIT 12;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    1 |    4 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
|    2 |    4 |    1 |
|    3 |    1 |    1 |
|    3 |    2 |    1 |
|    3 |    3 |    1 |
|    3 |    4 |    1 |
+------+------+------+
12 rows in set (0.00 sec)

It’s trivial, but it will work well for what we intend to show:

mysql [localhost] {msandbox} (test) > FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and f2 < 4;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    1 |    2 |    1 |
|    1 |    3 |    1 |
|    2 |    1 |    1 |
|    2 |    2 |    1 |
|    2 |    3 |    1 |
+------+------+------+
6 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 8              |
| HANDLER_ICP_MATCH    | 6              |
| ROWS_READ            | 6              |
| ROWS_SENT            | 6              |
+----------------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

In this scenario, the server sent a request to the storage engine to check on eight rows, from which six were a complete match. This is the case where a low attempts - match number is seen. The server scanned the index on the f1 column to decide which rows needed a “request for further check”, then the storage engine checked the WHERE condition on the f2 column with the pushed down (f2 < 4) clause.

Now let’s change the condition on f2:

mysql [localhost] {msandbox} (test) > FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+------+------+
| f1   | f2   | f3   |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    1 |
+------+------+------+
2 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 8              |
| HANDLER_ICP_MATCH    | 2              |
| ROWS_READ            | 2              |
| ROWS_SENT            | 2              |
+----------------------+----------------+
4 rows in set (0.00 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra                 |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using index condition |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

In this scenario, the server also sent a request for eight rows, of which only two ended up being a match, due to the changed condition on f2. This is the case where a “high” attempts - match number is seen.

Great, we understand how to see the amount of rows sent between the server and storage engine layers. Now let’s move forward with the “how can I make sense of these numbers?” part. We can use the other counters included in the outputs that haven’t been mentioned yet (ROWS_READ and ROWS_SENT) and compare them when running the same queries with ICP disabled (which can be conveniently done with a simple SET):

mysql [localhost] {msandbox} (test) > SET optimizer_switch='index_condition_pushdown=off';
Query OK, 0 rows affected (0.00 sec)

Let’s run the queries again. For the first query:

mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and f2 < 4;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 0              |
| HANDLER_ICP_MATCH    | 0              |
| ROWS_READ            | 9              |
| ROWS_SENT            | 6              |
+----------------------+----------------+
4 rows in set (0.01 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 AND f2 < 4;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using where |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

As we can see by the handler counters, ICP is indeed not being used. Now the server is reading nine rows, as opposed to six when using ICP. Moreover, notice how we now see a Using where in the “Extra” column in the EXPLAIN output. This means that we are doing the filtering on the server layer; and we are using the first column of the composite index for the range scan (f1 < 3).

For the second query:

mysql [localhost] {msandbox} (test) > FLUSH STATUS; SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
...
mysql [localhost] {msandbox} (test) > SELECT * FROM information_schema.SESSION_STATUS WHERE VARIABLE_NAME LIKE '%icp%' OR VARIABLE_NAME='ROWS_READ' OR VARIABLE_NAME='ROWS_SENT';
+----------------------+----------------+
| VARIABLE_NAME        | VARIABLE_VALUE |
+----------------------+----------------+
| HANDLER_ICP_ATTEMPTS | 0              |
| HANDLER_ICP_MATCH    | 0              |
| ROWS_READ            | 9              |
| ROWS_SENT            | 2              |
+----------------------+----------------+
4 rows in set (0.01 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE f1 < 3 and (f2 % 4) = 1;
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key       | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | range | idx_f1_f2     | idx_f1_f2 | 5       | NULL |    7 | Using where |
+------+-------------+-------+-------+---------------+-----------+---------+------+------+-------------+
1 row in set (0.00 sec)

The server is also reading nine rows (because it’s also using only column f1 from the composite index, and we have the same condition on it), with the difference that it used to read only two while using ICP. We could say that this case was much better (and it was), but as with most of the time the answer to the bigger “how much better” question is “it depends”. As stated in the documentation, it has two factors:

  • How many records are filtered out
  • How expensive it is to read them

So it really depends on the size and kind of data set, and if it’s in memory or not. It’s better to benchmark the queries to have more information (like actual response times), but if it’s not possible we can get some valuable information by taking a look at these counters.

Lastly, I wanted to go back to the “attempts to match ratio” mentioned initially. As we saw in these examples, the first one had a 8:6 (or 4:3) ratio and the second a 8:2 (or 4:1) ratio, and the second one filtered more rows. Given this, I believe that the inverse will hold true: “The bigger the ratio of Handler_icp_attempts to Handler_icp_match, the better the filtering.”

Stay tuned for the next part, where we’ll see how to get this information from MySQL and Percona Server, too!

May
08
2017
--

Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back

Hung MySQL Transaction

In this blog post, I’ll review how a hung MySQL transaction can cause the InnoDB history length to grow and negatively affect MySQL performance.

Recently I was helping a customer discover why SELECT queries were running slower and slower until the server restarts (which got things back to normal). It took some time to get from that symptom to a final diagnosis. Please follow me on the journey of chasing this strange MySQL behavior!

Symptoms

Changes in the query response time can mean tons of things. We can check everything from the query plan to the disk performance. However, fixing it with a restart is less common. After looking at “show engine innodb status”, I noticed some strange lines:

Trx read view will not see trx with id >= 41271309593, sees < 41268384363
---TRANSACTION 41271309586, ACTIVE 766132 sec
2 lock struct(s), heap size 376, 0 row lock(s), undo log entries 1
...

There was a total of 940 transactions like this.

Another insight was the InnoDB transaction history length graph from Percona Monitoring and Management (PMM):

Hung MySQL Transaction

History length of 6 million and growing clearly indicates a problem.

Problem localized

There have been a number of blog posts describing a similar problem: Peter stated in a blog post: “InnoDB transaction history often hides dangerous ‘debt’“. As the InnoDB transaction history grows, SELECTs need to scan more and more previous versions of the rows, and performance suffers. That explains the issue: SELECT queries get slower and slower until restart. Peter also filed this bug: Major regression having many row versions.

But why does the InnoDB transaction history start growing? There are 940 transactions in this state: ACTIVE 766132 sec. MySQL’s process list shows those transactions in “Sleep” state. It turns out that those transactions were “lost” or “hung”. As we can also see, each of those transactions holds two lock structures and one undo record, so they are not committed and not rolled-back. They are sitting there doing nothing. In this case, with the default isolation level REPEATABLE-READ, InnoDB can’t purge the undo records (transaction history) for other transactions until these “hung” transactions are finished.

The quick solution is simple: kill those connections and InnoDB will roll back those transactions and purge transaction history. After killing those 940 transactions, the graph looked like this:

Hung MySQL Transaction

However, several questions remain:

  1. What are the queries inside of this lost transaction? Where are they coming from? The problem is that neither MySQL’s process list nor InnoDB’s status shows the queries for this transaction, as it is not running those queries right now (the process list is a snapshot of what is happening inside MySQL right at this moment)
  2. Can we fix it so that the “hung” transactions don’t affect other SELECT queries and don’t cause the growth of transaction history?

Simulation

As it turns out, it is very easy to simulate this issue with sysbench.

Test preparation

To add some load, I’m using sysbench,16 threads (you can open less, it does not really matter here) and a script for a “write-only” load (running for 120 seconds):

conn=" --db-driver=mysql --mysql-host=localhost --mysql-user=user --mysql-password=password --mysql-db=sbtest "
sysbench --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-table-engine=InnoDB --oltp-table-size=1000000 $conn prepare
sysbench --num-threads=16 --max-requests=0 --max-time=120 --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=1000000 $conn
--oltp-test-mode=complex --oltp-point-selects=0 --oltp-simple-ranges=0 --oltp-sum-ranges=0
--oltp-order-ranges=0 --oltp-distinct-ranges=0 --oltp-index-updates=1 --oltp-non-index-updates=0 run

Simulate a “hung” transaction

While the above sysbench is running, open another connection to MySQL:

use test;
CREATE TABLE `a` (
`i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into a values(1);
begin; insert into a values(1); select * from a;

Note: we will need to run the SELECT as a part of this transaction. Do not close the connection.

Watch the history

mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 34324 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 36480 |
+----------------------+-------+
1 row in set (0.01 sec)

We can see it is growing. Now it is time to commit or rollback or even kill our original transaction:

mysql> rollback;
...
mysql> select name, count from information_schema.INNODB_METRICS where name like '%hist%';
+----------------------+-------+
| name                 | count |
+----------------------+-------+
| trx_rseg_history_len | 793   |
+----------------------+-------+
1 row in set (0.00 sec)

As we can see, it has purged the history length.

Finding the queries from the hung transactions

There are a number of options to find the queries from that “hung” transaction. In older MySQL versions, the only way is to enable the general log (or the slow query log). Starting with MySQL 5.6, we can use the Performance Schema. Here are the steps:

  1. Enable performance_schema if not enabled (it is disabled on RDS / Aurora by default).
  2. Enable events_statements_history:
    mysql> update performance_schema.setup_consumers set ENABLED = 'YES' where NAME='events_statements_history';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  3. Run the query to find all transaction started 10 seconds ago (change the number of seconds to match your workload):
    SELECT ps.id as processlist_id,
                 trx_started, trx_isolation_level,
                 esh.EVENT_ID,
                 esh.TIMER_WAIT,
                 esh.event_name as EVENT_NAME,
                 esh.sql_text as SQL,
                 esh.RETURNED_SQLSTATE, esh.MYSQL_ERRNO, esh.MESSAGE_TEXT, esh.ERRORS, esh.WARNINGS
    FROM information_schema.innodb_trx trx
    JOIN information_schema.processlist ps ON trx.trx_mysql_thread_id = ps.id
    LEFT JOIN performance_schema.threads th ON th.processlist_id = trx.trx_mysql_thread_id
    LEFT JOIN performance_schema.events_statements_history esh ON esh.thread_id = th.thread_id
    WHERE trx.trx_started < CURRENT_TIME - INTERVAL 10 SECOND
      AND ps.USER != 'SYSTEM_USER'
    ORDER BY esh.EVENT_IDG
    ...
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 79
             TIMER_WAIT: 33767000
             EVENT NAME: statement/sql/begin
                    SQL: begin
      RETURNED_SQLSTATE: 00000
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
    *************************** 9. row ***************************
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 80
             TIMER_WAIT: 2643082000
             EVENT NAME: statement/sql/insert
                    SQL: insert into a values(1)
      RETURNED_SQLSTATE: 00000
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0
    *************************** 10. row ***************************
             PROCESS ID: 1971
            trx_started: 2017-05-03 17:36:47
    trx_isolation_level: REPEATABLE READ
               EVENT_ID: 81
             TIMER_WAIT: 140305000
             EVENT NAME: statement/sql/select
                    SQL: select * from a
      RETURNED_SQLSTATE: NULL
            MYSQL_ERRNO: 0
           MESSAGE_TEXT: NULL
                 ERRORS: 0
               WARNINGS: 0

    Now we can see the list of queries from the old transaction (the MySQL query used was taken with modifications from this blog post: Tracking MySQL query history in long running transactions).

At this point, we can chase this issue at the application level and find out why this transaction was not committed. The typical causes:

  • There is a heavy, non-database-related process inside the application code. For example, the application starts a transaction to get a list of images for analysis and then starts an external application to process those images (machine learning or similar), which can take a very long time.
  • The application got an uncaught exception and exited, but the connection to MySQL was not closed for some reason (i.e., returned to the connection pool).

We can also try to configure the timeouts on MySQL or the application so that the connections are closed after “N” minutes.

Changing the transaction isolation level to fix the InnoDB transaction history issue

Now that we know which transaction is holding up the purge process of InnoDB history, we can find this transaction and make changes so it will not “hang”. We can change the transaction isolation level from REPEATABLE READ (default) to READ COMMITTED. In READ COMMITTED, InnoDB does not need to maintain history length when other transactions have committed changes. (More details about different isolation methods and how they affect InnoDB transactions.) That will work in MySQL 5.6 and later. However this doesn’t work in Amazon Aurora (as of now): even with READ COMMITTED isolation level, the history length still grows.

Here is the list of MySQL versions where changing the isolation level fixes the issue

MySQL Version  Transaction isolation  InnoDB History Length
MySQL 5.6 repeatable read history is not purged until “hung” transaction finishes
MySQL 5.6 read committed (fixed) history is purged
Aurora repeatable read history is not purged until “hung” transaction finishes
Aurora read committed history is not purged until “hung” transaction finishes


Summary

Hung transactions can cause the InnoDB history length to grow and (surprisingly, on the first glance) affect the performance of other running select queries. We can use the performance schema to chase the “hung” transaction. Changing the MySQL transaction isolation level can potentially help.

May
04
2017
--

How much disk space should I allocate for Percona Monitoring and Management?

Percona Monitoring and Management

I heard a frequent question at last week’s Percona Live conference regarding Percona Monitoring and Management (PMM): How much disk space should I allocate for PMM Server?

First, let’s review the three components of Percona Monitoring and Management that consume non-negligible disk space:

  1. Prometheus data source for the time series metrics
  2. Query Analytics (QAN) which uses Percona Server XtraDB (Percona’s enhanced version of the InnoDB storage engine)
  3. Orchestrator, also backed by Percona Server XtraDB

Of these, you’ll find that Prometheus is generally your largest consumer of disk space. Prometheus hits a steady state of disk utilization once you reach the defined storage.local.retention period. If you deploy Percona Monitoring and Management 1.1.3 (the latest stable version), you’ll be using a retention period of 30 days. “Steady state” in this case means you’re not adding or removing nodes frequently, since each node comes with its own 1k-7k metrics to be scraped. Prometheus stores a one-time series per metric scraped, and automatically trims chunks (like InnoDB pages) from the tail of the time series once they exceed the retention period (so the disk requirement per static list of metrics remains “fixed” for the retention period).

However, if you’re in a dynamic environment with nodes being added and removed frequently, or you’re on the extreme end like these guys who re-deploy data centers every day, steady state for Prometheus may remain an elusive goal. The guidance you find below may help you establish at least a minimum disk provisioning threshold.

Percona Monitoring and Management

QAN is based on a web application and uses Percona Server 5.7.17 as it’s datastore. The Percona QAN agent runs one instance per monitored MySQL server, and obtains queries from either the Slow log or Performance Schema. It performs analysis locally to generate a list of unique queries and their corresponding metrics: min, max, avg, med, and p95. There are dimensions based on Tmp table, InnoDB, Query time, Lock time, etc. Check the schema for a full listing, as there are actually 149 columns on this table (show create table pmm.query_class_metricsG). While the table is wide, it isn’t too long: PMM Demo is ~9mil rows and is approximately 1 row per distinct query per minute per host.

Finally, there is Orchestrator. While the disk requirements for Orchestrator are not zero, they are certainly dwarfed by Prometheus and QAN.  As you’ll read below, Percona’s Orchestrator footprint is a measly ~250MB, which is a rounding error. I’d love to hear other experiences with Orchestrator and how large your InnoDB footprint is for a large or active cluster.

For comparison, here is the resource consumption from Percona’s PMM Demo site:

  • ~47k time series
  • 25 hosts, which is on average ~1,900 time series/host, some are +4k
  • 8-day retention for metrics in Prometheus
  • Prometheus data is ~40GB
    • Which should not increase until we add more host, as this isn’t a dynamic Kubernetes environment ?
  • QAN db is 6.5GB
    • We don’t currently prune records, so this will continue to grow
    • 90% of space consumed is in query_class_metrics, which is ~9mil rows
    • Our first record is ~September 2016, but only in the past three months
    • This is MySQL QAN only, the MongoDB nodes don’t write anything into QAN (yet… we’re working on QAN for MongoDB and hope to ship this quarter!!)
  • Orchestrator db is ~250MB
    • audit table is 97% of the space consumed, ~2mil rows

So back to the original question: How much space should I allocate for Percona Monitoring and Management Server? The favorite answer at Percona is “It Depends®,” and this case is no different. Using PMM Demo as our basis, 46GB / 25 hosts / 8 days = ~230MB/host/day or ~6.9GB/host/30 day retention period. For those of you running 50 instances in PMM, you should be provisioning ~400GB of disk.

Of course, your environment is likely to be different and directly related to what you do and don’t enable. For example, a fully verbose Percona Server 5.7.17 configuration file like this:

## PMM Enhanced options
long_query_time=0
log_slow_rate_limit=100
log_slow_rate_type=query
log_slow_verbosity=full
log_slow_admin_statements=ON
log_slow_slave_statements=ON
slow_query_log_always_write_time=1
slow_query_log_use_global_control=all
innodb_monitor_enable=all
userstat=1
query_response_time_stats=ON
performance_schema_instrument='%=on'

with none of the mysqld_exporter features disabled:

--disable-binlogstats
--disable-processlist
--disable-queryexamples
--disable-tablestats
--disable-userstats

can lead to an instance that has +4k metrics and will push you above 230MB/host/day. This is what the top ten metrics and hosts by time series count from the PMM Demo look like:

Percona Monitoring and Management

What does the future hold related to minimizing disk space consumption?

  1. The PMM development team is working on the ability to purge a node’s data without access to the instance
    • Today you need to call pmm-admin purge from the instance – which becomes impossible if you’ve already terminated or decommissioned the instance!
  2. We are following Prometheus’ efforts on the 3rd Gen storage re-write in Prometheus 2.0, where InfluxDB will do more than just indices
  3. Again we are following Prometheus’ efforts on Remote Read / Remote Write so we can provide a longer-term storage model for users seeking > 30 days (another popular topic at PL2017)
    • Allows us to store less granular data (every 5s vs. every 1s)
    • Usage of Graphite, OpenTSDB, and InfluxDB as secondary data stores on the Remote end

I’d love to hear about your own experiences using Percona Monitoring and Management, and specifically the disk requirements you’ve faced! Please share them with us via the comments below, or feel free to drop me a line directly michael.coburn@percona.com. Thanks for reading!

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