Want to learn MySQL? Have an hour or so?  Go to Oracle's Learning Explorer and sign up for the FREE MySQL Explorer learning path.  It begins with an overview of MySQL, discusses the client/server model, and then leads you through the use of MySQL.  You do not need previous MySQL or database experience.

MySQL is the most-used opensource database and over the years I have has requests for introductory materials. Well, now I can send them to the MySQL Explorer for some web based training. And you can earn the explorer badge when you pass the end of class quiz.

…

In a previous post, A Horizontal Scalability Mindset for MySQL, I discussed the concerns around growing individual MySQL instances too large and some basic strategies:

• Optimizing/minimizing size with proper data types
• Removing unused/duplicate indexes
• Keeping your Primary Keys small
• Pruning data

Finally, if those methods have been exhausted, I touched on horizontal sharding as the approach to keep individual instances at a reasonable size. When discussing my thoughts across our internal teams, there was lots of feedback that we needed to dive into the sharding topic in more detail. This post aims to give more theory and considerations around sharding along with a lightweight ProxySQL sample implementation.

What is Sharding?

Sharding is a word that is frequently used but …

MySQL, the world’s most popular open source database, is available as a managed cloud service in Oracle Cloud Infrastructure (OCI) under the name of MySQL Database Service (MDS).

This is the ninth episode of “Discovering MySQL Database Service“, a series of tutorials where I will show you, step by step, how to use MySQL Database Service and some other Oracle Cloud Infrastructure services.

In this episode, we’ll learn how to connect to our MySQL Database Service instance using MySQL Workbench – a unified visual tool for database architects, developers, and DBAs that provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more.

The post Discovering MySQL Database …

I ranted about hiring interviews, and the canned questions that people have to answer. One of the interviews we do is a systems design interview, where we want to see how (senior) people use components and patterns to design a system for reliability and scale-out.

A sample question (based on a Twitter thread

in German):

It is 2010, and the company has a database structure where a fixed number front end machines form a cell. Reads and writes are already split: Writes go to the primary of a replication tree, and are being replicated to the read instance of the database in each cell. Reads go to the database instance that is a fixed part of the cell.

Read and write handles are split in the application. Clients write to a primary MySQL database, which then replicates to a database instance that is fixed part of a cell. Clients …

I ranted about hiring interviews, and the canned questions that people have to answer. One of the interviews we do is a systems design interview, where we want to see how (senior) people use components and patterns to design a system for reliability and scale-out.

A sample question (based on a Twitter thread in German):

It is 2010, and the company has a database structure where a fixed number front end machines form a cell. Reads and writes are already split: Writes go to the primary of a replication tree, and are being replicated to the read instance of the database in each cell. Reads go to the database instance that is a fixed part of the cell.

Read and write handles are split in the application. Clients write to a primary MySQL database, which then replicates to a database instance that is fixed part of a cell. Clients from a …

As a Technical Account Manager at Percona, I get to work with many of our largest clients. While the industry verticals vary, one main core challenge generally remains the same – what do I do with all this data? Dealing with massive data sets in MySQL isn’t a new challenge, but the best approach still isn’t trivial. Each application is obviously different, but I wanted to discuss some of the main best practices around dealing with lakes of data.

Keep MySQL Instances Small

First and foremost, the architecture needs to be designed to keep each MySQL instance relatively small. A very common question I get from teams new to working with MySQL is: “So what is the largest instance size MySQL supports?”. My answer goes back to my time in consulting: “It depends”. Can my MySQL instance support a 20TB dataset? Maybe, but it depends on the workload pattern. Should I store 20TB of data in a single MySQL instance? In most cases, …

MySQL introduced the MySQL “Upgrade Checker” initially to support upgrades from MySQL 5.7 to 8.0 series. However, the continuous development initiative in the MySQL 8.0 release series has continued this tools usefulness, as it can also report on continuous delivered features throughout the MySQL 8.0 release series. Additionally, “Yes”, bug fixes are always part of that development release… Read More »

MySQL 8 comes with a lot of good features, and recently I explored its password management systems. I wanted to put together a series of blogs about it, and this is the first part. In this post, I am going to explain the following topics in detail.

• Password Reuse Policy
• Random Password Generation

Password Reuse Policy

MySQL has implemented restrictions on reusing passwords. Restriction can be established in two ways:

• Number of password changes
• Time elapsed

Number of Password Changes

From the MySQL documents:

If an account is restricted on the basis of number of password changes, a new password cannot be chosen from a specified number of the most recent passwords.

To test this, in my local environment I have created the user with “number of password changes = 2”.

mysql> create user …

In some applications, data may never change. Yet, in others, data changes numerous times in its lifecycle. In SQL the UPDATE command changes existing rows of data. CodeIgniter 4 Models have 2 methods available for update operations: update() and save(). Continue reading and learn more about update()…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

CodeIgniter 4 CRUD Series with MySQL

This post is a re-share of an article I originally published on my Medium account and is part 3 in the CodeIgniter 4 CRUD with MySQL series. Be sure and …

Primary Key design is an important thing for InnoDB performance, and choosing a poor PK definition will have an impact on performance and also write propagation in databases. When this comes to Aurora, this impact is even worse than you may notice.

In short, we consider a poor definition of a Primary Key in InnoDB as “anything but quasi sequential values”, which may cause very random access to data and thus increase the IO dependency.

In this post, I’ll try to demonstrate the potential impact of the primary key design when running on Aurora, and how a bad design can lead to a 15x write latency penalty (or more).

The Analysis

Recently I worked on a case where a customer was having issues with scaling writes in Aurora MySQL. While this is a known limitation in Aurora considering how the distributed storage layer syncs out data among all nodes of the cluster, we observed additional latency occurring when more …