The latest from the MySQL community

The benefits of jemalloc versus glibc memory allocator for use with MySQL have been widely discussed. With jemalloc (along with Transparent Huge Pages disabled) there is less memory fragmentation, and thus more efficient resource management of the server memory. For MySQL 5.6, installing jemalloc is enough to enable it when starting the MySQL process. However, for MySQL 5.7 and 8.0.X series, you will need to take a few extra steps.

Enabling jemalloc on Percona Server for MySQL

Installing the jemalloc package is simple for Percona. This is because the library is available on the Percona repository, which is available for both apt and yum package management:

…

How scheduler and script stand in supporting failover (Percona and Marco example) 

In part one of this series,  I had illustrated how simple scenarios may fail or have problems when using Galera native support inside ProxySQL. In this post, I will repeat the same tests but using the scheduler option and the external script.

The Scheduler

First a brief explanation about the scheduler.

The scheduler inside ProxySQL was created to allow administrators to extend ProxySQL capabilities. The scheduler gives the option to add any kind of script or application and run it at the specified interval of time. The scheduler was also the initial first way we had to deal with Galera/Percona XtraDB Cluster (PXC) node management in case of issues. 

…

How native ProxySQL stands in failover support (both v2.0.15 and v2.1.0)

In recent times I have been designing several solutions focused on High Availability and Disaster Recovery. Some of them using Percona Server for MySQL with group replication, some using Percona XtraDB Cluster (PXC). What many of them had in common was the use of ProxySQL for the connection layer. This is because I consider the use of a layer 7 Proxy preferable, given the possible advantages provided in ReadWrite split and SQL filtering. 

The other positive aspect provided by ProxySQL, at least for Group Replication, is the native support which allows us to have a very quick resolution of possible node failures.

ProxySQL has Galera …

Editor’s Note: Because our bloggers have lots of useful tips, every now and then we update and bring forward a popular post from the past. Today’s post was originally published on November 26, 2019.

It’s not uncommon these days for us to use a high availability stack for MySQL consisting of Orchestrator, Consul and ProxySQL. You can read more details about this stack by reading Matthias Crauwels’ blog post How to Autoscale ProxySQL in the Cloud as well as Ivan Groenwold’s post on MySQL High Availability With ProxySQL, Consul and Orchestrator. The high-level concept is simply that Orchestrator will monitor the state of the MySQL replication topology and report changes to Consul which in turn can update ProxySQL hosts using …

I’m switching to MySQL and leveraging Alan Beaulieu’s Learning SQL as a supporting reference for my Database Design and Development course. While reviewing Alan’s Chapter 5: Querying Multiple Tables, I found his coverage of using self-joins minimal.

In fact, he adds a prequel_film_id column to the film table in the sakila database and then a single row to demonstrate a minimal self-join query. I wanted to show them how to view a series of rows interconnected by a self-join, like the following:

SELECT   f.title AS film
,        fp.title AS prequel
FROM     film f LEFT JOIN film fp
ON       f.prequel_id = fp.film_id
WHERE    f.series_name = 'Harry Potter'
AND      fp.series_name = 'Harry Potter'
ORDER BY f.series_number;

It returns the following result set:

 …

Recently the MySQL Database Service (MDS) was launched in Oracle Cloud Infrastructure (OCI). As Cloud is about automation you don't have to use the Web Console to configure your instances, but can do it via API, for instance with the oci command line tool or your favorite programming language. However often it is nice to define the world in a declarative way ("I want a network, like this, and a MySQL database like that") and let the tool figure out how to align the reality in the cloud with your wish. A tool doing this is Terraform.

With Terraform you can declare the state in description files, the tool creates a dependency graph and then applies what has to be applied and of course it supports OCI and as part of the default OCI Provider, there is even directly …

In this blog post, we are going to look into some key metrics and status when monitoring a Percona Server for MySQL to help us fine-tune the MySQL server configuration for a long run. Just for the heads up, Percona Server has some monitoring metrics that are only available on this build. When comparing on version 8.0.20, the following 51 statuses are only available on Percona Server for MySQL, which are not available in the upstream Oracle's MySQL Community Server:

• Binlog_snapshot_file
• Binlog_snapshot_position
• Binlog_snapshot_gtid_executed
• Com_create_compression_dictionary
• Com_drop_compression_dictionary
…

It’s now more than two months since MDS, the managed MySQL Server solution by Oracle MySQL team, hosted on Oracle Cloud Infrastructure (OCI), became available in several regions on the 1st of September. You can find online several resources online, such as:

• MDS documentation
• Oracle MySQL blog
• MySQL Database Service Release Notes

If you haven’t tried it for free, time to do so with the …

Here at Pythian we get a lot of exposure to new technologies and implementation strategies via the work we do internally and for our clients. The most noteworthy technology stack that I’ve seen get a lot of traction in the MySQL community recently is the high availability stack including Orchestrator, Consul and ProxySQL. 

I won’t dive too deeply into the details of this implementation as there are several blog posts that our team has submitted on this topic, but the key thing I want you to keep in mind for this particular topic is the usage of Consul as a “source of truth” for the state of your MySQL replication clusters. If Orchestrator or its adjacent scripts are running as expected, Consul should always have the latest information pertaining to the state of your cluster. This is incredibly valuable. In fact, …

MySQL Ndb Cluster provides durability for data by default via logging and checkpointing.

In addition, users can take backups at any time, which allows for disaster recovery, replication synchronisation, data portability and other use cases.

This post looks at the backup and restore mechanisms in MySQL Ndb Cluster.

MySQL Ndb Cluster architecture recap

MySQL Ndb Cluster is a distributed SQL relational database :

• Designed for low overhead read + write scale out, high availability, high throughput and low latency.
• Providing distributed parallel joins, transactions, row locks, foreign keys.
• Data is primarily stored and managed by a set of independent data node processes.
• Data is accessed via distributed MySQL servers and …