performance

MySQL, JSON, indexing and generated columns

For quite some time now I wanted to play around with the recently added JSON type in MySQL. Finally, I have a project where MySQL version is high enough to support it, and the requirements are such that this choice makes sense.

The first impression was great – JSON type is basically LONGTEXT type with a bunch of added functionality to manipulate JSON data. It took no time to setup tables and necessary queries to work with it.

The second iteration though raised a few questions. Large tables, with complex JSON structures were rather slow in some of the more complex queries. The first solution to look at was obviously indexes. Turns out, MySQL does not support indexing of the JSON fields. Bummer.

But there is a rather elegant work around. It involves another recently added feature, of which I haven’t heard about until today – GENERATED columns. Think of table views, but on the column level, not table level. And generated columns can be indexed.

In fact, there’s a whole lot that you can do with GENERATED columns in general, and JSON data in particular. This blog post – “MySQL for JSON: Generated Columns and Indexing” – provides a great starting point with examples and explanations, including a scenario with the primary key of the table being a generated column, with the data from the JSON-typed column.

Awesomeness!

k6 – API performance testing tool

k6 is a developer centric open source load and performance regression testing tool for testing the performance of your cloud native backend infrastructure: APIs, microservices, serverless, containers and websites. It’s built to integrate well into your development workflow and CI/CD automation pipelines.

This is one of the better tools that I’ve seen in a long time. Not only it does its job great, but it integrates brilliantly with your development and testing pipelines.

You can either build your tests from scratch, or you can convert import them from your existing tools. For example, Postman collections, environments, and tests can be converted to k6 with postman-to-k6. Here’s a blog post to get you started on that path.

Side note: if you hit the “EACCES: permission denied, mkdir ‘/usr/local/lib/node_modules/postman-to-k6/vendor’” durin the postman-to-k6 installation, then simply append “–unsafe-perm=true –allow-root” to the “npm install” command, as suggested in this GitHub thread.

k6 provides excellent functionality for extending your basic performance tests with additional checks, metrics, and thresholds. You can even keep using your existing Postman tests within k6.

There’s also a variety of output formats, ranging from CSV and JSON, all the way to InfluxDB with Grafana charts.

40x speed up of ls on large dirs

If you ever tried listing a directory with a lot (10,000+) of files in it, I’m sure you know how annoyingly slow ‘ls’ can be. Turns out there is a simple way to make it better. Have a look at the “When setting an environment variable gives you a 40x speedup” blog post.

Or just do the following:

export LS_COLORS='ex=00:su=00:sg=00:ca=00:'

This will disable colors on the executable files, setuid/setgid bits, and capabilities.

Packets-per-second limits in EC2

“Packets-per-second limits in EC2” is an interesting dive into network limits on the Amazon EC2. Even if you aren’t hitting any limits yet, this article provides plenty of useful information, including benchmarking tools and quick reference links for Enhanced Networking.

The conclusion of the article is:

By running these experiments, we determined that each EC2 instance type has a packet-per-second budget. Surprisingly, this budget goes toward the total of incoming and outgoing packets. Even more surprisingly, the same budget gets split between multiple network interfaces, with some additional performance penalty. This last result informs against using multiple network interfaces when tuning the system for higher networking performance.
The maximum budget for m5.metal and m5.24xlarge is 2.2M packets per second. Given that each HTTP transaction takes at least four packets, we can translate this to a maximum of 550k requests per second on the largest m5 instance with Enhanced Networking enabled.

Tips to Speed up Your PHPunit Tests

I came across this collection of “Tips to Speed up Your PHPunit Tests“. Apart from the few usual ones, like disabling XDebug and using groups, I found a couple that linked to handy tools:

ParaTest – a PHPUnit extension that runs PHPUnit tests in parallel, significantly minimizing the test run time, and
PHPUnit Report – a tool that visualizes test run times, clearly showing which unit tests take the longest to run.

Very cool! Needs trying …