MySQL, an open source database developed by Oracle, powers some of Facebook’s most important workloads. We actively develop new features in MySQL to support our evolving requirements. These features change many different areas of MySQL, including client connectors, storage engine, optimizer, and replication. Each new major version of MySQL requires significant time and effort to migrate our workloads. The challenges  include:

• Porting our custom features to the new version
• Ensuring replication is compatible between the major versions
• Minimizing changes needed for existing application queries
• Fixing performance regressions that prevent the server from supporting our workloads

Our last major version upgrade, to MySQL 5.6, took more than a year to roll out. When version 5.7 was released, we were still in the …

Somebody needed a job queue in Python: Multiple writers insert into it in random order, and the jobs are written into the MySQL table jobs. From the jobs table, multiple consumers claim jobs in batches of n or smaller (n=100), and process them. After processing, the consumers delete the jobs. We need concurrent job generation and consumption, with proper and efficient locking.

The full source for this example can be seen in mysql-dev-examples

in mysql-claim-jobs.py

.

Base Program

Using our usual includes and setup,

from time import sleep
from random import …

Somebody needed a job queue in Python: Multiple writers insert into it in random order, and the jobs are written into the MySQL table jobs. From the jobs table, multiple consumers claim jobs in batches of n or smaller (n=100), and process them. After processing, the consumers delete the jobs. We need concurrent job generation and consumption, with proper and efficient locking.

The full source for this example can be seen in mysql-dev-examples in mysql-claim-jobs.py.

Base Program

Using our usual includes and setup,

from time import sleep
from random import randint
from sys import exit
from multiprocessing import Process

import click
import MySQLdb
import MySQLdb.cursors …

As some of you likely know, I have a favorable view of ZFS and especially of MySQL on ZFS. As I published a few years ago, the argument for ZFS was less about performance than its useful features like data compression and snapshots. At the time, ZFS was significantly slower than xfs and ext4 except when the L2ARC was used.

Since then, however, ZFS on Linux has progressed a lot and I also learned how to better tune it. Also, I found out the sysbench benchmark I used at the time was not a fair choice since the dataset it generates compresses much less than a realistic one. For all these reasons, I believe that it is time to revisit the performance aspect of MySQL on ZFS.

ZFS Evolution

In 2018, I reported ZFS performance results based on version 0.6.5.6, the default version available in Ubuntu Xenial. The present post is using …

MySQL 8 brought many highly anticipated features, with support for user roles, a new shell, a more robust data dictionary, and better SQL support, just to name a few. There are lesser-known new features, however, that aim to reduce overall DBA workload and streamline management processes – and one of these is support for dual passwords, first implemented in MySQL 8.0.14. User accounts are now permitted to have dual passwords, with a designated primary and secondary. This makes it possible to seamlessly perform user credential changes even with a large number of servers, or with multiple applications connecting to different MySQL servers.

Historically, a MySQL credential change had to be timed so that when the password change was made and propagated throughout the database nodes, all applications that use that account for connections had to be updated at the same time. This is problematic for many reasons, but with database and application …

My students liked the MySQL Transaction post but wanted one that showed how an external web application would interact with MySQL in the scope of a transaction. So, I put a little PHP function together that write across two related tables in the context of a transaction. It uses mysqli (MySQL Improved Extension) to connect PHP to the MySQL database.

The function is barebones and uses the oldest approach of hidden inputs to maintain context between rendered forms using an HTML POST method. The hidden inputs are preceded with “h_” and snake case is used for variable names.

The function only writes to two tables. It writes to the member table and when that completes successfully to the contact table. The function:

• Submits credentials from a file and raises an error when they don’t work.
• Initializes a …

Here’s a cheat sheet to run MySQL in a Docker container in your Windows or Mac laptop in a few minutes. In this brief how-to you will:

1. Install Docker Desktop in your laptop
2. Download official Oracle MySQL image or Docker Inc. image
3. Start the container
4. Administer it
5. Start a MySQL session and start a Linux session

Download and install Docker

You can download Docker Desktop for Windows or Mac from https://docs.docker.com/desktop/. Install like any other application and you’re ready to move to the next step. As you can read from the docs, Docker Desktop will install several things like Docker Compose or Kubernetes. We’ll use both in advanced examples in future posts.

Pull MySQL Server image

Now you can pull the MySQL Server image. You have two options. You can download the …

Just a quick how-to, to download, compile and use Sysbench on your RHEL/OL Linux box to test MySQL Server (note: compiling and using Sysbench is no mistery, the README file has all the instructions clearly explained, but I write this little guide to setup and use this tool in minutes).

Download and compile

The repository is hosted at: https://github.com/akopytov/sysbench. You can choose to download the latest master branch as usual or the latest stable. I have picked the latest stable. In order to compile and use certain MySQL library version, make sure mysql_config (used by sysbench auto configure to determine what libraries must be used) and a whole MySQL distribution is installed and/ or in the PATH.

If you have MySQL headers and libraries in non-standard locations (and no …

If you haven’t already upgraded to MySQL 5.6, NOW IS THE TIME!!

Starting, August 3rd 2021, RDS will automatically upgrade MySQL 5.6 instances to version 5.7 within the earliest scheduled maintenance window that follows.
Starting, September 1st 2021, RDS will any remaining MySQL 5.6 instances to version 5.7 whether or not they are in a maintenance window.

AWS RDS

If you’re thinking to yourself, “It’s an automatic update, so why should I care?” the answer is no.

Whilst, the upgrade seems to be automatic but sometimes the aftermath is catastrophic. Listed below are the significant impact which few organization face after the automatic upgrade:

Data loss
Application getting choked
Data inconsistency flow
Performance degradation
Insert failure
& so …

When organizing things helps to simplify life.

In the previous article, we start to explore dynamic privileges and the interaction with static ones. We also saw how to remove SUPER privilege from a DBA account. 

What we did was go by subtraction. But in real life, we should act differently. We should ADD only what is really needed for the account to work correctly.

Adding privilege one by one, and for each user is problematic given the level of interaction they may have, and also prone to mistakes. 

Instead, we can use ROLES to group, assign, and revoke the correct privileges in a much easier way.

This is becoming even more important in MySQL with the advent of dynamic privileges.

What should we do to correctly use ROLES? Well first of all design.   …