My very first writings were done in Org-mode for Emacs. org-mode is a beat of a format for the Emacs text editor. With it, you can take notes, track time, organize projects, make to-do lists, and write blog posts.

I loved working with org-mode. But, since no one uses it, I had to convert my lovely org files into Markdown or Word to get a review or share a publishable package.

The conversion itself was fine. Pandoc is quite capable of parsing org files. The nag was that I had two juggle org and Markdown formats in my head. Too many times, I found myself adding org directives in Markdown and wondering why it didn’t work. Eventually, I decided to ditch org and move to Markdown for good.

Org-mode includes

The thing I loved most about org (for writing, at least) was its ability to use includes. I can add an #+INCLUDE directive, and Emacs/pandoc would expand the contents of that file into the main document. For example, with org, I could do this:

* My Title

Check out this awesome code:

#+INCLUDE: "./example.js" src javascript

Having code in separate files makes it very easy to copy/paste the text into tools like Grammarly or Hemingway Editor.

Adding includes to Markdown

Markdown, unfortunately, does not have includes. With meant that I had to mix code and text on the same file. I didn’t want to do that. Was there any way of adding includes to Markdown?

After some Googling around, I found m4, a macro processor that has an include directive. To use this feature on Markdown, we must first change the default quotation marks by adding this at the top of the main Markdown file:

changequote(`{{', `}}')

This enables me to write my code sample on a separate file, e.g., example.js:

```javascript
console.log("Hey!")
```

And expand its contents by adding this line in my main Markdown file:

include({{example.js}})

To render the expanded Markdown, we call m4 like this:

m4 -E -I./ main-post.md > expanded-post.md

The -I argument points to the include folder. So, this:

changequote(`{{', `}}')

# My Title

Check out this awesome code:

include({{example.js}})

Renders as:

# My Title

Check out this awesome code:

```javascript
console.log("Hey!")
```

Putting everything together

Over time, I put together small Makefiles that use this technique in conjunction with pandoc to convert my flavor of Markdown into plain Markdown, Word, or plain text. You can check the templates I use in my writing repository.

Happy writing!

-Tommy

But lately I’ve been paying more attention to how I write and I’ve noted that I’m kind of following a process after all. After some though, I think I’m ready to formalize it a bit. For now, I’ll only name the steps:

• Research
• Sketch
• 1st draft
• 2nd draft
• 3rd draft
• Review
• Publish

Let me explain what happens in each one.

Research

Any good article starts from good knowledge. This is probably my favorite part of the process as I don’t really need to worry about the writing. I just can learn interesting.

During this process I take notes, collect links, grab interesting paragraphs, see videos and presentations, save screenshots and try to learn a little bit of the people behind the tech (an often forgotten part of technical writing).

I keep track of everything with Notion.

Once I think I know enough get started with the article is sketching time.

Sketch

During this phase I create a tentative outline: write headers, describe the contents of each section, make notes of there diagrams will be needed and plan the ideas. I write all my articles in Markdown and use pandoc if I need to switch formats.

Before I start writing paragraphs I like to place a single sentence descring its main idea. It helps me keep focused and form an idea of how the article will flow from paragraph to paragran and section to section.

For most of the first phases of the writing I use Vim as my primary text editor, because it’s so easy to move large chunks of text around.

1st draft

The first draft is usually the hardest. If I’m lucky the work I did on the sketch will help me get over the first draft soon.

In this phase, guided by the ideas/paragraph flow, I flesh out each paragragh. I also design most diagrams, write their alt and caption texts. I do this here because a diagram with a good caption can save a lot of writing.

The first draft doesn’t have to be perfect, it rarely is any good in fact. The important part is fill the empty space with something to work with.

2nd draft

On the second draft I want to make sure:

• I haven’t missed something important.
• I’m writing accurately.
• I haven’t gone any rabbit holes.

So I go back to my reseach notes and compare what I’ve written with the facts. Having written about the topic I can understand my notes more deeply.

I add any missing details, do a little fact checking (usually do a more research), take out parts that aren’t needed.

By the second draft, I expect to have a more coherent article. So I go streamlining the text paragraph by paragraph section by section. I use Grammarly heavily at this point to correct and consolidate. I love Grammarly, its suggestions are usually spot on. Although, it’s a bit stubborn at times regarding word choices. There’s a lot of “this word is overused, use this one instead” that really makes no sense. But overall is a great tool.

3rd draft

By the third draft I can take a more panoramic view of the draft. I check if it flows well and make little adjustments. For that, I switch to Typora as it gives me the full rendering of the page with graphics.

I’m a strong believer of simple language so during this phase I use Hemingway Editor to simplify complex sentences. In all honesty I wish I could write simple sentences from the get go. It would simplify things so much. Regardless, the Hemingway editor is great. It highlights complex sentences, and offers a few simpler alternative words.

I tend to rewrite a lot of paragraphs during this last draft. When I’m out of ideas or have difficulty finding different ways of expressing a though I use Quillbot, a rephrasing engine, to get altenate versions and inspiration.

Before sending the draft to review do a pass with Google Docs. It’s surprising how good this humble word editor is at picking errors no other tool had.

Review

I’m blesseed with having people who can review my draft. Doubly blessed with a technical reviewer that fact checks and offers insightful suggestions, and a proofreader that doesn’t let me get away with lazy writing. After the review process, the draft has reached a new level in quality.

Publish

The last stretch involves preparing the draft for publication. I check that graphics all looking good, code is lined up, links are broken and everything is ready for publication.

Once published, it’s rinse and repeat with the next draft.

Recap

Here’s how the process looks visually.

Happy writing

-Tommy

Docker Hub introduced rate limits for image pulls earlier this month. Being the default registry for Docker images, this is one of the biggest in the ecosystem in the last few years, and it will affect not only Docker users but also Kubernetes setups.

The limits

The new limits affect free users. No more unlimited free pulls, the party is over.

• Anonymous free users: 100 container pulls every 6 hours.
• Authenticated free users: 200 container pulls every 6 hours.
• Paid accounts: unlimited.

At first, 100 pulls may sound like a lot. But think what happens when you start a Kubernetes deployment. Each pod pulls independently: if you spin up a ReplicaSet with 100 replicas, that’s 100 pulls at once.

The same thing happens when we’re doing a rolling update, a rollback, a canary release, or a blue-green deployment. As pods keep spawning, the chance of hitting the limit increases, and we face the most annoying of problems: random, unexpected, avoidable failures.

What to do?

What can you do? To see if you’re affected, check if you’re pulling images from Docker Hub with the following command:

kubectl get pods --all-namespaces -ojson | jq -r '.items[].spec | .containers[] // [] += .initContainers[] // [] | .image' | sort -u

If you see images with no registry (just the image name) or where the registry is docker.io, you’re pulling from Docker Hub, so keep reading.

Next, check if you’re using authentication. All images should be pulled with authentication. Anonymous pulls are rate limited by IP. Even if you don’t need a lot of pulls, you may still be affected by other users sharing the network.

The bright side is that adding authentication is really easy. It only takes two steps. Here’s how to do it.

Step 1: tell Kubernetes how to authenticate

The first step is to add your Docker Hub username and password to the cluster. We can do this with kubectl. You only have to do this once per cluster.

$ kubectl create secret docker-registry my-dockerhub-account \
    --docker-server=docker.io \
    --docker-username=DOCKER_HUB_USERNAME \
    --docker-password=DOCKER_HUB_PASSWORD

Step 2: add ImagePullSecrets in the deployment

Once the secret is safely stored in Kubernetes, we only need to mention it on the deployment manifest. For this, add imagePullSecrets in the spec.template.spec tree:

      imagePullSecrets:
      - name: my-dockerhub-account

For example, if I want to run a deployment with three nginx pods, the end result would be:

apiVersion: apps/v1
kind: Deployment
metadata:
  name: nginx-deployment
  labels:
    app: nginx
spec:
  replicas: 3
  selector:
    matchLabels:
      app: nginx
  template:
    metadata:
      labels:
        app: nginx
    spec:
      imagePullSecrets:
      - name: my-dockerhub-secret
      containers:
      - name: nginx
        image: nginx:1.14.2
        ports:
        - containerPort: 80

That’s all you need. When you have the chance, kubectl apply the new manifest to start using authentication.

Better safe than sorry

I hope this helps you to prevent avoidable errors. Once authenticated, you should have a more consistent experience and a higher limit.

I’ve not heard of anyone hitting the limit yet. Docker Hub said they’ll enforce them gradually, so I’m sure it’s a matter of time until someone gets bitten by the new caps.

While working on the slides for the talk I gave a few weeks ago, it occurred to me that writing and coding have more than a few things in common. I originally planned to speak a bit about that but had to take it out for lack of space. I’ll make a post instead, this one.

Thinks in common

Language

Both writing and coding use a language. Writing happens in any of the hundreds of languages people speak all over the world. Coding is done in synthetic languages that are mostly inspired by English.

Rules

Both have rules. Speaking on general terms, coding has strict rules because the computer needs to parse it unequivocally—the developer’s intention must be defined with mathematical accuracy. Human languages also have rules, but they are looser, ever-changing, and don’t always make logical sense. Rules are lax because humans can interpret and make assumptions. We can use this extra bandwidth to communicate on a deeper level.

Movement

With this I say that both do something. Writing tells a story, shares an idea (like I’m doing now), or argues about an opinion. After reading, the reader may have a new thing to consider (even if they don’t agree) or know how to do something they didn’t know before. Whether as a book, as a script for a movie, or a screenplay, writing moves people.

The same goes for code. Programs move the world, and we wouldn’t be able to live here without them.

Virtues

Coding and writing also share some virtues. Simplicity and clarity come to mind—simple trumps complex in both writing and coding. Style and elegance are harder to define, but we know it when they see it.

Humans have minds

Coding is one of the most complicated fields anyone can get in these days. But at least computers don’t have their own minds (although, sometimes they seem to do).

Humans will pick and choose. They won’t read boring things. So not only do writers must be clear, but they also must pique the reader’s interest and give them something of value.

Language: the I/O of the mind

Even though writing and coding touch on many points. Both activities need very different skillsets. Otherwise, playwrights would be the best software engineers, and all developers would be bestsellers authors (many are).

Like a clockmaker quietly putting together a watch, developers’ primary skills are knowledge and logic. Every piece of code must work in a very definite way, and there is no room for mistakes.

To me, a writer is a bit closer to a musician. They have more freedom, they have more options, but their prose must still be ruled by logic. It also—like a composer—must be guided by a kind of metric. One reads with the ear.

Salute your fellow writer

If you’re a dev, the next time you see a writer, give them a nod and a hat tip. You have more in common with them than you think.

-Tomas

I gave a talk about technical writing at nerdear.la, a bilingual convention about technology and people that took place October 2020 in Argentina.

The Talk

The slides are in English, the talk is in Spanish. At the end of the talk, I’ll be answering questions.

Slides

You may download the slides here. You may use them or modify them, but if you do, please make the appropiate attribution.

-Tommy

Imagine my hapiness when I found about a project that lets me code for mobile without leaving the command line.

React Native and Expo

React Native is a Facebook project that converts Javascript into native Android and iOS code. Using one language for both platforms is definetly enticing and conventient.

Expo is an official React Native project that lets you do without full featured IDEs like Android Studio.

https://expo.io/

Curious about how it worked, I tried it for a few days and here’s the result.

Setting Up

We only need Node.js to get started:

$ npm install -g expo-cli
$ expo init
? Choose a template: (Use arrow keys)
----- Managed workflow -----
❯ blank                 a minimal app as clean as an empty canvas
blank (TypeScript)    same as blank but with TypeScript configuration
tabs                  several example screens and tabs using react-navigation
----- Bare workflow -----
minimal               bare and minimal, just the essentials to get you started
minimal (TypeScript)  same as minimal but with TypeScript configuration
$ npm install

Running

Running the application is as easy as:

$ npm run start

The command starts a bunch of processes, including a local dashboard running on localhost:19002.

The quickest way of viewing the application is by scanning the QR code with your phone and running it on the physical device.

Additionally, you can run the application in a virtual device if you have installed the emulator (Android Studio and avd)

Building

The Expo platform offers a cloud build service. Compiling an Android package is as easy as:

$ expo build:android

After a few minutes, you’ll find the signed APK from the website, ready to upload to the Play Store.

Ejecting

The Expo build service is nice but it can get inflexible. I’d like to be able to do End-to-End testing, and manage the project entirely in my CI/CD workflow instead of depending on 3rd parties.

Fortunately, Expo can create native Android and iOS projects that are will feel familiar to mobile developers.

To get these files, we must eject the project. The price we pay is that we no longer can build it from the Expo service:

$ expo eject
? How would you like to eject your app?
Read more: https://docs.expo.io/versions/latest/expokit/eject/ (Use arrow keys)
❯ Bare: I'd like a bare React Native project.
ExpoKit: I'll create or log in with an Expo account to use React Native and the Expo SDK.
Cancel: I'll continue with my current project structure.

Of course, Expo may not be the best alternative for every case: https://docs.expo.io/versions/latest/introduction/why-not-expo/

The android directory can be opened by Android Studio. We can compile an APK with gradle and run the project in an emulator as usual.

Summary

Expo is a nice alternative to full-featured IDEs. We can prototype pretty quickly a mobile project and when it’s time for more serious work. We can get a good Android project.

When I started freelancing, I never imagined I would end up writing for a living. Never in a hundred years. Yet, I’ve been doing it full-time for almost a year now.

I got interested in freelancing because I wanted to try something different. I needed a break from the office routine. The job was OK. I had excellent colleagues and was friends with everyone. And while it wasn’t making me rich by any means, I never lacked for anything. It was a comfortable job—perhaps too much.

The trouble was that going to the office got harder and harder as time passed. It got to the point that I started sensing that the whole thing was a terrible waste of time. Meetings and busywork consumed most of my and everyone else’s workday. Going back and forth to the office four days a week (+1 home office day) took too long and squandered too much energy. At least that’s how I perceived it.

Now, don’t get me wrong, I know meetings and some degree of paperwork is unavoidable. I do. I’m fine with that. I made my peace with that; it’s just how companies work. But there is a point where bureaucracy takes over, and I felt we had long gone past it.

At first I thought that maybe I needed to change jobs. So I updated my resume, went to a few interviews, and even landed an offer that would have meant a substantial pay bump. In all honesty, when it was time to make the decision, I realized that I wasn’t too excited about the change—it felt like swapping one office for another. I realized that more money just wouldn’t cut it.

One friend who was already a freelancer recommended me to try Upwork. The way he described it sounded great, just what I needed. So I created a profile and started sending proposals for jobs I thought I could do.

Freelancing was a whole new and intimidating universe. It was exciting and scary. I did a few programming jobs here and there, I coded a chatbot, I did some database migrations.

One day I saw a job post asking for a 2000-word PHP installation tutorial. I knew PHP and MySQL pretty well, so I figured that it was easy money. By that time, I already had a personal blog with a few posts, and I thought that alone qualified me. So, without thinking too much about it, I sent my proposal. After all, how long could it take? Two thousand words… easy peasy, piece of cake… easy as falling off a log.

I landed the job and said I would have it in 2 days, 3 tops.

Boy, did I planning-fallacy-ed the thing…

I spend 2 entire days just writing down the steps and taking screenshots. Then 3 more writing the damn thing (all this while I did my daytime job). It dawned on me that proper writing is much harder than blogging for fun. It was exhausting, fun, and sleep-depriving. And when the draft got accepted, I was incredibly proud.

That was the point I started considering writing as a job.

In the following months, a lot of people were very kind and let me write for them. One person took a big chance and gave me enough work to take the plunge and try writing as a full-time job, for which I’ll be eternally grateful.

So, what I like about writing

It’s different

Professional writing is different than any other job I had. For me, it’s the perfect mix of learning, playing, and working.

It’s challenging

Putting words on paper is easy. Making them make sense and tell a story, not so much. Finding the correct tone, showing just the right amount of information, and figuring out how best to deliver is a balancing act. It’s the kind of challenge that makes me want to keep trying harder.

Give something back

It feels good to contribute, even if it is only a grain of sand in the infinite expanse that is the Internet.

View things in a different light

Any text that doesn’t consider the audience is destined to fail. Writing forces me to put myself in other people’s shoes.

Has its own pace

There’s no on-call duty, no overtime, no meetings back to back, and (usually) no super-long hours. Within certain limits, I can choose how many hours I want to work.

The other side of the coin is that staying productive at a consistent pace it’s a lot more difficult. Some days I’m on fire. Others, I can’t write a word to save my life. And there is no way of knowing until I sit and try.

Learn new things

Writing is a way of learning. If you ask me, it’s one of the best ways. It has given me the chance to play with many wonderful technologies that I otherwise wouldn’t have paid attention to.

Defrags the noggin

Writing forces me to put things down and examine them in the critical light of the narrative. I frequently find that what made sense in the nebulous circumvolutions of the mind breaks down into a mess of disconnected sentences and repeated words as soon as I put them on the screen.

Relearn old things

We’ve all been tripped by an innocent question while explaining something that we thought we knew well. Without fail, writing reveals the flaws in our thinking and shows how well (or bad) we understand things.

Give it a try

Some people say that writing is art. That may be true for the likes of Poe or Bradbury. My problem with calling it art by default is that it puts it on a pedestal. For me, writing is like carpentry. It’s a skill that must be honed every day. Something that takes time and effort, sure enough, but is entirely within reach of anyone.

So give it a try.

For me, that software is Docker Desktop; not much for the sake of Docker, but mainly for the standalone Kubernetes cluster.

I’m afraid I can’t give you Docker Desktop for Linux, but I’ll offer the next best thing instead:

7 tips to get the most out of Minikube

Use the Dashboard

This is one of the best features for me. Type minikube dashboard, which starts the Web UI that lets you manage all your Kubernetes resources. You can even start a terminal in a pod for a quick debug.

Periodically Reset Minikube

Every now and then, delete the cluster with minikube delete and start a new one with minikube start. I’ve been bitten by strange networking problems in the past and they all cleared up everytime after resetting the cluster.

SSH to the VM

Minikube runs a single-node Kubernetes on your laptop. Sometimes we need to log in to the VM to get a closer look. The problem is that it runs in headless mode, so we don’t have a terminal window. But Minikube has a built-in ssh command for this: minikube ssh

File Sync

If you put a file in $HOME/.minikube/files it will automatically be copied into the VM when you start the cluster.

For instance you can override the DNS servers in the VM like this:

$ mkdir -p ~/.minikube/files/etc
$ echo nameserver 1.1.1.1 > ~/.minikube/files/etc/resolv.conf
$ minikube start

Automount $HOME

Did you know that if you start Minikube with minikube start --mount it automatically mounts $HOME in the VM? On my machine, home gets mapped to /minikube-host (which is different that documented path).

You can also mount any directory in the VM with:

$ minikube mount /path/on/host:/path/on/vm &

Try Different Versions

It’s a good idea to always use the same Kubernetes version that you have in production. You can change the local version with --kubernetes-version. For example:

$ minikube start --kubernetes-version="v1.18.5"

Connect with Tunnel

You may have noticed this when you deploy a load balancer service:

$ kubectl get service
NAME         TYPE           CLUSTER-IP     EXTERNAL-IP    PORT(S)        AGE
my-service   LoadBalancer   10.97.97.121   <pending>      80:32446/TCP   88s

It never gets assigned an external IP.

At first, I didn’t pay much attention to that, I just went with the links provided minikube service --url SERVICE_NAME.

But see what happens when you start a tunnel (do this on a second terminal)

$ minikube tunnel

Now suddenly the LoadBalancer service has an IP:

$ kubectl get service
NAME         TYPE           CLUSTER-IP     EXTERNAL-IP    PORT(S)        AGE
my-service   LoadBalancer   10.97.97.121   10.97.97.121   80:32446/TCP   88s

And it should be connectable:

$ curl 10.97.97.121:80

I’m glad we have Minikube; it’s an excellent way to try out and develop Kubernetes applications. I hope these tips helped you get the most out of it.

-Tomas 👋

I still remember the first time I came in contact with Vim; it was during my very first job. It was a part-time gig in a government office. A few months in, they decided to go open-source full in. Out the window went Windows and in came Linux. At the time, I was doing PHP development, and I was sorry to have to let go of my dear Dreamweaver.

I decided that if I had to work on Linux, I would go all the way, no compromises for me. That meant getting used to working on the console and learning Vim.

When one of the resident Linux gurus walked by my seat and saw my screen, he said, “Are you using Vi? Maybe you’re not so useless after all.” (Funny story, I came across him on facebook the other day. Now that he’s bald, he doesn’t look intimidating at all).

Lately, I’ve been writing a lot. I found that armed with the right plugins, Vim is a great tool writing:

• vim-pencil: my favorite writing plugin. Vim-pencil brings a ton of nice things like navigation aids, smarter undo based on punctuation, and proper soft wrapping.
• vim-ditto: ditto highlights repeated words in a paragraph, just what I need to avoid repeating words all the time.
• vim-goyo: a Writeroom lookalike for Vim, goyo removes all distracting elements like modeline and line numbers.
• vim-colors-pencil: an elegant, low contrast colorscheme geared towards writing.
• vim-litecorrect: litecorrect automatically corrects common typing errors like “teh” instead of “the”.
• vim-lexical: combined spellchecker and thesaurus. Vim-lexical lets me navigate between spell errors with ]s, [s and quickly find synonyms with <leader> t
• vim-textobj-sentence: a plugin for better sentence navigation. I can move between sentences with ( and ), I can cut a sentence with dis. Depends on vim-textobj-user.
• vim-textobj-quote: this plugin smartly creates “quotes” so I don’t have to.
• ALE: the Asynchronous Lint Engine is a polyglot analysis tool that is not limited to code. It supports a bunch of style checkers like proselint and LanguageTool.
• vim-orgmode: I’ll admit that, before I saw the errors of my ways, I communed with the one that should not be named. During the time I used Emacs, I never fully switched away from Vim, that alone should have told me something.

For my labors, I gained something invaluable: I found Org mode. For me, Org-mode is the most intuitive and straightforward way of interacting with text. This filetype plugin only implements a subset of all its features, but even so, it’s good enough for my purposes.

While not Vim-related, I also find these tools irreplaceable in my workflow:

• pandoc: a command-line markup converter. Supports dozens of formats, including Org-mode and markdown. I usually write in Org and then export to the target type.
• TitleCase.pl: John Gruber’s title case Perl script. I haven’t found a good native Title Case plugin for Vim, so I just use a !TitleCase.pl filter.
• grip: view local Markdown files with GitHub renderer. I use it to preview how my files will look before pushing them.

Happy writing!

Tomas

A recent project I worked on provided an opportunity to work with MariaDB ColumnStore.

The Product

The product is a business intelligence application. Its main view, a very comprehensive dashboard with an abundance of counters, showing all operational aspects: from server status, database backups, support tickets and SLA to datacenter atmospheric conditions.

In the middle of all this: several MariaDB databases fed through multiple ETL jobs.

As the application grew, the dashboard got increasingly more intricate. The tables got bigger and the queries slower. To cope with the higher load, aggregate tables were added at some point. These tables contained precomputed subtotals for various predefined filters.

The Issue

The aggregate tables worked really well for a very long time. However, it still had its downsides, all of which got worse as data ingestion rate increased:

• db schema and operation gets more complex
• tables need to be updated frequently as new data is added
• higher resource utilization, locks and timeouts during update jobs
• even on the best cases, some lag between precomputed and actual data is always present

That last one was causing the big trouble. Dashboards where getting out of date fast, new data was being added faster than it could be processed. As a result, users where more frequently seeing stale data.

The Requirements

The main requirement is to have the dashboards to be as close as possible to real time, while keeping page load times within reason.

Several solutions were proposed, including partitioning, sharding and adding more replication slaves. After talking and discussing options, we settled trying out MariaDB ColumnStore.

Some reasons we thought it would be a good solution:

• Familiarity: They were already running MySQL and MariaDB.
• Simplicity: We could get rid of the aggregate tables and their update processes.
• Integration: No need to overhaul the whole setup, we could copy the tables at our convenience.

The ColumnStore

MariaDB ColumnStore (CS) is a special version of MariaDB, it supports all the traditional MariaDB features while providing an additional storage engine: ColumnStore, for columnar-based tables.

But while MariaDB has a 1 query = 1 CPU restriction, CS is all about parallelism. Not only a single query can be processed by multiple CPUs: queries can be distributed among multiple CS servers,

The Test

For the test I was given a modest VM with 8 CPUs and 15 GB of RAM. We picked a pilot table, extracted one month worth of data from the table (about 5M rows) and proceded to run the tests.

As a “control group”, a vanilla MariaDB server was installed and the usual production queries were benchmarked.

Then the second phase began: after removing MariaDB, a CS server was installed and data imported in a ColumnStore table. Again, bechmarks were run.

Here are the times to compute an aggregate table:

Quite impressive: about 44 times faster.

We can’t claim victory yet, as this does not really reflect the actual user experience. Remember the app is fed from the precomputed aggregate table.

The real test is about estimating page load time. How does the realtime aggregation compares against getting the precomputed table?

From the user perspective, performance with CS is worse, the page load time increases in about 2 orders of magnitude. True, the data show is as realtime as possible. But can we do better?

Yes, of course. So far we only scratched the surface.

MariaDB ColumnStore has 3 modes of operation, these can be set at global level or session level:

• Mode 0: generic row-by-row mode, highly compatible but much slower.
• Mode 1: distributed, very fast but non-parallelizable queries are rejected.
• Mode 2: automatic, switches between mode 0 and 1 depending on query.

Mode 1 is the default, which means it wants to be fed good queries. To make the previous tests work, I had to switch to mode 0, so we’re not getting the best possible performance.

With a surprisingly little bit of rewriting, the same queries were running on mode 1.

Let’s see how well it does:

Query times improved a lot, realtime mode 1 takes only 1 order of magnitude more than getting the precomputed data, that’s quite a feat. Page load times are still worse, but it’s very much within the usual latency for a web application.

The Solution

Once the plan was green-lighted, I proceeded to install a proper CS server (only one node for now, but we can add more later).

To provide load-balancing a MariaDB MaxScale proxy was added. This proved a key component as it also allows to capture changes on the main db, which are then sent to CS without needing to modify any other processes.

With proper load balancing in place, we took to opportunity to add more slave replicas to increase the performance of the general queries.

Caveats

Some problems we encountered along the way:

• Not all queries were as easy to optimize for mode 1.
• Not all aggregation functions are available for CS tables: Things like SUM(FIND_IN_SET(code,'0,1')) don’t work. In some cases, we had to add additional columns to work around this.

• Datatypes in CS are more strict than in MariaDB, JOINS require types to match much more closely.
• Some queries where trying to aggregate too many columns at a time, which probably made sense of a Row based database. But for CS we found it’s better to split them in separate queries.
• Big joins can fail due to lack of memory, disk-based joins are disabled by default. Enabling the feature fixes the issue, but performance goes down fast. As a result, we had to tweak joins and where conditions on some queries.

Related links

• MariaDB ColumnStore: https://mariadb.com/kb/en/library/mariadb-columnstore/
• Operating modes: https://mariadb.com/kb/en/library/columnstore-operating-mode/
• Aggregate functions: https://mariadb.com/kb/en/library/columnstore-distributed-aggregate-functions/
• MariaDB MaxScale: https://mariadb.com/kb/en/maxscale/
• Change Data Capture: https://mariadb.com/kb/en/mariadb-maxscale-22-change-data-capture-cdc-protocol/

Thanks

I’d like to thank the customer who very graciously allowed me to publish this entry and share the experience.

Later

Tomas