Oct
07
2019
--

Centralization Vs. Decentralization of DBA Teams

Centralization Vs. Decentralization of DBA Teams

Centralization Vs. Decentralization of DBA TeamsAs a Technical Account Manager (TAM), I have seen many of our clients adopt a decentralized DBA Team.  In many cases, this is an effort to better align the DBA Team with the Development Teams.  This is an admirable and logical goal.  As often happens, you often trade one set of challenges for another.

Centralized DBA Teams

First, let’s talk about the challenges of a centralized DBA Team.  Here, the DBAs are all on a single team which is likely separated by platform.  So, you often have a MySQL Team, Oracle Team, SQL Server Team, etc.  These teams usually report up to one manager and although they act somewhat independently by platform, there is also some level of standardization in documentation, reporting structure, procedure, etc.  There are a number of benefits to this approach and a few challenges.  One benefit is having standardization across the whole of the company for how a given technology is documented, deployed, managed, etc.  Of course, this can be a limiting factor as well since there is little opportunity for customization.  Everything tends to become “vanilla” in nature and everything looks alike; from a positive perspective, this is consistency.

Consistency can be very useful.  When new members are added to the team, all servers are essentially the same.  It really doesn’t matter what the application is, a new team member can become proficient very quickly across the whole of the infrastructure.  Lessons learned on one set of servers translates very well to another set of servers supporting a completely different application.

As noted above, however, this is also a challenge.  What happens when a particular application needs something different?  Breaking the norm is the antithesis of consistency and resistance is met from team leadership.

Another challenge is that often the DBAs are supporting so many different technologies that they are challenged to fully understand the application and how it works.   There are just too many applications to become intimately aware of each.  In this case, DBAs are often more reactive rather than being proactive and becoming advisers to the development teams.  This is quite common in larger enterprises that have many diverse applications.

Decentralized DBA Teams

To combat this, many enterprises adopted the decentralized model and break the DBA Teams up into smaller teams aligned closely with the development team.  This seems to make much more sense in many ways since the DBAs will be laser-focused on fewer applications and work much more closely with the developers to ensure an improved solution.

So, what is the issue with this approach?  There are always trade-offs with any approach.  If there were one clear winner, everyone would just use it.  One of the largest challenges I have seen as a TAM with decentralization has been the lack of standardization.  Each DBA team acts virtually independently from every other team.  Problems that once were solved once and for all are suddenly being faced in parallel by multiple teams.  As a result, teams are often “re-inventing the wheel” each time they are confronted by a challenge that may have already been resolved by another team.  Without strong internal communication, teams are wasting time looking for solutions that have already been found.

This is one of the most fulfilling aspects of my role as a TAM.  I am in the unique position of often meeting with multiple teams and socializing these solutions across teams.  I am often asked in meetings with my clients whether I have seen this issue with another team or even another client.  If the answer is affirmative, the next question is obviously about how they resolved it.  Experience wins the course here and provides significant improvement in time to resolution of the issue.

Another challenge I see pertains to consistency.  In decentralized teams, DBAs will sometimes move from team to team as demand for resources changes.  In such cases, new team members require significant time to get up to speed on systems as consistency has been compromised due to each team doing things differently.  Installations may be in different directories or folders on the servers, documentation may be better or worse, and so on.  With no centralized oversight of standards, moving to a new team can slow the process of getting the DBA up to speed.

Communication is Key

Whether you decide to keep a centralized DBA Team or to decentralize the team, some level of consistency and communication are critical.  If you chose to decentralize the DBA Team, be sure someone is acting as a centralized resource, such as a TAM, who is looking for patterns of issues and working to find proven solutions.

Oct
04
2019
--

Percona XtraDB Cluster 8.0 (experimental release) : SST Improvements

xtradb sst improvements

xtradb sst improvementsStarting with the experimental release of Percona XtraDB Cluster 8.0, we have made changes to the SST process to make the process more robust and easier to use.

  • mysqldump and rsync are no longer supported SST methods.

    Support for mysqldump was deprecated starting with PXC 5.7 and has now been completely removed.

    MySQL 8.0 introduced a new Redo Log format that limited the use of rsync while upgrading from PXC 5.7 to 8.0. In addition, the new Galera-4 also introduced changes that further limits the use of rsync.

    The only supported SST method is xtrabackup-v2.

  • A separate Percona XtraBackup installation is no longer required.

    The required Percona XtraBackup (PXB) binaries are now shipped as part of PXC 8.0, they are not installed for general use. So if you want to use PXB outside of an SST, you will have to install PXB separately.

  • SST logging now uses MySQL error logging

    Previously, the SST script would write directly to the error log file. Now, the SST script uses MySQL error logging. A side effect of this change is that the SST logs are not immediately visible. This is due to the logging subsystem being initialized after the SST has completed.

  • The wsrep_sst_auth variable has been removed.

    PXC 8.0 now creates an internal user (mysql.pxc.sst.user) with a random password for use by PXB to take the backup. The cleartext of the password is not saved and the user is deleted after the SST has completed.

    (This feature is still in development and may change before PXC 8.0 GA)

  • PXC SST auto-upgrade

    When PXC 8.0 detects that the SST came from a lower version, mysql_upgrade is automatically invoked. Also “RESET SLAVE ALL” is run on the new node if needed. This is invoked when receiving an SST from PXC 5.7 and PXC 8.0.

    (This feature is still in development and may change before PXC 8.0 GA)

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!

Oct
03
2019
--

Percona XtraDB Cluster 8.0 New Feature: wsrep_sst_auth Removal

Experimental Binary XtraDB 8.0

Experimental Binary XtraDB 8.0The problem

In PXC 5.6 and 5.7, when using xtrabackup-v2 as the SST method, the DBA must create a user with the appropriate privileges for use by Percona XtraBackup (PXB). The username and password of this backup user are specified in the wsrep_sst_auth variable.

This is a problem because this username and password was being stored in plaintext and required that the configuration file be secured.

The PXC 8.0 solution

(This feature is still under development and may change before PXC 8.0 GA)

Because the wsrep_sst_auth is only needed on the donor side to take a backup, PXC 8.0 uses an internal user (created specifically for use by PXC) with a randomly generated password. Since this user is only needed on the donor, the plaintext password is not needed on the joiner node.

This password consists of 32 characters generated at random. A new password is generated for each SST request. The plaintext of the password is never saved and never leaves the node. The username/password is sent to the SST script via unnamed pipes (stdin).

New PXC internal user accounts

mysql.pxc.internal.session

The mysql.pxc.internal.session user account provides the appropriate security context to create and set up the other PXC accounts. This account has a limited set of privileges, enough needed to create the mysql.pxc.sst.user??.

This account is locked and cannot be used to login (the password field will not allow login).

mysql.pxc.sst.user

The mysql.pxc.sst.user is used by XtraBackup to perform the backup. This account has the full set of privileges needed by XtraBackup.?? This account is created for an SST and is dropped at the end of an SST and also when the PXC node is shutdown. The creation/provisioning of this user account is not written to the binlog and is not replicated to other nodes. However, this account is sent with the backup to the joiner node. So the joiner node also has to drop this user after the SST has finished.

mysql.pxc.sst.role

The mysql.pxc.sst.role is the MySQL role that provides the privileges needed for XtraBackup. This allows for easy addition/removal of privileges needed for an SST.

The experimental release of PXC is based on MySQL 8.0.15, and we have not implemented the role-based support due to issues found with MySQL 8.0.15. This will be revisited in future versions of PXC 8.0.

Program flow

  1. DONOR node receives SST request from the JOINER
  2. DONOR node generates a random password and creates the internal SST user
    SET SESSION sql_log_bin = OFF;
    DROP USER IF EXISTS 'mysql.pxc.sst.user'@localhost;
    CREATE USER 'mysql.pxc.sst.user'@localhost IDENTIFIED WITH 'mysql_native_password' BY 'XXXXXXXX' ACCOUNT LOCK;
    GRANT 'mysql.pxc.sst.role'@localhost TO 'mysql.pxc.sst.user'@localhost;
    SET DEFAULT ROLE 'mysql.pxc.sst.role'@localhost to 'mysql.pxc.sst.user'@localhost;
    ALTER USER 'mysql.pxc.sst.user'@localhost ACCOUNT UNLOCK;

    The code that uses role is not being used in the current release due to issues with MySQL 8.0.15. Currently, we create the user with all the permissions needed explicitly.

  3. Launch the SST script (passing the username/password via stdin)
  4. SST uses the username/password to perform the backup
  5. SST script exits
  6. The DONOR node drops the user.
  7. The JOINER node receives the backup and drops the user. Note that the JOINER node also contains the internal SST user!

As a precaution, the user is also dropped when the server is shutdown.

Oct
01
2019
--

Experimental Binary of Percona XtraDB Cluster 8.0

Experimental Binary XtraDB 8.0

Experimental Binary XtraDB 8.0Percona is happy to announce the first experimental binary of Percona XtraDB Cluster 8.0 on October 1, 2019. This is a major step for tuning Percona XtraDB Cluster to be more cloud- and user-friendly. This release combines the updated and feature-rich Galera 4, with substantial improvements made by our development team.

Improvements and New Features

Galera 4, included in Percona XtraDB Cluster 8.0, has many new features. Here is a list of the most essential improvements:

  • Streaming replication supports large transactions
  • The synchronization functions allow action coordination (wsrep_last_seen_gtid, wsrep_last_written_gtid, wsrep_sync_wait_upto_gtid)
  • More granular and improved error logging. wsrep_debug is now a multi-valued variable to assist in controlling the logging, and logging messages have been significantly improved.
  • Some DML and DDL errors on a replicating node can either be ignored or suppressed. Use the wsrep_ignore_apply_errors variable to configure.
  • Multiple system tables help find out more about the state of the cluster state.
  • The wsrep infrastructure of Galera 4 is more robust than that of Galera 3. It features a faster execution of code with better state handling, improved predictability, and error handling.

Percona XtraDB Cluster 8.0 has been reworked in order to improve security and reliability as well as to provide more information about your cluster:

  • There is no need to create a backup user or maintain the credentials in plain text (a security flaw). An internal SST user is created, with a random password for making a backup, and this user is discarded immediately once the backup is done.
  • Percona XtraDB Cluster 8.0 now automatically launches the upgrade as needed (even for minor releases). This avoids manual intervention and simplifies the operation in the cloud.
  • SST (State Snapshot Transfer) rolls back or fixes an unwanted action. It is no more “a copy only block” but a smart operation to make the best use of the copy-phase.
  • Additional visibility statistics are introduced in order to obtain more information about Galera internal objects. This enables easy tracking of the state of execution and flow control.

Installation

You can only install this release from a tarball and it, therefore, cannot be installed through a package management system, such as apt or yum. Note that this release is not ready for use in any production environment.

Percona XtraDB Cluster 8.0 is based on the following:

Please be aware that this release will not be supported in the future, and as such, neither the upgrade to this release nor the downgrade from higher versions is supported.

This release is also packaged with Percona XtraBackup 8.0.5. All Percona software is open-source and free.

In order to experiment with Percona XtraDB Cluster 8.0 in your environment, download and unpack the tarball for your platform.

Note

Be sure to check your system and make sure that the packages are installed which Percona XtraDB Cluster 8.0 depends on.

For Debian or Ubuntu:

$ sudo apt-get install -y \
socat libdbd-mysql-perl \
rsync libaio1 libc6 libcurl3 libev4 libgcc1 libgcrypt20 \
libgpg-error0 libssl1.1 libstdc++6 zlib1g libatomic1

For Red Hat Enterprise Linux or CentOS:

$ sudo yum install -y openssl socat  \
procps-ng chkconfig procps-ng coreutils shadow-utils \
grep libaio libev libcurl perl-DBD-MySQL perl-Digest-MD5 \
libgcc rsync libstdc++ libgcrypt libgpg-error zlib glibc openssl-libs

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!

Sep
26
2019
--

Running Percona XtraDB Cluster on Raspberry PI 3

Percona XtraDB Cluster on Raspberry PI 3

Percona XtraDB Cluster on Raspberry PI 3In a previous post, I showed you how to compile Percona Mysql 5.7 on Raspberry PI 3. Now, I’ll show you how to compile and run the latest version of Percona XtraDB Cluster 5.7.26.

We will need at least 3 RaspberryPi 3 boards, and I recommend you use an external SSD drive to compile and use as MySQL’s “datadir” to avoid the stalls associated with the microSD card, which will cause PXC to run slow.

In this post, we are going to run many OS commands and configure PXC. I recommend having minimal knowledge about PXC and Linux commands.

How to install CentOS

Download the centos image from this link http://mirror.ufro.cl/centos-altarch/7.6.1810/isos/armhfp/CentOS-Userland-7-armv7hl-RaspberryPI-Minimal-1810-sda.raw.xz

I’m using this open source software https://www.balena.io/etcher/ to flash/burn the microSD card because it is very simple and works fine. I recommend using a 16GB card or larger.

If the previous process finished ok, take out the microSD card and put it into the raspberry and boot it. Wait until the boot process finishes and log in with this user and password

user: root
password: centos

By default, the root partition is very small and you need to expand it. Run the next command, this process is fast.

/usr/bin/rootfs-expand

Finally, to finish the installation process it is necessary to install many packages to compile PXC and percona-xtrabackup,

yum install -y cmake wget automake bzr gcc make telnet gcc-c++ vim libcurl-devel readline-devel ncurses-devel screen zlib-devel bison locate libaio libaio-devel autoconf socat libtool  libgcrypt-devel libev-devel vim-common check-devel openssl-devel boost-devel glib2-devel

Repeat all the previous steps over all those 3 dbnodes because some libs are needed to start mysql and to work with percona-xtrabackup.

How to compile PXC + Galera + Xtrabackup

I am using an external SSD disk because it’s faster than the microSD which I mounted on /mnt directory, so we will use this partition to download, compile, and create the datadir.

For the latest PXC version, download and decompress it using the following commands:

cd /mnt
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-57/Percona-XtraDB-Cluster-5.7.26-31.37/source/tarball/Percona-XtraDB-Cluster-5.7.26-31.37.tar.gz
tar zxf Percona-XtraDB-Cluster-5.7.26-31.37.tar.gz
cd Percona-XtraDB-Cluster-5.7.26-31.37

RaspberryPi3’s are not x86_64 architecture; it uses an ARMv7 which forces us to compile PXC from scratch. For me, this process took 6-ish hours. I recommend running the next commands in a screen session.

screen -SDRL compile_pxc
cmake -DINSTALL_LAYOUT=STANDALONE -DCMAKE_INSTALL_PREFIX=/mnt/pxc5726 -DDOWNLOAD_BOOST=ON -DWITH_BOOST=/mnt/sda1/my_boost -DWITH_WSREP=1 .
make
make install

Create a new OS user to be used by the mysql process.

useradd --no-create-home mysql

Now to continue it is necessary to create the datadir directory. I recommend to use the SSD disk attached and set the mysql permissions to the directory:

mkdir /mnt/pxc5726/data/
chown mysql.mysql /mnt/pxc5726/data/

Configure my.cnf with the minimal params to start mysql.

vim /etc/my.cnf

[client]
socket=/mnt/pxc5726/data/mysql.sock

[mysqld]
datadir = /mnt/pxc5726/data
binlog-format = row
log_bin = /mnt/pxc5726/data/binlog
innodb_buffer_pool_size = 128M
socket=/mnt/pxc5726/data/mysql.sock
symbolic-links=0

wsrep_provider=/mnt/pxc5726/libgalera_smm.so

wsrep_on=ON
wsrep_cluster_name="pxc-cluster"
wsrep_cluster_address="gcomm://192.168.88.134"

wsrep_node_name="pxc1"
wsrep_node_address=192.168.88.134

wsrep_debug=1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd

pxc_strict_mode=ENFORCING

[mysqld_safe]
log-error=/mnt/pxc5726/data/mysqld.log
pid-file=/mnt/pxc5726/data/mysqld.pid

So far we’ve only compiled PXC. Before we can start the mysql process, we also need to compile the Galera library, as, without this library, mysql will start as a standalone db-node and will not have any “cluster” capabilities.

To build libgalera_smm.so you need to install scons.

Download using the next command:

cd /mnt
wget http://prdownloads.sourceforge.net/scons/scons-3.1.0.tar.gz
tar zxf scons-3.1.0.tar.gz
cd scons-3.1.0
python setup.py install

Now we will proceed to compile libgalera. The source code for this library exists in the Percona-XtraDB-Cluster-57 directory download:

cd /mnt/Percona-XtraDB-Cluster-5.7.26-31.37/percona-xtradb-cluster-galera
scons -j4 libgalera_smm.so debug=3 psi=1 BOOSTINCLUDE=/mnt/my_boost/boost_1_59_0/boost BOOST_LIBS=/mnt/my_boost/boost_1_59_0/libs

If the compile process finishes ok, it will create a new file like this:

/mnt/Percona-XtraDB-Cluster-5.7.26-31.37/percona-xtradb-cluster-galera/libgalera_smm.so

I recommend copying libgalera_smm.so to the installed PXC directory to avoid deleting, in that case, remove the source directory.

cp /mnt/Percona-XtraDB-Cluster-5.7.26-31.37/percona-xtradb-cluster-galera/libgalera_smm.so /mnt/pxc5726

Also, this is useful because after installing and compiling all the packages we can create a compressed file and copy to the rest of the db-nodes to avoid having to compile these packages again.

The last step is to download and compile percona-xtrabackup. I used the latest version:

screen -SDRL compile_xtrabackup

cd /mnt
wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/source/tarball/percona-xtrabackup-2.4.15.tar.gz
tar zxf percona-xtrabackup-2.4.15.tar.gz
cd percona-xtrabackup-2.4.15
cmake -DBUILD_CONFIG=xtrabackup_release -DWITH_MAN_PAGES=OFF -DCMAKE_INSTALL_PREFIX=/mnt/xtrabackup .
make
make install

If all the previous steps were successful, we will add those binary directories in the PATH variable:

vim /etc/profile.d/percona.sh

export PATH=$PATH:/mnt/xtrabackup/bin:/mnt/pxc5726/bin

Save and exit and run the “export” command manually to set it in the active session:

export PATH=$PATH:/mnt/xtrabackup/bin:/mnt/pxc5726/bin

Well, so far we have PXC, Galera-lib, and percona-xtrabackup compiled for the ARMv7 architecture, which is all that is needed to work with PXC, so now we can start playing.

We will call the first host where we compiled “pxc1”. We will proceed to set the hostname:

$ vim /etc/hostname
pxc1

$ hostname pxc1
$ exit

It is necessary to connect again to ssh to refresh the hostname and you will see something like this:

$ ssh ip
[root@pxc1 ~]#

Now we are ready to open the ports needed by PXC;  xtrabackup, galera, and mysql.

$ firewall-cmd --permanent --add-port=3306/tcp
$ firewall-cmd --permanent --add-port=4567/tcp
$ firewall-cmd --permanent --add-port=4444/tcp

$ firewall-cmd --reload

$ firewall-cmd --list-ports
22/tcp 3306/tcp 4567/tcp 4444/tcp

Repeat the above procedures to set the hostname and open ports in the other db nodes.

Finally, to start the first node you’ll need to initialize the system databases and then launch the mysql process with the –wsrep-new-cluster option. This will bootstrap the cluster so that it starts with 1 node. Run the following from the command-line:

$ mysqld --initialize-insecure --user=mysql --basedir=/mnt/pxc5726 --datadir=/mnt/pxc5726/data
$ mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --wsrep-new-cluster &

Check the mysql error log to see any errors:

$ tailf /mnt/pxc5726/data/mysqld.log

...
2019-09-08T23:23:17.415991Z 0 [Note] /mnt/pxc5726/bin/mysqld: ready for connections.
...

Create the new mysql user, and this will be used for xtrabackup to sync another db node on IST or SST process.

$ mysql

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
GRANT PROCESS, RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
FLUSH PRIVILEGES;

Lastly, I recommend checking if the galera library was loaded successfully:

$ mysql

show global status where Variable_name='wsrep_provider_name' 
or Variable_name='wsrep_provider_name' 
or Variable_name='wsrep_provider_vendor' 
or Variable_name='wsrep_local_state' 
or Variable_name='wsrep_local_state_comment' 
or Variable_name='wsrep_cluster_size' 
or Variable_name='wsrep_cluster_status' 
or Variable_name='wsrep_connected' 
or Variable_name='wsrep_ready' 
or Variable_name='wsrep_evs_state' 
or Variable_name like 'wsrep_flow_control%';

You will see something like this:

+----------------------------------+-----------------------------------+
| Variable_name                    | Value                             |
+----------------------------------+-----------------------------------+
| wsrep_flow_control_paused_ns     | 0                                 |
| wsrep_flow_control_paused        | 0.000000                          |
| wsrep_flow_control_sent          | 0                                 |
| wsrep_flow_control_recv          | 0                                 |
| wsrep_flow_control_interval      | [ 100, 100 ]                      |
| wsrep_flow_control_interval_low  | 100                               |
| wsrep_flow_control_interval_high | 100                               |
| wsrep_flow_control_status        | OFF                               |
| wsrep_local_state                | 4                                 |
| wsrep_local_state_comment        | Synced                            |
| wsrep_evs_state                  | OPERATIONAL                       |
| wsrep_cluster_size               | 1                                 | <----
| wsrep_cluster_status             | Primary                           |
| wsrep_connected                  | ON                                |
| wsrep_provider_name              | Galera                            |
| wsrep_provider_vendor            | Codership Oy <info@codership.com> |
| wsrep_ready                      | ON                                |
+----------------------------------+-----------------------------------+

As you can see this is the first node and the cluster size is 1.

How to copy the previous compiled source code to the rest of the db nodes

You don’t need to compile again over all the rest of the db nodes, just compress PXC and Xtrabackup directories and copy to the rest of the db nodes, nothing else.

In the first step, we compiled and installed on the next directories:

/mnt/pxc5726
/mnt/xtrabackup

We’re going to proceed to compress both directories and copy to the other servers (pxc2 and pxc3)

$ cd /mnt
$ tar czf pxc5726.tgz pxc5726
$ tar czf xtrabackup.tgz xtrabackup

Let’s copy to each db node:

$ cd /mnt
$ scp pxc5726.tgz xtrabackup.tgz IP_PXC2:/mnt

Now connect to each db node and start decompressing, configure my.cnf, other stuff, and start mysql.

$ ssh IP_PXC2
$ cd /mnt
$ tar zxf pxc5726.tgz
$ tar zxf xtrabackup.tgz

From here we are going to repeat several steps that we did previously.

Connect to IP_PXC2.

$ ssh IP_PXC2

Add the next directories in the PATH variable:

vim /etc/profile.d/percona.sh

export PATH=$PATH:/mnt/xtrabackup/bin:/mnt/pxc5726/bin

Save and exit and run the “export” command manually to set it in the active session:

export PATH=$PATH:/mnt/xtrabackup/bin:/mnt/pxc5726/bin

Create a new OS user to be used by mysql process:

useradd --no-create-home mysql

Now to continue it’s necessary to create the datadir directory. I recommend using the SSD disk attached and set the mysql permissions to the directory.

mkdir /mnt/pxc5726/data/
chown mysql.mysql /mnt/pxc5726/data/

Configure my.cnf with the minimal params to start mysql.

vim /etc/my.cnf

[client]
socket=/mnt/pxc5726/data/mysql.sock

[mysqld]
datadir = /mnt/pxc5726/data
binlog-format = row
log_bin = /mnt/pxc5726/data/binlog
innodb_buffer_pool_size = 128M
socket=/mnt/pxc5726/data/mysql.sock
symbolic-links=0

wsrep_provider=/mnt/pxc5726/libgalera_smm.so

wsrep_on=ON
wsrep_cluster_name="pxc-cluster"
wsrep_cluster_address="gcomm://IP_PXC1,IP_PXC2"

wsrep_node_name="pxc2"
wsrep_node_address=IP_PXC2

wsrep_debug=1

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:passw0rd

pxc_strict_mode=ENFORCING

[mysqld_safe]
log-error=/mnt/pxc5726/data/mysqld.log
pid-file=/mnt/pxc5726/data/mysqld.pid

Now we need to start the second db node. This time you don’t need to add “–wsrep-new-cluster” param (from the second and next nodes it’s not needed), just start mysql and run the next command:

$ mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

Check the mysql error log to see any error:

$ tailf /mnt/pxc5726/data/mysqld.log

...
2019-09-08T23:53:17.415991Z 0 [Note] /mnt/pxc5726/bin/mysqld: ready for connections.
...

Check if the galera library was loaded successfully:

$ mysql

show global status where Variable_name='wsrep_provider_name' 
or Variable_name='wsrep_provider_name' 
or Variable_name='wsrep_provider_vendor' 
or Variable_name='wsrep_local_state' 
or Variable_name='wsrep_local_state_comment' 
or Variable_name='wsrep_cluster_size' 
or Variable_name='wsrep_cluster_status' 
or Variable_name='wsrep_connected' 
or Variable_name='wsrep_ready' 
or Variable_name='wsrep_evs_state' 
or Variable_name like 'wsrep_flow_control%';

You will see something like this:

+----------------------------------+-----------------------------------+
| Variable_name                    | Value                             |
+----------------------------------+-----------------------------------+
| wsrep_flow_control_paused_ns     | 0                                 |
| wsrep_flow_control_paused        | 0.000000                          |
| wsrep_flow_control_sent          | 0                                 |
| wsrep_flow_control_recv          | 0                                 |
| wsrep_flow_control_interval      | [ 100, 100 ]                      |
| wsrep_flow_control_interval_low  | 100                               |
| wsrep_flow_control_interval_high | 100                               |
| wsrep_flow_control_status        | OFF                               |
| wsrep_local_state                | 4                                 |
| wsrep_local_state_comment        | Synced                            |
| wsrep_evs_state                  | OPERATIONAL                       |
| wsrep_cluster_size               | 2                                 | <---
| wsrep_cluster_status             | Primary                           |
| wsrep_connected                  | ON                                |
| wsrep_provider_name              | Galera                            |
| wsrep_provider_vendor            | Codership Oy <info@codership.com> |
| wsrep_ready                      | ON                                |
+----------------------------------+-----------------------------------+

Excellent, we have the second node up, now is it’s part of the cluster and the cluster size is 2.

Repeat the previous steps to configure IP_PXC3 and nexts.

Summary

We are ready to start playing and doing a lot of tests. I recommend running sysbench to test how many transactions this environment supports, kill some nodes to test SST and IST, and I recommend using pt-online-schema-change and check this blog “How to Perform Compatible Schema Changes in Percona XtraDB Cluster” to learn how this tool works in PXC.

I hope you enjoyed this guide, and if you want to compile other PXC 5.7.X versions, please go ahead, the steps will be very similar.

Sep
23
2019
--

Why Database Schema Optimization Matters

Schema Optimization

If you have been around MySQL for any length of time, you are probably aware that choosing the correct data types and optimizing your schema are actually important tasks.  A few years back at Percona Live 2016, I gave an introductory talk on schema review and optimization. Here’s the video:

 

I was thinking about that talk in the context of some of my current clients.  Though I had worked on extremely large database deployments during my earlier tenure at Percona, it was often more of an outlier.  Now, working as a Technical Account Manager with our largest clients, it is much more common.

The Fundamental Problem

I’d like to expand my thoughts on the “choosing the smallest data type you can” principle from my 2016 slides through the lens of a few of my 2019 clients.  I gave an example of two copies of the same table (a simple 4 column, 3 index table with ~4 million rows), one using a bigint for the primary key and one using a regular unsigned int for the primary key:

[root@sample-host plive_2016]# ls -alh action*ibd
-rw-rw---- 1 mysql mysql 908M Apr 7 16:22 action_bigint.ibd 
-rw-rw---- 1 mysql mysql 636M Apr 7 16:23 action.ibd

In this example, there was almost a 30% space savings associated with using the smaller data type.  Obviously, at the scale of 1GB of space, this is trivial. One comment I made during the talk references the adage “storage is cheap”.  While this can be true at a certain scale, I’m seeing this thinking break down more frequently with my largest clients.

The Problem Magnified at Scale

As an illustration, one of my clients is running roughly 10,000 Percona Server instances in their sharded deployment.  These servers are running on bare metal with above-average hardware (for performance concerns). While that sounds like a lot of servers (and it definitely is), you also have to take into consideration other operational concerns such as backups.  For the sake of some easier math, let’s assume the following:

  • 5 servers/shard
  • 500G data directory
  • 5 backups of each shard (various time ranges such as daily, weekly, monthly)

Using those numbers as an estimate, one would be looking at roughly the following for space:

  • ~4.7 petabytes storage for running instances (SSD)
  • ~6 petabytes storage for backups (HDD)

The Business Impact on Large Deployments

Suddenly, at that scale, the 30% space that seemed trivial in my example seems a bit more important.  Let’s run some numbers based on the current $/TB pricing of SSD and spinning HDD with the 30% reduction in space:

  • SSD Savings ~ $140,000
    • $100/TB Cost
    • 30% of 4.7PB = 1.4PB
  • HDD Savings ~ $46,000
    • $25/TB Cost
    • 30% of 6PB = 1.8PB

Saving 3 petabytes of storage would reduce the raw storage costs by nearly $200,000.  On top of the actual capital costs of 3PB of storage, you have to consider server count, power, and maintenance (among others) which would increase this cost significantly.  Clearly, this is just a theoretical example of the potential impact a small mistake like picking the wrong data type can have on the overall infrastructure cost at scale. Generally, by the time a company reaches this scale, these sorts of optimizations have already been made and we need to start looking deeper and more creatively at other alternatives.

While this is definitely an oversimplification of calculating storage costs based on raw hardware prices, it does beg the question: even though it may not seem important now, why not ensure your schema is optimized before it becomes an expensive problem to fix!

For a more holistic view of how optimizations such as this one can save you money, check out our Database Management Savings Calculator or reach out to us for a more thorough review.

Sep
23
2019
--

Percona Backup for MongoDB 1.0.0 GA Is Now Available

Percona Backup for MongoDB

Percona Backup for MongoDBPercona is happy to announce the GA release of our latest software product Percona Backup for MongoDB 1.0.0 on September 23, 2019.

Percona Backup for MongoDB is a distributed, low-impact solution for consistent backups of MongoDB sharded clusters and replica sets. This is a tool for creating consistent backups across a MongoDB sharded cluster (or a single replica set), and for restoring those backups to a specific point in time. The project was inspired by (and intends to replace) the Percona-Lab/mongodb_consistent_backup tool.

Percona Backup for MongoDB supports Percona Server for MongoDB or MongoDB Community Server version 3.6 or higher with MongoDB replication enabled. Binaries for the supported platforms as well as the tarball with source code are available from the Percona Backup for MongoDB download page. For more information about and the installation steps, see the documentation.

Percona Backup for MongoDB 1.0.0 features the following:

  • The architecture and the authentication of Percona Backup MongoDB have been simplified.
  • Stores backup data on Amazon Simple Storage Service or compatible storages, such as MinIO.
  • The output of pbm list shows all backups created from the connected MongoDB sharded cluster or replica set.

Percona Backup for MongoDB, a free open source back-up solution, will enable you to manage your backups without third party involvement or costly licenses. Percona Backup for MongoDB is distributed under the terms of Apache License v2.0.

Please report any bugs you encounter in our bug tracking system.

Sep
18
2019
--

Percona XtraDB Cluster 5.7.27-31.39 Is Now Available

Percona XtraDB Cluster

Percona XtraDB ClusterPercona is happy to announce the release of Percona XtraDB Cluster 5.7.27-31.39 on September 18, 2019. Binaries are available from the downloads section or from our software repositories.

Percona XtraDB Cluster 5.7.27-31.39 is now the current release, based on the following:

All Percona software is open-source and free.

Bugs Fixed

  • PXC-2432: PXC was not updating the information_schema user/client statistics properly.
  • PXC-2555: SST initialization delay: fixed a bug where the SST process took too long to detect if a child process was running.
  • PXC-2557: Fixed a crash when a node goes NON-PRIMARY and SHOW STATUS is executed.
  • PXC-2592: PXC restarting automatically on data inconsistency.
  • PXC-2605: PXC could crash when log_slow_verbosity included InnoDB.  Fixed upstream PS-5820.
  • PXC-2639: Fixed an issue where a SQL admin command (like OPTIMIZE) could cause a deadlock.

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!

Sep
17
2019
--

Percona XtraDB Cluster 5.6.45-28.36 Is Now Available

Percona XtraDB Cluster

Percona XtraDB Cluster

Percona is glad to announce the release of Percona XtraDB Cluster 5.6.45-28.36 on September 17, 2019. Binaries are available from the downloads section or from our software repositories.

Percona XtraDB Cluster 5.6.45-28.36 is now the current release, based on the following:

All Percona software is open-source and free.

Bugs Fixed

  • PXC-2432: PXC was not updating the information schema user/client statistics properly.
  • PXC-2555: SST initialization delay: fixed a bug where the SST process took too long to detect if a child process was running.

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!

Aug
13
2019
--

SET PERSIST in MySQL: A Small Thing for Setting System Variable Values

SET PERSIST in MySQL

To set correct system variable values is the essential step to get the correct server behavior against the workload. SET PERSIST in MySQL
In MySQL, we have many System variables that can be changed at runtime, and most of them can be set at the session or global level.

To change the value of a system variable at the global level in the past, users needed to have SUPER privileges. Once the system variable value is modified as global, the server will change this behavior for the session, and obviously as global scope.

For instance, one of the most commonly adjusted variables is probably max_connections.

If you have max_connection=100 in your my.cnf or as the default value, and during the day as DBA you notice that it is not enough, it is easy just to add new connections on the fly with the command:

SET GLOBAL MAX_CONNECTIONS=500;

This will do the work.

But here is the issue. We had changed a GLOBAL value, that applies to the whole server, but this change is ephemeral and if the server restarts, the setting is lost. In the past, I have seen millions of times servers with different configurations between my.cnf and Current Server settings. To prevent this, or at least keep it under control, good DBAs had developed scripts to check if and where the differences exist and fix them. The main issue is that very often, we forget to update the configuration file while doing the changes, or we do it on purpose to do “Fine-tuning first” and forgot afterward.

What’s new in MySQL8 about that?

Well, we have a couple of small changes. First of all the privileges, as for MySQL8 users can have SYSTEM_VARIABLES_ADMIN or SUPER to modify the GLOBAL system variables. Also, the ability to have GLOBAL changes to variable to PERSIST on disk and finally, to know who did it and when.

The new option for SET command is PERSIST

So, if I have:

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 1500  |
+-----------------+-------+

and I want to change the value of max_connection and be sure this value is reloaded at the restart, I will do this:

(root@localhost) [(none)]>set PERSIST max_connections=150;

(root@localhost) [(none)]>show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 150   |
+-----------------+-------+

With the usage of PERSIST, MySQL will write the information related to:

– key (variable name)
– value
– timestamp (including microseconds)
– user
– host

A new file in the data directory: mysqld-auto.cnf contains the information. The file is in Json format and will have the following:

{ "Version" : 1 , "mysql_server" : {
 "max_connections" : { 
"Value" : "150" , "Metadata" : {
 "Timestamp" : 1565363808318848 , "User" : "root" , "Host" : "localhost" 
} } } }

The information is also in Performance Schema:

select a.VARIABLE_NAME,b.VARIABLE_value ,SET_TIME,SET_USER,SET_HOST  
    from performance_schema.variables_info a 
        join performance_schema.global_variables b 
        on a.VARIABLE_NAME=b.VARIABLE_NAME  
    where b.VARIABLE_NAME like 'max_connections'\G

*************************** 1. row ***************************
 VARIABLE_NAME: max_connections
VARIABLE_value: 150
      SET_TIME: 2019-08-09 11:16:48.318989
      SET_USER: root
      SET_HOST: localhost

As you see, it reports who did the change, from where, when, and the value. Unfortunately, there is no history here, but this can be easily implemented.

To clear the PERSIST settings, run RESET PERSIST and all the Persistent setting will be removed.

If you have:

{ "Version" : 1 , "mysql_server" : {
  "max_connections" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565367524946371 , "User" : "root" , "Host" : "localhost" } } , 
  "wait_timeout" : { "Value" : "151" , "Metadata" : { "Timestamp" : 1565368154036275 , "User" : "root" , "Host" : "localhost" } } 
} }

RESET PERSIST will do:

{ "Version" : 1 , "mysql_server" : {  }

Which is removing ALL THE SETTINGS, not just one.

Anyhow, why is this a good thing to have?

First, because we have no excuse now when we change a variable, as we have all the tools needed to make sure we will have it up at startup if this is the intention of the change.

Second, it is good because storing the information in a file, and not only showing it from PS, allows us to include such information in any automation tool we have. This is in case we decide to take action or just to keep track of it, like comparison with my.cnf and fixing the discrepancies automatically also at service down or when cloning. On this let me say that WHILE you can change the value in the file mysqld-auto.cnf, have the server at restart use that value as the valid one.

This is not recommended, instead please fix my.cnf and remove the information related to PERSIST.

To touch that file is also dangerous because if you do stupid things like removing a double quote or in any way affecting the Json format, the server will not start, but there will be NO error in the log.

{ "Version" : 1 , "mysql_server" : { "wait_timeout": { "Value : "150" , "Metadata" : { "Timestamp" : 1565455891278414, "User" : "root" , "Host" : "localhost" } } } }
                                                           ^^^ missing double quote

tusa@tusa-dev:/opt/mysql_templates/mysql-8.0.17futex$ ps aux|grep 8113
tusa      8119  0.0  0.0  14224   896 pts/1    S+   12:54   0:00 grep --color=auto 8113
[1]+  Exit 1                  bin/mysqld --defaults-file=./my.cnf

I have opened a bug for this (https://bugs.mysql.com/bug.php?id=96501).

A short deep dive in the code (you can jump it if you don’t care)

The new feature is handled in the files <source>/sql/persisted_variable.(h/cc). The new structure dealing with the PERSIST actions is:

struct st_persist_var {
  std::string key;
  std::string value;
  ulonglong timestamp;
  std::string user;
  std::string host;
  bool is_null;
  st_persist_var();
  st_persist_var(THD *thd);
  st_persist_var(const std::string key, const std::string value,
                 const ulonglong timestamp, const std::string user,
                 const std::string host, const bool is_null);
};

And the main steps are in the constructors st_persist_var. It should be noted that when creating the timestamp, the code is generating a value that is NOT fully compatible with the MySQL functions FROM_UNIXTIME.

The code assigning the timestamp value pass/assign also passes the microseconds from the timeval (tv) structure:

st_persist_var::st_persist_var(THD *thd) {
  timeval tv = thd->query_start_timeval_trunc(DATETIME_MAX_DECIMALS);
  timestamp = tv.tv_sec * 1000000ULL + tv.tv_usec;
  user = thd->security_context()->user().str;
  host = thd->security_context()->host().str;
  is_null = false;
}

Where:

tv.tv_sec = 1565267482
    tv.tc_usec = 692276

will generate:
timestamp = 1565267482692276

This TIMESTAMP is not valid in MySQL and cannot be read from the time functions, while the segment related to tv.tv_sec = 1565267482 works perfectly.

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482);
+---------------------------+
| FROM_UNIXTIME(1565267482) |
+---------------------------+
| 2019-08-08 08:31:22       |
+---------------------------+

(root@localhost) [(none)]>select FROM_UNIXTIME(1565267482692276);
+---------------------------------+
| FROM_UNIXTIME(1565267482692276) |
+---------------------------------+
| NULL                            |
+---------------------------------+

This because the timestamp with microseconds is formatted differently in MySQL :
PERSIST_code = 1565267482692276
MySQL = 1565267482.692276

If I run: select FROM_UNIXTIME(1565267482.692276);

I get the right result:

+----------------------------------+
| FROM_UNIXTIME(1565267482.692276) |
+----------------------------------+
| 2019-08-08 08:31:22.692276       |
+----------------------------------+

And of course, I can use the trick:

select FROM_UNIXTIME(1565267482692276/1000000);
+-----------------------------------------+
| FROM_UNIXTIME(1565267482692276/1000000) |
+-----------------------------------------+
| 2019-08-08 08:31:22.6922                |
+-----------------------------------------+

Well, that’s all for the behind the scene info.  Keep this in mind if you want to deal with the value coming from the Json file.

SET PERSIST Conclusion

Sometimes the small things can be better than the HUGE shiny things. Many times I saw DBAs in trouble because they do not have this simple feature in MySQL, and many MySQL fails to start or doesn’t behave as expected. Given that, I welcome SET PERSIST and I am sure that the people who manage thousands of servers, with different workloads and automation in place, will see this as a good thing as well.

References:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestamp
https://lefred.be/content/where-does-my-mysql-configuration-variable-value-come-from/
https://lefred.be/content/what-configuration-settings-did-i-change-on-my-mysql-server/

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