Percona Open Source Database Conference 2019

This year, the most important open source database conference in Europe has been in Amsterdam, lovely city and people, and perfect place elected by Percona.

As usual, Percona Conference has split into Tutorial day and conferences days. Advanced Programming Solutions only went to conferences days.

Overview

As a simple summary, the database world is evolving, and this year the key notes did reference to expose the growth of clustering solutions, as merged between on-premise and [public|private] cloud. 

The well known concept hybrid cloud where the on-premise part has effort to ensure the high availability via new tools or “old” with new features. The public/private cloud, as AWS, CGP, Azure, Oracle Cloud or DigitalOcean follow the same way, more enhancements to these auto-managed services, whether MySQL, PostgreSQL or commercials Databases Management Systems (DBMS).

Let’s take a closer look at news of MySQL and PostgreSQL.

MySQL milestones

The Dolphin has an excellent state of form. With all new features and improvements applied in the latest version has improved a lot. Remember to use or migrate to MySQL 8.

While last year the concept of semi-structured databases was highlight (having the key in JSON and its environment), this year it has been focused effort in improvements of performance. The presentation offered by Dimitri presents the results of MySQL 8 is better in different situations than MySQL 5.x, and in many cases in concerning with MariaDB. 

The question we ask ourselves is: Is really MySQL 8 really better? Yes, one of the keys for it's, in comparison to older versions, it has a new design for InnoDB fundamentals and redesigned redo log. In addition, that it includes resource groups, you can assign more or less resources (nice) to a group. Other new features in order to help the “typical” DBA tasks, such as cloning databases for previous environments, more detailed information in utilities like as “explain analyze” from others DBMS. Everything is very interesting and necessary.

Amazed? If you don't believe it, please test it on your staging environment.

There are other features pending to improve, for example the read only blocks locks and lookups via Sec IDX, read write transaction management contentions, locks management, isolation gaps or large amount of data large.

As a concern, the performance impact when InnoDB undo truncate the log, it’s strongly recommended disable it and set manual running.

For high availability, Slack team presented their solution using Vitess. Vitess is a database solution for deploying, scaling and managing large clusters of MySQL instances. It’s designed to run as effectively in a public or private cloud architecture as it does on dedicated hardware. It combines and extends many important MySQL features with the scalability of a NoSQL database.

In order to manage hybrids clouds, HashiCorp have many tools as a solution to a different layers. Terraform as an answer for Infrastructure As a Code (IAC), Vault to save your secrets, Consul as a network features layer and Nomad as orchestrator. The most interesting feature is the column or row encryption using Vault, great presentation from Pythian colleagues,

To end this section, say to be careful when you're upgrading your MySQL to 8 version, new DML & DDL statements, changes and improvements on the indexing, new JSON features and functions. 

Feel free and test your MySQL upgrade on previous environments, never on your production environment.

PostgreSQL works

This is the second edition of Percona in which PostgreSQL is discussed, because it is the last open DBMS supported by them. Percona team explained that packages mesh their distribution pack, these are PostgreSQL 11, pgAudit, Patroni, pgBackRest and pg_repack. You want more information about them? Ask us.

The internal database, pg_catalog offers a lot of information about the PostgreSQL status. It hideaway some interesting views to expose very significance details about the different DBMS condition keys. For example, you can determine the size of any database, connections problems, checks hit/read ratio, check hit/read ratio during an interval, check temporary files, look for conflicts, search the amount of index scan, check replication statistics, checks receiver’s statistics, monitor the replication slots, workarounds of typical queries, etc.

One of the frequently asked questions in the PostgreSQL community is how to provide the high availability. For second year in a row, PostgreSQL 3rd parties have different solutions, the most popular as Patroni, repmgr, pg_auto_failover, pgPool-II, PostgreSQL Automatic Failover (PAF) Stolon or pglookout. Patroni is gaining a lot of strength.

The most important feature introduced in the latest versions is the partitions and this management. Partitioning was introduced natively in version 10, before these version its management was does with a 3rd party package. Version 11 and 12 have greatly improved their features and management. Today, if you have use partitioning, you should upgrade to 11 or 12 version, while it’s possible.

Finally, currently a topic is to use Kubernetes, and the question is: Can I install and run PostgreSQL in Kubernetes? Yes, but you need to know the pros and cons.

Conclusions

Great conference and great people. Thanks to APSL to let me go, and kind regards to Percona for leading this --necessary-- event every year.

See you next year 😉