Buy Percona ServicesBuy Now!

How We Made Percona XtraDB Cluster Scale

Latest MySQL Performance Blog posts - April 19, 2017 - 2:46pm

In this blog post, we’ll look at the actions and efforts Percona experts took to scale Percona XtraDB Cluster.

Introduction

When we first started analyzing Percona XtraDB Cluster performance, it was pretty bad. We would see contention even with 16 threads. Performance was even worse with sync binlog=1, although the same pattern was observed even with the binary log disabled. The effect was not only limited to OLTP workloads, as even other workloads (like update-key/non-key) were also affected in a wider sense than OLTP.

That’s when we started analyzing the contention issues and found multiple problems. We will discuss all these problems and the solutions we adapted. But before that, let’s look at the current performance level.

Check this blog post for more details.

The good news is Percona XtraDB Cluster is now optimized to scale well for all scenarios, and the gain is in the range of 3x-10x.

Understanding How MySQL Commits a Transaction

Percona XtraDB Cluster contention is associated mainly with Commit Monitor contention, which comes into the picture during commit time. It is important to understand the context around it.

When a commit is invoked, it proceeds in two phases:

  • Prepare phase: mark the transaction as PREPARE, updating the undo segment to capture the updated state.
    • If bin-log is enabled, redo changes are not persisted immediately. Instead, a batch flush is done during Group Commit Flush stage.
    • If bin-log is disabled, then redo changes are persisted immediately.
  • Commit phase: Mark the transaction commit in memory.
    • If bin-log is enabled, Group Commit optimization kicks in, thereby causing a flush of redo-logs (that persists changes done to db-objects + PREPARE state of transaction) and this action is followed by a flush of the binary logs. Since the binary logs are flushed, redo log capturing of transaction commit doesn’t need to flush immediately (Saving fsync)
    • If bin-log is disabled, redo logs are flushed on completion of the transaction to persist the updated commit state of the transaction.
What is a Monitor in Percona XtraDB Cluster World?

Monitors help maintain transaction ordering. For example, the Commit Monitor ensures that no transaction with a global-seqno greater than the current commit-processing transaction’s global seqno is allowed to proceed.

How Percona XtraDB Cluster Commits a Transaction

Percona XtraDB Cluster follows the existing MySQL semantics of course, but has its own step to commit the transaction in the replication world. There are two important themes:

  1. Apply/Execution of transaction can proceed in parallel
  2. Commit is serialized based on cluster-wide global seqno.

Let’s understand the commit flow with Percona XtraDB Cluster involved (Percona XtraDB Cluster registers wsrep as an additional storage engine for replication).

  • Prepare phase:
    • wsrep prepare: executes two main actions:
      • Replicate the transaction (adding the write-set to group-channel)
      • Entering CommitMonitor. Thereby enforcing ordering of transaction.
    • binlog prepare: nothing significant (for this flow).
    • innobase prepare: mark the transaction in PREPARE state.
      • As discussed above, the persistence of the REDO log depends on if the binlog is enabled/disabled.
  • Commit phase
    • If bin-log is enabled
      • MySQL Group Commit Logic kicks in. The semantics ensure that the order of transaction commit is the same as the order of them getting added to the flush-queue of the group-commit.
    • If bin-log is disabled
      • Normal commit action for all registered storage engines is called with immediate persistence of redo log.
    • Percona XtraDB Cluster then invokes the post_commit hook, thereby releasing the Commit Monitor so that the next transaction can make progress.

With that understanding, let’s look at the problems and solutions:

PROBLEM-1:

Commit Monitor is exercised such that the complete commit operation is serialized. This limits the parallelism associated with the prepare-stage. With log-bin enabled, this is still ok since redo logs are flushed at group-commit flush-stage (starting with 5.7). But if log-bin is disabled, then each commit causes an independent redo-log-flush (in turn probable fsync).

OPTIMIZATION-1:

Split the replication pre-commit hook into two explicit actions: replicate (add write-set to group-channel) + pre-commit (enter commit-monitor).

The replicate action is performed just like before (as part of storage engine prepare). That will help complete the InnoDB prepare action in parallel (exploring much-needed parallelism in REDO flush with log-bin disabled).

On completion of replication, the pre-commit hook is called. That leads to entering the Commit Monitor for enforcing the commit ordering of the transactions. (Note: Replication action assigns the global seqno. So even if a transaction with a higher global seqno finishes the replication action earlier (due to CPU scheduling) than the transaction with a lower global seqno, it will wait in the pre-commit hook.)

Improved parallelism in the innodb-prepare stage helps accelerate log-bin enabled flow, and the same improved parallelism significantly helps in the log-bin disabled case by reducing redo-flush contention, thereby reducing fsyncs.

PROBLEM-2:

MySQL Group Commit already has a concept of ordering transactions based on the order of their addition to the GROUP COMMIT queue (FLUSH STAGE queue to be specific). Commit Monitor enforces the same, making the action redundant but limiting parallelism in MySQL Group Commit Logic (including redo-log flush that is now delayed to the flush stage).

With the existing flow (due to the involvement of Commit Monitor), only one transaction can enter the GROUP COMMIT Queue, thereby limiting optimal use of Group Commit Logic.

OPTIMIZATION-2:

Release the Commit Monitor once the transaction is successfully added to flush-stage of group-commit. MySQL will take it from there to maintain the commit ordering. (We call this interim-commit.)

Releasing the Commit Monitor early helps other transactions to make progress and real MySQL Group Commit Leader-Follower Optimization (batch flushing/sync/commit) comes into play.

This also helps ensure batch REDO log flushing.

PROBLEM-3:

This problem is specific to when the log-bin is disabled. Percona XtraDB Cluster still generates the log-bin, as it needs it for forming a replication write-set (it just doesn’t persist this log-bin information). If disk space is not a constraint, then I would suggest operating Percona XtraDB Cluster with log-bin enabled.

With log-bin disabled, OPTIMIZATION-1 is still relevant, but OPTIMIZATION-2 isn’t, as there is no group-commit protocol involved. Instead, MySQL ensures that the redo-log (capturing state change of transaction) is persisted before reporting COMMIT as a success. As per the original flow, the Commit Monitor is not released till the commit action is complete.

OPTIMIZATION-3:

The transaction is already committed to memory and the state change is captured. This is about persisting the REDO log only (REDO log modification is already captured by mtr_commit). This means we can release the Commit Monitor just before the REDO flush stage kicks in. Correctness is still ensured as the REDO log flush always persists the data sequentially. So even if trx-1 loses its slots before the flush kicks in, and trx-2 is allowed to make progress, trx-2’s REDO log flush ensures that trx-1’s REDO log is also flushed.

Conclusion

With these three main optimizations, and some small tweaks, we have tuned Percona XtraDB Cluster to scale better and made it fast enough for the growing demands of your applications. All of this is available with the recently released Percona XtraDB Cluster 5.7.17-29.20. Give it a try and watch your application scale in a multi-master environment, making Percona XtraDB Cluster the best fit for your HA workloads.

Percona XtraDB Cluster 5.6.35-26.20-3 is now available

Latest MySQL Performance Blog posts - April 19, 2017 - 1:40pm

Percona announces the release of Percona XtraDB Cluster 5.6.35-26.20-3 on April 13, 2017. Binaries are available from the downloads section or our software repositories.

Percona XtraDB Cluster 5.6.35-26.20-3 is now the current release, based on the following:

All Percona software is open-source and free. Details of this release can be found in the 5.6.35-26.20-3 milestone on Launchpad.

NOTE: Due to end of life, Percona will stop producing packages for the following distributions after July 31, 2017:

  • Red Hat Enterprise Linux 5 (Tikanga)
  • Ubuntu 12.04 LTS (Precise Pangolin)

You are strongly advised to upgrade to latest stable versions if you want to continue using Percona software.

Fixed Bugs

  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-797: Blocked wsrep_desync toggling while the node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • Fixed several packaging and dependency issues.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

cannot login with public key

Lastest Forum Posts - April 19, 2017 - 10:36am
Hello
I have installed pmm-server and I can log data from my clients, but I cannot login to the pmm server.

I created the public key and set it through the web interface during setup, but when I try to log in, I get this message:

sign_and_send_pubkey: signing failed: agent refused operation
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).


The key is the following
2048 SHA256:EyfiOgKJidxr/pOiFtSjlzpaVkjsOQfpKX0n762SgLg /home/mox/.ssh/id_rsa.pub (RSA)

I tried different way to log in, I found in the forum several suggestions, like using different login names (admin, pmm), but I couldn't manage to connect.

Thanks

Moreno


p.s.
Below the debug log from ssh

debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: Applying options for *
debug1: Connecting to 192.168.178.59 [192.168.178.59] port 22.
debug1: Connection established.
debug1: identity file id_rsa.pub type 1
debug1: key_load_public: No such file or directory
debug1: identity file id_rsa.pub-cert type -1
debug1: Enabling compatibility mode for protocol 2.0
debug1: Local version string SSH-2.0-OpenSSH_7.2p2 Ubuntu-4ubuntu2.1
debug1: Remote protocol version 2.0, remote software version OpenSSH_6.6.1
debug1: match: OpenSSH_6.6.1 pat OpenSSH_6.6.1* compat 0x04000000
debug1: Authenticating to 192.168.178.59:22 as 'mox'
debug1: SSH2_MSG_KEXINIT sent
debug1: SSH2_MSG_KEXINIT received
debug1: kex: algorithm: curve25519-sha256@libssh.org
debug1: kex: host key algorithm: ecdsa-sha2-nistp256
debug1: kex: server->client cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: kex: client->server cipher: chacha20-poly1305@openssh.com MAC: <implicit> compression: none
debug1: expecting SSH2_MSG_KEX_ECDH_REPLY
debug1: Server host key: ecdsa-sha2-nistp256 SHA256:tBmNep22sfXseHo1INDzjF7vy6KsR3vUhNBbLUSBIgM
debug1: Host '192.168.178.59' is known and matches the ECDSA host key.
debug1: Found key in /home/mox/.ssh/known_hosts:36
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS sent
debug1: expecting SSH2_MSG_NEWKEYS
debug1: rekey after 134217728 blocks
debug1: SSH2_MSG_NEWKEYS received
debug1: SSH2_MSG_SERVICE_ACCEPT received
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic
debug1: Next authentication method: gssapi-keyex
debug1: No valid Key exchange context
debug1: Next authentication method: gssapi-with-mic
debug1: Unspecified GSS failure. Minor code may provide more information
No Kerberos credentials available

debug1: Unspecified GSS failure. Minor code may provide more information
No Kerberos credentials available

debug1: Unspecified GSS failure. Minor code may provide more information


debug1: Unspecified GSS failure. Minor code may provide more information
No Kerberos credentials available

debug1: Next authentication method: publickey
debug1: Offering RSA public key: id_rsa.pub
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic
debug1: Offering RSA public key: /home/mox/.ssh/id_rsa
debug1: Authentications that can continue: publickey,gssapi-keyex,gssapi-with-mic
debug1: No more authentication methods to try.
Permission denied (publickey,gssapi-keyex,gssapi-with-mic).



Percona XtraDB Cluster 5.7.17-29.20 is now available

Lastest Forum Posts - April 19, 2017 - 7:23am
Percona announces the release of Percona XtraDB Cluster 5.7.17-29.20 on April 19, 2017. Binaries are available from the downloads section or our software repositories.

NOTE: You can also run Docker containers from the images in the Docker Hub repository.

Percona XtraDB Cluster 5.7.17-29.20 is now the current release, based on the following:All Percona software is open-source and free.

Performance Improvements

This release is focused on performance and scalability with increasing workload threads. Tests show up to 10 times increase in performance.

Fixed Bugs
  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • Improved SST and IST log messages for better readability and unification.
  • Excluded the garbd node from flow control calculations.
  • Added extra checks to verify that SSL files (certificate, certificate authority, and key) are compatible before openning connection.
  • Improved parallelism for better scaling with multiple threads.
  • Added validations for DISCARD TABLESPACE and IMPORT TABLESPACE in PXC Strict Mode to prevent data inconsistency.
  • Added the wsrep_flow_control_status variable to indicate if node is in flow control (paused).
  • PXC-766: Added the wsrep_ist_receive_status variable to show progress during an IST.
  • Allowed CREATE TABLE ... AS SELECT (CTAS) statements with temporary tables (CREATE TEMPORARY TABLE ... AS SELECT) in PXC Strict Mode. For more information, see 1666899.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-783: Improved the wsrep stage framework.
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-795: Set --parallel=4 as default option for wsrep_sst_xtrabackup-v2 to run four threads with XtraBackup.
  • PXC-797: Blocked wsrep_desync toggling while node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • PXC-805: Inherited upstream fix to avoid using deprecated variables, such as INFORMATION_SCHEMA.SESSION_VARIABLE. For more information, see 1676401.
  • PXC-811: Changed default values for the following variables:
    • fc_limit from 16 to 100
    • send_window from 4 to 10
    • user_send_window from 2 to 4
  • Moved wsrep settings into a separate configuration file (/etc/my.cnf.d/wsrep.cnf).
  • Fixed mysqladmin shutdown to correctly stop the server on systems using systemd.
  • Fixed several packaging and dependency issues.
Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Performance improvements in Percona XtraDB Cluster 5.7.17-29.20

Latest MySQL Performance Blog posts - April 19, 2017 - 6:50am

In our latest release of Percona XtraDB Cluster, we’ve introduced major performance improvements to the MySQLwrite-set replication layer. In this post, we want to show what these improvements look like.

For the test, we used the sysbench OLTP_RW, UPDATE_KEY and UPDATE_NOKEY workloads with 100 tables, 4mln rows each, which gives about 100GB of datasize. In all the tests we use a three-node setup, connected via a 10GB network, with the sysbench load directed to the one primary node.

In the first chart, we show improvements comparing to the previous version (5.7.16):

The main improvements come from concurrent workloads, under multiple threads.

The previous chart is for cases using enabled binary logs, but in some situations we will have deployments without binary logs enabled (Percona XtraDB Cluster does not require them). The latest release significantly improves performance for this case as well.

Here is a chart showing throughput without binary logs:

Where does Percona XtraDB Cluster place in comparison with similar technologies? To find out, we’ll compare this release with MySQL 5.7.17 Group Replication and with the recently released MariaDB 10.2.5 RC.

For MySQL 5.7.17 Group Replication, I’ve reviewed two cases: “durable” with sync_binlog=1, and “relaxed durability” with sync_binlog=0.

Also for MySQL 5.7.17 Group Replication, we want to review two cases with different flow_control settings. The first setting is flow_control=25000 (the default setting). It provides better performance, but with the drawbacks that non-primary nodes will fall behind significantly and MySQL Group Replication does not provide a way to protect from reading stale data. So with a default flow_control=25000, we risk reading very outdated data. We also tested MySQL Group Replication with flow_control=1000 to minimize stale data on non-primary nodes.

A note on the Flow Control topic: it is worth mentioning that we also changed the flow_control default for Percona XtraDB Cluster. The default value is 100 instead of 16 (as in version 5.7.16).

Comparison chart with sync_binlog=1 (for MySQL Group Replication):

Comparison chart with sync_binlog=0 (for MySQL Group Replication):

So there are couple conclusions we can make out of these charts.

  1. The new version of Percona XtraDB Cluster performs on the level with MySQL Group Replication
  2. flow_control for MySQl Group Replication really makes a difference for performance, and default flow_control=25000 is better (with the risk of a lot of outdated data on non-primary nodes)

The reference our benchmark files and config files are here.

Percona XtraDB Cluster 5.7.17-29.20 is now available

Latest MySQL Performance Blog posts - April 19, 2017 - 6:37am

Percona announces the release of Percona XtraDB Cluster 5.7.17-29.20 on April 19, 2017. Binaries are available from the downloads section or our software repositories.

NOTE: You can also run Docker containers from the images in the Docker Hub repository.

Percona XtraDB Cluster 5.7.17-29.20 is now the current release, based on the following:

All Percona software is open-source and free.

Performance Improvements

This release is focused on performance and scalability with increasing workload threads. Tests show up to 10 times increase in performance.

Fixed Bugs

  • Updated semantics for gcache page cleanup to trigger when either gcache.keep_pages_size or gcache.keep_pages_count exceeds the limit, instead of both at the same time.
  • Added support for passing the XtraBackup buffer pool size with the use-memory option under [xtrabackup] and the innodb_buffer_pool_size option under [mysqld] when the --use-memory option is not passed with the inno-apply-opts option under [sst].
  • Fixed gcache page cleanup not triggering when limits are exceeded.
  • Improved SST and IST log messages for better readability and unification.
  • Excluded the garbd node from flow control calculations.
  • Added extra checks to verify that SSL files (certificate, certificate authority, and key) are compatible before openning connection.
  • Improved parallelism for better scaling with multiple threads.
  • Added validations for DISCARD TABLESPACE and IMPORT TABLESPACE in PXC Strict Mode to prevent data inconsistency.
  • Added the wsrep_flow_control_status variable to indicate if node is in flow control (paused).
  • PXC-766: Added the wsrep_ist_receive_status variable to show progress during an IST.
  • Allowed CREATE TABLE ... AS SELECT (CTAS) statements with temporary tables (CREATE TEMPORARY TABLE ... AS SELECT) in PXC Strict Mode. For more information, see 1666899.
  • PXC-782: Updated xtrabackup-v2 script to use the tmpdir option (if it is set under [sst], [xtrabackup] or [mysqld], in that order).
  • PXC-783: Improved the wsrep stage framework.
  • PXC-784: Fixed the pc.recovery procedure to abort if the gvwstate.dat file is empty or invalid, and fall back to normal joining process. For more information, see 1669333.
  • PXC-794: Updated the sockopt option to include a comma at the beginning if it is not set by the user.
  • PXC-795: Set --parallel=4 as default option for wsrep_sst_xtrabackup-v2 to run four threads with XtraBackup.
  • PXC-797: Blocked wsrep_desync toggling while node is paused to avoid halting the cluster when running FLUSH TABLES WITH READ LOCK. For more information, see 1370532.
  • PXC-805: Inherited upstream fix to avoid using deprecated variables, such as INFORMATION_SCHEMA.SESSION_VARIABLE. For more information, see 1676401.
  • PXC-811: Changed default values for the following variables:
    • fc_limit from 16 to 100
    • send_window from 4 to 10
    • user_send_window from 2 to 4
  • Moved wsrep settings into a separate configuration file (/etc/my.cnf.d/wsrep.cnf).
  • Fixed mysqladmin shutdown to correctly stop the server on systems using systemd.
  • Fixed several packaging and dependency issues.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Percona XtraBackup 2.4.7 is Now Available

Lastest Forum Posts - April 18, 2017 - 11:53pm
Percona announces the GA release of Percona XtraBackup 2.4.7 on April 18, 2017. You can download it from our download site and apt and yumrepositories.

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 free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups. New features:

  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:

  • Backups were missing *.isl files for general tablespace. Bug fixed #1658692.
  • In 5.7, MySQL changed default checksum algorithm to crc32, while xtrabackup was using innodb. This caused xtrabackup to perform extra checksum calculations which were not needed. Bug fixed #1664405.
  • For system tablespaces consisting of multiple files xtrabackup updated LSN only in first file. This caused MySQLversions lower than 5.7 to fail on startup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.
Release notes with all the bugfixes for Percona XtraBackup 2.4.7 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Percona XtraBackup 2.3.8 is Now Available

Lastest Forum Posts - April 18, 2017 - 11:51pm
Percona announces the release of Percona XtraBackup 2.3.8 on April 18, 2017. Downloads are available from our download site or Percona Software 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 free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series. New Features

  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:

  • xtrabackup would not create fresh InnoDB redo logs when preparing incremental backup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.
Release notes with all the bugfixes for Percona XtraBackup 2.3.8 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

Cluster failure following a filesystem-level error

Lastest Forum Posts - April 18, 2017 - 12:51pm
We have a three-node cluster, on VMs using ExtremeIO storage for the data filesystem, which suffered a failure this morning. The event that triggered the failure appears to have been a storage-level error which caused node 3 to fail to create a new binlog file, in response to which mysqld declared that it was ceasing all logging. Some time afterward, nodes 1 and 2 experienced simultaneous failures to commit a set of updates, declared themselves inconsistent, and shut down, whereupon node 3 lost quorum and declared itself non-primary.

Galera does use ROW replication data, as we all know. At what level does Galera obtain the data, and at what level does logging get shut off in response to a storage-level failure as described here? Would mysqld disabling all logging cause Galera replication from node 3 to fail? Our working theory at present is that nodes 1 and 2 failed because the attempted to update rows which had been written by node 3, but never replicated to nodes 1 and 2 because the binary logging failure on node 3 also disabled outgoing Galera replication from node 3. Does this hypothesis make sense?

Percona XtraBackup 2.4.7 is Now Available

Latest MySQL Performance Blog posts - April 18, 2017 - 12:19pm

Percona announces the GA release of Percona XtraBackup 2.4.7 on April 18, 2017. You can download it from our download site and 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 free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

New features:
  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:
  • Backups were missing *.isl files for general tablespace. Bug fixed #1658692.
  • In 5.7, MySQL changed default checksum algorithm to crc32, while xtrabackup was using innodb. This caused xtrabackup to perform extra checksum calculations which were not needed. Bug fixed #1664405.
  • For system tablespaces consisting of multiple files xtrabackup updated LSN only in first file. This caused MySQL versions lower than 5.7 to fail on startup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

Release notes with all the bugfixes for Percona XtraBackup 2.4.7 are available in our online documentation. Please report any bugs to the launchpad bug tracker.

Percona XtraBackup 2.3.8 is Now Available

Latest MySQL Performance Blog posts - April 18, 2017 - 11:36am

Percona announces the release of Percona XtraBackup 2.3.8 on April 18, 2017. Downloads are available from our download site or Percona Software 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 free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

This release is the current GA (Generally Available) stable release in the 2.3 series.

New Features
  • Percona XtraBackup now uses hardware accelerated implementation of crc32 where it is supported.
  • Percona XtraBackup has implemented new options: --tables-exclude and --databases-exclude that work similar to --tables and --databases options, but exclude given names/paths from backup.
  • The xbstream binary now supports parallel extraction with the --parallel option.
  • The xbstream binary now supports following new options: --decrypt, --encrypt-threads, --encrypt-key, and --encrypt-key-file. When --decrypt option is specified xbstream will automatically decrypt encrypted files when extracting input stream. Either --encrypt-key or --encrypt-key-file options must be specified to provide encryption key, but not both. Option --encrypt-threads specifies the number of worker threads doing the encryption, default is 1.
Bugs Fixed:
  • xtrabackup would not create fresh InnoDB redo logs when preparing incremental backup. Bug fixed #1669592.
  • xtrabackup --export can now export tables that have more than 31 index. Bug fixed #1089681.
  • Unrecognized character x01; marked by message could be seen if backups were taken with the version check enabled. Bug fixed #1651978.

Release notes with all the bugfixes for Percona XtraBackup 2.3.8 are available in our online documentation. Bugs can be reported on the launchpad bug tracker.

M17 Conference Observations on the Future of MariaDB

Latest MySQL Performance Blog posts - April 18, 2017 - 10:52am

In this blog post, I’ll discuss some of my thoughts about the future of MariaDB after attending the M17 Conference.

Let me start with full disclosure: I’m the CEO of Percona, and we compete with the MariaDB Corporation in providing Support for MariaDB and other services. I probably have some biases!

Last week I attended the MariaDB Developers UnConference and the M17 Conference, which provided great insights into MariaDB’s positioning as a project and as a business. Below are some of my thoughts as I attended various sessions at the conference:

Breaking away from their MySQL past. Michael Howard’s (MariaDB CEO) keynote focused on breaking away from the past and embracing the future. In this case, the “past” means proprietary databases. But I think MariaDB is also trying to break away from their past of being a MySQL variant, and focus on becoming completely independent technology. If I didn’t know their history, I wouldn’t recognize how much codebase MariaDB shares with MySQL – and how much MariaDB innovation Oracle still drives.

MySQL compatibility is no longer the primary goal. In its first version, MariaDB 5.1 was truly compatible with MySQL (it had relatively few differences). By contrast, MariaDB 10.2 has different replication, JSON support and a very different optimizer. With MariaDB 10.3, more changes are planned for InnoDB on disk format, and no plans exist to remove .frm files and use the MySQL 8 Data Dictionary. With these features, another level of MySQL compatibility is removed. The MariaDB knowledgebase states: “For all practical purposes, MariaDB is a binary drop in replacement for the same MySQL version.” The argument can still be made that this is true for MySQL 5.7 (as long as your application does not use some of the new features), but this does not seem to be the case for MySQL 8.

The idea seems to be that since MariaDB has replaced MySQL in many (most?) Linux distributions, and many people use MariaDB when they think they are using MySQL, compatibility is not that big of a deal anymore.

Embracing contributions and keeping the development process open is a big focus of MariaDB. Facebook, Google, Alibaba and Tencent have contributed to MariaDB, along with many independent smaller companies and independent developers (Percona among them). This is different from the MySQL team at Oracle, who have provided some contributions, but not nearly to the extent that MariaDB has. An open source model is a double-edged sword – while it gives you more features, it also makes it harder to maintain a consistent user experience and consistent quality of code and documentation. It will be interesting to see how MariaDB deals with these challenges.

Oracle compatibility. MariaDB strives to be the open source database that is the most compatible with Oracle, and therefore the easiest to migrate to. I have heard people compare MariaDB’s desire for Oracle compatibility to EDB Postgres – only with the advantage of being open source as opposed to proprietary software.  For MariaDB 10.3 (alpha), they are developing support for Oracle PL/SQL syntax for stored procedures to be able to migrate applications with little, if any, changes. They are also developing support for SEQUENCE and other Oracle features, including a special sql_mode=ORACLE to maximize compatibility.

BSL as a key for success. When it comes to business source licensing (BSL), I couldn’t quite resolve the conflict I found in MariaDB’s messaging. On the one hand, MariaDB promotes open source as a great way to escape vendor lock-in (which we at Percona completely agree with). But on the other hand, Michael Howard stated that BSL software (“Eventual Open Source”) is absolutely critical for MariaDB’s commercial success. Is the assumption here that if vendor lock-in is not forever, it is not really lock-in? Currently, only MariaDB MaxScale is BSL, but it sounds like we should expect more of their software to follow this model.

Note. As MariaDB Server and MariaDB Columnstore use a lot of Oracle’s GPL code, these will most likely remain GPL.

I enjoyed attending both conferences. I had a chance to meet with many old friends and past colleagues, as well as get a much better feel for where MariaDB is going and when it is appropriate to advise its usage.

Database crashes when binary logging not possible

Lastest Forum Posts - April 18, 2017 - 7:04am
Hi everyone,
We have been experiencing this issue at least once a day for the last 5 days:

Code: 2017-04-18T00:11:49.499793Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 11624ms. The settings might not be optimal. (flushed=200, during the time.) 2017-04-18T00:11:49.649596Z 14 [ERROR] /usr/sbin/mysqld: Binary logging not possible. Message: An error occurred during flush stage of the commit. 'binlog_error_action' is set to 'ABORT_SERVER'. Hence aborting the server. 00:11:49 UTC - mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail. Please help us make Percona Server better by reporting any bugs at http://bugs.percona.com/ key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=189 max_threads=215 thread_count=101 connection_count=101 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 93386 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x7f04ff816000 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 7f0da45dce00 thread_stack 0x30000 /usr/sbin/mysqld(my_print_stacktrace+0x2c)[0xec707c] /usr/sbin/mysqld(handle_fatal_signal+0x461)[0x79d941] /lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7f0dadae3330] /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x37)[0x7f0dacf24c37] /lib/x86_64-linux-gnu/libc.so.6(abort+0x148)[0x7f0dacf28028] /usr/sbin/mysqld[0x761bc0] /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG33handle_binlog_flush_or_sync_errorEP3THDb+0x19b)[0xe5d1ab] /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG14ordered_commitEP3THDbb+0x131)[0xe65691] /usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG6commitEP3THDb+0xcb3)[0xe676e3] /usr/sbin/mysqld(_Z15ha_commit_transP3THDbb+0x131)[0x7fd571] /usr/sbin/mysqld(_Z12trans_commitP3THD+0x39)[0xd51cd9] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0xc92)[0xca4f12] /usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x5c5)[0xcaba75] /usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0x92f)[0xcac42f] /usr/sbin/mysqld(_Z10do_commandP3THD+0x1b7)[0xcaddc7] /usr/sbin/mysqld(handle_connection+0x2a0)[0xd6f8c0] /usr/sbin/mysqld(pfs_spawn_thread+0x1b4)[0x1246fc4] /lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7f0dadadb184] /lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f0dacfe837d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (7f04ff828030): COMMIT Connection ID (thread ID): 14 Status: NOT_KILLED You may download the Percona Server operations manual by visiting http://www.percona.com/software/percona-server/. You may find information in the manual which will help you identify the cause of the crash. 2017-04-18T00:11:51.272796Z mysqld_safe Number of processes running now: 0 2017-04-18T00:11:51.274351Z mysqld_safe mysqld restarted 2017-04-18T00:11:51.296136Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2017-04-18T00:11:51.296265Z 0 [Warning] Changed limits: max_connections: 214 (requested 300) 2017-04-18T00:11:51.296278Z 0 [Warning] Changed limits: table_open_cache: 400 (requested 2000) 2017-04-18T00:11:51.443192Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-04-18T00:11:51.444043Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.14-7-log) starting as process 8607 ... 2017-04-18T00:11:51.454822Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed. 2017-04-18T00:11:51.454848Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html 2017-04-18T00:11:51.454912Z 0 [Note] InnoDB: PUNCH HOLE support available 2017-04-18T00:11:51.454921Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-04-18T00:11:51.454925Z 0 [Note] InnoDB: Uses event mutexes 2017-04-18T00:11:51.454929Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier 2017-04-18T00:11:51.454933Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.8 2017-04-18T00:11:51.454937Z 0 [Note] InnoDB: Using Linux native AIO 2017-04-18T00:11:51.455688Z 0 [Note] InnoDB: Number of pools: 1 2017-04-18T00:11:51.455770Z 0 [Note] InnoDB: Using CPU crc32 instructions 2017-04-18T00:11:51.456478Z 0 [Note] InnoDB: Initializing buffer pool, total size = 32G, instances = 8, chunk size = 128M 2017-04-18T00:11:52.181194Z 0 [Note] InnoDB: Completed initialization of buffer pool 2017-04-18T00:11:52.312596Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2017-04-18T00:11:52.324805Z 0 [Note] InnoDB: Recovering partial pages from the parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite 2017-04-18T00:11:52.405692Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2017-04-18T00:11:52.542600Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 303947279741 2017-04-18T00:11:52.613070Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303952522240 2017-04-18T00:11:52.681503Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303957765120 2017-04-18T00:11:52.841734Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303948327936 2017-04-18T00:11:52.919589Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303953570816 2017-04-18T00:11:53.005760Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303958813696 2017-04-18T00:11:53.082855Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303964056576 2017-04-18T00:11:53.086403Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 303964205271 2017-04-18T00:11:53.086696Z 0 [Note] InnoDB: Database was not shutdown normally! 2017-04-18T00:11:53.086701Z 0 [Note] InnoDB: Starting crash recovery. 2017-04-18T00:11:53.338831Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /var/lib/mysql/xb_doublewrite, size 31457280 bytes 2017-04-18T00:11:53.427281Z 0 [Note] InnoDB: Transaction 432564072 was in the XA prepared state. 2017-04-18T00:11:53.428568Z 0 [Note] InnoDB: Transaction 432564072 was in the XA prepared state. 2017-04-18T00:11:53.428713Z 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 0 row operations to undo 2017-04-18T00:11:53.428726Z 0 [Note] InnoDB: Trx id counter is 432564480 2017-04-18T00:11:53.428740Z 0 [Note] InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 I have no idea why this happens or how to solve it. Any help will be appreciated.

Procedure for Percona XtraDB-Cluster 5.5 --&amp;gt; 5.7 and CentOS update

Lastest Forum Posts - April 18, 2017 - 6:19am
Hi

I have a 3-nodes Percona XtraDB-Cluster. Each node runs CentOS 6.6 and Percona XtraDB-Cluster v5.5. These are the installed Percona rpms:

Percona-Server-shared-compat-51-5.1.73-rel14.12.624.rhel6.x86_64
Percona-XtraDB-Cluster-galera-3-3.11-1.rhel6.x86_64
Percona-Server-shared-55-5.5.44-rel37.3.el6.x86_64
Percona-XtraDB-Cluster-client-55-5.5.41-25.11.853.el6.x86_64
Percona-XtraDB-Cluster-server-55-5.5.41-25.11.853.el6.x86_64

I'd like to update this cluster to CentOS 7 and to the latest (5.7) version of Percona XtraDB-Cluster.


In case I don't want a service downtime, I think the procedure should be:

- reinstall from scratch each node, one by one, with CentOS 7.x and Percona XtraDB-Cluster v5.5 (I assume there are no problems if the different nodes of the cluster run different operating systems)
- Migrate Percona XtraDB-Cluster from v5.5 to v5.6
- Migrate Percona XtraDB-Cluster from v5.6 to v5.7

Is this correct or are there better options ?

What about if instead I could afford some downtime ?




Thanks, Massimo

Cluster security

Lastest Forum Posts - April 18, 2017 - 3:07am
I just set up my new 2-node cluster + 1 Galera Arbitrator and found out that anyone can join the cluster, without knowing the 'wsrep_sst_auth' credentials.
To test this I set up a 4th node which is not in any of the other nodes' 'wsrep_cluster_address', but it does know the correct 'wsrep_cluster_name'.

Is this a feature I can only block by firewall, or did I forget to configure security?

Experimental Build of MyRocks with Percona Server for MySQL

Latest MySQL Performance Blog posts - April 17, 2017 - 5:07pm

We have been working on bringing out a build of MyRocks with Percona Server for MySQL.

MyRocks is a RocksDB-based storage engine. You can find more information about MyRocks here.

While there is still a lot of work to do, I want to share an experimental build of Percona Server for MySQL with MyRocks, which you can use to evaluate and test this engine

(WARNING: in NO WAY is this build supposed to be for production usage! Consider this ALPHA quality.)

The tar.gz binaries are available from our TESTING area. To start Percona Server for MySQL with the MyRocks engine, use following line in my.cnf:

plugin-load=rocksdb=ha_rocksdb.so;rocksdb_cfstats=ha_rocksdb.so;rocksdb_dbstats=ha_rocksdb.so;rocksdb_perf_context=ha_rocksdb.so;rocksdb_perf_context_global=ha_rocksdb.so;rocksdb_cf_options=ha_rocksdb.so;rocksdb_compaction_stats=ha_rocksdb.so;rocksdb_global_info=ha_rocksdb.so;rocksdb_ddl=ha_rocksdb.so;rocksdb_index_file_map=ha_rocksdb.so;rocksdb_locks=ha_rocksdb.so;rocksdb_trx=ha_rocksdb.so

Later we will provide experimental RPM and DEB packages from our testing repositories. Please let us know how MyRocks is working for you!

mongodb:metrics: exporter files fill up /tmp

Lastest Forum Posts - April 17, 2017 - 12:38pm
Hello:

I'm new to the forum, and first time poster.....

We have a problem the pmm-admin process for "mongdb:metrics" where it appends to files in /tmp which eventually fills up that filesystem. Can you change the location of the "mongodb_exporter" INFO/WARNING/ERROR files? Or run some sort of cleanup command from "pmm-admin" to get rid of old files?

(FYI, we just started using pmm and the graphs look great!)

Dan

The mysqlpump Utility

Latest MySQL Performance Blog posts - April 17, 2017 - 10:45am

In 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.

MySQL 5.6 crashing with stack_bottom = 0 thread_stack 0x30000

Lastest Forum Posts - April 15, 2017 - 7:38pm
HI,
I'm having serous trouble with one MySQL server. I was getting permission errors in the log that the plugin and user table was not readable. I tried starting the server with innodb_force_recovery = 1 and --skip-grant-tables, then I could access the tables but the user table was still read only.

But when i now starts the server it just crashes with the errors below:

root@DB11a:~# /usr/sbin/mysqld --skip-grant-tables --basedir=/ --datadir=/data/mysql/data --user=mysql --pid-file=/var/lib/mysql/db11a.pid --skip-external-locking --port=3306 --socket=/var/lib/mysql/mysql.sock
2017-04-16 04:33:09 0 [Note] /usr/sbin/mysqld (mysqld 5.6.30-log) starting as process 15357 ...
2017-04-16 04:33:09 15357 [Note] Plugin 'FEDERATED' is disabled.
2017-04-16 04:33:09 15357 [Note] InnoDB: Using atomics to ref count buffer pool pages
2017-04-16 04:33:09 15357 [Note] InnoDB: The InnoDB memory heap is disabled
2017-04-16 04:33:09 15357 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-04-16 04:33:09 15357 [Note] InnoDB: Memory barrier is not used
2017-04-16 04:33:09 15357 [Note] InnoDB: Compressed tables use zlib 1.2.8
2017-04-16 04:33:09 15357 [Note] InnoDB: Using Linux native AIO
2017-04-16 04:33:09 15357 [Note] InnoDB: Using CPU crc32 instructions
2017-04-16 04:33:09 15357 [Note] InnoDB: Initializing buffer pool, size = 64.0G
2017-04-16 04:33:11 15357 [Note] InnoDB: Completed initialization of buffer pool
2017-04-16 04:33:11 15357 [Note] InnoDB: Highest supported file format is Barracuda.
2017-04-16 04:33:12 15357 [Note] InnoDB: 128 rollback segment(s) are active.
2017-04-16 04:33:12 15357 [Note] InnoDB: Waiting for purge to start
02:33:12 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=33554432
read_buffer_size=2097152
max_used_connections=0
max_threads=6000
thread_count=0
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61549549 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0x8d192b]
/usr/sbin/mysqld(handle_fatal_signal+0x4a1)[0x65ce81]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fbf93ade390]
/usr/sbin/mysqld(my_realpath+0x8b)[0x8cebdb]
/usr/sbin/mysqld(_Z19find_or_create_fileP10PFS_threadP14PFS_ file_classPKcjb+0x9c)[0x93711c]
/usr/sbin/mysqld[0x95d134]
/usr/sbin/mysqld[0xacf54f]
/usr/sbin/mysqld[0xad02c3]
/usr/sbin/mysqld[0xad5268]
/usr/sbin/mysqld[0xad9ebe]
/usr/sbin/mysqld[0xaa2bf3]
/usr/sbin/mysqld[0xa88a22]
/usr/sbin/mysqld[0xa6f212]
/usr/sbin/mysqld[0xa15cb3]
/usr/sbin/mysqld[0xa1280f]
/usr/sbin/mysqld[0xa14386]
/usr/sbin/mysqld[0x9e2ad8]
/usr/sbin/mysqld[0xa3a072]
/usr/sbin/mysqld[0xa2ca0b]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fbf93ad46ba]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fbf92f6982d]
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

I would realy appriciate any help here.

Regards
Jens

Run Percona Server on Bash on Windows on Ubuntu

Latest MySQL Performance Blog posts - April 14, 2017 - 1:54pm

In this post, I’ll explain how to run Percona Server for MySQL and Percona Server for MongoDB on Bash on Windows on Ubuntu.

We are getting a good number of questions about whether Percona Server (for both MySQL and MongoDB) is available for Windows for evaluation or development purposes. I want to provide a guide to how to get it running.

In comments to the post Running Percona XtraBackup on Windows … in Docker, Peter Laursen recommend Bash on Ubuntu on Windows. That hadn’t occurred to me before, so the credit goes to Peter Laursen.

As of that older post, it appears that Percona XtraBackup was not working right in Bash on Ubuntu on Windows. But in my latest test on Windows 10 Creators Edition, the problem seems resolved.

But you can get Percona Server for MySQL Percona Server for and MongoDB)  running in Bash on Windows on Ubuntu right away. It is quite easy and does not require extra work. Probably the biggest step is to get Bash on Ubuntu on Windows enabled by itself. A manual on how to do this is here:

https://www.howtogeek.com/249966/how-to-install-and-use-the-linux-bash-shell-on-windows-10/

After this, follow the steps to install Percona Server for MySQL or Percona Server for MongoDB from our Ubuntu repositories:

After this, you can start the server as you would in a regular Linux environment.

Visit Percona Store


General Inquiries

For general inquiries, please send us your question and someone will contact you.