(based on a conversation with a colleague, and a bit of Twitter)

A Conundrum

A developer colleague paged me with this:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 2 YEAR) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 2 YEAR) as delta\G
delta: 420

It is obviously wrong, and weirdly so. It only works for “2 year”, not with other values:

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-11 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-11 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-12 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-12 year_month) as delta\G
delta: 3600

mysql> select
UNIX_TIMESTAMP("2021-03-26 03:07:00" + INTERVAL 1-13 year_month) -
UNIX_TIMESTAMP("2021-03-26 02:07:00" + INTERVAL 1-13 …

As database footprints continue to explode, many companies are looking for ways to deal with such rapid growth.  One approach is to refactor traditional relational databases to fit into a NoSQL engine, where horizontal scalability is easier.  However, in many cases, this is in no way a trivial undertaking.

Another approach that has been gaining interest is the use of MyRocks as an alternative storage engine to the traditional InnoDB.  While not for everyone, in certain use cases it could be a potential solution.  As with so many things open source, the next standard questions are: which version should I use?  Any differences with the engine if I use MyRocks with MySQL 5.7 vs 8.0?

In this post, I wanted to touch on this and give some high-level thoughts on MyRocks when it comes to the version of MySQL.

…

Sometimes clients want that the information they collected from the user should be encrypted and stored in a database. Data encryption and…

Continue reading on Thinkdiff »

…

It’s a problem for my students who purchased the new Apple hardware that uses Apple Silicon because they can’t install a Docker MySQL instance. However, there is Homebrew formula that works on macOS Big Sur and the new Apple silicon. It supports:

• Intel Silicon: macOS Big Sur, Catalina, and Mojave
• Apple Silicon: macOS Big Sur

The Homebrew Formula does have conflicts that you may need to avoid. It is a solution for those with the new Apple silicon.

As always, I hope this helps those looking for a solution.

Recovering to a particular time in the past is called Point-In-Time Recovery (PITR). With PITR you can rollback unwanted DELETE without WHERE clause or any other harmful command.

PITR with Percona XtraBackup is pretty straightforward and perfectly described in the user manual. You need to restore the data from the backup, then apply all binary logs created or updated after the backup was taken, but skip harmful event(s).

However, if your data set is large you may want to recover only the affected database or table. This is possible but you need to be smart when filtering events from the binary log. In this post, I will show how to perform such a partial recovery using Percona XtraBackup, …

Nobody likes change, especially when that change may be challenging.  When faced with a technical challenge, I try to remember this comment from Theodore Roosevelt: “Nothing in the world is worth having or worth doing unless it means effort, pain, difficulty.”  While this is a bit of an exaggeration, in this case, the main concept is still valid.  We shouldn’t shy away from an upgrade path because it may be difficult.

MySQL 8.0 is maturing and stabilizing.  There are new features (too many to list here) and performance improvements.  More and more organizations are upgrading to MySQL 8 and running it in production, which expedites the stabilization.  While there is still some significant runway on 5.7 and it is definitely stable (EOL slated for October 2023), organizations need to be preparing to make the jump if they haven’t already. 

What Changed?

So …

Percona Technical Account Managers get the privilege of working with some of our largest enterprise clients day in and day out.  As such, we get to really focus on how to best leverage our technology to generate measurable benefits for our users.  While it is fun to “nerd out” and always strive to use the latest and greatest, we need to stay focused on demonstrating business value and a genuine need.  Over the past few months, I’ve been working with one of my larger clients, Dropbox, along with our professional services team to validate the use of Percona Server for MySQL with the MyRocks storage engine over a large portion of their MySQL infrastructure.

Please note – this is not meant to be a deep dive into the technical details around …

It is no secret that compute and storage costs are the main drivers of cloud bills. Migration of data from the legacy data center to the cloud looks appealing at first as it significantly reduces capital expense (CapEx) and keeps operational expenses (OpEx) under control. But once you see the bill, the lift and shift project does not look that promising anymore. See Percona’s recent open source survey which shows that many organizations saw an unexpected growth around cloud and data.

Storage growth is an organic process for the expanding business: more customers store more data, and more data needs more backups and disaster recovery storage for low RTO.

Today, the Percona Innovation Team, which is part of the Engineering organization, is proud to announce a new feature – High Compression. With this feature enabled, …

Whats new in NDB Cluster 8.0 version (8.0.23)

With new configuration variables introduced in NDB cluster version 8.0.23, user now have more control in table partitioning. Below are the new config variables that can influence the table partitioning scheme:

• PartitionsPerNode
• ClassicFragmentation

PartitionsPerNode:

In earlier cluster versions, the default number of table partitions is based on the number of LDM threads running on a node multiplied by the number of data nodes in the cluster. User can not set any random values to MaxNoOfExecThreads (#LDM) rather the value should be less than or equal to NoOfFragmentLogParts. With cluster version 8.0.23, user can have many no of LDM threads assign to a data node.

The rationale is:

- Having many LDMs allows a data node to make good use of modern hardware.
- Having one …

On March 31st, 2021, MySQL introduced a new MySQL Database Service (MDS) option named MDS High Availability (MDS H/A).

“The High Availability option enables applications to meet higher uptime requirements and zero data loss tolerance. When you select the High Availability option, a MySQL DB System with three instances is provisioned across different availability or fault domains. The data is replicated among the instances using a Paxos-based consensus protocol implemented by the MySQL Group Replication technology. Your application connects to a single endpoint to read and write data to the database. In case of failure, the MySQL Database Service will automatically failover within minutes to a secondary instance without data loss and without requiring to reconfigure the application. See the documentation to learn more about MySQL Database Service High Availability.” From: …