The Light MySQL Startup Optimization on EBS Volumes

In the last weeks / months, I have been working on understanding / improving MySQL startup with many tables.  I already wrote four posts on the subject, they are listed below.  In this post, I use the system analysis of the previous post to revisit the light optimization on EBS volumes.  With this analysis, I am able to determine why the previous tests did not show improvements, and I am able to provide an example of a faster startup.

Trick to Simulate Higher Latency IOs on Linux

When running tests, it is sometimes useful to slow down IOs to clearly show the impact of reading from the disk.  Modern laptops and servers usually have fast local SSDs, and most cloud vendors provide, in addition to fast local disks, relatively fast network block devices (EBS for AWS and Persistent Disk for GCP).  Even when using magnetic network block devices, IOs are not guaranteed to be slow because they might hit the cache of the storage appliance (EBS volumes sometime have sub-millisecond latency).  This blog post describes the solution I am using for simulating high latency IOs on Linux. TL&DR: device-mapper’s delay target.

Free Page Consumption by InnoDB Table Compression (Percona backoff Algo. part #2)

In my previous post about InnoDB Stalls on Empty Free List, I used a test environment that might look a little artificial : a table larger than the InnoDB Buffer Pool but fitting in the Linux Page Cache.  This configuration allows serving very quickly what MySQL thinks are IOs because these are hit in the filesystem cache.  In this post, I explain why this environment is not totally artificial and can be a good representation of another more complex environment.  I also explain how an Empty Free List backoff stall can occur when no IO is done.  You have probably already guessed what this post is covering from the title, so let's dive in.

Tail Latencies in Percona Server because of InnoDB Stalls on Empty Free List

If, in Percona Server, you are observing tail latencies on queries that should be fast, this might be a side effect of Percona's improved InnoDB Empty Free List Algorithm.  When using this algorithm (the default in 5.6 and 5.7 and optional configuration in 8.0), a query needing a free page while none are available waits until the LRU Manager Thread refills the free list.  Because this thread is waking-up only at regular intervals, the query is blocked (which explains the latency) until the LRU Manager Thread is done sleeping and completes its work.  These latencies / stalls might last up to one second in the worse case.  All the details are in this post, starting with InnoDB Free Pages.

Trick to Simulate a Linux Server with less RAM

I created the first draft of this post many years ago.  At that time, I was working with physical servers having 192 GB of RAM or more.  On such systems, doing memory pressure tests with MySQL is complicated.  I used a trick to simulate a Linux server with less RAM (also works with vms, probably not with Kubernetes or containers).  I recently needed the trick again and as I will refer to it in a future post, now is a good time to complete and publish this.  TL&DR: huge pages...

MySQL Master Replication Crash Safety Part #5a: making things faster without reducing durability - using better hardware

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the previous posts, we explored the consequences of reducing durability on masters (different data inconsistencies after an OS crash depending on replication type) and the performance boost associated with this configuration (benchmark results done on Google Cloud Platform / GCP).  The consequences are summarised in the introduction of Part #4, and the tests are the subject of this last post.  Also in this last post, I mentioned that my results for high durability are limited by the sync latencies of GCP persistent disks.  As I found a system with better latencies, I am able to present new results.  And this system is a vm in Amazon Web Services (AWS) with local SSD.

MySQL Master Replication Crash Safety Part #5: faster without reducing durability (under the hood)

This post is a sister post to MySQL Master Replication Crash Safety Part #5: making things faster without reducing durability.  There is no introduction or conclusion to this post, only landing sections: reading this post without its context is not not recommended. You should start with the main post and come back here for more details.

MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability

This is a follow-up post in the MySQL Master Replication Crash Safety series.  In the three previous posts, we explored the consequence of reducing durability on masters (including setting sync_binlog to a value different from 1).  But so far, I only quickly presented why a DBA would run MySQL with such configuration.  In this post, I present actual benchmark results.  I also present a fundamental difference between on-premise servers and cloud virtual machines as my tests are done in Google Cloud Platform (GCP).  But before going further, let's summarise the previous posts.

MySQL Master Replication Crash Safety part #4: benchmarks (under the hood)

This post is a sister post to MySQL Master Replication Crash Safety Part #4: benchmarks of high and low durability.  There are no introduction or conclusion to this post, only landing sections: reading this post without its context is not recommended. You should start with the main post and come back here for more details.

Getting past 2400 threads with sysbench and dbdeployer for benchmarking MySQL 5.7.26

I am currently running tests with sysbench and dbdeployer on a Google Cloud Platform Debian 9 instance.  I ran into an interesting limit and lifting it was not straightforward.  I hope that by sharing this, you can avoid losing too much time.