Personal blog of Yzmir Ramirez

Here at Percona, we operate on the premise that full transparency makes a product better. We strive to build the best open-source database products, but also to help you manage any issues that arise in any of the databases that we support. And, in true open source form, report back on any issues or bugs you might encounter along the way.

We constantly update our bug reports and monitor other boards to ensure we have the latest information, but we wanted to make it a little easier for you to keep track of the most critical ones. These posts are a central place to get information on the most noteworthy open and recently resolved bugs. 

In this September 2022 edition of our monthly bug report, we have the following list of bugs:

 

Percona Server for MySQL/MySQL Bugs

PS-8328(MySQL#108316): Some queries with “group_concat” and “Group by with rollup” can either crash the server or return very unexpected wrong results.

It is happening with 8.0 whereas 5.7 is not repeating this behavior.

Reported Affected Version/s: 8.0.28, 8.0.29, 8.0.30

 

MySQL#96071: According to performance schema the new TempTable engine uses about three times as much memory as MEMORY for the internal TempTable created by Query 10 of DBT3.

Q10:

mysql> select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= '1993-11-01' and o_orderdate < date_add( '1993-11-01' ,interval '3' month) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20;

MySQL 8.0 has introduced TempTable as the new, default internal temporary table storage engine to speed up query processing.

Please note that this behavior is not repeating in MySQL 8.0.29

Reported Affected Version/s: 8.0.16

 

PS-8327: ALTER TABLE … CHECK PARTITION inside procedure causes server to Aborted connection and Got an error writing communication packets in 5.7 where in 8.0 we encountered with Trying to reconnect.

Reported Affected Version/s: 5.7.38, 8.0.28

 

ZFS Bug: 

Failure reported when InnoDB cluster in Multi-Primary mode used and it upgraded to 8.0.30 from 8.0.29 and restarted one of the nodes after it successfully joined the cluster after upgrade and then it failed to start with error “The redo log file ./#innodb_redo/#ib_redo1 size 647168 is not a multiple of innodb_page_size”

So the issue is with the FALLOC_FL_ZERO_RANGE flag where documentation says that not all filesystems support it and an error should be returned if it’s not supported. However, with ZFS no error is returned so if FALLOC_FL_ZERO_RANGE is replaced with 0 in f.cpp then the file is created properly.

fallocate() fails on ZFS when used with FALLOC_FL_ZERO_RANGE and it doesn’t even return the error, so it’s a problem in kernel/glibc/driver, not from the MySQL end.

So fallocate implementation is broken in the sense of returning 0 (success) even though it has not changed the (uncompressed) size of the file (which your experiments with f.cpp), then indeed using glibc2.12 variant of MySQL could have saved you from experiencing this bug, while using glibc2.17 variant (which is built on machine which supports FALLOC_FL_ZERO_RANGE) has exposed you to the problem.

For a workaround and detailed explanation of this issue, please refer https://bugs.mysql.com/bug.php?id=108012

Reported Affected Version/s: 8.0.30

Please note that this is a ZFS bug, not a MySQL bug and users need to study workarounds.

 

Percona XtraDB Cluster

PXC-4012: Node leaves Percona XtraDB Cluster while running concurrent CREATE USER IF NOT EXISTS when using password_history option.

Reported Affected Version/s: 8.0.28

 

PXC-3944: DDLs replicated as Total Order Isolation (TOI) fails constantly with Deadlock found when trying to get lock; try restarting transaction error after setting repl.max_ws_size. However, the problem is not seen after setting wsrep_max_ws_size` (maximum write-set size) from a different session.

Reported Affected Version/s: 8.0.27

 

Percona Toolkit

PT-2072: pt-duplicate-key-checker chokes on columns that contain a line-break

Reported Affected Version/s: 3.4.0

 

PT-2066: While running pt-online-schema-change where MySQL instance is in AWS RDS and MySQL is connected to a non-default port is raising the error saying. 

Cannot connect to MySQL: DBI connect(‘my_db;host=[db instance].rds.amazonaws.com;mysql_read_default_group=client’,’admin’,…) failed: Can’t connect to MySQL server on ‘[db instance].rds.amazonaws.com:3306’ (110) at /usr/bin/pt-online-schema-change line 2345.

As a workaround to this problem, we can pass the connection options via DSN.

Please note that it is a private bug that won’t be available publicly. 

Reported Affected Version/s: 3.3.1

 

Percona Monitoring and Management (PMM)

PMM-10075: When there is at least one service added to PMM and we changed the password where password contains quotes say “admin123” then re-login is failed saying invalid user or password.

Reported Affected Version/s: 2.28.0

 

PMM-10454: By going “share” over a panel’s title on any dashboard and clicking on the ‘Direct link rendered image’ logged error “Failed to load resource: the server responded with a status of 404 (Not Found)” and dashboard image has 404 error message along with Welcome panel.

Reported Affected Version/s: 2.29.0

 

PMM-10364: After upgraded percona-pg_stat_monitor version to 1.0.1, and after that pmm-agent raised the errors getNewBuckets failed: failed to query pg_stat_monitor: pq: pg_stat_monitor: incorrect number of output arguments…

Reported Affected Version/s: 2.28.0

Percona XtraBackup

PXB-2797: When importing a single table (IMPORT TABLESPACE) from a backup made using XtraBackup and the table contains a full-text index the import process failed with ERROR 1808 (HY000) at line 132: Schema mismatch (Index x field y is ascending which does not match metadata file which is descending).

Reported Affected Version/s: 8.0.28

 

Summary

We welcome community input and feedback on all our products. If you find a bug or would like to suggest an improvement or a feature, learn how in our post, How to Report Bugs, Improvements, and New Feature Requests for Percona Products.

For the most up-to-date information, be sure to follow us on Twitter, LinkedIn, and Facebook.

Quick References:

Percona JIRA

MySQL Bug Report

Report a Bug in a Percona Product

___

About Percona:

As the only provider of distributions for all three of the most popular open source databases—PostgreSQL, MySQL, and MongoDB—Percona provides expertise, software, support, and services no matter the technology.

Whether it’s enabling developers or DBAs to realize value faster with tools, advice, and guidance, or making sure applications can scale and handle peak loads, Percona is here to help.

Percona is committed to being open source and preventing vendor lock-in. Percona contributes all changes to the upstream community for possible inclusion in future product releases.

Autumn brought new cool features and improvements to Percona Monitoring and Management (PMM) in V2.31. Enhanced user experience with the updated main menu, Alerting, and better PostgreSQL autovacuum observability with the new Vacuum dashboard are the major themes that we focused on in this release. Check out our Release Note of 2.31 for the full list of new features, enhancements, and bug fixes.

You can get started with PMM in minutes with the PMM Demo to check out the latest version of PMM V2.31.

Some of the highlights in PMM V2.31 include:

General availability of Percona Alerting

We are excited to introduce a streamlined alert setup process in PMM with an overhauled, unified alerting system based on Grafana. 

All Alerting functionality is now consolidated in a single pane of glass on the Alerting page. From here, you can configure, create and monitor alerts based on Percona or Grafana templates. 

The Alert Rules tab has also evolved into a more intuitive interface with an added layer for simplifying complex Grafana rules. You’ll find that the new Percona templated alert option here offers the same functionality available in the Tech Preview of Integrated Alerting but uses a friendlier interface with very advanced alerting capabilities. 

As an important and generally useful feature, this new Alerting feature is now enabled by default and ready to use in production! 

For more information about Percona Alerting, check out Alerting doc.

Deprecated Integrated Alerting

The new Percona Alerting feature fully replaces the old Integrated Alerting Tech Preview available in previous PMM versions. The new alerting brings full feature parity with Integrated Alerting, along with additional benefits like Grafana-based alert rules and a unified alerting command center. 

However, alert rules created with Integrated Alerting are not automatically migrated to Percona Alerting. After upgrading, make sure to manually migrate any custom alert rules that you want to transfer to PMM 2.31 using the script

Easier query building, enhanced main menu in PMM 2.31

We have powered-up PMM with Grafana 9.1 by drawing on its latest features and improvements. Here is the list of features and enhancements that have been shipped in this release: 

Redesigned expandable main menu (side menu)

With the 2.31 release, we introduce a more user-friendly and accessible main menu inspired by Grafana’s expandable side menu. PMM dashboards are the heart of the monitoring, and we aimed to provide quick and easy access to the frequently used dashboard from the main menu. On this menu, you’ll be able to browse dashboards with one click, like Operating System, MySQL, MongoDB, PostgreSQL, etc. 

Pin your favorite dashboards to the main menu (side menu)

PMM provides many custom dashboards with dozens of metrics to monitor your databases. Most users in an organization use just a handful of dashboards regularly; now, it is much easier to access them by saving the most frequently used dashboards to the main menu. You see your saved dashboards under the Starred section on the main menu.

This feature is enabled by default in PMM. You can disable it by disabling the savedItems feature flag if you have server admin or Grafa admin roles.

Tip:

You can follow these steps to add your dashboard to Starred on the main menu:

1. Open your dashboard
2. Mark it by clicking the star icon next to the Dashboard name on the top right corner
3. Hover Starred icon on the main menu and see all saved dashboards.

Search dashboards on Panel titles

While looking for a specific metric or panel inside dashboards, it is easy to forget which dashboard presents it. Now you can quickly find the dashboard you need on the Search dashboard page.

Command palette

A new shortcut which is named “command palette” in Grafana, has been provided in this PMM version. You can easily access main menu sections, dashboards, or other tasks using cmd+K (MacOS) or ctrl+K (Linux/Windows).  Run the command on the Explore section to quickly run a query or on the Preferences section to easily change theme preferences. 

Visual Prometheus Query Builder in Explore (Beta)

A new Prometheus query builder has been introduced in Grafana 9. This feature allows everyone, especially new users, to build queries on PromQL without extensive expertise. Visual query builder UI in Explore allows anyone to write queries and understand what the query means. 

You can easily switch to the new Prometheus query builder (Builder) by clicking on Builder mode in the top-right corner. The Builder mode allows you to build your queries by choosing the metric from the dropdown menu. If you want to continue on Text mode, you can switch to Code mode while having your text changes preserved. Please check this blog to learn more about Builder mode.

Add your queries to a dashboard or create a new dashboard from Explore

You’ll probably like this news if you’re a fan of the Explore feature or frequently use it. Now, creating a panel/dashboard from Explore with one click is possible by saving you from jobs like copy-paste or re-write queries. You only need to click the “Add to dashboard” button after you run your query.  Then, your panel will be automatically created with the query and a default visualization. You can change the visualization on the dashboard later by clicking the “Edit” panel. Note that you need to have the Editor/Admin/SuperAdmin role to save the panel to the chosen dashboard and follow the current dashboard save flow to save the added panel. Otherwise, you’ll lose the added new panel on your dashboard.

Experimental Vacuum Dashboard

The autovacuum process in PostgreSQL is designed to prevent table bloat by removing dead tuples. These dead tuples can accumulate because of the unique way that PostgreSQL handles MVCC. Because PostgreSQL’s architecture is so unique, the autovacuum process is sometimes not understood well enough to be able to tune its parameters for peak performance. After talking to many customers and realizing that this is a recurring problem, we decided to help our users by providing a dashboard that allows you to monitor metrics related to the vacuum process – thereby helping you tune these parameters for better performance.

Now, you can monitor PostgreSQL vacuum processes with a new experimental dashboard named PostgreSQL Vacuum Monitoring which is available in the Experimental folder. We’re still working on this dashboard to add more metrics. Please let us know your feedback about this dashboard in the comments.

Tip

If you’d like to move the Vacuum experimental dashboard to the PostgreSQL folder or other folders that you internally use to gather all PostgreSQL dashboards, please check this document to see how you can move dashboards to a different folder.

General availability of Podman

We are excited to announce the General Availability (GA) of Podman support for deploying PMM 2.31.0. We had introduced it in 2.29.0 as a preview feature, but now we are production ready with this feature. 

Simplified deployment with Database as a Service (DBaaS)

In our constant endeavor and focus on an enhanced user experience, in PMM 2.31.0, we have simplified the deployment and configuration of DBaaS as follows:

• With PMM 2.31.0, you can easily add a DB cluster from a newly created K8s cluster. All the DB cluster window fields are auto-populated with the values based on the existing K8s cluster. 
• For PMM 2.31.0, while accessing DbaaS, if you have an existing Kubernetes cluster configured for DBaaS, you will be automatically redirected to the DB Cluster page. Otherwise, you would be redirected to the Kubernetes Cluster page.

What’s next?

• New UX improvements are baking! We’re working on making our main menu easy to use and minimal. Next release, the main menu will present only monitored services, and you’ll have a clearer and less crowded main menu.
• The home dashboard will replace the experimental Home dashboard, which is available in the Experimental folder after v2.30. Please do not forget to share your feedback with us if you have tried it. 
• We’ll improve the vacuum dashboard with more metrics. If you’d like to enhance it with us, you can share your feedback in the comments.
• We have started to work on two new and big projects: High Availability in PMM and advanced role-based access control (RBAC). We’d love to hear your needs, use cases, and suggestions. You can quickly book a short call with the product team to collaborate with us. 

Thanks to Community and Perconians

We love our community and team in Percona, who help shape PMM and improve better! 

Thank you for your collaboration on the new main menu:

Pedro Fernandes, Fábio Silva, Matej Kubinec

Thank you for your collaboration on Vacuum Dashboards:

Anton Bystrov, Daniel Burgos, Jiri Ctvrtka, Nailya Kutlubaeva, Umair Shahid

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

Download Percona Monitoring and Management Today

Geo locations are a cornerstone of modern applications. Whether you’re a food delivery business or a family photographer, knowing the closest “something” to you or your clients can be a great feature.

In our ‘Scaling and Optimization’ training class for MySQL, one of the things we discuss is column types. The spatial types are only mentioned in passing, as less than 0.5% of MySQL users know of their existence (that’s a wild guess, with no factual basis). In this post, we briefly discuss the POINT type and how it can be used to calculate distances to the closest public park.

Import the data

To start off, we need a few tables and some data. The first table will hold the mapping between the zip code and its associated latitude/longitude. GeoNames has this data under the Creative Commons v3 license, available here for most countries. The data files are CSV and the readme.txt explains the various columns. We are only interested in a few of the columns.

CREATE TABLE usazips (
 id int unsigned NOT NULL AUTO_INCREMENT,
 zipCode int unsigned NOT NULL COMMENT 'All USA postal codes are integers',
 state varchar(20) NOT NULL,
 placeName varchar(200) NOT NULL,
 placeDesc varchar(100) DEFAULT NULL,
 latLong point NOT NULL /*!80003 SRID 4326 */,
 PRIMARY KEY (id));

There are a couple of things about this schema that are not typical in MySQL.

Firstly, the latLong column type is POINT which can store an X and Y coordinate. By default, these coordinates could be on any plane. They could represent latitude and longitude, but they could also be centimeters up and down on the surface of your desk, or yards left and right of the biggest tree at your local park. How do you know which? You need a Spatial Reference System. Thankfully, MySQL comes preloaded with about 5,100 of these systems (See INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS). Each SRS has an associated Spatial Reference ID (SRID).

The SQL comment on the POINT column above ties the data in this column with a specific SRID, 4326. We can see in the I_S table noted earlier, this SRID maps to the ‘World Geodetic System 1984’ (aka WGS84) which is the same SRS used in the GeoNames dataset. By having our column and the dataset aligned, we won’t need to do any geo-transformation later on.

The second thing to note in the schema is the use of a SPATIAL index on the latLong column. A SPATIAL index in MySQL is created using R-Trees which are geospatial-specific data structures.

Let’s load this data into the table. There are 12 fields in the CSV but we only care about six of them. The LOAD DATA command allows you to associate each CSV field, positionally, with either the column name in the table or a user variable. The first field in our CSV is the country code, which we don’t care about, so we assign that field to the @dummy variable. The second field is the zip code, and we want that to go directly into the table-column so we specify the column name. We do the same for the third, fifth, sixth, and ninth fields.

The 10th and 11th CSV fields are the latitude and longitude. We need to convert those two VARCHAR fields into a POINT. We can apply some SQL transformations using the SET command which can reference user variables assigned earlier. These two fields are assigned to @lat and @lon. The remaining fields all go into @dummy since they are not used.

LOAD DATA INFILE '/var/lib/mysql-files/US.txt'
INTO TABLE usazips
FIELDS TERMINATED BY '\t' (@dummy, zipCode, placeName, @dummy, state, placeDesc, @dummy, @dummy, @dummy, @lat, @lon, @dummy)
SET id = NULL, latLong = ST_PointFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326);

Unfortunately, the POINT() function in MySQL always returns an SRID of 0. Since we specified our column to be a specific SRID, a direct import will fail.

mysql> LOAD ... SET latLong = POINT(@lat, @lon);
ERROR 3643 (HY000): The SRID of the geometry does not match the SRID of the column 'latLong'.
The SRID of the geometry is 0, but the SRID of the column is 4326. Consider changing the SRID
of the geometry or the SRID property of the column.

Instead, we must “go the long route” by creating a string representation of a POINT object in the Well-Known-Text format, which MySQL can parse into a Point column type with an associated SRID.

mysql> LOAD ... SET latLong = ST_PointFromText(CONCAT('POINT(', @lat, ' ', @lon, ')'), 4326);

Yes, this was very confusing to me as well:

Here’s a quick verification of our data with an additional column showing the lat-long binary data being converted back into WKT format.

mysql> SELECT *, ST_AsText(latLong) FROM usazips WHERE zipCode = 76455;
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| id    | zipCode | state | placeName | placeDesc | latLong                                              | ST_AsText(latLong)      |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
| 34292 |   76455 | TX    | Gustine   | Comanche  | 0xE6100000010100000068B3EA73B59958C0B84082E2C7D83F40 | POINT(31.8468 -98.4017) |
+-------+---------+-------+-----------+-----------+------------------------------------------------------+-------------------------+
1 row in set (0.04 sec)

More data to load

Now that we have all this zip code + latitude and longitude data, we next need the locations of where we want to find our distances. This could be a list of grocery stores or coffee shops. In this example, we will use a list of public parks in San Antonio, TX. Thankfully, San Antonio has all of this data openly available. I downloaded the ‘Park Boundaries’ dataset in GeoJSON format since the CSV did not contain any latitude/longitude coordinates.

CREATE TABLE parks (
 parkId int unsigned NOT NULL PRIMARY KEY,
 parkName varchar(100) NOT NULL,
 parkLocation point NOT NULL /*!80003 SRID 4326 */);

The GeoJSON data is one giant JSON string with all of the needed data nested in various JSON objects and arrays. Instead of writing some Python script to ETL the data, I decided to try out the JSON import feature of the MySQL Shell and first import the JSON directly into a temporary table.

$ mysqlsh appUser@127.0.0.1/world --import Park_Boundaries.geojson tempParks
Importing from file "Park_Boundaries.geojson" to collection `world`.`tempParks` in MySQL Server at 127.0.0.1:33060
..1..1
Processed 3.29 MB in 1 document in 0.3478 sec (1.00 document/s)
Total successfully imported documents 1 (1.00 document/s)

The MySQL Shell utility created a new table called ‘tempParks’, in the ‘world’ database with the following schema. One row was inserted, which was the entire dataset as one JSON object.

CREATE TABLE `tempParks` (
 `doc` json DEFAULT NULL,
 `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
 `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
 PRIMARY KEY (`_id`),
 CONSTRAINT `$val_strict_75A2A431C77036365C11677C92B55F4B307FB335` CHECK (json_schema_valid(`_json_schema`,`doc`)) /*!80016 NOT ENFORCED */
);

From here, I extracted the needed information from within the JSON and inserted it into the final table, shown above. This SQL uses the new JSON_TABLE function in MySQL 8 to create a result set from nested JSON data (strangely, there were a couple of duplicate parks in the dataset, which is why you see the ON DUPLICATE modifier). Note: that this dataset does longitude at index 0, and latitude at index 1.

INSERT INTO parks
  SELECT k.parkID, k.parkName, ST_PointFromText(CONCAT('POINT(', parkLat, ' ', parkLon, ')'), 4326)
  FROM tempParks, JSON_TABLE(
    tempParks.doc,
    "$.features[*]" COLUMNS (
      parkID int PATH "$.properties.ParkID",
      parkName varchar(150) PATH "$.properties.ParkName",
      parkLat varchar(20) PATH "$.geometry.coordinates[0][0][0][1]",
      parkLon varchar(20) PATH "$.geometry.coordinates[0][0][0][0]"
    )
  ) AS k ON DUPLICATE KEY UPDATE parkId = k.parkID;

Query OK, 391 rows affected (0.11 sec)
Records: 392  Duplicates: 0  Warnings: 0

What’s the closest park?

Now that all the information is finally loaded, let’s find the nearest park to where you might live, based on your zip code.

First, get the reference location (as MySQL POINT data) for the zip code in question:

mysql> SELECT latLong INTO @zipLocation FROM usazips WHERE zipCode = 78218;

Then, calculate the distance between our reference zip code and all of the park locations. Show the five closest (not including any school parks):

mysql> SELECT p.parkName, ST_AsText(p.parkLocation) AS location, ST_Distance_Sphere(@zipLocation, p.parkLocation) AS metersAway
FROM parks p WHERE parkName NOT LIKE '%School%' ORDER BY metersAway LIMIT 5;
+-----------------------+----------------------------------------------+--------------------+
| parkName              | location                                     | metersAway         |
+-----------------------+----------------------------------------------+--------------------+
| Robert L B Tobin Park | POINT(29.501796110000043 -98.42111988)       |   1817.72881969296 |
| Wilshire Terrace Park | POINT(29.486397887000063 -98.41776879999996) | 1830.8541086553364 |
| James Park            | POINT(29.48168424800008 -98.41725954999998)  |  2171.268012559491 |
| Perrin Homestead Park | POINT(29.524477369000067 -98.41258342199997) |  3198.082796589456 |
| Tobin Library Park    | POINT(29.510899863000077 -98.43343147299998) |  3314.044809806559 |
+-----------------------+----------------------------------------------+--------------------+
5 rows in set (0.01 sec)

The closest park is 1,817.7 meters away! Time for a picnic!

Conclusion

Geo locations in MySQL are not difficult. The hardest part was just finding the data and ETL’ing into some tables. MySQL’s native column types and support for the OpenGIS standards make working with the data quite easy.

Several things to note:

1) The reference location for your zip code may be several hundred or a thousand meters away. Because of this, that park that is right behind your house might not show up as the actual closest because another park is closer to the reference.

2) No SPATIAL indexes were added to the tables as they would be of no use because we must execute the distance function against all parks (ie: all rows) to find the closest park, hence the ‘metersAway’ query will always perform a full table scan.

3) All USA postal codes are integers. You can expand this to your country by simply changing the column type.

Today we are talking about The Schema.org Blueprints Module with Jacob Rockowitz.

www.talkingDrupal.com/366

Topics

• What is Schema.org
• What is the Schema.org first approach
• Is this just for SEO
• What is the Schema.org Blueprints Module
• What are the goals
• How does this module benefit your project
• Can you give us a demo
• Why Paragraphs
• How do you handle schema you do not need
• How do you handle missing schema
• Have any sites used this
• What is your maintenance approach
• Roadmap
• Recommendations
• Contributing to Schema.org

Resources

• Introducing the Schema.org Blueprints Module for Ambitious Drupal Site Builders
• Schema.org Blueprints project page
• Schema.org-first blog posts
  • The future of our Drupal CMS and Schema.org: APIs, UI, and UX
  • Using a Schema.org-First approach to build a single source of truth and a unified Content Management System
  • Exploring a Schema-First approach to Drupal and Content Management Systems
• Videos
  • Here is a 7 minute demo of the module’s features and functionality
  • Here is a demo of a podcast Github issue

Guests

Jacob Rockowitz – www.jrockowitz.com @jrockowitz

Hosts

Nic Laflin – www.nLighteneddevelopment.com @nicxvan John Picozzi – www.epam.com @johnpicozzi Tim Lehnen – @hestenet

MOTW

Flex Field Defines a new “FlexField” field type that lets you create simple inline multiple-value fields without having to use entity references.

The microservice architecture is not a new pattern but has become very popular lately for mainly two reasons: cloud computing and containers. That combo helped increase adoption by tackling the two main concerns on every infrastructure: Cost reduction and infrastructure management.

However, all that beauty hides a dark truth:

The hardest part of microservices is the data layer

And that is especially true when it comes to classic relational databases like MySQL. Let’s figure out why that is.

MySQL and the microservice

Following the same two pillars of microservices (cloud computing and containers), what can one do with that in the MySQL space? What do cloud computing and containers bring to the table?

Cloud computing

The magic of the cloud is that it allows you to be cost savvy by letting you easily SCALE UP/SCALE DOWN the size of your instances. No more wasted money on big servers that are underutilized most of the time. What’s the catch? It’s gotta be fast. Quick scale up to be ready to deal with traffic and quick scale down to cut costs when traffic is low. 

Containers

The magic of containers is that one can slice hardware to the resource requirements. The catch here is that containers were traditionally used on stateless applications. Disposable containers.

Relational databases in general and MySQL, in particular, are not fast to scale and are stateful. However, it can be adapted to the cloud and be used for the data layer on microservices.

The Scale Cube

The book “The Art of Scalability” by Abott and Fisher describes a really useful, three dimension scalability model: the Scale Cube. In this model, scaling an application by running clones behind a load balancer is known as X-axis scaling. The other two kinds of scaling are Y-axis scaling and Z-axis scaling. The microservice architecture is an application of Y-axis scaling: It defines an architecture that structures the application as a set of loosely coupled, collaborating services.

• X-Axis: Horizontal Duplication and Cloning of services and data (READ REPLICAS)
• Y-Axis: Functional Decomposition and Segmentation – (MULTI-TENANT)
• Z-Axis: Service and Data Partitioning along Customer Boundaries – (SHARDING)

On microservices, each service has its own database in order to be decoupled from other services. In other words: a service’s transactions only involve its database; data is private and accessible only via the microservice API.

It’s natural that the first approach to divide the data is by using the multi-tenant pattern:

Actually before trying multi-tenant, one can use a tables-per-service model where each service owns a set of tables that must only be accessed by that service, but by having that “soft” division, the temptation to skip the API and access directly other services’ tables is huge.

Schema-per-service, where each service has a database schema that is private to that service is appealing since it makes ownership clearer. It is easy to create a user per database, with specific grants to limit database access.

This pattern of “shared database” however comes with some drawbacks like:

• Single hardware: a failure in your database will hurt all the microservices
• Resource-intensive tasks related to a particular database will impact the other databases (think on DDLs)
• Shared resources: disk latency, IOPS, and bandwidth needs to be shared, as well as other resources like CPU, Network bandwidth, etc.

The alternative is to go “Database per service”

Share nothing. Cleaner logical separation. Isolated issues. Services are loosely coupled. In fact, this opens the door for microservices to use a database that best suits their needs, like a graph db, a document-oriented database, etc. But as with everything, this also comes with drawbacks:

• The most obvious: cost. More instances to deploy
• The most critical: Distributed transactions. As we mentioned before, microservices are collaborative between them and that means that transactions span several services. 

The simplistic approach is to use a two-phase commit implementation. But that solution is just an open invitation to a huge amount of locking issues. It just doesn’t scale. So what are the alternatives?

• Implementing transactions that span services: The Saga pattern
• Implementing queries that span services: API composition or Command Query Responsibility Segregation (CQRS)

A saga is a sequence of local transactions. Each local transaction updates the database and publishes messages or events that trigger the next local transaction in the saga. If a local transaction fails for whatever reason, then the saga executes a series of compensating transactions that undo the changes made by the previous transactions. More on Saga here: https://microservices.io/patterns/data/saga.html

An API composition is just a composer that invokes queries on each microservice and then performs an in-memory join of the results:
https://microservices.io/patterns/data/api-composition.html

CQRS is keeping one or more materialized views that contain data from multiple services. This avoids the need to do joins on the query size: https://microservices.io/patterns/data/cqrs.html

What do all these alternatives have in common? That is taken care of at the API level: it becomes the responsibility of the developer to implement and maintain it. The data layer keep continues to be data, not information.

Make it cloud

There are means for your MySQL to be cloud-native: Easy to scale up and down fast; running on containers, a lot of containers; orchestrated with Kubernetes; with all the pros of Kubernetes (health checks, I’m looking at you).

Percona Operator for MySQL based on Percona XtraDB Cluster

A Kubernetes Operator is a special type of controller introduced to simplify complex deployments. Operators provide full application lifecycle automation and make use of the Kubernetes primitives above to build and manage your application. 

In our blog post “Introduction to Percona Operator for MySQL Based on Percona XtraDB Cluster” an overview of the operator and its benefits are covered. However, it’s worth mentioning what does it make it cloud native:

• It takes advantage of cloud computing, scaling up and down
• Runs con containers
• Is orchestrated by the cloud orchestrator itself: Kubernetes

Under the hood is a Percona XtraDB Cluster running on PODs. Easy to scale out (increase the number of nodes: https://www.percona.com/doc/kubernetes-operator-for-pxc/scaling.html) and can be scaled up by giving more resources to the POD definition (without downtime)

Give it a try https://www.percona.com/doc/kubernetes-operator-for-pxc/index.html and unleash the power of the cloud on MySQL.

September was and is an extremely fruitful month (especially for the black-hat hackers) for news about data leaks and breaches:

1. Uber suffers computer system breach, alerts authorities
2. GTA 6 source code and videos leaked after Rockstar Games hack
3. Revolut breach: personal and banking data exposed

In this blog post, we want to remind you how to keep your data safe when running your favorite open source databases.

Network exposure

Search engines like Shodan are an easy way to search for publicly available databases. Over 3.6 million MySQL servers found exposed on the Internet.

The best practice here is to run database servers in the isolated private network, even from the rest of your corporate network. In this case, you have a low risk of exposure even in the case of server misconfiguration.

If for some reason you run your database on the server in a public network, you still can avoid network exposure:

• Bind your server to the localhost or private IP address of the server

For example, for MySQL use bind-address option in your my.cnf:

bind-address = 192.168.0.123

• Configure your firewall to block access through a public network interface on the operating system

Users and passwords

To complement the network exposure story, ensure that your users cannot connect from just any IP address. Taking MySQL as an example, the following GRANT command allows to connect from one of the private networks only:

GRANT ALL ON db1.* TO 'perconaAdmin'@'192.168.0.0/255.255.0.0';

MySQL also has an auth_socket plugin, that controls the connection to the database through Unix sockets. Read more in this blog post: Use MySQL Without a Password (and Still be Secure).

Minimize the risk and do not use default usernames and passwords. SecList is a good example of bad choices for passwords: MySQL, PostgreSQL, and a misc list. Percona Platform provides users with Advisors (read more below) that preemptively check for misconfigured grants, weak passwords, and more.

So now we agree that a strong password is a must. Did you know that you can enforce it? This Percona post talks about Improving MySQL Password Security with Validation Plugin that performs such enforcement.

A strong password is set, great! To make your system even more resilient to security risks, it is recommended to have a password rotation policy. This policy can be manually executed, but also can be automated through various integrations, like LDAP, KMIP, HashiCorp Vault, and many more. For example, this document describes how Percona Server for MongoDB can work with LDAP.

Encryption

There are two types of encryption when you talk about databases and ideally, you’re going to use both of them:

1. Transport encryption – secure the traffic between client and server and between cluster nodes
2. Data-at-rest encryption (or Transparent Data Encryption – TDE) – encrypt the data on a disk to prevent unauthorized access

Transport

With an unencrypted connection between the client and the server, someone with access to the network could watch all your traffic and steal the credentials and sensitive data. We recommend enabling network encryption by default. Read the following blog posts highlighting the details:

• Enabling and Enforcing SSL/TLS for PostgreSQL Connections
• Percona Server for MySQL Encryption Options and Choices
• Network (Transport) Encryption for MongoDB

Data-at-rest

Someone can get access to the physical disk or a network block storage and read the data. To mitigate this risk, you can encrypt the data on the disk. It can be done on the file system, block storage level, and with the database storage engine itself.

Tools like fscrypt or in-built encryption in ZFS can help with file system encryption. Public clouds provide built-in encryption for their network storage solutions (ex AWS EBS, GCP). Private storage solutions, like Ceph, also come with the support of data-at-rest encryption on the block level.

Percona takes security seriously, which is why we recommend enabling data-at-rest encryption by default, especially for production workloads. Percona Server for MySQL and Percona Server for MongoDB provides you with a wide variety of options to perform TDE on the database level.

Preventive measures

Mistakes and misconfiguration can happen and it would be cool if there was a mechanism to alert you about issues before it is too late. Guess what – we have it! 

Percona Monitoring and Management (PMM) comes with Advisors which are the checks that identify potential security threats, vulnerabilities, data loss or data corruption, and other issues. Advisors are the software representation of the years of Percona’s expertise in database security and performance.

By connecting PMM to Percona Platform, users can get more sophisticated Advisors for free, whereas our paid customers are getting even deeper database checks, which discover various misconfiguration or non-compliance gems.

Learn more about Percona Platform with PMM on our website and check if your databases are secured and fine-tuned right away.

If you still believe you need more help, please let us know through our Community Forums or contact the Percona team directly.

When you develop an application you expect success, and often success comes with growth problems.  These problems especially show themselves in the area of data storage, where being stateful is not as easy to scale as the stateless parts of the application.

There are several stages of approaching database scalability:

1. Configuration and query optimization. This step can help a lot, and I would recommend a recent book by Daniel Nichter “Efficient MySQL Performance: Best Practices and Techniques”, which goes into this topic.
2. If #1 is done and you continue to push the limits of your database, the next step is to improve the hardware: adding extra memory, improving storage throughput (regular SSD, NVMe storage layer, etc.), or increasing the size of the cloud instances (this is what I call “optimization by credit card”). This typically should help, but only to a certain limit. And there is only so much memory or storage you can push into a server before it becomes very expensive very quickly, never mind the physical limits of how much memory you can fit into a single server.
3. Step 3 is distributing the workload on multiple servers when the limit of the single server is met. When the workload is read-intensive, it can be typically solved by a regular MySQL source->replica replication, however, if you need to scale writes, it becomes quite complicated.

I would like to provide an excerpt from the Square Cash Engineering blog, which describes their experience (Sharding Cash | Square Corner Blog (squareup.com)):

“Cash was growing tremendously and struggled to stay up during peak traffic. We were running through the classic playbook for scaling out: caching, moving out historical data, replica reads, buying expensive hardware. But it wasn’t enough. Each one of these things bought us time, but we were also growing really fast and we needed a solution that would let us scale out infinitely. We had one final item left in the playbook.”

Before jumping to the solution that Square Cash is using, I should mention a traditional solution to sharding (this is how we name distributing MySQL workload into multiple smaller pieces – servers) –  sharding on the application level – basically, an application decides what server to use to execute a query. However, there is a constantly increasing need to have sharding logic separated from the application, so developers are focusing on creating business outcomes rather than solving database workload distribution problems again and again. Therefore there is a need to move the “distribute workload” logic from an application level to a middleware or even to a database level.

There is already an available middleware that helps with these problems (and this is what Square Cash is using): Vitess (Vitess | A database clustering system for horizontal scaling of MySQL)

Vitess is an open source software that works like a proxy and helps to distribute MySQL workload across multiple servers (for more details about Vitess architecture I will refer to the Sugu Sougoumarane presentation: 2019-sugu-highload.pdf (vitess.io) and to our introduction: Introduction to Vitess on Kubernetes for MySQL – Part I of III – Percona Database Performance Blog).

Originally, Vitess was developed to scale YouTube, although I’ve heard recent rumors that Google migrates YouTube to a different backend. Later, it was adopted not only by Square Cash but also by companies like Slack, Pinterest, GitHub, and HubSpot. They all are looking to solve MySQL scalability problems.

I wrote this blog post to collect your opinions:

• Did you try and have an experience with scaling-out solutions, and can you share it?
• Would you be interested in hearing more about scale-out from Percona, like basic usage, setup, and general guidance?
• Would you be interested in having support for products for scaling-out from Percona?

Please share your thoughts with us in this survey.

October is close, and it is time for Hacktoberfest! If you love open source as much as we do, this event is created for you. It is a unique opportunity to be a part of a great open source community. And you get not only a memorable experience but also some fantastic prizes! You can even have a tree planted in your name, isn’t that amazing?

The aim of Hacktoberfest is to provide support to open source projects and teams maintaining them. Your contribution can be a helping hand to them. And it is a unique opportunity for you to sharpen your development skills and give back to the project you love.

This year Percona participates in this fabulous event with its open source software. You can contribute to 12 repositories with Percona Monitoring and Management (PMM), Percona Kubernetes Operators, pg_stat_monitor, and other projects! In addition to official Hacktoberfest prizes, you can receive a piece of Percona swag: a T-shirt or a hat.

Follow these simple steps to start:

1. Register on the Hacktoberfest website anytime between September 26 and October 31, 2022.
2. Look through the participating projects and choose your favorites.
3. Contribute to their code with PR/MRs during the period from October 1 to October 31. You should have at least four pull/merge requests accepted that meet the Hacktoberfest requirements.
4. Get your prizes!

Follow the link to find all Percona GitHub repositories participating in the event. You can easily find them by searching the tag “hacktoberfest”.

We also encourage you to look through some issues that may not be that hard to complete. They may give you an idea of where to start.

You can find them:

• On GitHub, tagged with the “good-first-issue” tag.
• On Jira, tagged with “newbie”, “hacktoberfest”, and “onboarding”:
  • Percona Monitoring and Management (PMM)
  • Percona Kubernetes Operators

Experienced contributors check all Jira issues to find a challenging task.

Did you know that not only code contributions are welcome? Contributing to open source projects is not only for those who code. This time Hacktoberfest is making a point to boost low-code or non-code contributions. Designers, testers, content creators, translators… Even if you have little technical experience or none at all, you can have your seat at the table and use this opportunity to bump up the open source with your professional skills.

If you made a non-code contribution (for example, wrote an article or recorded a demo), create a Pull Request to our Percona Community repository with links to your work to participate in Hackroberfest. Add your contribution to our list of blog posts and videos, and follow the process of submitting changes to documentation. Other types of contributions (custom dashboards, configuration files, etc.) can be added to our developer artifacts collection.

Also, Hacktoberfest prepared some useful resources for those taking the first steps in the open source world.

The Percona team will review your PRs constantly and give you feedback. We would love to advise you and provide you with all the support that you might need. Ask any questions in the Discord chat channel and may the open source power be with you!

MongoDB is a cross-platform, document-oriented NoSQL database. It has been developed as an answer to the growing need for easy-to-use yet very performant, scalable, and content-agnostic storage. MongoDB has been widely adopted by engineers across application development anywhere from banking to social media. Unfortunately, after MongoDB Inc.’s IPO in 2017, they chose an aggressive path of monetization, changing the license to SSPL (which is a license model that’s bad for you) and promoting Atlas (MongoDB’s Database as a Service (DBaaS) solution) even over the costly MongoDB Enterprise. The company put the community’s user needs way behind catering to high-end enterprise customers, leaving the MongoDB community stranded.

While limited by the SSPL license (not recognized by the Open Standards Initiative (OSI) as open source), Percona, known for its deep commitment to open source software, chose to come with support to the stranded MongoDB community by:

• Providing Percona Server for MongoDB (PSMDB) – a source-available MongoDB drop-in replacement database based on MongoDB Community Edition (CE) yet adding enterprise features developed on top of that by Percona.
  • Let’s also remember that while MongoDB Inc. chose to move the MongoDB CE to SSPL effective immediately, not respectful of the compliance and approval processes their users had to face, Percona, unlike MongoDB Inc, chose to still provide patches to the AGPL-based versions that remained open source.
• Delivering a freely available open source product for MongoDB backup and recovery: Percona Backup for MongoDB (PBM) works with PSMDB as well as (to some extent) with MongoDB Inc.’s MongoDB Community and Enterprise editions.
• Packaging PSMDB and PBM in Percona Distribution for MongoDB: an easy-to-deploy complete solution for MongoDB.
• Providing Percona Monitoring and Management (PMM), which can be used as an open source, multi-database alternative to MongoDB Ops Manager.
• Developing a MongoDB Prometheus exporter, free to use for anyone (from Grafana, through Ansible to Dynatrace) needing insights into how their MongoDB instance is doing that’s widely used in the industry both by the open source communities and enterprise APM tools.
• Delivering a Percona Operator for MongoDB, a complete solution for containerized environments of your Percona Server for MongoDB, containing the necessary Kubernetes settings to maintain consistent PSMDB instance (if you’re unsure whether running MongoDB in Kubernetes is for you, check out pros and cons of solutions for that).

I think it’s fair to say that the list is impressive. Individuals, organizations, and even enterprises benefit from the fact that the software Percona provides is free and open.

A bittersweet edition

Seeing all the critical bugs that MongoDB 5.0 has introduced, it feels as if its release has been rushed, allowing for half-baked features to go GA. Looking at numerous critical problems that could result in data corruption, you could still argue that it’s a natural state of things in IT development, to quote Albert Einstein:

a person who never made a mistake never tried anything new.

True point, but following the story by The Register, is not an argument I’d use here. The “accelerated release cadence” introduced by MongoDB Inc. assumes that major improvements are released in “rapid releases” available only for Atlas (DBaaS) customers. Neither MongoDB Community nor even MongoDB Enterprise customers will get a taste of those improvements in 5.0.x, even though they have a chance to taste all the instabilities, limitations, and bugs available with 5.0.

Of course, MongoDB Inc will argue that the Rapid Releases are for the bleeding edge adopters, that they include new features, whereas all the issues are fixed in the bugfix, patchset releases. From my experience, not only bug fixes solve user issues. Think of the release cycle and the situations where, due to the deadlines, some features are released in the major version in a limited scope. It does sound all too familiar, right? Now that’s not that bad, as (with semantic versioning) minor versions will fill in the missing capabilities, lift the limitations, and make the often go-to-market spotlight features of the major version complete. Not in this case, at least not if you are the “second class citizen user” of the Community or Enterprise edition. Rapid Releases are what semantic versioning calls minor releases. Meaning you have to live with the limitations and lacking features till the next major release, for now having to satisfy with the bug-fix holding patches only.

Consider that MongoDB 5.0 introduced very appealing capabilities like time-series collections or resharding that allows for an automatic changing of a shard-key for a collection. Choosing a good shard-key during the application design when the initial sharding takes place is often challenging. Having a poorly designed sharding in your database means everything to MongoDB’s performance. For now, to change it, a manual and cumbersome process has been needed. Even taking into account the downfalls of the introduced resharding like performance and storage overhead during the process, it is still a very tempting feature that for many situations could be a game changer. Unfortunately, with a lack of trust in MongoDB 5.0 and the new release cadence not having the community’s back, the community often simply cannot benefit from it.

Percona has waited a long time for the release of 5.0 to feel stable enough to release. It was not until MongoDB 5.0.6 CE was released almost half a year after 5.0.0 that Percona decided it was safe enough for our users and customers. This sort of third-party overwatch is an invaluable asset that open source brings. With companies like Percona standing behind a software release, you get the added benefit of extra verification of your software “for free”.

End-of-life strategy

Looking at the previous chapter, the adoption of releases of 5.0 being not as impressive as one could expect is not that surprising. As this blog post is being written, the telemetry data gathered by Percona shows:

• MongoDB 4.4 = 47%
• MongoDB 4.2 = 17%
• MongoDB 5.0 = 15%
• MongoDB 4.0 = 13%

With the end-of-life calendar MongoDB 4.x looking as follows:

• 4.0 EOL April 2022
• 4.2 EOL April 2023
• 4.4 EOL April 2024

Add in the apparent lack of trust in 5.0, we see a growing trend for the adoption of MongoDB 4.4 that gives some “breathing space” until the EOL.

That’s a fair strategy that makes sense, but limits the value you are getting. What if there was another way that could allow you to get some more benefits?

Here comes Percona Server for MongoDB 6.0

With the introduction of MongoDB 6.0, users got long-awaited improvements and usability fixes to MongoDB 5.0 that only the Atlas customers could taste before. After the EOL of major versions that required users to upgrade, 6.0 could become their landing zone. This way users can benefit from more advanced features of the new version as well as a later EOL.

A quick look at the features that made it to MongoDB 6.0 shows a range of interesting ones, like:

• Cluster to cluster sync
• Queryable encryption
• Time-series collections improvements
• Analytics improvements
• Change streams improvements
• New aggregation operators
• Improved search

Obviously, not all of these will make it to MongoDB Community Edition, since some are reserved for MongoDB Enterprise or even Atlas only.

Even without the features not available in the Community Edition, the 6.0 release providing fixes over the unstable 5.0 is a large improvement that’s worth considering in your long-time update strategy.

While updating your Community Edition, it’s worth considering migrating from MongoDB CE to Percona Server for MongoDB. This way you have all the benefits of MongoDB CE 6.0 plus the advantages that Percona brings to the release cycle for the community. With the upcoming release of Percona Server for MongoDB 6.0, as well as the freshly released Percona Backup for MongoDB 2.0 and the support of PBM in Percona Monitoring and Management, the solution becomes complete. With features like an in-memory engine, extensive data at rest encryption, hot backups, LDAP, and Kerberos integration on top of what MongoDB Community Edition already provides, PDMDB provides a complete solution that Percona is committed to keeping open. Be on the lookout for the announcement of PSMDB 6.0 very soon!

What now?

We see over the years that companies change their licenses, becoming less open source while claiming to be more open in an obvious marketing play. At its core, Percona chooses to stay true to the open source philosophy.

Over the years, Percona experts have meticulously delivered increments of Percona Server for MongoDB based on the same upstream codebase as the MongoDB Community Edition. As a drop-in replacement for MongoDB CE, it’s the enterprise features like these that PSMDB adds on top of it that make it so interesting:

• in-memory storage engine,
• KMIP support,
• HashiCorp Vault integration,
• data-at-rest encryption,
• audit logging,
• external LDAP authentication with SASL,
• hot backups.

These enterprise-grade feature enhancements were added to Percona Server for MongoDB. This way the open source community could benefit from features previously reserved only for MongoDB Enterprise customers. With PSMDB 6.0, things are not going to change. Percona is on a mission to provide open database solutions to everyone and everywhere. With this in mind, we are open to your suggestions as to which features are the most important to you, our users. Reach out and let us know!

Learn more about Percona Server for MongoDB

We are looking to provide simplified ways to deploy Percona software in cloud environments, especially for more advanced scenarios like replication and multi-node cluster (in the case of Percona XtraDB Cluster).

For this I propose trying out our new Terraform provider, with the capabilities:

• Deploy in AWS or GCP Cloud. The provider will automatically create instances and deploy Percona Server for MySQL
• Deploy either Percona Server for MySQL or Percona XtraDB Cluster
• Choose instance size
• Choose storage volume size, type, and IOPs
• Multi-node async replication or multi-node Percona XtraDB Cluster
• Customize MySQL configuration file
• Capability to deploy MyRocks Engine

To get more understanding let’s review some examples, but before that, where you can obtain modules:

The provider is available from Terraform registry:

https://registry.terraform.io/providers/Percona-Lab/percona/0.9.0

And source code at GitHub:

Percona-Lab/terraform-provider-percona: Terraform modules to deploy Percona Server and Percona XtraDB Cluster (github.com)

Keep in mind that this is an EXPERIMENTAL software yet and is not covered by Percona Support.

Examples:

As a single server is quite trivial, let’s jump to more complex scenarios: Asynchronous replication between two nodes.

Here is our main.tf file:

# AWS provider configuration
provider "percona" {
  region  = "us-east-2"
  profile = "default"
  cloud   = "aws"
}
 
 
resource "percona_ps" "psrepl2" {
  instance_type            = "t3.micro" # for AWS
  key_pair_name            = "sshKey2"
  password                 = "password"
  replica_password         = "replicaPassword"
  cluster_size             = 2
  volume_size              = 30
}

And we apply with:

terraform apply
…
percona_ps.psrepl2: Still creating... [4m20s elapsed]
percona_ps.psrepl2: Still creating... [4m30s elapsed]
percona_ps.psrepl2: Still creating... [4m40s elapsed]
percona_ps.psrepl2: Creation complete after 4m47s [id=JZnEGllTfJIOqjgMyyRl]

So five minutes later we have the following:

• We created and initialized two t3.micro AWS instances in us-east-2 region
• Each instance uses 30GB volume sizes
• We can log in to instances with an ssh key (which will be created if it didn’t exist before)
• Each instance will have installed Percona Server for MySQL with the latest 8.0 version
• Instances will be connected by SOURCE->Replica MySQL replication

Similarly, for deploying a 3 node Percona XtraDB Cluster, we use:

# AWS provider configuration
provider "percona" {
  region  = "us-east-2"
  profile = "default"
  cloud   = "aws"
}
 
 
resource "percona_pxc" "pxc3" {
  instance_type            = "t3.micro" # for AWS
  key_pair_name            = "sshKey2"
  password                 = "password"
  cluster_size             = 3
  volume_size              = 30
}

Now we also can show an example for Google Cloud:

provider "percona" {
  region  = "us-central1"
  zone    = "us-central1-a"
  project = "cto-lab-284219"
  cloud =   "gcp"
}
 
 
resource "percona_ps" "ps3" {
  instance_type            = "e2-highmem-2" 
  key_pair_name            = "sshKey2"
  password                 = "password"
  cluster_size             = 3
  volume_type              = "pd-ssd"
  volume_size              = 40
}

In this case, we will use more powerful instances with dedicated SSD volumes of 40GB each.

The script will deploy instances and install three Percona Server for MySQL servers connected in replication (one source and two replicas).

In conclusion:

We offer a Terraform provider that simplifies the deployment of Percona Server for MySQL and Percona XtraDB Cluster in cloud environments and offers various customizations for instances and replication configurations.

Try out our new Terraform provider