Jun
01
2018
--

This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road

Colin CharlesJoin Percona Chief Evangelist Colin Charles as he covers happenings, gives pointers and provides musings on the open source database community.

Shortly after the last dispatch, I jetted off for a spot of vacation (which really meant I was checking out the hype behind Blockchain with a database developer lens at the Blockchain Week NYC), and then some customer visits in Seoul, which explains the short hiatus. Here’s to making this more regular as the summer approaches.

I am about to embark on a fairly long trip, covering a few upcoming appearances: Lisbon for the Percona Engineering meeting, SouthEastLinuxFest in Charlotte, the Open Source Data Centre Conference in Berlin and then the DataOps Barcelona event. I have some discount codes: 50% discount for OSDC with the code OSDC_FOR_FRIENDS, and 50% discount for DataOps Barcelona with the code dataopsbcn50. Expect this column to reflect my travels over the next few weeks.

There has been a lot of news on the MariaDB front: MariaDB 10.3.7 went stable/GA! You might have noticed more fanfare around the release name MariaDB TX 3.0, but the reality is you can still get this download from your usual MariaDB Foundation site. It is worth noting that the MariaDB Foundation 2017 financials have also been released. Some may have noticed a couple months back there was a press release titled Report “State of the Open-Source DBMS Market, 2018” by Gartner Includes Pricing Comparison With MariaDB. This led to a Gartner report on the State of the Open-Source DBMS Market, 2018; although the report has since been pulled. Hopefully we see it surface again.

In the meantime, please do try out MariaDB 10.3.7 and it would be great to hear feedback. I also have an upcoming Percona webinar on MariaDB Server 10.3 on June 26 2018 — when the sign up link appears, I will be sure to include it here.

Well written, and something worth discussing: Should Red Hat Buy or Build a Database?. The Twitter discussion is also worth looking at.

Releases

Link List

Upcoming appearances

Feedback

I look forward to receiving feedback/tips via e-mail at colin.charles@percona.com or on Twitter @bytebot.

The post This Week in Data with Colin Charles 40: a Peak at Blockchain, Lots of MariaDB News, then Back on the Road appeared first on Percona Database Performance Blog.

May
31
2018
--

Percona Server for MySQL 5.7.22-22 Is Now Available

Percona Server for MySQL

Percona Server for MySQLPercona announces the GA release of Percona Server for MySQL 5.7.22-22 on on May 31, 2018. Download the latest version from the Percona web site or the Percona Software Repositories. You can also run Docker containers from the images in the Docker Hub repository.

Based on MySQL 5.7.22, including all the bug fixes in it, Percona Server for MySQL 5.7.22-22 is the current GA release in the Percona Server for MySQL 5.7 series. Percona provides completely open-source and free software.

New Features:
  • A new --encrypt-tmp-files option turns on encryption for the temporary files which Percona Server may create on disk for filesort, binary log transactional caches and Group Replication caches.
Bugs Fixed:
  • Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
  • NUMA support was improved in Percona Server, reverting upstream implementation back to the original one,due to upstream variant being less effective in memory allocation. Now  innodb_numa_interleave variable not only enables NUMA interleave memory policy for the InnoDB buffer pool allocation, but forces NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
  • audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
  • key_block_size value was set automatically by the Improved MEMORY Storage Engine, which resulted in warnings when changing the engine type to InnoDB, and constantly growing key_block_size during alter operations. Bugs fixed #3936#3940, and #3943.
  • Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build. Bug fixed #3950.
  • An InnoDB Memcached Plugin code clean-up was backported from MySQL 8.0. Bug fixed  #4506.
  • Percona Server could not be built with -DWITH_LZ4=system option on Ubuntu 14.04 (Trusty) because of too old LZ4 packages. Bug fixed #3842.
  • A regression brought during TokuDB code clean-up in 5.7.21-21 was causing assertion in cases when the FT layer returns an error during an alter table operation. Bug fixed #4294.
MyRocks Changes and fixes:
  • UPDATE statements were returning incorrect results because of not making a full table scan on tables with unique secondary index. Bug fixed #4495 (upstream facebook/mysql-5.6#830).
Other Bugs Fixed:
  • #4451 “Implement better compression algo testing”
  • #4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”
  • #4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”
  • #4488 “-Werror is always disabled for innodb_memcached
  • #1114 “Assertion `inited == INDEX’ failed”
  • #1130 “RBR Replication with concurrent XA in READ-COMMITTED takes supremum pseudo-records and breaks replication”

Find the release notes for Percona Server for MySQL 5.7.22-22 in our online documentation. Report bugs in the Jira bug tracker.

The post Percona Server for MySQL 5.7.22-22 Is Now Available appeared first on Percona Database Performance Blog.

May
31
2018
--

Don’t Drown in your Data Lake

Don't drown in your data lake

Don't drown in your data lakeA data lake is “…a method of storing data within a system or repository, in its natural format, that facilitates the collocation of data in various schemata and structural forms…”1. Many companies find value in using a data lake but aren’t clear that they need to properly plan for it and maintain it in order to prevent issues.

The idea of a data lake rose from the need to store data in a raw format that is accessible to a variety of applications and authorized users. Hadoop is often used to query the data, and the necessary structures for querying are created through the query tool (schema on read) rather than as part of the data design (schema on write). There are other tools available for analysis, and many cloud providers are actively developing additional options for creating and managing your data lake. The cloud is often viewed as an ideal place for your data lake since it is inherently elastic and can expand to meet the needs of your data.

Data Lake or Data Swamp?

One of the key components of a functioning data lake is the continuing inflow and egress of data. Some data must be kept indefinitely but some can be archived or deleted after a defined period of time. Failure to remove stale data can result in a data swamp, where the out of date data is taking up valuable and costly space and may be causing queries to take longer to complete. This is one of the first issues that companies encounter in maintaining their data lake. Often, people view the data lake as a “final resting place” for data, but it really should be used for data that is accessed often, or at least occasionally.

A natural spring-fed lake can turn into a swamp due to a variety of factors. If fresh water is not allowed to flow into the lake, this can cause stagnation, meaning that plants and animals that previously were not able to be supported by the lake take hold. Similarly, if water cannot exit the lake at some point, the borders will be breached, and the surrounding land will be inundated. Both of these conditions can cause a once pristine lake to turn into a fetid and undesirable swamp. If data is no longer being added to your data lake, the results will become dated and eventually unreliable. Also, if data is always being added to the lake but is not accessed on a regular basis, this can lead to unrestricted growth of your data lake, with no real plan for how the data will be used. This can become an expensive “cold storage” facility that is likely more expensive than archived storage.

If bad or undesirable items, like old cars or garbage, are thrown into a lake, this can damage the ecosystem, causing unwanted reactions. In a data lake, this is akin to simply throwing data into the data lake with no real rules or rationale. While the data is saved, it may not be useful and can cause negative consequences across the whole environment since it is consuming space and may slow response times. Even though a basic concept of a data lake is that the data does not need to conform to a predefined structure, like you would see with a relational database, it is important that some rules and guidelines exist regarding the type and quality of data that is included in the lake. In the absence of some guidelines, it becomes difficult to access the relevant data for your needs. Proper definition and tagging of content help to ensure that the correct data is accessible and available when needed.

Unrestricted Growth Consequences

Many people have a junk drawer somewhere in their house; a drawer that is filled with old receipts, used tickets, theater programs, and the like. Some of this may be stored for sentimental reasons, but a lot of it is put into this drawer since it was a convenient dropping place for things. Similarly, if we look to the data lake as the “junk drawer” for our company, it is guaranteed to be bigger and more expensive than it truly needs to be.

It is important that the data that is stored in your data lake has a current or expected purpose. While you may not have a current use for some data, it can be helpful to keep it around in case a need arises. An example of this is in the area of machine learning. Providing more ancillary data enables better decisions since it provides a deeper view into the decision process. Therefore, maintaining some data that may not have a specific and current need can be helpful. However, there are cases where maintaining a huge volume of data can be counterproductive. Consider temperature information delivered from a switch. If the temperature reaches a specific threshold, the switch should be shut down. Reporting on the temperature in an immediate and timely manner is important to make an informed decision, but stable temperature data from days, week, or months ago could be summarized and stored in a more efficient manner. The granular details can then be purged from the lake.

So, where is the balance? If you keep all the data, it can make your data lake unwieldy and costly. If you only keep data that has a specific current purpose, you may be impairing your future plans. Obviously, the key is to monitor your access and use of the data frequently, and purge or archive some of the data that is not being regularly used.

Uncontrolled Access Concerns

Since much of the data in your data lake is company confidential, it is imperative that access to that data be controlled. The fact that the data in the lake is stored in its raw format means that it is more difficult to control access. The structures of a relational database provide some of the basis for access control, allowing us to limit who has access to specific queries, tables, fields, schemas, databases, and other objects. In the absence of these structures, controlling access requires more finesse. Determining who has access to what parts of the data in the lake must be handled, as well as isolating the data within your own network environment. Many of these restrictions may already be in place in your current environment, but they should be reviewed before being relied on fully, since the data lake may store information that was previously unavailable to some users. Access should be regularly reviewed to identify potential rogue activities. Encryption options also exist to further secure the data from unwanted access, and file system security can be used to limit access. All of these components must be considered, implemented, and reviewed to ensure that the data is secure.

User Considerations

In a relational database, the data structure inherently determines some of the consistencies and format of the data. This enables users to easily query the data and be assured that they are returning valid results. The lack of such structures in the data lake means that users must be more highly skilled at data manipulation. Having users with less skill accessing the data is possible, but it may not provide the best results. A data scientist is better positioned to access and query the complete data set. Obviously, users with a higher skill set are rare and cost more to hire, but the return may be worth it in the long run.

So What Do I Do Now?

This is an area where there are no hard and fast rules. Each company must develop and implement processes and procedures that make sense for their individual needs. Only with a plan for monitoring inputs, outputs, access patterns, and the like are you able to make a solid determination for your company’s needs. Percona can help to determine a plan for reporting usage, assess security settings, and more. As you are using the data in your data lake, we can also provide guidance regarding tools used to access the data.

1 Wikipedia, May 22, 2018

The post Don’t Drown in your Data Lake appeared first on Percona Database Performance Blog.

May
30
2018
--

Percona Server for MySQL 5.6.40-84.0 Is Now Available

Percona Server for MySQL

Percona Server for MySQL 5.6Percona announces the release of Percona Server for MySQL 5.6.40-84.0 on May 30, 2018 (downloads are available here and from the Percona Software Repositories). Based on MySQL 5.6.40, including all the bug fixes in it, Percona Server for MySQL 5.6.40-84.0 is now the current GA release in the 5.6 series. All of Percona’s software is open-source and free.

New Features
  • A new string variable version_suffix allows to change suffix for the Percona Server version string returned by the read-only version variable. This allows to append the version number for the server with a custom suffix to reflect some build or configuration specifics. Also version_comment (default value of which is taken from the CMake COMPILATION_COMMENT option) is converted from a global read-only to a global read-write variable and thereby it is now cutomizable.
  • Query response time plugin now can be disabled at session level with use of a new variable query_response_time_session_stats.
Bugs Fixed
  • Compilation warning was fixed for -DWITH_QUERY_RESPONSE_TIME=ON CMake compilation option, which makes QRT to be linked statically. Bug fixed #3841.
  • A code clean-up was done to fix clang 6 specific compilation warnings and errors (bug fixed #3893, upstream #90111).
  • Using -DWITHOUT_<PLUGIN>=ON CMake variable to exclude a plugin from the build didn’t work for some plugins, including a number of storage engines. Bug fixed #3901.
  • A clean-up in Percona Server binlog-related code was made to avoid uninitialized memory comparison. Bug fixed #3925 (upstream #90238).
  • Temporary file I/O was not instrumented for Performance Schema. Bug fixed  #3937  (upstream  #90264).
  • A key_block_size value was set automatically by the Improved MEMORY Storage Engine, which resulted in warnings when changing the engine type to InnoDB, and constantly growing key_block_size during alter operations. Bugs fixed #3936#3940, and #3943.
  • Percona Server Debian packages description included reference to /etc/mysql/my.cnf file, which is not actually present in these packages. Bug fixed #2046.
  • Fixes were introduced to remove GCC 8 compilation warnings for the Percona Server build, retaining compatibility with old compiler versions, including GCC 4.4. Bugs fixed #3950 and #4471.
  • A typo in plugin.cmake file prevented to compile plugins statically into the server. Bug fixed #3871 (upstream #89766).
  • -DWITH_NUMA=ON build option was silently ignored by CMake when NUMA development package was not installed, instead of exiting by error. Bug fixed #4487.
  • Variables innodb_buffer_pool_populate and numa_interleave mapped to the upstream innodb_numa_interleave variable in 5.6.27-75.0 were reverted to their original implementation due to upstream variant being less effective in memory allocation. Now buffer pool is allocated with MAP_POPULATE, forcing NUMA interleaved allocation at the buffer pool initialization time. Bug fixed #3967.
  • audit_log_include_accounts variable did not take effect if placed in my.cnf configuration file, while still working as intended if set dynamically. Bug fixed #3867.
  • Synchronization between between innodb_kill_idle_transaction and kill_idle_transaction system variables was broken because of the regression in Percona Server 5.6.40-83.2. Bug fixed #3955.
  • Executing the SHOW GLOBAL STATUS expression could cause “data drift” on global status variables in case of a query rollback: the variable, being by its nature a counter and allowing only an increase, could return to its previous value. Bug fixed #3951 (upstream #90351).
  • ALTER TABLE … COMMENT = … statement caused TokuDB to rebuild the whole table, which is not needed, as only FRM metadata should be changed. The fix was provided as a contribution by Fungo Wang. Bugs fixed #4280 and #4292.
  • A number of Percona Server 8.0 TokuDB fixes have been backported to Percona Server 5.6 in preparation for using MySQL 8.0. Bugs fixed  #4379#4380#4387#4378#4383#4384#4386#4382, #4391#4390#4392, and #4381.
TokuDB Changes and Fixes
  • Two new variables, tokudb_enable_fast_update and tokudb_enable_fast_upsert, were introduced to facilitate the TokuDB fast updates feature, which involves queries optimization to avoid random reads during their execution. Bug fixed #4365.
  • A data race was fixed in minicron utility of the PerconaFT, as a contribution by Rik Prohaska. Bug fixed #4281.
  • Row count and cardinality decrease to zero took place after long-running REPLACE load, ending up with full table scans for any action.
Other Bugs Fixed
  • #3818 “Orphaned file mysql-test/suite/innodb/r/percona_innodb_kill_idle_trx.result”
  • #3926 “Potentially truncated bitmap file name in log_online_open_bitmap_file_read_only() (storage/innobase/log/log0online.cc)”
  • #2204 “Test main.audit_log_default_db is unstable”
  • #3767 “Fix compilation warnings/errors with clang”
  • #3773 “Incorrect key file for table frequently for tokudb”
  • #3794 “MTR test main.percona_show_temp_tables_stress does not wait for events to start”
  • #3798 “MTR test innodb.percona_extended_innodb_status fails if InnoDB status contains unquoted special characters”
  • #3887 “TokuDB does not compile with -DWITH_PERFSCHEMA_STORAGE_ENGINE=OFF”
  • #4388 “5.7 code still has TOKU_INCLUDE_OPTION_STRUCTS which is a MariaDB specific construct”
  • #4265 “TDB-114 (Change use of MySQL HASH to unordered_map) introduces memory leak”
  • #4277 “memory leaks in TDB-2 and TDB-89 tests”
  • #4276 “Data race on cache table attributes detected by the thread sanitizer”
  • #4451 “Implement better compression algo testing”
  • #4469 “variable use out of scope bug in get_last_key test detected by ASAN in clang 6”
  • #4470 “the cachetable-simple-pin-nonblocking-cheap test occasionally fails due to a locking conflict with the cachetable evictor”
  • #1131 “User_var_log_event::User_var_log_event(const char*, uint, const Format_description_log_event*): Assertion `(bytes_read == (data_written – ((old_pre_checksum_fd || (description_event->checksum_alg == BINLOG_CHECKSUM_ALG_OFF)) ? 0 : 4))) || ((“.

Find the release notes for Percona Server for MySQL 5.6.40-84.0 in our online documentation. Report bugs in the Jira bug tracker.

The post Percona Server for MySQL 5.6.40-84.0 Is Now Available appeared first on Percona Database Performance Blog.

May
30
2018
--

MySQL Test Framework for Percona XtraDB Cluster

MySQL Test Framework

At my latest webinar “MySQL Test Framework (MTR) for Troubleshooting”, I received an interesting question about MTR test cases for Percona XtraDB Cluster (PXC). Particularly about testing SST and IST.

This post is intended to answer this question. It assumes you are familiar with MTR and can write tests for MySQL servers. If you are not, please watch the webinar recording first.

You can find example tests in any PXC tarball package. They are located in directories

mysql-test/suite/galera

 ,

mysql-test/suite/galera_3nodes

  and

mysql-test/suite/wsrep

 , though that last directory only contains a configuration file.

If you simply try to run tests in galera suite you will find they all are disabled, because the environment variable

WSREP_PROVIDER

  was not set:

sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ ./mtr --suite=galera
Logging: ./mtr --suite=galera
MySQL Version 5.7.19
Too long tmpdir path '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/tmp' creating a shorter one...
- using tmpdir: '/tmp/xYgQqOa5b7'
Checking supported features...
- SSL connections supported
- binaries built with wsrep patch
Using suites: galera
Collecting tests...
Checking leftover processes...
- found old pid 30624 in 'mysqld.3.pid', killing it...
process did not exist!
Removing old var directory...
Creating var directory '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
galera.GAL-419 [ skipped ] Test needs 'big-test' option
...
galera.galera_binlog_checksum [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
galera.galera_binlog_event_max_size_min [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
galera.galera_flush_gtid [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
galera.galera_gtid [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
galera.lp1435482 [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
^Cmysql-test-run: *** ERROR: Got ^C signal

In order to run these tests you need to set this variable first.

I use the quite outdated 5.7.19 PXC package (the version does not matter for the purpose of this post) and run tests as:

WSREP_PROVIDER=/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/lib/libgalera_smm.so ./mtr --suite=galera

After the variable

WSREP_PROVIDER

  is set, 

mtr

  can successfully run:

sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test

WSREP_PROVIDER=/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/lib/libgalera_smm.so ./mtr --suite=galera
Logging: ./mtr --suite=galera
MySQL Version 5.7.19
Too long tmpdir path '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/tmp' creating a shorter one...
- using tmpdir: '/tmp/I6HfuqkwR1'
Checking supported features...
- SSL connections supported
- binaries built with wsrep patch
Using suites: galera
Collecting tests...
Checking leftover processes...
- found old pid 14271 in 'mysqld.1.pid', killing it...
process did not exist!
- found old pid 14273 in 'mysqld.2.pid', killing it...
process did not exist!
Removing old var directory...
Creating var directory '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
galera.GAL-419 [ skipped ] Test needs 'big-test' option
...
worker[1] mysql-test-run: WARNING: Waited 60 seconds for /home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/run/mysqld.2.pid to be created, still waiting for 120 seconds...
galera.galera_binlog_checksum [ pass ] 2787
worker[1] mysql-test-run: WARNING: Waited 60 seconds for /home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/run/mysqld.2.pid to be created, still waiting for 120 seconds...
galera.galera_binlog_event_max_size_min [ pass ] 2200
...

Now we are ready to write our first PXC test. The easiest way to get started is to open any existing test and check how it is written. Then modify it so that it replays our own scenario.

Since the question was about testing

IST

  and

SST

, I will use the test

galera_ist_progress

  as an example. First let’s check that it runs successfully and that it does not have any requirements that could prevent it from running inside regular production binaries:

sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ WSREP_PROVIDER=/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/lib/libgalera_smm.so ./mtr --suite=galera galera_ist_progress
Logging: ./mtr --suite=galera galera_ist_progress
MySQL Version 5.7.19
Too long tmpdir path '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/tmp' creating a shorter one...
- using tmpdir: '/tmp/EodvOyCJwo'
Checking supported features...
- SSL connections supported
- binaries built with wsrep patch
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1] mysql-test-run: WARNING: Waited 60 seconds for /home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/run/mysqld.2.pid to be created, still waiting for 120 seconds...
galera.galera_ist_progress [ pass ] 17970
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 17.970 of 218 seconds executing testcases
Completed: All 1 tests were successful.

Everything is fine. Now let’s look into the test itself.

First, this test has its own configuration file. Let’s check what’s in there:

$ cat suite/galera/t/galera_ist_progress.cnf
!include ../galera_2nodes.cnf
[mysqld.1]
wsrep_provider_options='base_port=@mysqld.1.#galera_port;pc.ignore_sb=true'

galera_2nodes.cnf

  is one of the standard configuration files in galera suite. If we look into it we may notice that 

wsrep_provider_options

  is defined and overriding this option is not required for all tests.

We’ll continue our review. The test script includes the 

galera_cluster.inc

  file:

--source include/galera_cluster.inc

This file is located outside of galera suite and contains 2 lines:

--let $galera_cluster_size = 2
--source include/galera_init.inc

galera_init.inc

 , in its turn, creates as many nodes as defined by the 

galera_cluster_size

  variable and additionally creates a default connection for each of them.

Now let’s step out from

galera_ist_progress

  and check if this knowledge is enough to create our first PXC test.

I created a simple test based on a two node setup which checks a few status and system variables, creates a table, inserts data into it, and ensures that content is accessible on both nodes:

$ cat ~/src/tests/t/pxc.test
--source include/galera_cluster.inc
--connection node_1
--echo We are on node 1
select @@hostname, @@port;
show status like 'wsrep_cluster_size';
show status like 'wsrep_cluster_status';
show status like 'wsrep_connected';
create table t1(id int not null auto_increment primary key, f1 int) engine=innodb;
insert into t1(f1) values(1),(2),(3);
select * from t1;
--connection node_2
--echo We are on node 2
select @@hostname, @@port;
show status like 'wsrep_cluster_size';
show status like 'wsrep_cluster_status';
show status like 'wsrep_connected';
select * from t1;
insert into t1(f1) values(1),(2),(3);
select * from t1;
--connection node_1
--echo We are on node 1
select * from t1;
drop table t1;

However, if I run this test in the main suite, it will fail:

sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ export WSREP_PROVIDER=/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/lib/libgalera_smm.so
sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ do_test.sh -s ~/mysql_packages -b Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100
Logging: ./mysql-test-run.pl --record --force pxc
MySQL Version 5.7.19
Too long tmpdir path '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/tmp' creating a shorter one...
- using tmpdir: '/tmp/uUmBztSWUA'
Checking supported features...
- SSL connections supported
- binaries built with wsrep patch
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.pxc [ skipped ] Test requires wsrep provider library (libgalera_smm.so). Did you set $WSREP_PROVIDER?
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 0.000 of 108 seconds executing testcases
Completed: All 0 tests were successful.
1 tests were skipped, 1 by the test itself.
=====Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100=====
=====pxc=====
sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ echo $WSREP_PROVIDER
/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/lib/libgalera_smm.so

The reason for this failure is that galera suite has default option files that set the necessary variables. Let’s skip those option files for a while and simply run our test in galera suite:

sveta@Thinkie:~/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test$ do_test.sh -s ~/mysql_packages -b Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100 -t galera
Logging: ./mysql-test-run.pl --record --force --suite=galera pxc
MySQL Version 5.7.19
Too long tmpdir path '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/tmp' creating a shorter one...
- using tmpdir: '/tmp/ytqEjnfM7i'
Checking supported features...
- SSL connections supported
- binaries built with wsrep patch
Collecting tests...
Checking leftover processes...
Removing old var directory...
Creating var directory '/home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var'...
Installing system database...
Using parallel: 1
==============================================================================
TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
worker[1] mysql-test-run: WARNING: Waited 60 seconds for /home/sveta/mysql_packages/Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100/mysql-test/var/run/mysqld.2.pid to be created, still waiting for 120 seconds...
galera.pxc [ pass ] 2420
--------------------------------------------------------------------------
The servers were restarted 0 times
Spent 2.420 of 208 seconds executing testcases
Completed: All 1 tests were successful.
pxc.result
=====Percona-XtraDB-Cluster-5.7.19-rel17-29.22.3.Linux.x86_64.ssl100=====
=====pxc=====
We are on node 1
select @@hostname, @@port;
@@hostname @@port
Thinkie 13000
show status like 'wsrep_cluster_size';
Variable_name Value
wsrep_cluster_size 2
show status like 'wsrep_cluster_status';
Variable_name Value
wsrep_cluster_status Primary
show status like 'wsrep_connected';
Variable_name Value
wsrep_connected ON
create table t1(id int not null auto_increment primary key, f1 int) engine=innodb;
insert into t1(f1) values(1),(2),(3);
select * from t1;
id f1
2 1
4 2
6 3
We are on node 2
select @@hostname, @@port;
@@hostname @@port
Thinkie 13004
show status like 'wsrep_cluster_size';
Variable_name Value
wsrep_cluster_size 2
show status like 'wsrep_cluster_status';
Variable_name Value
wsrep_cluster_status Primary
show status like 'wsrep_connected';
Variable_name Value
wsrep_connected ON
select * from t1;
id f1
2 1
4 2
6 3
insert into t1(f1) values(1),(2),(3);
select * from t1;
id f1
2 1
4 2
6 3
7 1
9 2
11 3
We are on node 1
select * from t1;
id f1
2 1
4 2
6 3
7 1
9 2
11 3
drop table t1;

You will see that the test reports that the two nodes run on different ports:

We are on node 1
select @@hostname, @@port;
@@hostname @@port
Thinkie 13000
...
We are on node 2
select @@hostname, @@port;
@@hostname @@port
Thinkie 13004

… and that PXC started:

show status like 'wsrep_cluster_size';
Variable_name Value
wsrep_cluster_size 2
show status like 'wsrep_cluster_status';
Variable_name Value
wsrep_cluster_status Primary
show status like 'wsrep_connected';
Variable_name Value
wsrep_connected ON

And we can also clearly see that each node sees the changes to our test table that were made by the other node.

Now let’s get back to

IST

  test, defined in

galera_ist_progress.test

 .

In order to test

IST

  it first stops writes to the cluster:

# Isolate node #2
--connection node_2
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1';

Then it connects to node 1 and waits until 

wsrep_cluster_size

  becomes 1:

--connection node_1
--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';
--source include/wait_condition.inc

Then it turns

wsrep_on OFF

  on node 2:

--connection node_2
SET SESSION wsrep_on = OFF;
--let $wait_condition = SELECT VARIABLE_VALUE = 'non-Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status';
--source include/wait_condition.inc
SET SESSION wsrep_on = ON;

Now node 2 is completely isolated and node 1 can be updated, so we can test

IST

  when we bring node 2 back online.

--connection node_1
CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);
INSERT INTO t1 VALUES (3);
INSERT INTO t1 VALUES (4);
INSERT INTO t1 VALUES (5);
INSERT INTO t1 VALUES (6);
INSERT INTO t1 VALUES (7);
INSERT INTO t1 VALUES (8);
INSERT INTO t1 VALUES (9);
INSERT INTO t1 VALUES (10);

After the update is done, node 2 is brought online:

--connection node_2
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 0';
--connection node_1
--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';
--source include/wait_condition.inc
--connection node_2
--let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status';
--source include/wait_condition.inc

Once node 2 is online, checks for IST progress are performed. To check for IST progress, the test greps the error log file from node 2 where any messages about IST progress are printed:

#
# Grep for expected IST output in joiner log
#
--connection node_1
--let $assert_count = 1
--let $assert_file = $MYSQLTEST_VARDIR/log/mysqld.2.err
--let $assert_only_after = Need state transfer
--let $assert_text = Receiving IST: 11 writesets, seqnos
--let $assert_select = Receiving IST: 11 writesets, seqnos
--source include/assert_grep.inc
--let $assert_text = Receiving IST... 0.0% ( 0/11 events) complete
--let $assert_select = Receiving IST... 0.0% ( 0/11 events) complete
--source include/assert_grep.inc
--let $assert_text = Receiving IST...100.0% (11/11 events) complete
--let $assert_select = Receiving IST...100.0% (11/11 events) complete
--source include/assert_grep.inc

Here is the error log snipped from node 2 when it re-joined the cluster and initiated state transfer.

2018-05-25T17:00:46.908569Z 0 [Note] WSREP: Shifting OPEN -> PRIMARY (TO: 13)
2018-05-25T17:00:46.908637Z 2 [Note] WSREP: State transfer required:
	Group state: f364a69b-603c-11e8-a632-ce5a4a7d5964:13
	Local state: f364a69b-603c-11e8-a632-ce5a4a7d5964:2
2018-05-25T17:00:46.908673Z 2 [Note] WSREP: New cluster view: global state: f364a69b-603c-11e8-a632-ce5a4a7d5964:13, view# 4: Primary, number of nodes: 2, my index: 1, protocol version 3
2018-05-25T17:00:46.908694Z 2 [Note] WSREP: Setting wsrep_ready to true
2018-05-25T17:00:46.908717Z 2 [Warning] WSREP: Gap in state sequence. Need state transfer.
2018-05-25T17:00:46.908737Z 2 [Note] WSREP: Setting wsrep_ready to false
2018-05-25T17:00:46.908757Z 2 [Note] WSREP: You have configured 'xtrabackup-v2' state snapshot transfer method which cannot be performed on a running server. Wsrep provider won't be able to fall back to it if other means of state transfer are unavailable. In that case you will need to restart the server.
2018-05-25T17:00:46.908777Z 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
2018-05-25T17:00:46.908799Z 2 [Note] WSREP: REPL Protocols: 7 (3, 2)
2018-05-25T17:00:46.908831Z 2 [Note] WSREP: Assign initial position for certification: 13, protocol version: 3
2018-05-25T17:00:46.908886Z 0 [Note] WSREP: Service thread queue flushed.
2018-05-25T17:00:46.908934Z 2 [Note] WSREP: Check if state gap can be serviced using IST
2018-05-25T17:00:46.909062Z 2 [Note] WSREP: IST receiver addr using tcp://127.0.0.1:13006
2018-05-25T17:00:46.909232Z 2 [Note] WSREP: Prepared IST receiver, listening at: tcp://127.0.0.1:13006
2018-05-25T17:00:46.909267Z 2 [Note] WSREP: State gap can be likely serviced using IST. SST request though present would be void.
2018-05-25T17:00:46.909489Z 0 [Note] WSREP: Member 1.0 (Thinkie) requested state transfer from '*any*'. Selected 0.0 (Thinkie)(SYNCED) as donor.
2018-05-25T17:00:46.909513Z 0 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 13)
2018-05-25T17:00:46.909557Z 2 [Note] WSREP: Requesting state transfer: success, donor: 0
2018-05-25T17:00:46.909602Z 2 [Note] WSREP: GCache history reset: f364a69b-603c-11e8-a632-ce5a4a7d5964:2 -> f364a69b-603c-11e8-a632-ce5a4a7d5964:13
2018-05-25T17:00:46.910221Z 0 [Note] WSREP: 0.0 (Thinkie): State transfer to 1.0 (Thinkie) complete.
2018-05-25T17:00:46.910422Z 0 [Note] WSREP: Member 0.0 (Thinkie) synced with group.
2018-05-25T17:00:47.006802Z 2 [Note] WSREP: GCache DEBUG: RingBuffer::seqno_reset(): full reset
2018-05-25T17:00:47.106423Z 2 [Note] WSREP: Receiving IST: 11 writesets, seqnos 2-13
2018-05-25T17:00:47.106764Z 0 [Note] WSREP: Receiving IST...  0.0% ( 0/11 events) complete.
2018-05-25T17:00:47.109740Z 0 [Note] WSREP: Receiving IST...100.0% (11/11 events) complete.
2018-05-25T17:00:47.110029Z 2 [Note] WSREP: IST received: f364a69b-603c-11e8-a632-ce5a4a7d5964:13
2018-05-25T17:00:47.110433Z 0 [Note] WSREP: 1.0 (Thinkie): State transfer from 0.0 (Thinkie) complete.
2018-05-25T17:00:47.110480Z 0 [Note] WSREP: SST leaving flow control
2018-05-25T17:00:47.110509Z 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 13)
2018-05-25T17:00:47.110778Z 0 [Note] WSREP: Member 1.0 (Thinkie) synced with group.
2018-05-25T17:00:47.110830Z 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 13)
2018-05-25T17:00:47.110890Z 2 [Note] WSREP: Synchronized with group, ready for connections

If you want to write your own tests for IST and SST operations you can use existing test cases as a baseline. You are not required to use grep, and can explore your own scenarios. The important parts of the code are:

  • The variable
    WSREP_PROVIDER

     must be set before the test run

  • The test should be either in galera suite or if you choose to use your own suite you must copy the definitions from the galera suite default configuration file
  • The test should include the file
    include/galera_cluster.inc
  • To isolate the node from the cluster run the following code:
# Isolate node #2
--connection node_2
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 1';
--connection node_1
--let $wait_condition = SELECT VARIABLE_VALUE = 1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';
--source include/wait_condition.inc
--connection node_2
SET SESSION wsrep_on = OFF;
--let $wait_condition = SELECT VARIABLE_VALUE = 'non-Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status';
--source include/wait_condition.inc
SET SESSION wsrep_on = ON;

Replace the node numbers if needed.

To bring the node back to the cluster run the following code:

# Restore node #2, IST is performed
--connection node_2
SET GLOBAL wsrep_provider_options = 'gmcast.isolate = 0';
--connection node_1
--let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size';
--source include/wait_condition.inc
--connection node_2
--let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status';
--source include/wait_condition.inc

Depending on the size of the updates and

gcache

 you can test either IST or SST in this way.

The post MySQL Test Framework for Percona XtraDB Cluster appeared first on Percona Database Performance Blog.

May
29
2018
--

Deploying PMM on DigitalOcean

Log in to DigitalOcean panel and click "Create Droplet."

It’s very easy to install Percona Monitoring and Management (PMM) on DigitalOcean. If you’ve never used DigitalOcean before, you will find that it is user-friendly and not very expensive. For $5/month you can easily host your PMM on it, letting you monitor your simple infrastructure or try out PMM before implementing it to monitor your production environments.

Let’s prepare the DigitalOcean instance

Log in to DigitalOcean (DO) control panel and click “Create Droplet.”

Log in to DigitalOcean panel and click "Create Droplet."

Thanks to DO you can skip the boring OS setup and save time by using the Docker “One click app” in DO and the Docker image from PMM.

Create Droplet on DigitalOcean

Note: After clicking on “Docker…” choose an instance size that accommodates your budget – PMM can run on as little as the 1GB 1vCPU instance!

Choose Droplet Size

Note: Scroll again!

Next step – select a nearby region

Since the next Percona Live Europe, 2018 will be in Frankfurt (https://www.percona.com/blog/2018/04/05/percona-live-europe-2018-save-the-date/ ) for me the location choice is obvious.

Choose DigitalOcean datacenter region

The final step in this section is ‘Set Hostname’

I recommend you add ‘pmm-server-‘ at the beginning so that you can easily find it in your control panel. The name in my case is ‘pmm-server-docker-s-1vcpu-1gb-fra1-01’ and I’ll use it later in this tutorial.

Finalize and create Droplet hostname

Click “Create” and wait a while.You can follow the process on the dashboard:

Creating the instance of DigitalOcean Droplet

When the Droplet is created, you’ll get an email with your login details.

The next step is ‘Set up PMM into the Droplet’

SSH to the server, change the password, and let’s prepare to install the PMM server.

==================
random@random-vb:~$ ssh root@X.X.X.X
...
"ufw" has been enabled. All ports except 22 (SSH), 80 (http) and 443 (https)
have been blocked by default.
...
Changing password for root.
(current) UNIX password:
Enter new UNIX password:
Retype new UNIX password:
root@pmm-server-docker-s-1vcpu-1gb-fra1-01:~#
====================

Note the output for the first login. You are getting Ubuntu 16.04 with pre-installed Docker.

The instructions for installing PMM are very simple. You can read them at https://www.percona.com/doc/percona-monitoring-and-management/deploy/server/docker.html

1) Pull the latest version from Docker Hub:

docker pull percona/pmm-server:latest

Wait for some time (this depends on your internet connection)

2) Create a container for persistent PMM data

docker create
-v /opt/prometheus/data
-v /opt/consul-data
-v /var/lib/mysql
-v /var/lib/grafana
--name pmm-data
percona/pmm-server:latest /bin/true

3) Create and launch PMM Server in one command

docker run -d
-p 80:80
--volumes-from pmm-data
--name pmm-server
--restart always
percona/pmm-server:latest

Just to confirm that your containers are available, go ahead and run “docker ps.” You’ll see something like this:

root@pmm-server-docker-s-1vcpu-1gb-fra1-01:~# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
5513858041f7 percona/pmm-server:latest "/opt/entrypoint.sh" 2 minutes ago Up 2 minutes 0.0.0.0:80->80/tcp, 443/tcp pmm-server

That’s all! Congratulations! Your PMM server is running.

If you open the IP of your server in the browser, you’ll see something like this:

PMM running in DigitalOcean Droplet instance

There you can see that PMM has already started monitoring itself.

Now you need to install PMM client on your database server and configure it, instructions for this are at https://www.percona.com/doc/percona-monitoring-and-management/deploy/client/index.html

Please note, if you also use DO for the database server by external IP, you’ll probably face “the firewall problem.” In this case, you need to open ports using the “ufw” tool. (See the welcome message from Digital Ocean). For testing purposes, you can use

ufw allow 42000:42999/tcp

To open only pmm-client related ports, follow https://www.percona.com/doc/percona-monitoring-and-management/glossary.terminology.html#term-ports  To run ufw, you need to use the terminal, and you can find more information about ufw at https://www.digitalocean.com/community/tutorials/ufw-essentials-common-firewall-rules-and-commands  Once you have opened up the ports, PMM should now work correctly for this setup.

Final recommendation: Depending on your load you may need to monitor your System Overview dashboard which you’ll find at http://X.X.X.X/graph/somesymbols/system-overview

If you are out of space, upgrade your DO Droplet.

The post Deploying PMM on DigitalOcean appeared first on Percona Database Performance Blog.

May
25
2018
--

Webinar Tues, 5/29: MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together

Please join Percona’s Principal Architect Alex Rubin as he presents MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together to give you a highly available cluster database environment on Tuesday, May 29th at 7:00 AM PDT (UTC-7) / 10:00 AM EDT (UTC-4).

 

In this webinar, Alex will discuss how to deploy a highly available MySQL database environment on Kubernetes/Openshift using Percona XtraDB Cluster (PXC) together with MySQL Proxy to implement read/write splitting.

If you have never used Kubernetes and Openshift, or never used PXC / MySQL Proxy, Alex will do a quick introduction to these technologies. There will also be a demo where Alex sets up a PXC cluster with ProxySQL in Openshift Origin and tries to break it.

By the end of this webinar you will have a better understanding of:

  • How to deploy Percona XtraDB Cluster with ProxySQL for HA solutions
  • How to leverage Kubernetes/Openshift in your environments
  • How to troubleshoot performance issues

Register for the webinar

Alexander Rubin, Principal Consultant

Alexander RubinAlexander joined Percona in 2013. Alexander worked with MySQL since 2000 as DBA and Application Developer. Before joining Percona he was doing MySQL consulting as a principal consultant for over 7 years (started with MySQL AB in 2006, then Sun Microsystems and then Oracle). He helped many customers design large, scalable and highly available MySQL systems and optimize MySQL performance. Alexander also helped customers design Big Data stores with Apache Hadoop and related technologies.

The post Webinar Tues, 5/29: MySQL, Percona XtraDB Cluster, ProxySQL, Kubernetes: How they work together appeared first on Percona Database Performance Blog.

May
24
2018
--

Using dbdeployer to manage MySQL, Percona Server and MariaDB sandboxes

dbdeployer by Giuseppe Maxia

Some years ago, Peter Z wrote a blogpost about using MySQL Sandbox to deploy multiple server versions. Last February, Giuseppe  introduced us to its successor: dbdeployer. In this blogpost we will demonstrate how to use it. There is a lot of information in Giuseppe’s post, so head there if you want a deeper dive.

First step is to install it, which is really easy to do now since it’s developed in Go, and standalone executables are provided. You can get the latest version here.

shell> wget https://github.com/datacharmer/dbdeployer/releases/download/1.5.0/dbdeployer-1.5.0.linux.tar.gz
shell> tar xzf dbdeployer-1.5.0.linux.tar.gz
shell> mv dbdeployer-1.5.0.linux ~/bin/dbdeployer

If you have your ~/bin/ directory in the path, you should now be able to run dbdeployer commands.

dbdeployer by Giuseppe Maxia

Let’s start with deploying a latest version vanilla MySQL sandbox.

In the Support Team, we extensively use MySQL Sandbox (the predecessor to dbdeployer) to easily run different flavours and versions of MySQL so that we can test with the same versions our customers present us with. We store MySQL binaries in /opt/, so we can all share them and avoid wasting disk space on duplicated binaries.

The first step to using dbdeployer is getting the binary we want to run, and then unpacking it into the binaries directory.

shell> wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
shell> dbdeployer --sandbox-binary=/opt/mysql/ unpack mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz

This command will extract and move the files to the appropriate directory, which in this case is under /opt/mysql/ as overridden with the --sandbox-binary argument, so we can use them with the deploy command.

Standalone

To create a new standalone MySQL sandbox with the newly extracted binary, we can use the following command.

shell> dbdeployer --sandbox-binary=/opt/mysql/ deploy single 8.0.11
Creating directory /home/vagrant/sandboxes
Database installed in $HOME/sandboxes/msb_8_0_11
run 'dbdeployer usage single' for basic instructions'
.. sandbox server started

You can read the dbdeployer usage output to have even more information on how the tool works. Next, let’s connect to it.

shell> cd sandboxes/msb_8_0_11/
shell> ./use
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
mysql [localhost] {msandbox} ((none)) > select @@version, @@port;
+-----------+--------+
| @@version | @@port |
+-----------+--------+
| 8.0.11    | 8011 |
+-----------+--------+
1 row in set (0.00 sec)

And that was it! When creating the new instance, dbdeployer will try to use the same port as the version numbers concatenated. If that port is in use, it will try another one, or we can manually override it with the --port argument.

Replication

We can also easily setup a replication environment with just one command.

shell> dbdeployer --sandbox-binary=/opt/mariadb/ deploy replication 10.2.15
Installing and starting master
. sandbox server started
Installing and starting slave1
. sandbox server started
Installing and starting slave2
. sandbox server started
$HOME/sandboxes/rsandbox_10_2_15/initialize_slaves
initializing slave 1
initializing slave 2
Replication directory installed in $HOME/sandboxes/rsandbox_10_2_15
run 'dbdeployer usage multiple' for basic instructions'

Again, you should run the recommended command to get more insight into what can be done. We can use the ./m script to connect to the master, and ./s1 to connect to the first slave. The ./use_all* scripts can come in handy to run commands in many servers at a time.

Multiple sandboxes

Finally, we will see how to create multiple sandboxes with the same version at the same time.

shell> dbdeployer --sandbox-binary=/opt/percona_server/ deploy multiple 5.7.21
Installing and starting node 1
. sandbox server started
Installing and starting node 2
. sandbox server started
Installing and starting node 3
. sandbox server started
multiple directory installed in $HOME/sandboxes/multi_msb_5_7_21
run 'dbdeployer usage multiple' for basic instructions'

This could be useful for setting up environments that are not already covered by the tool, like Galera clusters or semi-sync replication. With this approach, we will at least have a base to start from, and then can use our own custom scripts. dbdeployer now has templates, which would allow extending functionality to support this, if needed. I have not yet tried to do so, but sounds like an interesting project for the future! Let me know if you would be interested in reading more about it.

The post Using dbdeployer to manage MySQL, Percona Server and MariaDB sandboxes appeared first on Percona Database Performance Blog.

May
24
2018
--

Setting up PMM on Google Compute Engine in 15 minutes or less

Percona Monitoring and Management on Google Compute Engine

In this blog post, I will show you how easy it is to set up a Percona Monitoring and Management server on Google Compute Engine from the command line.

First off you will need to have a Google account and install the Cloud SDK tool. You need to create a GCP (Google Cloud Platform) project and enable billing to proceed. This blog assumes you are able to authenticate and SSH into instances from the command line.

Here are the steps to install PMM server in Google Cloud Platform.

1) Create the Compute engine instance with the following command. The example creates an Ubuntu Xenial 16.04 LTS compute instance in the us-west1-b zone with a 100GB persistent disk. For production systems it would be best to use a 500GB disk instead (size=500GB). This should be enough for default data retention settings, although your needs may vary.

jerichorivera@percona-support:~/GCE$ gcloud compute instances create pmm-server --tags pmmserver --image-family ubuntu-1604-lts --image-project ubuntu-os-cloud --machine-type n1-standard-4 --zone us-west1-b --create-disk=size=100GB,type=pd-ssd,device-name=sdb --description "PMM Server on GCP" --metadata-from-file startup-script=deploy-pmm-xenial64.sh
Created [https://www.googleapis.com/compute/v1/projects/thematic-acumen-204008/zones/us-west1-b/instances/pmm-server].
NAME        ZONE        MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP   STATUS
pmm-server  us-west1-b  n1-standard-4               10.138.0.2   35.233.216.225  RUNNING

Notice that we’ve used

--metadata-from-file startup-script=deploy-pmm-xenial64.sh

  The file has the following contents:

jerichorivera@percona-support:~$ cat GCE/deploy-pmm-xenial64.sh
#!/bin/bash
set -v
sudo apt-get update
sudo apt-get upgrade -y
sudo apt-get install apt-transport-https ca-certificates curl software-properties-common
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
# Format the persistent disk, mount it then add to /etc/fstab
sudo mkfs.ext4 -m 0 -F -E lazy_itable_init=0,lazy_journal_init=0,discard /dev/sdb
sudo mkdir -p /mnt/disks/pdssd
sudo mount -o discard,defaults /dev/sdb /mnt/disks/pdssd/
sudo chmod a+w /mnt/disks/pdssd/
sudo cp /etc/fstab /etc/fstab.backup
echo UUID=`sudo blkid -s UUID -o value /dev/sdb` /mnt/disks/pdssd ext4 discard,defaults,nofail 0 2 | sudo tee -a /etc/fstab
# Change docker’s root directory before installing Docker
sudo mkdir /etc/systemd/system/docker.service.d/
cat << EOF > /etc/systemd/system/docker.service.d/docker.root.conf
[Service]
ExecStart=
ExecStart=/usr/bin/dockerd -H fd:// -g /mnt/disks/pdssd/docker/
EOF
sudo apt-get install -y docker-ce
# Creates the deploy.sh script
cat << EOF > /tmp/deploy.sh
#!/bin/bash
set -v
docker pull percona/pmm-server:latest
docker create -v /opt/prometheus/data -v /opt/consul-data -v /var/lib/mysql -v /var/lib/grafana --name pmm-data percona/pmm-server:latest /bin/true
docker run -d -p 80:80 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:latest
EOF

This startup script will be executed right after the compute instance is created. The script will format the persistent disk and mount the file system; create a custom Docker unit file for the purpose of creating Docker’s root directory from /var/lib/docker to /mnt/disks/pdssd/docker; install the Docker package; and create the deploy.sh script.

2) Once the compute engine instance is created, SSH into the instance, check that Docker is running and the root directory pointing to the desired folder.

jerichorivera@pmm-server:~$ sudo systemctl status docker
? docker.service - Docker Application Container Engine
   Loaded: loaded (/lib/systemd/system/docker.service; enabled; vendor preset: enabled)
  Drop-In: /etc/systemd/system/docker.service.d
           ??docker.root.conf
   Active: active (running) since Wed 2018-05-16 12:53:30 UTC; 45s ago
     Docs: https://docs.docker.com
 Main PID: 4744 (dockerd)
   CGroup: /system.slice/docker.service
           ??4744 /usr/bin/dockerd -H fd:// -g /mnt/disks/pdssd/docker/
           ??4764 docker-containerd --config /var/run/docker/containerd/containerd.toml
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.391566708Z" level=warning msg="Your kernel does not support swap memory limit"
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.391638253Z" level=warning msg="Your kernel does not support cgroup rt period"
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.391680203Z" level=warning msg="Your kernel does not support cgroup rt runtime"
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.392913043Z" level=info msg="Loading containers: start."
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.767048674Z" level=info msg="Default bridge (docker0) is assigned with an IP address 172.17.0.0/16. Daemon option --bip can be used to set a preferred IP address"
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.847907241Z" level=info msg="Loading containers: done."
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.875129963Z" level=info msg="Docker daemon" commit=9ee9f40 graphdriver(s)=overlay2 version=18.03.1-ce
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.875285809Z" level=info msg="Daemon has completed initialization"
May 16 12:53:30 pmm-server dockerd[4744]: time="2018-05-16T12:53:30.884566419Z" level=info msg="API listen on /var/run/docker.sock"
May 16 12:53:30 pmm-server systemd[1]: Started Docker Application Container Engine.

3) Add your user to the docker group as shown below and change deploy.sh script to executable.

jerichorivera@pmm-server:~$ sudo usermod -aG docker $USER
jerichorivera@pmm-server:~$ sudo chmod +x /tmp/deploy.sh

4) Log off from the instance, and then log back in and then execute the deploy.sh script.

jerichorivera@pmm-server:~$ cd /tmp/
jerichorivera@pmm-server:/tmp$ ./deploy.sh
docker pull percona/pmm-server:latest
latest: Pulling from percona/pmm-server
697841bfe295: Pull complete
fa45d21b9629: Pull complete
Digest: sha256:98d2717b4f0ae83fbca63330c39590d69a7fca7ae6788f52906253ac75db6838
Status: Downloaded newer image for percona/pmm-server:latest
docker create -v /opt/prometheus/data -v /opt/consul-data -v /var/lib/mysql -v /var/lib/grafana --name pmm-data percona/pmm-server:latest /bin/true
8977102d419cf8955fd8bbd0ed2c663c75a39f9fbc635238d56b480ecca8e749
docker run -d -p 80:80 --volumes-from pmm-data --name pmm-server --restart always percona/pmm-server:latest
83c2e6db2efc752a6beeff0559b472f012062d3f163c042e5e0d41cda6481d33

5) Finally, create a firewall rule to allow HTTP port 80 to access the PMM Server. For security reasons, we recommend that you secure your PMM server by adding a password, or limit access to it with a stricter firewall rule to specify which IP addresses can access port 80.

jerichorivera@percona-support:~$ gcloud compute firewall-rules create allow-http-pmm-server --allow tcp:80 --target-tags pmmserver --description "Allow HTTP traffic to PMM Server"
Creating firewall...-Created [https://www.googleapis.com/compute/v1/projects/thematic-acumen-204008/global/firewalls/allow-http-pmm-server].
Creating firewall...done.
NAME                   NETWORK  DIRECTION  PRIORITY  ALLOW   DENY
allow-http-pmm-server  default  INGRESS    1000      tcp:80
jerichorivera@percona-support:~/GCE$ gcloud compute firewall-rules list
NAME                    NETWORK  DIRECTION  PRIORITY  ALLOW                         DENY
allow-http-pmm-server   default  INGRESS    1000      tcp:80
default-allow-icmp      default  INGRESS    65534     icmp
default-allow-internal  default  INGRESS    65534     tcp:0-65535,udp:0-65535,icmp
default-allow-rdp       default  INGRESS    65534     tcp:3389
default-allow-ssh       default  INGRESS    65534     tcp:22

At this point you should have a PMM Server in GCP running on a Compute Engine instance.

The next steps is to install pmm-client on the database hosts and add services for monitoring.

Here I’ve launched a single standalone Percona Server 5.6 on another Compute Engine instance in the same project (thematic-acumen-204008).

jerichorivera@percona-support:~/GCE$ gcloud compute instances create mysql1 --tags mysql1 --image-family centos-7 --image-project centos-cloud --machine-type n1-standard-2 --zone us-west1-b --create-disk=size=50GB,type=pd-standard,device-name=sdb --description "MySQL1 on GCP" --metadata-from-file startup-script=compute-instance-deploy.sh
Created [https://www.googleapis.com/compute/v1/projects/thematic-acumen-204008/zones/us-west1-b/instances/mysql1].
NAME    ZONE        MACHINE_TYPE   PREEMPTIBLE  INTERNAL_IP  EXTERNAL_IP     STATUS
mysql1  us-west1-b  n1-standard-2               10.138.0.3   35.233.187.253  RUNNING

Installed Percona Server 5.6 and pmm-client and then added services. Take note that since the PMM Server and the MySQL server is in the same project and same VPC network, we can connect directly through INTERNAL_IP 10.138.0.2, otherwise use the EXTERNAL_IP 35.223.216.225.

[root@mysql1 jerichorivera]# pmm-admin config --server 10.138.0.2
OK, PMM server is alive.
PMM Server      | 10.138.0.2
Client Name     | mysql1
Client Address  | 10.138.0.3
[root@mysql1 jerichorivera]#
[root@mysql1 jerichorivera]# pmm-admin check-network
PMM Network Status
Server Address | 10.138.0.2
Client Address | 10.138.0.3
* System Time
NTP Server (0.pool.ntp.org)         | 2018-05-22 06:45:47 +0000 UTC
PMM Server                          | 2018-05-22 06:45:47 +0000 GMT
PMM Client                          | 2018-05-22 06:45:47 +0000 UTC
PMM Server Time Drift               | OK
PMM Client Time Drift               | OK
PMM Client to PMM Server Time Drift | OK
* Connection: Client --> Server
-------------------- -------
SERVER SERVICE       STATUS
-------------------- -------
Consul API           OK
Prometheus API       OK
Query Analytics API  OK
Connection duration | 408.185µs
Request duration    | 6.810709ms
Full round trip     | 7.218894ms
No monitoring registered for this node identified as 'mysql1'.
[root@mysql1 jerichorivera]# pmm-admin add mysql --create-user
[linux:metrics] OK, now monitoring this system.
[mysql:metrics] OK, now monitoring MySQL metrics using DSN pmm:***@unix(/mnt/disks/disk1/data/mysql.sock)
[mysql:queries] OK, now monitoring MySQL queries from slowlog using DSN pmm:***@unix(/mnt/disks/disk1/data/mysql.sock)
[root@mysql1 jerichorivera]# pmm-admin list
pmm-admin 1.10.0
PMM Server      | 10.138.0.2
Client Name     | mysql1
Client Address  | 10.138.0.3
Service Manager | linux-systemd
-------------- ------- ----------- -------- ----------------------------------------------- ------------------------------------------
SERVICE TYPE   NAME    LOCAL PORT  RUNNING  DATA SOURCE                                     OPTIONS
-------------- ------- ----------- -------- ----------------------------------------------- ------------------------------------------
mysql:queries  mysql1  -           YES      pmm:***@unix(/mnt/disks/disk1/data/mysql.sock)  query_source=slowlog, query_examples=true
linux:metrics  mysql1  42000       YES      -
mysql:metrics  mysql1  42002       YES      pmm:***@unix(/mnt/disks/disk1/data/mysql.sock)

Lastly, in case you need to delete the PMM Server instance. Just execute this delete command below to completely remove the instance and the attached disk. Be aware that you may remove the boot disk and retain the attached persistent disk if you prefer.

jerichorivera@percona-support:~/GCE$ gcloud compute instances delete pmm-server
The following instances will be deleted. Any attached disks configured
 to be auto-deleted will be deleted unless they are attached to any
other instances or the `--keep-disks` flag is given and specifies them
 for keeping. Deleting a disk is irreversible and any data on the disk
 will be lost.
 - [pmm-server] in [us-west1-b]
Do you want to continue (Y/n)?  y
Deleted [https://www.googleapis.com/compute/v1/projects/thematic-acumen-204008/zones/us-west1-b/instances/pmm-server].

The other option is to install PMM on Google Container engine which was explained by Manjot Singh in his blog post.

The post Setting up PMM on Google Compute Engine in 15 minutes or less appeared first on Percona Database Performance Blog.

May
23
2018
--

Percona Monitoring and Management 1.11.0 Is Now Available

Percona Monitoring and Management

Percona Monitoring and ManagementPercona Monitoring and Management (PMM) is a free and open-source platform for managing and monitoring MySQL® and MongoDB® performance. You can run PMM in your own environment for maximum security and reliability. It provides thorough time-based analysis for MySQL® and MongoDB® servers to ensure that your data works as efficiently as possible.

In PMM Release 1.11.0, we deliver the following changes:

  • Configurable MySQL Slow Log Rotation – enable or disable rotation, and specify how many files to keep on disk
  • Predictable Graphs – we’ve updated our formulas to use aggregation functions over time for more reliable graphs
  • MySQL Exporter Parsing of my.cnf – we’ve improved how we read my.cnf
  • Annotation improvements – passing multiple strings results in single annotation being written

The issues in the release includes 1 new features & improvements, and 9 bugs fixed.

MySQL Slow Log Rotation Improvements

We spent some time this release going over how we handle MySQL’s Slow Log rotation logic. Query Analytics requires that slow logging be enabled (either to file, or to PERFORMANCE_SCHEMA) and we found that users of Percona Server for MySQL overwhelmingly choose logging to a file in order to take advantage of log_slow_verbosity which provides enhanced InnoDB Usage information. However, the challenge with MySQL’s Slow Log is that it is very verbose and thus the number one concern is disk space. PMM strives to do no harm and so MySQL Slow Log Rotation was a natural fit, but until this release we were very strict and hadn’t enabled any configuration of these parameters.

Percona Server for MySQL Users have long known about Slow Query Log Rotation and Expiration, but until now had no way of using the in-built Percona Server for MySQL feature while ensuring that PMM wasn’t missing any queries from the Slow Log during file rotation. Or perhaps your use case is that you want to do Slow Log Rotation using logrotate or some other facility. Today with Release 1.11 this is now possible!

We’ve made two significant changes:

  1. You can now specify the number of Slow Log files to remain on disk, and let PMM handle deleting the oldest files first. Default remains unchanged – 1 Slow Log to remain on disk.
  2. Slow Log rotation can now be disabled, for example if you want to manage rotation using logrotate or Percona Server for MySQL Slow Query Log Rotation and Expiration. Default remains unchanged – Slow Log Rotation is ON.

Number of Slow Logs Retained on Disk

Slow Logs Rotation – On or Off

You specify each of these two new controls when setting up the MySQL service. The following example specifies that 5 Slow Log files should remain on disk:

pmm-admin add mysql ... --retain-slow-logs=5

While the following example specifies that Slow Log rotation is to be disabled (flag value of false), with the assumption that you will perform your own Slow Log Rotation:

pmm-admin add mysql ... --slow-log-rotation=false

We don’t currently support modifying option parameters for an existing service definition. This means you must remove, then re-add the service and include the new options.

We’re including a logrotate script in this post to get you started, and it is designed to keep 30 copies of Slow Logs at 1GB each. Note that you’ll need to update the Slow Log location, and ensure a MySQL User Account with SUPER, RELOAD are used for this script to successfully execute.

Example logrotate
/var/mysql/mysql-slow.log {
    nocompress
    create 660 mysql mysql
    size 1G
    dateext
    missingok
    notifempty
    sharedscripts
    postrotate
       /bin/mysql -e 'SELECT @@global.long_query_time INTO @LQT_SAVE; SET GLOBAL long_query_time=2000; SELECT SLEEP(2); FLUSH SLOW LOGS; SELECT SLEEP(2); SET GLOBAL long_query_time=@LQT_SAVE;'
    endscript
    rotate 30
}

Predictable Graphs

We’ve updated the logic on four dashboards to better handle predictability and also to allow zooming to look at shorter time ranges.  For example, refreshing PXC/Galera graphs prior to 1.11 led to graphs spiking at different points during the metric series. We’ve reviewed each of these graphs and their corresponding queries and added in <aggregation>_over_time() functions so that graphs display a consistent view of the metric series. This improves your ability to drill in on the dashboards so that no matter how short your time range, you will still observe the same spikes and troughs in your metric series. The four dashboards affected by this improvement are:

  • Home Dashboard
  • PXC/Galera Graphs Dashboard
  • MySQL Overview Dashboard
  • MySQL InnoDB Metrics Dashboard

MySQL Exporter parsing of my.cnf

In earlier releases, the MySQL Exporter expected only key=value type flags. It would ignore options without values (i.e. disable-auto-rehash), and could sometimes read the wrong section of the my.cnf file.  We’ve updated the parsing engine to be more MySQL compatible.

Annotation improvements

Annotations permit the display of an event on all dashboards in PMM.  Users reported that passing more than one string to pmm-admin annotate would generate an error, so we updated the parsing logic to assume all strings passed during annotation creation generates a single annotation event.  Previously you needed to enclose your strings in quotes so that it would be parsed as a single string.

Issues in this release

New Features & Improvements

  • PMM-2432 – Configurable MySQL Slow Log File Rotation

Bug fixes

  • PMM-1187 – Graphs breaks at tight resolution 
  • PMM-2362 – Explain is a part of query 
  • PMM-2399 – RPM for pmm-server is missing some files 
  • PMM-2407 – Menu items are not visible on PMM QAN dashboard 
  • PMM-2469 – Parsing of a valid my.cnf can break the mysqld_exporter 
  • PMM-2479 – PXC/Galera Cluster Overview dashboard: typo in metric names 
  • PMM-2484 – PXC/Galera Graphs display unpredictable results each time they are refreshed 
  • PMM-2503 – Wrong InnoDB Adaptive Hash Index Statistics 
  • PMM-2513 – QAN-agent always changes max_slowlog_size to 0 
  • PMM-2514 – pmm-admin annotate help – fix typos
  • PMM-2515 – pmm-admin annotate – more than 1 annotation 

How to get PMM

PMM is available for installation using three methods:

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system.

The post Percona Monitoring and Management 1.11.0 Is Now Available appeared first on Percona Database Performance Blog.

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