XtraDB

Performance regression between Percona Server 5.6 & 5.7

I) An upgrade which hasn’t gone as planned
One of our customer needed help after trying to upgrade their slave from Percona Server 5.6 to Percona Server 5.7. (here 5.7.17 is used)
Indeed, from time to time, the replication lag was linearly growing, which could be quite annoying…

(good guest, 5.7 is the red dashed line!)
Their use case is quite interesting :

a lot of Queries Per Second (read & write, nearly 10k queries/s on this slave)
a lot of tables (about 600k spread across different databases)
a lot of writes
24 CPUs, and 386G of RAM
A big buffer pool (270G)
not that fast SSD disks.

Because we have a high QPS but also a lot of writes, we need to fine tune the innodb settings.
The idea is to make sure to not flush too fast on disk the dirty pages in the innodb buffer pool, and to have enough room in the redo log. The most important settings to maintain a good balance are:

innodb_io_capacity=2000
innodb_io_capacity_max=12000
innodb_log_file_size=32G

Other parameters like the innodb_buffer_pool_instances, innodb_flush_neighbors, innodb_lru_scan_depth, innodb_purge_threads or innodb_page_cleaners… have been fine tuned as well, but it doesn’t impact much the flushing speed of the dirty pages.
In practical, with those settings, the server has about 24% of dirty pages, and 20% of redo log used in average under a “normal” usage.
It seems to be not bad, so why the server is sometimes so slow?
II) Let’s investigate!
a) The LRU manager
I have first investigated if the innodb buffer pool flush was working properly. Although it was not the main cause of the performance regression, I noticed a new threaded LRU (Least Recently Used) manager has been introduced in XtraDB 5.7. Basically, for each buffer pool instance, a list of dirty pages is stored in an LRU queue. The job of this LRU manager is to flush a few dirty pages, when there are not enough free pages available in the buffer pool.
However, the mecanism to wake up those threads is not optimal: it’s based on a timer which will wake up the thread every 1s + an extra time which is auto-ajusted depending on the size of the buffer pool free list and what happened during the last thread execution.
With this implementation, XtraDB is spending a lot of CPU cycle sleeping (thru the use of the os_thread_sleep function). A better implementation would be perhaps to use an event wait which would trigger a thread wake up once the free list reaches some defined threshold.
As a result, I opened a bug report on percona server to report this issue:
https://bugs.launchpad.net/percona-server/+bug/1690399
For the record, Mark Callaghan already opened a few years ago a bug report on MySQL asking to try to remove the os_thread_sleep calls : https://bugs.mysql.com/bug.php?id=68588
I’ve just noticed XtraDB 5.7.18 included a few improvement in the LRU Manager, we need to test if it improves things: https://bugs.launchpad.net/percona-server/+bug/1631309
 
b) ALTER TABLE are slow!
Well actually, it’s not always the case, that’s why it was tricky to track it down.It’s worth noting this ALTER TABLE issue in an upstream bug, from MySQL 5.7 (thanks Marko Mäkelä for noticing it). So this one is affecting Percona Server 5.7, MySQL 5.7 and MariaDB 10.2.
The workload of this customer can create from time to time a few new tables, fill them, and use an ALTER TABLE to add new indexes.
In the introduction, I mentioned the server has been fine tuned to avoid flushing too much the buffer pool. It means the amount of dirty pages is potentially high, depending on the size of the buffer pool. In our case, with 24% of dirty pages, it represents about 4.2m of dirty pages.
The issue in 5.7 is that when we rebuild an index, the function FlushObserver:flush is called to flush the dirty pages associated with the modified table. Before doing the flush, it tries to count the number of dirty pages to estimate the amount of time the operation could take. Unfortunately, the implementation of this count is really inefficient : it iterates on all the buffer pool instances, and for each buffer pool, on all the dirty pages, to try to find those one corresponding to the modified table.
With 4.2m of dirty pages, it means it iterates on 4.2m dirty pages (even if the table is empty!).
Here are the performance results:

I created another bug report for this specific issue : https://bugs.launchpad.net/percona-server/+bug/1690588
Let’s hope they will fix those issues quickly!
The post Performance regression between Percona Server 5.6 & 5.7 appeared first on Softizy Blog.

Percona XtraBackup 2.4.5 is now available

Percona XtraBackup 2.4.5Percona announces the GA release of Percona XtraBackup 2.4.5 on November 29th, 2016. You can download it from our download site and from apt and yum repositories. Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered […]

Percona Server 5.5.51-38.1 is now available

Percona announces the release of Percona Server 5.5.51-38.1 on August 19, 2016. Based on MySQL 5.5.51, including all the bug fixes in it, Percona Server 5.5.51-38.1 is now the current stable release in the 5.5 series.
Percona Server is open-source and free. You can find release details of the release in the 5.5.51-38.1 milestone on Launchpad. Downloads are available here and from the Percona Software Repositories.
Bugs Fixed:

PAM Authentication Plugin would abort authentication while checking UNIX user group membership if there were more than a thousand members. Bug fixed #1608902.
PAM Authentication Plugin didn’t support spaces in the UNIX user group names. Bug fixed #1544443.
If DROP DATABASE would fail to delete some of the tables in the database, the partially-executed command is logged in the binlog as DROP TABLE t1, t2, … for the tables for which drop succeeded. A slave might fail to replicate such DROP TABLE statement if there exist foreign key relationships to any of the dropped tables and the slave has a different schema from the master. Fixed by checking, on the master, whether any of the database to be dropped tables participate in a Foreign Key relationship, and fail the DROP DATABASE statement immediately. Bug fixed #1525407 (upstream #79610).
Percona Server 5.5 could not be built with the -DMYSQL_MAINTAINER_MODE=ON option. Bug fixed #1590454.
In the client library, any EINTR received during network I/O was not handled correctly. Bug fixed #1591202 (upstream #82019).
The included .gitignore in the percona-server source distribution had a line *.spec, which means someone trying to check in a copy of the percona-server source would be missing the spec file required to build the RPM packages. Bug fixed #1600051.
The fix for bug #1341067 added a call to free some of the heap memory allocated by OpenSSL. This was not safe for repeated calls if OpenSSL is linked twice through different libraries and each is trying to free the same. Bug fixed #1604676.
If the changed page bitmap redo log tracking thread stops due to any reason, then shutdown will wait for a long time for the log tracker thread to quit, which it never does. Bug fixed #1606821.
Performing slow InnoDB shutdown (innodb_fast_shutdown set to 0) could result in an incomplete purge, if a separate purge thread is running (which is a default in Percona Server). Bug fixed #1609364.
Due to security reasons ld_preload libraries can now only be loaded from the system directories (/usr/lib64, /usr/lib) and the MySQL installation base directory.

Other bugs fixed:
#1515591 (upstream #79249), #1612551, #1609523, #756387, #1097870, #1603073, #1606478, #1606572, #1606782, #1607224, #1607359, #1607606, #1607607, #1607671, #1608385, #1608424, #1608437, #1608515, #1608845, #1609422, #1610858, #1612084, #1612118, and #1613641.
Find the release notes for Percona Server 5.5.51-38.1 in our online documentation. Report bugs on the launchpad bug tracker.

MariaDB 10.1.6 Overview and Highlights

MariaDB 10.1.6 was recently released, and is available for download here:
https://downloads.mariadb.org/mariadb/10.1.6/
This is the 4th beta, and 7th overall, release of MariaDB 10.1. There were not many major changes in this release, but a few notable items, as well as many overall bugs fixed (I counted 156, down ~50% from 10.1.5).
Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).
To me, these are the highlights:

RESET_MASTER is extended with TO # clause which allows one to specify the number of the first binary log. (MDEV-8469)
Added support for binlog_row_image=minimal for compatibility with MySQL.
New status variables: log_bin_basename, log_bin_index and relay_log_basename.
New status variables: innodb_buf_dump_status_frequency for determining how often the buffer pool dump status should be printed in the logs.
New status variables: Com_create_temporary_table and Com_drop_temporary_table for tracking the number of CREATE/DROP TEMPORARY TABLE statements.
Connect updated to 1.04.0001.
Mroonga updated to 5.04 (earlier versions of Mroonga did not work in 10.1).

Of course it goes without saying that do not use this for production systems since it is still only beta. However, I definitely recommend installing it on a test server and testing it out. And if you happen to be running a previous version of 10.1, then you should definitely upgrade to this latest release.
You can read more about the 10.1.6 release here:
https://mariadb.com/kb/en/mariadb-1016-release-notes/
And if interested, you can review the full list of changes in 10.1.6 (changelogs) here:
https://mariadb.com/kb/en/mariadb-1016-changelog/
Hope this helps.

MariaDB 5.5.44 Overview and Highlights

MariaDB 5.5.44 was recently released (it is the latest MariaDB 5.5), and is available for download here:
https://downloads.mariadb.org/mariadb/5.5.44/
This is a maintenance release, and no major changes, so there are only several noteworthy items (but one of those being a security fix and five potential crashing bug):

Security Fix: Client command line option –ssl-verify-server-cert (and MYSQL_OPT_SSL_VERIFY_SERVER_CERT option of the client API) when used together with –ssl will ensure that the established connection is SSL-encrypted and the MariaDB server has a valid certificate. This fixes CVE-2015-3152.
Crashing Bug: mysql_upgrade crashes the server with REPAIR VIEW (MDEV-8115).
Crashing Bug: Server crashes in intern_plugin_lock on concurrent installing semisync plugin and setting rpl_semi_sync_master_enabled (MDEV-363).
Crashing Bug: Server crash on updates with joins still on 10.0.18 (MDEV-8114).
Crashing Bug: Too large scale in DECIMAL dynamic column getter crashes mysqld (MDEV-7505).
Crashing Bug: Server crashes in get_server_from_table_to_cache on empty name (MDEV-8224).
XtraDB upgraded to 5.5.42-37.2
TokuDB upgraded to 7.5.7

Given the security fix, you may want to review the CVE to see if this is something you need to address. Also, please review the crashing bugs to see if they might affect you, and upgrade if so. Also, if running TokuDB or XtraDB, you may also want to benefit from those fixes, as well as the new MariaDB fixes (59 in all).
If interested, the official MariaDB 5.5.44 release notes are here:
https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5544-release-notes/
And the full list of fixed bugs and changes in MariaDB 5.5.44 can be found here:
https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5544-changelog/
Hope this helps.

MariaDB 10.1.5 Overview and Highlights

MariaDB 10.1.5 was recently released, and is available for download here:
https://downloads.mariadb.org/mariadb/10.1.5/
This is the 3rd beta, and 6th overall, release of MariaDB 10.1. There were not many major changes in this release, but a few notable items, as well as many overall bugs fixed (I counted 306).
Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).
To me, these are the highlights:

New status variables: New status variables Binlog_group_commit_trigger_count, Binlog_group_commit_trigger_timeout, and Binlog_group_commit_trigger_lock_wait used to examine which triggers caused a group commit to be made.
Encryption: Encryption for temporary files: temporary files created by the server (for binary log caches, for filesort, etc) are now encrypted if the encryption plugin is loaded and –encrypt-tmp-files was specified.
Encryption: Numerous encryption bugfixes.
A new column, INFO_BINARY, has been added to the Information Schema PROCESSLIST Table in order to avoid truncating queries with binary data (MDEV-7807).
Spider updated to 3.2.21.
Mroonga updated to 5.02.
Performance: mysqldump performance increased for large databases (MDEV-6714).

Of course it goes without saying that do not use this for production systems since it is still only beta. However, I definitely recommend installing it on a test server and testing it out. And if you happen to be running a previous version of 10.1, then you should definitely upgrade to this latest release.
You can read more about the 10.1.5 release here:
https://mariadb.com/kb/en/mariadb-1015-release-notes/
And if interested, you can review the full list of changes in 10.1.5 (changelogs) here:
https://mariadb.com/kb/en/mariadb-1015-changelog/
Hope this helps.

MariaDB 10.1.4 Overview and Highlights

MariaDB 10.1.4 was recently released, and is available for download here:
https://downloads.mariadb.org/mariadb/10.1.4/
This is the 2nd beta, and 5th overall, release of MariaDB 10.1. Now that it is beta, there were not as many major changes in this release (compared to 10.1.3), but there were a few notable items as well as many overall bugs fixed (I counted 367).
Since it’s beta, I’ll only cover the major changes and additions, and omit covering general bug fixes (feel free to browse them all here).
To me, these are the highlights:

Encryption: Many, many changes related to Table and Tablespace Encryption that was implemented in the last release, 10.1.3. With so many changes, I would refer to the updated documentation for the most specifics, and there is a nice summarization here as well (under the very first bullet point item).
Consistent support for IF EXISTS, IF NOT EXISTS, and OR REPLACE clauses was added to:

CREATE EVENT and DROP EVENT
CREATE INDEX and DROP INDEX
CREATE TRIGGER and DROP TRIGGER

New server variable: New server variable enforce_storage_engine
New status variable: New status variables to show the number of grants on different objects (Also see Status Variables Added in MariaDB 10.1)
New status variable: New status variables to track MASTER_GTID_WAIT time. This feature was contributed by Daniel Black.
New status variable: New status variable Slave_skipped_errors. This feature was contributed by Daniel Black.
Performance: xid cache scalability was significantly improved (by using lock-free hash)
Performance: VIEW definitions are now cached in memory (in the table definition cache)
Replication: Starting with this release, commits in certain instances in parallel replication complete immediately, avoiding losing throughput when many transactions need conflicting locks. See binlog_commit_wait_count.

Of course it goes without saying that do not use this for production systems as it is only the 2nd beta release of 10.1. However, I definitely recommend installing it on a test server and testing it out. And if you happen to be running a previous version of 10.1, then you should definitely upgrade to this latest release.
You can read more about the 10.1.4 release here:
https://mariadb.com/kb/en/mariadb-1014-release-notes/
And if interested, you can review the full list of changes in 10.1.4 (changelogs) here:
https://mariadb.com/kb/en/mariadb-1014-changelog/
Hope this helps.

MariaDB 5.5.43 Overview and Highlights

MariaDB 5.5.43 was recently released (it is the latest MariaDB 5.5), and is available for download here:
https://downloads.mariadb.org/mariadb/5.5.43/
This is a maintenance release, and so there were not too many major changes, but definitely a few worth mentioning, as well as one *important* caution:

Security Fixes: Fixes for the following security vulnerabilities:

CVE-2015-0501
CVE-2015-2571
CVE-2015-0505
CVE-2015-0499

Deprecation Notice: As per the MariaDB Deprecation Policy, this will be the final release of MariaDB 5.5 for Fedora 19 “Schrödinger’s Cat”, Ubuntu 10.04 LTS “Lucid”, and Mint 9 LTS “Isadora”. When the next version of MariaDB 5.5 is released, repositories for these distributions will go away.
Includes all bugfixes and updates from MySQL 5.5.43 (MySQL 5.5.43 Overview and Highlights)
TokuDB upgraded to 7.5.6
XtraDB upgraded to 5.5.42-37.1
Important mysql_upgrade Caution: The mysql_upgrade in this version introduced a serious bug which affects mysql_upgrade. If already running a MariaDB 5.5.x version, then you can safely skip running mysql_upgrade. However, if migrating from MySQL to MariaDB 5.5, then note this bug. For this specific bug, the problem appears if the targeted databases include data structures such as views with binary or text blobs. The malfunction is in the REPAIR VIEW statement which the script calls.

The fix will appear in MariaDB 5.5.44, which will be available soon (MariaDB 5.5.44 includes all MySQL 5.5.44 fixes, so it will be available very shortly after MySQL 5.5.44 is released).

Given the security fixes, you may want to review the CVEs to see if this is something you need to address. Also, if running TokuDB or XtraDB, you may also want to benefit from those fixes, as well as the new MariaDB fixes. However, if you plan on migrating from MySQL, if the above bug is relevant to you, then you should either upgrade to MariaDB 5.5.42, wait for 5.5.44, or possibly upgrade to MariaDB 10.0 (10.0.19 also contains the fix).
If interested, the official MariaDB 5.5.43 release notes are here:
https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5543-release-notes/
And the full list of fixed bugs and changes in MariaDB 5.5.43 can be found here:
https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5543-changelog/
Hope this helps.
 

MariaDB 5.5.42 Overview and Highlights

MariaDB 5.5.42 was recently released (it is the latest MariaDB 5.5), and is available for download here:
https://downloads.mariadb.org/mariadb/5.5.42/
This is a maintenance release, and so there were not too many changes at all and only 3 changes (enhancements) I felt noteworthy:

The new version of the Audit Plugin is 1.2 is included with the following new features:

In the audit log passwords are now masked, i.e. the password characters are replaced with asterisks.
It’s now possible to filter logging to include only DDL (CREATE, ALTER, etc.) or DML (INSERT, UPDATE, etc.) statements.
For more information please refer to the About the MariaDB Audit Plugin page. The plugin is disabled by default.

With this release we introduce a low-level Client API. Applications, linked with libmysqlclient client library can use these functions to read and parse raw protocol packets:

unsigned long mysql_net_read_packet(MYSQL *mysql);
unsigned long mysql_net_field_length(unsigned char **packet);

Includes all bugfixes and updates from MySQL 5.5.42 (MySQL 5.5.42 Overview and Highlights)
TokuDB upgraded to 7.5.5

So there are no real crucial fixes requiring an upgrade, however, if you’re running the audit plugin, or TokuDB, or you want the benefits of the new fixes in general, then you should consider an upgrade.
If interested, the official MariaDB 5.5.42 release notes are here:
https://mariadb.com/kb/en/mariadb/development/release-notes/mariadb-5542-release-notes/
And the full list of fixed bugs and changes in MariaDB 5.5.42 can be found here:
https://mariadb.com/kb/en/mariadb/development/changelogs/mariadb-5542-changelog/
Hope this helps.
 

Table and tablespace encryption on MariaDB 10.1.3

Introduction
For the moment, the only engines that fully support encryption are XtraDB and InnoDB. The Aria storage engine also supports encryption, but only for temporary tables.
MariaDB supports 2 different way to encrypt data in InnoDB/XtraDB:

Table encryption: Only tables which you create with PAGE_ENCRYPTION=1 are encrypted. This feature was created by eperi.
Tablespace encryption: Everything is encrypted (including log files). This feature was created by Google and is based on their MySQL branch.

InnoDB Table Level Encryption
Table level encryption means that you choose which tables to encrypt. This allows you to balance security with speed. To use table encryption, you have to:

Set the value of encryption-algorithm to the algorithm of your choice.
Load the file-key-management-plugin (or similar)
Define the location of key file
Create keys

Example:# Table level encryption configuration
plugin-load-add=file_key_management_plugin.so
file-key-management-plugin
file-key-management-plugin-filename=/mnt/dfs/keys.txt
encryption-algorithm=aes_ctrKeys can be generated using OpenSSL with following commandshell>openssl enc -aes-256-ctr -k mypass -P -md sha1
salt=BFA606C6079DAD33
key=3DB1F43A606DA6ADF4AEB25B44A5E5FE2126EDEACF5AF8DF7B982D8143191936
iv =21C4592A16C870DD47B162F8959E562FThe key file is a text file containing an key id, the hex-encoded iv and the hex-encoded key. Example keys.txt using above generated key:1;21C4592A16C870DD47B162F8959E562F;3DB1F43A606DA6ADF4AEB25B44A5E5FE2126EDEACF5AF8DF7B982D8143191936After this is it up to database designer to select tables that contain sensitive data for encryption. Encryption can be enabled to table in table creation time or using ALTER TABLE. As an example:CREATE TABLE customer(CUSTOMER_ID BIGINT NOT NULL PRIMARY KEY, CUSTOMER_NAME VARCHAR(80), CUSTOMER_CREDITCARD VARCHAR(20)) ENGINE=InnoDB page_encryption=1 page_encryption_key=1;
ALTER TABLE sales page_encryption=1 page_encryption_key=1;In table encryption currently keys can’t be changed but used key can be changed using ALTER TABLE. If no key identifier is provided a default key is used. Default key can be set either on my.cnf withinnodb_default_page_encryption_key=4or dynamically using global setting:set global innodb_default_page_encryption_key=4;Default key is used e.g.create table t1(a int not null primary key) engine=innodb page_encryption=1;
InnoDB Tablespace Encryption
In tablespace encryption all InnoDB tables are encrypted. Additionally, you may encrypt InnoDB log files, Aria tables (ROW_FORMAT=PAGE) and Aria temporary tables. To use tablespace encryption, you have to:

 Set the value of encryption-algorithm to the algorithm of your choice.
Load the example-key-management-plugin (or similar)

Example:# Tablespace encryption configuration
encryption-algorithm=aes_ctr
innodb-encrypt-tables
plugin-load-add=example_key_management_plugin.so
example_key_management_plugin
# encrypt Aria tables
aria
aria-encrypt-tables
# encrypt tmp tables
encrypt-tmp-disk-tables
# encrypt InnoDB log files
innodb-encrypt-log
# key rotation
innodb-encryption-threads=4
innodb-encryption-rotate-key-age=1800In tablespace encryption keys are not static. Instead so called key rotation is used. In key rotation used encryption key is changed if key used on a page is older than innodb-encryption-rotate-key-age seconds.
InnoDB Tablespace Scrubbing
Scrubbing means that there is a background process that regularly scans through all tables and upgrades the encryption keys for the pages. This happens either as part of purge (non compressed) or scrubbing by scanning whole tablespaces (added into key rotation threads). Purge is a a type of garbage collection that InnoDB internally runs to improve performance. Configuration for this feature might look as follows:# InnoDB Tablespace scrubbing
innodb-immediate_scrub_data_uncompressed
innodb-background-scrub-data-uncompressed
innodb-background-scrub-data-compressed
# check if spaces needs scrubbing every 500 seconds
innodb_background_scrub_data_check_interval=500
# scrub spaces that were last scrubbed longer than 1800 seconds
innodb_background_scrub_data_interval=1800
Performance Impact
Encrypting the tables or tablespaces naturally have some effect on overall performance of the system. Naturally, the amount of performance effect encryption has is dependent on used hardware, workload and used encryption method. Goal of this section is to give some indication how much effect on performance there is when table encryption is used or when tablespace encryption is used when compared to setup where no encryption is used.
All experiments where conducted on Intel Xeon E5-2690 @ 2.9GHz CPU containing 2 sockets with 8 cores each using hyper threading, thus 32 total cores and Linux 3.4.12 with 132G main memory. The database is stored on a Fusion-io ioDrive2 Duo 2.41TB Firmware v7.2.5, rev 110646, Driver 3.3.4 build 5833069. The database filesystem is using NVMFS and all test logs and outputs are stored on second ioDrive using EXT4. We use On-Line Transaction Processing (OLTP) benchmark from Percona https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql. This TPC-C like workload involves a mix of five concurrent transaction types executed on-line or queued for deferred execution. The database is comprised of nine tables with a wide range of record and population sizes. Results are measured in terms of transactions per minute (tpmC). We will use 1000 warehouses producing ~100G database and buffer pool size 50G, so that full database does not fit to buffer pool. Additionally, we will use only InnoDB plugin as a storage engine. Finally, we use 3 hour measure time.
In the first graph we compare the resulting tpmC results on normal InnoDB tables (unencrypted tables), page encrypted tables and tablespace encryption (google encrypted on graph).

 
In the second graph we compare the number of New Order transactions/second with InnoDB tables (unencrypted tables), page encrypted tables and tablespace encryption (google encrypted on graph)

Conclusions
MariaDB Corporation would like to thank eperi and Google for their contributions to MariaDB.

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569