Es beginnt mit einem Tweet von Manuel Atug :

“Wegen Überlastung der Server: #Notruf-App vorerst nicht mehr in App-Stores”

und der Tweet verlinkte einen (inzwischen nicht mehr existierenden) Artikel beim Deutschlandfunk. Christoph Petrausch erklärt, wie solche Projekte falsch laufen können:

Sowas erfordert eine Fehlerkultur. Du musst als Organisation in der Lage sein, nach so einem Incident einen Schritt zurückzutreten. Alle Fakten auf den Tisch zu legen und zu fragen: Warum kam es dazu? Wo liegen die Probleme? Und daraus musst du dann für das nächste Mal lernen.

Golem titelt Intel will Xeon-Funktionen als Lizenz-Update verkaufen :

Intel will Xeon-Funktionen als Lizenz-Update verkaufen.

Mit dem Software Defined Silicon will Intel in Xeon-Hardware zunächst abgeschaltete Funktionen künftig als Lizenz-Upgrade bereitstellen.

Manuel Atug ranted darüber auf Twitter:

Wenn dir die eigene Hardware nicht mehr gehört… Intel will Xeon-Funktionen als Lizenz-Update verkaufen “Mit dem Software Defined Silicon will Intel in Xeon-Hardware zunächst abgeschaltete Funktionen künftig als Lizenz-Upgrade bereitstellen.”

Ich antwortete :

Das braucht wesentlich mehr Kontext.

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.

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

My task is to collect performance data about a single query, using PERFORMANCE_SCHEMA (P_S for short) in MySQL, to ship it elsewhere for integration with other data.

In a grander scheme of things, I will need to define what performance data from a query I am actually interested in. I will also need to find a way to attribute the query (as seen on the server) to a point in the codebase of the client, which is not always easy when an ORM or other SQL generator is being used. And finally I will need to find a way to view the query execution in the context of the client code execution, because the data access is only a part of the system performance.

Like I said, I never had much reason to use table compression, and only recently looked into the topic. MySQL Page Compression looks a lot easier at the database end of things, but relies on hole punching support in the file system. Let’s have a look at what that means.

Files, Inodes and Arrays of Blocks

The original Unix filesystem saw the disk as a sea of blocks, which were represented in a free map as an array of bits. Files have numbers, which are an index into an array of so-called inode structures. Inodes store the files metadata and contain an array of block numbers, which make up the actual file. The array is folded multiple times, to optimize for the more common case of small files: The first few block numbers were stored in the inode, followed by a pointer to a block containing file block numbers, then a pointer to a block containing pointers to blocks of file block numbers and so on.

For the MySQL Million Challenge, I was going through the server syntax in order to understand what things can be created in the server. And now my OCD triggered. DDL is a mess.

Creation

As a database developer, I want to be able to create server objects using the CREATE thing syntax.

The server gives you that for the following things:

• DATABASE
• EVENT
• FUNCTION (and FUNCTION SONAME)
• INDEX
• LOGFILE GROUP (NDB only, not going to look at this)
• PROCEDURE
• RESOURCE GROUP
• ROLE
• SERVER
• SPATIAL REFERENCE SYSTEM
• TABLE
• TABLESPACE
• TRIGGER
• USER
• VIEW

Safe creation

As a database developer I want to be able to script things safely, so I need IF NOT EXISTS clauses in my CREATE syntax.

A long-standing idea that I have is to test the servers limits: How does it fail and break if there are very many of a thing? Previously that was too easy, because many structures were constructed in a way that it was obvious they would not scale. But with MySQL 8 many things were overhauled, so let’s see what we can make many of and see how the server fares.

We observe a large number of messages of the kind

The table '../tmp/#sql…' is full

Before MySQL 8

In older Versions of MySQL, implied temporary tables are being created whenever your EXPLAIN contained the phrase using temporary.

In this case, MySQL would create an in-memory temporary table to materialize an intermediate query result, and then continue to process the data from there. If that temporary table was larger than some configurable limit, the temporary table would instead be converted to a MyISAM table on disk, streamed out, and then work would continue with this.

I never had much reason to use table compression, so I largely ignored the topic in MySQL. I knew that MySQL had table compression since 5.1, but I also knew the implementation was horribly complicated and double stored all data. There is also page compression, a feature introduced with 5.7, which replaces table compression and works much better.

Table Compression

Table Compression is available in MySQL 5.1 and newer. It is used by setting an InnoDB table up with ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 or similar, for even smaller key block sizes. A lot of status tables in INFORMATION_SCHEMA.%CMP% are available to monitor it.