(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" + INTERV ..read more

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™. The Best™ here being that our future access pattern is very similar to our most recent past access pattern. Why Memory Matters In MySQL, the “things we access” in the Working Set definition is ..read more

The Problem HELP, HELP! My Percona XtraDB Cluster version: 5.7.31-31. Single Node is stuck in a joined state. I recently had the privilege to help a client with a fascinating issue. NODE-B dropped out of the 3 node PXC cluster. It looked to be DISK IO that caused NODE-B to fall far behind and eventually be removed from the cluster. A restart of NODE-B allowed it to rejoin the cluster. NODE-B looked to have been down for about 4 hours. Once NODE-B was back as part of the cluster, it required a full SST. When NODE-B stayed in a joint state for more than 12 hours, the client gave me a call. They ..read more

This is the english version of a 2007 article. In de.comp.os.unix.linux.misc somebody asked: Are commands in a script executed strictly sequentially, that is, will the next command only be executed when the previous command has completed, or will the shell automatically start the next command if the system has spare capacity? Can I change the default behavior – whatever it may be – in any way? If you are looking into the fine manual, it may explain at some point that the shell starts each command in a separate process. Then you may continue your thought process and ask what that actually ..read more

So this happened: CentOS Project shifts focus to CentOS Stream The future of the CentOS Project is CentOS Stream, and over the next year we’ll be shifting focus from CentOS Linux, the rebuild of Red Hat Enterprise Linux (RHEL), to CentOS Stream, which tracks just ahead of a current RHEL release. CentOS Linux 8, as a rebuild of RHEL 8, will end at the end of 2021. CentOS Stream continues after that date, serving as the upstream (development) branch of Red Hat Enterprise Linux. And a lot of people react like this: Oracle buys Sun: Solaris Unix, Sun servers/workstation, and MySQL went to /dev ..read more

Overview In this article, I look at how poorly designed sharding systems and replication systems in PostgreSQL affect query performance in high volume situations, and how to ensure data consistency across many servers.  I also discuss how excessive vacuuming generates I/O traffic, and how connection pooling is used to improve transaction throughput by caching connections from clients.  I also cover how insufficient memory could affect PostgreSQL performance.  These are key problems I have encountered as a database consultant, and how I’ve overcome them. Sharding Bottlenecks Shar ..read more

The tables in PERFORMANCE_SCHEMA (P_S) are not actually tables. You should not think of them as tables, even if your SQL works on them. You should not JOIN them, and you should not GROUP or ORDER BY them. Unlocked memory buffers without indexes The stuff in P_S has been created with “keep the impact on production small” in mind. That is, from a users point of view, you can think of them as unlocked memory buffers – the values in there change as you look at them, and there are precisely zero stability guarantees. There are also no indexes. Unstable comparisons When sorting a table for a GROUP B ..read more

At work, I am in an ongoing discussion with a number of people on the Observability of Outliers. It started with the age-old question “How do I find slow queries in my application?” aka “What would I want from tooling to get that data and where should that tooling sit?” As a developer, I just want to automatically identify and isolate slow queries! Where I work, we do have SolarWinds Database Performance Monitor aka Vividcortex to find slow queries, so that helps. But that collects data at the database, which means you get to see slow queries, but maybe not application context. There is als ..read more

I came to Mail.ru Group in 2013, and I required a queue for one task. First of all, I decided to check what the company had already got. They told me they had this Tarantool product, and I checked how it worked and decided that adding a queue broker to it could work perfectly well. I contacted Kostja Osipov, the senior expert in Tarantool, and the next day he gave me a 250-string script that was capable of managing almost everything I needed. Since that moment, I have been in love with Tarantool. It turned out that a small amount of code written with a quite simple script language was capable ..read more

I am always surprised to learn something new whenever I talk to a member of the open-source community. No matter how much I think I have heard of every use case there is for Liquibase (and database change management in general), I always hear something that makes this space still feel new. There’s always something left to discover. Today, that new something is the problem of how to perform large batches of changes with SQL ALTER TABLE statements. No problem you say? Okay, but this ALTER needs to happen in production. Still not worried? Well, let’s say you have millions of rows, and because you ..read more