Where I work, we are using MySQL in a scale-out configuration to handle our database needs. That means, you write to a primary server, but reads generally go to a replica database further down in a replication tree. A number of additional requirements that should not concern you as a developer make it a little bit more elaborate than a simple “primary and a number of replicas” configuration. But the gist of all that is:

Where I work, there is an ongoing discussion about test data generation. At the moment we do not replace or change any production data for use in the test environments, and we don’t generate test data. That is safe and legal, because production data is tokenized. That is, PII and PCI data is being replaced by placeholder tokens which can be used by applications to access the actual protected data through specially protected access services.

Query latencies in one data center are larger than elsewhere for one replication hierarchy, but only in the high percentiles. This impacts production and traffic is being failed away from that data center to protect production. When the P50 and P90 look okay, but the P99 and the P99.9 do not, the database(s) operate normally, and only some queries are running slow. The initial guess was “for some queries the plan has flipped, but only in that data center.

Two weeks ago I was being drawn into the debug of artdb, the Replication hierarchy used by our Artifactory instance. TL;DR Artifactory overloaded the database. This was incident-handled by optimizing a number of slow queries using some covering index trickery, and by upgrading the hardware substantially. Using the runway we bought, we found and partially fixed the following problems: Fixed: A number of very expensive reporting queries were sped up 16x to 20x using covering indexes, from 180s runtime to 8s-12s runtime.

A database is showing replication delay, and so are all the other instances of the same replication hierarchy, all of which reside in Openstack. Shortly before 21:30 the database begins to lag, until around 23:45, when it starts to catch up, slowly. After 00:30, we gain delay again, plateau and then around 01:45, we catch up. The database is moving deep into replication delay sometimes. It does not do that on bare metal.

OH: “And now let’s quickly push 2 billion rows into this database VM.” That is best done in YOLO mode. This is a mode of operation for a database that minimizes disk writes in favor of batched bulk writes. It is not ACID, so if anything goes wrong during the load, the instance is lost. That is why it is called YOLO mode. You are supposed to do this on a spare replica and not the production primary.

A work problem: A commercial application, Artifactory, where we do not control the source or the schema has performance problems involving a certain long running query. The data size and row counts are not outrageous, and the query itself and the schema are not broken. But the data is very skewed and for certain values the query is very slow, as almost the entire table is selected. We introduce an experimental covering index, and show a 16x improvement, going from 143s to 9s execution time.

The MySQL InnoDB storage engine lives off primary keys: Records are stored physically in a B+-Tree, a balanced tree where the data pages are the leaves of the primary key B-Tree. That implies that the data is stored physically in primary key order – records with similar primary keys are usually physically closer together than records with larger differences in primary key value. In literature, such physically ordered indexes are often called clustered indexes.

Question on the Libera/#mysql IRC channel: Is there a way to split a simple select into multiple returned rows? For example, select 1, 2, 3 to be returned as rows? This is actually asking for a table literal notation. I know of four ways to construct a table literal in MySQL: UNION ALL The oldest way to construct a table literal in any SQL that supports UNION is the UNION ALL construct.

Two good news in one day. First, my energy provider tells me: Levert u meer terug dan u van het net afneemt, dan betalen we u vanaf die datum 16.8 eurocent per kWh. De oude vergoeding was 7 eurocent per kWh. “If you push energy into the grid, we now pay you 16.8 Eurocent per kWh. The old payment was 7.0 Eurocent per kWh.” The other good news is that it is very likely that the existing “Salderingsregeling” may be continued until 2025.

When running Keynote, MacOS prevents the screen blanker from kicking in. I needed a similar thing in one of my Python applications, so I needed to find out how it does that. It turns out, there is an API for that: beginActivity:withOptions: , which returns an Object token activity. Calling endActivity with that Token ends the activity, and resumes the screen blanker countdown. There is a list of activity options, NSActivityOptions .

Der Wikipedia-Artikel zum Thema Energieverbrauch ist wirklich interessant. Schaut man sich zum Beispiel diese Grafik (oder die Tabelle, auf der sie basiert) an, dann sieht man: Primärenergieverbrauch Deutschland 1990 bis jetzt Wie man sieht, ist der Primärenergieverbrauch von Deutschland seit 1990 fast konstant geblieben. Wikipedia schreibt im Detail: “Zwischen 1990 und 2011 nahm das reale Bruttoinlandprodukt um 34 % zu, der Energieverbrauch nahm jedoch um 9 % ab.” Rechnet man um, sind es bummelig 14000 Petajoule, was circa 4000 TWh entspricht.

Another Friday, another replication hierarchy lost. The error from June, July edition? The errors reported look awfully familiar: Binlog position is supposedly 4, and the error message has text about “max_allowed_packet”. Could it be another instance of this bug from early last month? Indeed, one symptom was “a large binlog, larger than max_binlog_size”. We check. -rw-r----- 1 mysql mysql 1073794077 Jul 1 06:39 binlog.000914 -rw-r----- 1 mysql mysql 1073869075 Jul 1 07:09 binlog.

We got our solar roof around 1st of April. I am describing our setup here . Today we got the bill for Summer 2021 to Summer 2022. What I measured I installed a P1 interface on my electricity counter late in April, so my data does not cover all of the lifetime of the Solar Roof. Starting the graph at 01-Apr-2022, I get this: At this resolution, not very helpful.

I am on a Mac or on a Windows machine, and always I have to develop for a Linux target. On Windows, this is easy, because most Jetbrains environment already support working inside WSL2, so simply choosing this is a no-brainer. On MacOS, I can create a “Docker Container” do develop in, but it is kind of a joke. That is, because MacOS cannot run Linux cgroups and namespaces natively, so you actually get a lightweight VM with Linux running, and then a container inside that.

MySQL 8.0.29 adds ALGORITHM=INSTANT as a way to run ALTER TABLE commands with less wait. The documentation can be found in Online DDL Operators and instant column operations can be found here . Example Syntax looks like this: mysql> use kris; mysql> create table t (id serial, d varchar(20)); Query OK, 0 rows affected (0.12 sec) mysql> alter table t add column i integer not null, algorithm=instant; Query OK, 0 rows affected (0.

After getting a solar roof we have been looking at electric mobility solutions. But existing electrical cars seemed pretty much oversized. We do not really need a 4-seater with 650km range and two tons of total empty weight. Katja Diehl coined the german tongue-in-cheek term “Maximaleventualbedarfsauto” for it, the car that meets biggest use-case you could possibly reasonably have. Looking around, we test drove a Carver.Earth , which was the right size, but limited to 45 km/h (and bike pathes in some areas).

I have been asked what kind of bike I ride and how to choose one. Gazelle Grenoble 2017, with vaude Aquaback plus. I am over 50 years old, over 200cm tall and weigh more than 120 kilo before I dresss. Back when we still had an office, that was a 22km trip, each direction. I need an upright sitting position, a soft and comfortable seat. I need to be able to carry luggage, and a return-home guarantee.

(Twitter thread , reproduction in english, and update) On Friday, 2022-06-03, 14:42, we lost a replication hierarchy, on the primary, all replicas down. At 16:30 the escalation hits my desk, because this one is special. Replication stops, and wants more max_allowed_packet There is a sequence of binlogs, each 1 GB in size, as configured, except for the broken one, which is 3 GB. Replication stops with Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event '' at 4, the last event read from '.

Where I work, we manage databases in an automated way . Not as automated as I wish it to be, but largely without touching boxes. We have been doing so for a long time. Over ten years ago, I set the team the challenge “be on an arbitrary version of MySQL within 20 workdays (one calendar month), no matter how many servers we have”. We are there now, in a way: we are on a 30-day refresh cycle for our bare metal cloud, and we match that cycle for our virtualized fleet.