The Vanishing Middle Class: Prejudice and Power in a Dual Economy by Peter Temin analyzes the economical and political structure of the USA. Temin does this through the lens of the “Dual Economy ” Model.

Dual Economies are normally prevalent in colonial societies of “less developed” countries. These countries usually have local production and production for a global export market, and besides economical and income structures around this separation, there are also social segration and a certain degree of isolation of the social groups from each other - in politics as well as attempts to prevent social migration into the higher ranked group.

In ALTER TABLE for UUID we discuss currently proper way to store and handle UUID in MySQL. Currently it works, even in a performant way, but it still hurts. It should not.

Definition of UUID

The RFC 4122 defines various types of UUID, and how they are being formatted for presentation and as a bit field on the wire. As this document was written bei Leach and Salz, among others, RFC 4122 UUIDs are also called “Leach-Salz UUIDs” (for example in the Java Documentation ).

(based on a find by Ruud van Tol, and several Twitter contributions)

Ruud commented on our DST discussion with

mysql> SELECT 
'2019-02-28 12:34:56'+ INTERVAL 1 YEAR + INTERVAL 1 DAY as a, 
'2019-02-28 12:34:56'+ INTERVAL 1 DAY + INTERVAL 1 YEAR  as b\G
a: 2020-02-29 12:34:56
b: 2020-03-01 12:34:56

2019 is a year before a leap year. Adding (left to right) a year brings us to 2020-02-28, and then adding a day makes this 2020-02-29, because it’s a leap year.

(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 year_month) as delta\G
delta: 3600

It has to be exactly 730 days (2 * 365 days, 2 years):

Where I work, we are using MySQL a lot. The databases are being organized in replication hierarchies, and each hierarchy is a tree topology with a single primary and a number of intermediate replicas.

Replication is a tree managed by Orchestrator

We are using MySQL orchestrator to manage the replication topology.

MySQL Orchestrator shows a typical replication hierarchy. Each color indicates a different data center/availability zone. Replication is a tree, from the primary to per-AZ intermediate replicas for fan-out to leaf replicas. Clients connect to the primary for writing, and to the leaf replicas for reading. Special instances exist as clone sources and backup instances.

»If at all possible, we build databases so that the working set of the database fits into memory.« What does that even mean?

Working Set

In computer science, the “Working Set” of a program is the set of things it will be accessing in the near future. Because computer science has not yet solved looking into the future, we are looking at the set of things we accessed most recently and hope for The Best™.

Das Kind möchte ein Programm zum Üben von Rechenaufgaben sehen. Nun gut. Hier ist eine Version in PyQt5.

Unsere Oberfläche soll so aussehen.

Wir wollen ein kleines Fenster, in dem eine billig generierte Rechenaufgabe angezeigt wird. Der Schüler soll die Antwort eingeben und den Knopf “Antworten” drücken. Danach wird angesagt, ob die Antwort korrekt war, oder ob sie falsch war. Wenn sie falsch war, wird auch die korrekte Antwort angezeigt.

When you insert data into a database and run COMMIT you expect things to be there: Atomically, Consistent, Isolated and Durable , like Codd commanded us 40 years ago, but also quickly. There is a surprising amount of sophistication being poured into this, but since I do not want to shame MongoDB and Redis developers in this post, I am not going to talk about that much in this place.

We are instead trying to understand what our databases are doing all day, from the point of view of the storage stack.

Where I work, we try to run databases in a memory saturated way. That is, we try to provide so much memory that the working set of the database is memory resident, or in other words, the number of disk reads after an initial warmup is no longer dependent on the database load.

Workload Intelligence Analytics showing “IOPS over time” for a mixed read/write benchmark on Datera iSCSI.

The DBA experience at work suggests that every single schema at some point in its lifecycle holds a queue table. These are tables in which some processes (the “producers”) put rows, which a swarm of other processes (the “consumers”) lock and consume.

A variation on that theme is the state machine, in which jobs are placed by producers. Consumers do not immediately delete them, but update them a few times to indicate processing progress, before the rows are ultimately being deleted.