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 …

    If you have not read MDS, PHP and authentication by my brilliant colleague LeFred please do so. The TL;DR is that most recent versions of the PHP support the MySQL 8.0 standard for authentication, Caching SHA 256, but with PHP 7.3 and earlier you will have to use the older MySQL Native Password authentication.

    Someone reached out to me with a direct message to ask me if they really needed to change from MySQL native to Caching SHA 256.  

    Well, what versions of MySQL are you using?  The good news is that they are running 8.0.23 in production, '25 in test and development. But all the accounts are using MySQL Native Authentication.

    What versions of PHP?  There was some …

If like me you are using MySQL Shell on many different systems and new installations, there are some commands I often use to benefit from my environment.

What I really want to enable is the history of course, a nicer prompt and sometimes the default mode I want to use.

This blog post is just a little reminder with those commands I usually repeat in all my systems:

mysql-js> shell.options.setPersist('history.autoSave', 1) mysql-js> shell.options.setPersist('history.maxSize', 5000)

The option above is when using MySQL Shell in JS mode which is the default. To play with options using the same command in every mode (JS, Python, SQL), you can use /option like:

\option --persist history.autoSave 1

Then I set the prompt to have something like this:

$ cp /usr/share/mysqlsh/prompt/prompt_256pl+aw.json …

If like me you are using MySQL Shell on many different systems and new installations, there are some commands I often use to benefit from my environment. What I really want to enable is the history of course, a nicer prompt and sometimes the default mode I want to use. This blog post is just a littl...

The Problem

State Snapshot Transfer can be a very long and expensive process, depending on the size of your Percona XtraDB Cluster (PXC)/Galera cluster, as well as network and disk bandwidth. There are situations where it is needed though, like after long enough node separation, where the gcache on other members was too small to keep all the needed transactions.

Let’s see how we can avoid SST, yet recover fast and without even the need for doing a full backup from another node.

Below, I will present a simple scenario, where one of the cluster nodes was having a broken network for long enough that it will make …

Problem Statement

Having backups of binary logs is fairly normal these days. The more recent binary logs are copied offsite, the better RPO (Recovery Point Objective) can be achieved. I was asked multiple times recently if something could be done to “stream” the binary logs to S3 as close to real-time as possible. Unfortunately, there is no readily available solution that would do that. Here, I show what can be done and also show a proof of concept implementation, which is not suitable for production use.

In this example, the instance has two binary log files (mysql-bin.000001 and mysql-bin.000002) already closed and mysql-bin.000003 being written. A trivial solution for backing up these binary log files would be to back up just the closed ones (the one that is not written). The default size of the binary log file is 1 GB. This means with this solution we would have a 1 GB binlog not backed up in the worst-case scenario. On …

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 …

Yesterday my friend and ex-colleague Sveta published a blog post on how to migrate ownership of stored routines, views and triggers in MySQL.

I usually agree a lot with her and even if I agree with what she wrote, there is one single point I would like to comment: use MySQL Shell !

In the blog post, Sveta recommends the use of mysqldump which I don’t. For any logical dump & load operations, I really recommend the use of MySQL Shell’s Dump & Load Utility ! It’s much faster, it has many nice options like hidden Primary Key creation, and is compatible with OCI.

And of course, MySQL Shell also provides a …

Yesterday my friend and ex-colleague Sveta published a blog post on how to migrate ownership of stored routines, views and triggers in MySQL. I usually agree a lot with her and even if I agree with what she wrote, there is one single point I would like to comment: use MySQL Shell ! In the blog post,...