# Another Boring Tech Blog > A blog about databases, mostly. A deadpan technical journal on databases, mostly MySQL, and the slow-moving infrastructure beneath them. Entries are long-form, footnoted, irregularly published. Voice is declarative and self-aware; no superlatives, no emoji. Code examples use JetBrains Mono; prose is set in Source Serif 4. ## Navigation - [Contents (current issue)](https://anotherboringtechblog.com/): Hero of recent entries and the serial archive list. - [Archive](https://anotherboringtechblog.com/#archive): Chronological listing of published entries. - [Topics](https://anotherboringtechblog.com/topics/): Active topic taxonomy. - [Colophon](https://anotherboringtechblog.com/colophon/): About the editor and the publication. - [RSS feed](https://anotherboringtechblog.com/feed/): Atom / RSS for machine consumption. ## Topics - [MySQL](https://anotherboringtechblog.com/category/mysql/) — 21 entries. - [Linux](https://anotherboringtechblog.com/category/linux/) — 9 entries. - [Postgres](https://anotherboringtechblog.com/category/postgres/) — 1 entry. ## Entries - [Big O for MySQL: Why the Same Query Gets Slow at Scale](https://anotherboringtechblog.com/2026/04/big-o-for-mysql/) (2026-04-22 · Linux, MySQL): Big O for MySQL, explained visually: how O(1), O(log n), O(n), O(n log n), O(n²), and O(2ⁿ) decide whether your query stays fast as your data grows. - [Instant Strapi Performance Without Changing Your Code](https://anotherboringtechblog.com/2026/04/strapi-performance-readyset/) (2026-04-18 · Linux, Postgres): How to scale your Strapi data layer using a wire compatible caching proxy and what the results look like on 43,000 real restaurants. Strapi is an excellent tool… - [Using PHP and Readyset for the First Time with MySQL](https://anotherboringtechblog.com/2026/03/php-readyset-mysql-getting-started/) (2026-03-31 · MySQL): Speed up your PHP application without changing a single query. A few days ago I set out to answer a simple question: how much faster can a PHP… - [Technical Analysis: Why 0.2ms Queries Can Still Result in Low QPS](https://anotherboringtechblog.com/2026/03/technical-analysis-0-2ms-queries-low-qps/) (2026-03-29 · Linux): It is a classic trap: you spend a week fine-tuning your cache and your database hits only to realize your application is essentially running with the handbrake on.… - [Determining the Correct innodb_buffer_pool_chunk_size](https://anotherboringtechblog.com/2026/02/innodb-buffer-pool-chunk-size-calculator/) (2026-02-28 · MySQL): A common source of instability in MySQL environments is the unexpected growth of memory usage at startup. You might set your innodb_buffer_pool_size to 16GB, only to find the… - [MySQL 8.4 Memory Limits: tmp_table_size vs. temptable_max_ram](https://anotherboringtechblog.com/2025/11/mysql-8-4-temptable-memory-limits/) (2025-11-20 · MySQL): I recently got into a bit of a debate about standardizing MySQL 8.4’s internal temporary table configuration. We wanted to cap memory usage efficiently, but relying on "rules… - [Checking Your MySQL Server Before an Upgrade with MySQL Shell](https://anotherboringtechblog.com/2025/11/mysql-upgrade-checker-mysql-shell/) (2025-11-02 · MySQL): Upgrading MySQL is often seen as straightforward — install the new binaries, restart, done. In reality, it’s more like upgrading a production jet engine mid-flight. Small changes in… - [My Impressions from Oracle AI World 2025 – MySQL, AI, and Its Open Source Future](https://anotherboringtechblog.com/2025/10/mysql-ai-world-2025-impressions/) (2025-10-17 · MySQL): Note: The opinions below represent my personal perspective only. They don’t include any confidential information or represent the views of Oracle or my current employer, Percona.   The… - [Benchmarking MySQL Server With mysqlslap: Estimating Capacity, Not Absolute Limits](https://anotherboringtechblog.com/2025/04/mysql-server-benchmarking-guide/) (2025-04-25 · MySQL): When I’m working with MySQL, I care not just about whether a single query is slow, but how much total load the server can handle before performance starts… - [MySQL Brazil Meetup 2025 - Aniversário de 30 Anos!](https://anotherboringtechblog.com/2025/04/mysql-brazil-meetup-2025/) (2025-04-14 · MySQL): Parece mentira, mas o MySQL está completando 30 anos em 2025. Três décadas de código aberto, performance, confiabilidade (sim, a gente sabe dos bugs também 😅) e uma… - [Black Friday or Dark Friday? Depends If MySQL Stands the Rush](https://anotherboringtechblog.com/2024/11/mysql-black-friday-survival-checklist/) (2024-11-26 · Linux, MySQL): Black Friday or Dark Friday? Tips to Ensure Your MySQL Instance Survives Black Friday is one of the most demanding days of the year for e-commerce and online… - [How to Identify Processes Using Swap Memory on Linux](https://anotherboringtechblog.com/2024/11/identify-processes-using-swap/) (2024-11-23 · Linux): Identifying which processes have pages swapped out of memory when troubleshooting performance issues on a Linux system is often helpful. Swapping can impact system performance, so knowing which… - [Testing Dolphie For the First Time: Features and Feedback](https://anotherboringtechblog.com/2024/11/testing-dolphie/) (2024-11-21 · MySQL): In this blog post, I will test Dolphie, an open-source project by Charles Thompson. Dolphie is an open-source tool that provides real-time analytics for MySQL, MariaDB, and ProxySQL.… - [Understanding MySQL DDL Space Requirements](https://anotherboringtechblog.com/2024/11/understanding-mysql-ddl-storage-req/) (2024-11-20 · MySQL): This blog post will explore MySQL’s file storage behavior during DDL operations, particularly with InnoDB tables. Using a practical example, we’ll discuss where and how MySQL stores data… - [Understanding Latency Through a 100m Race: From CPU to NVMe](https://anotherboringtechblog.com/2024/11/understanding-latency-from-cpu-to-nvme/) (2024-11-14 · Linux): In the world of computing, latency is everything. Whether you’re optimizing a database query or fine-tuning a high-performance application, understanding how quickly different components of your system respond… - [How to Add an Invisible Primary Key to an Existing Table in MySQL](https://anotherboringtechblog.com/2024/11/add-invisible-primary-key/) (2024-11-04 · MySQL): With MySQL 8.0.23, the concept of invisible columns was introduced. Columns marked as invisible do not appear in standard SELECT *  queries and don’t require changes to existing application… - [How to Monitor and Optimize Fragmented Tables in MySQL](https://anotherboringtechblog.com/2024/09/optimizing-fragmented-tables-in-mysql/) (2024-09-10 · MySQL): Random insertions or deletions in a secondary index can lead to fragmentation, where the physical order of index pages on disk no longer aligns with the logical order… - [MySQL Brazil Meetup 2024 - Informações do Evento e Agenda](https://anotherboringtechblog.com/2024/07/mysql-brazil-meetup-2024/) (2024-07-14 · MySQL): Salve a todos os nerds! O nosso meetup de MySQL de 2024 já tem uma data! Data: 05 de Outubro, Sábado, 2024 Horário: 9:00 AM - 5:30 PM… - [How to Sort MySQL Tables by Size and Why It Matters](https://anotherboringtechblog.com/2024/06/sorting-mysql-tables-by-size/) (2024-06-06 · Linux, MySQL): Introduction Efficient database management ensures optimal performance, especially as your data grows. One important aspect of managing a MySQL database is monitoring and managing the size of your… - [How to Run ANALYZE in MySQL](https://anotherboringtechblog.com/2024/05/analyze-in-mysql/) (2024-05-14 · MySQL): Introduction In database management, keeping your database running smoothly is essential. ANALYZE is a command in MySQL that helps update statistics for table indexes. These statistics help the query… - [[PT-BR] Desativar o Transparent Huge Pages(THP) no Ubuntu 22.04](https://anotherboringtechblog.com/2024/05/desativar-transparent-huge-pages-ubuntu2204/) (2024-05-09): Desativar as Transparent Huge Pages (THP) de forma permanente no Ubuntu 22.04 exige a modificação dos arquivos de configuração do sistema para que a alteração seja aplicada durante… - [Releem: MySQL Performance Tool](https://anotherboringtechblog.com/2024/04/releem-mysql-performance-tool/) (2024-04-28 · MySQL): Who wouldn't love a reliable database free from issues and allowing us to sleep peacefully? That's what Roman Agabekov, the CEO of Releem, aims to provide. As a… - [Improving Database Performance with ReadySet: A MySQL Caching Solution](https://anotherboringtechblog.com/2024/04/readyset-a-mysql-caching-solution/) (2024-04-10 · MySQL): Many developers face the challenge of achieving performance without compromising on real-time application functionality in database management. This brings us to today's spotlight technology: ReadySet. What is ReadySet?… - [MySQL: Identifying Tables Not Using InnoDB](https://anotherboringtechblog.com/2024/03/mysql-identifying-tables-not-using-innodb/) (2024-03-24 · MySQL): This blog post aims to spotlight tables within a MySQL database that do not employ the InnoDB storage engine. The essence of the query is to filter out… - [Introduction to the New Percona Everest Beta](https://anotherboringtechblog.com/2024/02/percona-everest-beta-impressions/) (2024-02-28): In the rapidly evolving world of technology, database management remains a crucial, albeit challenging, aspect for many organizations. Percona, a leading provider of open-source database solutions, comes with… - [How to Permanently Disable Transparent Huge Pages (THP) on Ubuntu 22.04](https://anotherboringtechblog.com/2024/02/disabling-thp-ubuntu-22-04/) (2024-02-23 · Linux): Disabling Transparent Huge Pages (THP) permanently on Ubuntu 22.04 requires editing system configuration files to apply the change at boot time. There are a few methods to achieve… - [How to Create SSH Keys](https://anotherboringtechblog.com/2024/02/creating-ssh-keys/) (2024-02-21 · Linux): An SSH key consists of a pair of files: the private key and the public key. The private key is confidential and should never be shared, while the… - [That's a Wrap: MySQL Belgian Days and FOSDEM 2024](https://anotherboringtechblog.com/2024/02/mysql-belgian-days-and-fosdem-2024/) (2024-02-06): Hey everyone! FOSDEM and MySQL Belgian days are over, and I wanted to share my impressions from an incredible 4-day journey filled with talks about databases and, of… - [How to Use MySQL Shell to Create an InnoDB Cluster](https://anotherboringtechblog.com/2024/01/mysql-shell-create-innodb-cluster/) (2024-01-28 · MySQL): Introduction The efficiency and reliability of database management systems are paramount. This blog post is your step-by-step guide to mastering the creation of an InnoDB Cluster from scratch… - [How to Add a New MySQL Server Node to an InnoDB Cluster](https://anotherboringtechblog.com/2024/01/adding-innodb-cluster-nodes/) (2024-01-24 · MySQL): Introduction It is possible to expand the capacity and the availability of an InnoDB Cluster by adding more MySQL servers. For example, a 5-node InnoDB cluster can tolerate… - [How to Execute a Switchover in InnoDB Cluster](https://anotherboringtechblog.com/2024/01/executing-switch-over-in-innodb-cluster/) (2024-01-22): Introduction The process of executing a switchover operation is important for an InnoDB cluster. It encompasses not merely routine maintenance but extends to recovery from system failures. The… - [MySQL Brazil Meetup 2023 - Informações do Evento e Agenda](https://anotherboringtechblog.com/2023/10/mysql-meetup-brazil-informacoes-do-evento-e-agenda/) (2023-10-05): [caption id="attachment_107" align="aligncenter" width="300"] Temos definido nosso MySQL Meetup![/caption]   Salve a todos os nerds! Temos definido nosso dia de banco de dados MySQL! Data: 02 de Dezembro,… - [[PT-BR] Rotacionando os logs de erro e consultas lentas do MySQL com logrotate](https://anotherboringtechblog.com/2023/10/rotacionando-logs-mysql-logrotate/) (2023-10-02): Banco de dados geram toneladas de logs, e é essencial gerenciá-los corretamente. Os logs ajudam os DBAs a entender o que está acontecendo nos bastidores, rastrear erros e… - [How to Rotate MySQL Slow and Error Logs Using Logrotate](https://anotherboringtechblog.com/2023/10/rotating-mysql-slow-and-error-logs-with-logrotate/) (2023-10-02): Databases generate tons of logs, and it's essential to manage them correctly. Logs help DBAs understand what's happening behind the scenes, trace errors, and optimize the performance of… - [Understanding and Creating a Deadlock in MySQL for Beginners](https://anotherboringtechblog.com/2023/09/understanding-and-creating-a-deadlock-in-mysql-for-beginners/) (2023-09-26): Are you new to MySQL and hearing the term "deadlock" for the first time? Don't worry! This article will explain a deadlock and provide a simple guide on… - [Profiling MySQL using perf and Flame Graphs](https://anotherboringtechblog.com/2023/09/profiling-mysql-using-perf-and-flame-graphs/) (2023-09-26): A common task in performance analysis is identifying which code sections consume the most CPU time. This often requires profiling stack traces. Enter Flame graphs - a game-changer… - [The Ins and Outs of innodb_flush_log_at_trx_commit](https://anotherboringtechblog.com/2023/09/mysql-innodb-flush-log-performance/) (2023-09-24): Today, we're going to take a journey through one of MySQL’s performance guardians - the innodb_flush_log_at_trx_commit. The innodb_flush_log_at_trx_commit has 3 valid values: 0, 1, and 2. Let's see… - [How To Estimate Redo Log Capacity](https://anotherboringtechblog.com/2023/09/dynamic-redo-log-sizing-mysql-8030/) (2023-09-21): With MySQL 8.0.30 comes a feature that is a game changer in ensuring optimal MySQL performance – the dynamic redo log sizing. Overcoming Previous Limitations Before version 8.0.30… - [Rethinking the 80% InnoDB Buffer Pool Rule: Digging into Server RAM Allocation](https://anotherboringtechblog.com/2023/09/server-optimization-80-percent-rule-analysis/) (2023-09-21): In server optimization, various rules of thumb have guided professionals in making informed decisions about resource allocation. One such directive is the 80% rule, which suggests allocating 80%… ## Full text ### Big O for MySQL: Why the Same Query Gets Slow at Scale - URL: https://anotherboringtechblog.com/2026/04/big-o-for-mysql/ - Published: 2026-04-22 - Topic: Linux, MySQL You ship a query on Monday. It runs in 2 ms. Six months later, the same query — same SQL, same indexes — takes 4 seconds. Nothing changed. Except one thing did: **the table grew.** Welcome to the everyday problem Big O was invented to describe. This post is a practical, MySQL-flavoured tour of time complexity. We’ll keep the math light, the pictures clear, and we’ll finish with the exact algorithms your database uses to keep your queries fast (or slow). ## What is Big O, really? Forget seconds. Big O is **not** a unit of time. It’s a way of describing *how the cost of an algorithm grows as the input grows*. Specifically: if you double the data, does the work double? Stay the same? Quadruple? That shape is the algorithm’s complexity. We call the input size `n`. A few shapes come up over and over in real systems: - **O(1)** — constant. The size of `n` doesn’t matter. Accessing an array by index, or a hash lookup. - **O(log n)** — logarithmic. Doubling `n` adds one more step. Binary search, B-tree traversal. - **O(n)** — linear. Work scales directly with size. A full table scan. - **O(n log n)** — linearithmic. A sort over `n` items. - **O(n²)** — quadratic. For every row, look at every other row. A naive nested-loop join. - **O(2ⁿ)** — exponential. One more row *doubles* the work. The ultimate danger class: combinatorial explosion, travelling salesman, brute-force subset enumeration. The useful picture is this: as `n` grows, these curves diverge *dramatically*. Big O tells you which curve your query is riding. * *Same axes, same `n` — wildly different fates. The curve your query rides is the single biggest predictor of whether it’ll still be fast next year.* ## Little, medium, lots: the numbers that will ruin your week Big O only matters because, at some size, the curves stop overlapping. Below is the same story with real numbers. We ignore constant factors — those matter in practice, but they don’t change the shape*. Complexity n = 100 n = 10,000 n = 10,000,000 O(1) 1 1 1 O(log n) 7 14 24 O(n) 100 10,000 10,000,000 O(n log n) 700 140,000 ≈ 240,000,000 O(n²) 10,000 100,000,000 100,000,000,000,000 O(2ⁿ) ≈ 1.3 × 1030 beyond atoms in the universe unthinkable At n = 100 the count already dwarfs the number of atoms in your body. Exponential is not a number you compute with — it’s a number you *avoid*. At **n = 100** everything is fine — a nested loop over 100 × 100 rows is 10 K operations, basically free. At **n = 10 million**, the same nested loop is 100 *trillion* operations. Your query planner’s job is to never let that happen. Notice something: **O(log n) barely moves**. Going from 100 rows to 10 million rows takes the count from 7 up to 24 — only *17 more operations* for 100,000× more data. That’s the superpower of a B-tree index, and it’s why the first rule of MySQL tuning is “add an index.” ## Why this matters specifically for MySQL MySQL’s optimizer is, at heart, a machine for picking the algorithm with the best Big O *for your actual data*. Every time you run `EXPLAIN`, you’re looking at the optimizer’s answer to one question: “Which shape of curve should this query ride?” Three things decide the answer: - **Which indexes exist** — because each index unlocks an O(log n) access path. - **How many rows are expected** — because at small `n`, a scan can beat an index (the constants win). - **How the tables join** — because the difference between nested loop and hash join is the difference between O(n × m) and O(n + m). Everything the optimizer does — index choice, join order, whether to use a temp table, whether to sort with `filesort` — is cost math. And cost math is just Big O with constants attached. ## MySQL’s workhorse algorithm: the B+tree search InnoDB stores every table as a B+tree on the primary key, and every secondary index as another B+tree. A B+tree of a billion rows is only about *four levels deep*. That’s the magic of O(log n) in action. Here’s the search in motion. A query arrives (“find row with id = 42”), and the tree is walked from the root, one level per read, until it lands on the leaf page holding the row. With one billion rows, it’s still only four page reads. * *A B+tree lookup visits one page per level. 10 million rows → 3–4 page reads. A billion rows → 4–5. The log is doing the heavy lifting.* This is why the single most impactful thing you can do for a slow query is usually “add an index on the column you’re filtering by.” You’re not making the query faster by a constant factor — you’re changing its complexity class* from O(n) to O(log n). At 10 million rows, that’s a 400,000× difference in pages touched. If you want to see this in motion with real MySQL semantics — clustered vs secondary index, covering vs non-covering — there’s an interactive version in the [myflames project](https://github.com/vgrippa/myflames) under `docs/teach/index/btree.html`. ## The other shapes, as MySQL operators ### O(n) — the full table scan No usable index? MySQL reads every row. Double the rows, double the work. That’s `type: ALL` in `EXPLAIN`, and it’s the reason “this query was fine last year” is such a common support ticket. ``` EXPLAIN SELECT * FROM orders WHERE note LIKE '%refund%'; -- type: ALL -- rows: 10,000,000 -- Extra: Using where ``` A `LIKE '%something%'` can’t use an index prefix — the wildcard at the front breaks the B-tree’s ordering. So the optimizer falls back to a scan: O(n). The fix is either FULLTEXT, a functional index, or rewriting the query so the wildcard is at the end. ### O(n × m) — the nested loop join For every row in the outer table, look up matching rows in the inner table. If the inner side has an index, each lookup is O(log m) and the whole join is O(n log m) — tolerable. If it doesn’t, each lookup is O(m) and the join is O(n × m) — catastrophic. * *Same two tables. Left: every A row compares against every B row. Right: MySQL builds a hash on A, then probes it once per B row. At a million rows per side, the left panel is a trillion ops; the right panel is two million.* This is why the introduction of **hash join in MySQL 8.0.18** was such a big deal. It gave the optimizer an O(n + m) option for cases where neither side had a useful index, turning a previously unshippable query into a merely slow one. ### O(n log n) — the sort When MySQL needs to return rows in a specific order and can’t get them pre-sorted from an index, it falls back to `filesort`. That step adds O(n log n) on top of whatever reading cost you already paid. For a million-row result set, that’s tens of millions of extra comparisons before a single byte goes back to the client. This is where a sort-ordered covering index* earns its keep. The classic “2-second query becomes 20 ms” speedup is actually **two separate complexity wins stacked on top of each other**: **1. “Sort-ordered” — the sort disappears entirely.** A B-tree index is physically stored in key order, and InnoDB links the leaf pages together as a sorted list. If your `ORDER BY col_x` matches the index’s leading column, MySQL just walks the leaves in that already-sorted order — no comparison step, no temp buffer, nothing. The O(n log n) sort doesn’t get faster; it *stops happening*. Add `LIMIT 20` and MySQL reads exactly 20 leaf entries and quits. **2. “Covering” — the per-row PK hops disappear.** A secondary index’s leaves only contain the indexed columns plus the primary key. If your `SELECT` needs a column that isn’t in the index, MySQL has to do a second B-tree walk into the clustered tree for every matching row — an extra O(log n) *per row*, or O(k log n) total. A covering index has every column you asked for right there on the leaf, so that whole step vanishes. Stacked, the complexity moves look like this: Step Without index With sort-ordered covering index Row access O(n) scan, or O(k log n) with PK hops O(log n + k) — seek + walk `ORDER BY` sort O(n log n) eliminated (0 ops) `LIMIT k` applied *after* the sort applied *during* the walk That last row is the one that matters most for paginated queries: without the index, MySQL must sort all `n` rows even if you only want the top 20, because it can’t know which rows are the top 20 until it’s sorted them. With the index, `LIMIT 20` stops the walk after 20 leaves. *That* is where the 100× speedup actually comes from. ### O(1) — the dream (sort of) Pure O(1) is rare in MySQL — even a primary-key lookup is O(log n) because it still walks the clustered tree. But *amortized* O(1) shows up in the InnoDB **adaptive hash index**, which builds an in-memory hash on top of frequently-accessed B-tree pages. When it hits, you skip the log-n tree walk entirely. ### O(2ⁿ) — where exponential hides in MySQL You almost never *execute* an exponential algorithm in MySQL — the optimizer works hard to keep it off your plate. But there’s one place where exponential work really does happen: **query planning**. Picking the best join order for N tables means weighing up to N! different orderings, which is in the same danger class as 2ⁿ. For 12 tables that’s nearly half a billion orderings to evaluate *before the query starts running*. MySQL tames this with **branch-and-bound pruning**, on by default (`optimizer_prune_level = 1`). As the planner tries out partial join orders, it drops any branch whose cost already exceeds the cheapest *complete* plan found so far — there’s no point finishing a plan that’s already losing. That turns the theoretical N! down to thousands or millions of orderings actually costed. For most queries, it’s done in milliseconds and you never notice. You *do* notice it on large joins (say, 15–20 tables) where the tables look similar enough that cost estimates can’t tell the good branches from the bad ones. Pruning loses its bite, and real planning time appears in the `Optimizing` / `statistics` / `preparing` stages (visible in `SHOW PROCESSLIST`) before a single row is touched. The exponential monster you avoided at runtime was waiting for you at plan time. ## The EXPLAIN mindset: reading for complexity Once you carry Big O around in your head, `EXPLAIN` reads differently. You stop asking “is this query fast?” and start asking “which complexity class is each row on?” Rough mapping: `EXPLAIN` clue Complexity Feeling `type: const` / `eq_ref` O(1) – O(log n) great `type: ref` / `range` O(log n + k) good `type: index` O(n) over index depends on n `type: ALL` O(n) risky at scale `Using filesort` + O(n log n) OK if n is small `Using join buffer (Block Nested Loop)` ≈ O(n × m) scary `Using join buffer (hash join)` O(n + m) good Tools like myflames visualize this directly — the flame graph width is proportional to row count, so an O(n²) operator *looks* wider and hotter than an O(log n) one. The visual is just Big O wearing colour. ## Takeaways - Big O describes the **shape** of a query’s cost, not the number of seconds. Shapes diverge brutally at large `n`. - Adding an index is usually a **complexity change** (O(n) → O(log n)), not just a speed tweak. - Joins without a usable inner-side index are O(n × m). Hash join is O(n + m). That’s often the difference between “works” and “doesn’t.” - Sorting is O(n log n). Pre-sorted indexes make sorts disappear entirely. - `EXPLAIN` is a complexity diagnosis, not a speed check. Read it that way. The next time a query is slow, skip the “what changed?” reflex and ask “what shape is it riding?” first. Ninety percent of the time, the answer — and the fix — are in the curve. Post notes come from work on the open-source [myflames](https://github.com/vgrippa/myflames) MySQL plan visualizer, inspired by [Brendan Gregg’s FlameGraph](https://github.com/brendangregg/FlameGraph) and [Tanel Poder’s SQL Plan FlameGraphs](https://tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/). --- ### Instant Strapi Performance Without Changing Your Code - URL: https://anotherboringtechblog.com/2026/04/strapi-performance-readyset/ - Published: 2026-04-18 - Topic: Linux, Postgres *How to scale your Strapi data layer using a wire compatible caching proxy and what the results look like on 43,000 real restaurants.* Strapi is an excellent tool for building content driven applications quickly. As a project grows and data becomes more interconnected, keeping the API layer snappy is a common goal for development teams. When you have deeply nested relations like a restaurant linked to locations, menus, and reviews, the database workload naturally increases. This is a typical stage in the lifecycle of a successful application. Rather than viewing performance bottlenecks as a flaw, they are better seen as an opportunity for architectural optimization. By using a caching proxy, you can make hot queries **10 to 19 times faster** at the SQL layer with a **one line environment variable change**. ## **What is Strapi** For those new to the ecosystem:[ Strapi](https://strapi.io/) is an open source headless CMS written in Node.js. It allows you to define content types in an admin UI and then auto generates REST and GraphQL APIs for you. Under the hood, it is a Koa app backed by[ knex](https://knexjs.org/). It usually runs on **Postgres, MySQL, or SQLite** in production, issuing standard SQL just like any other web application. To test this at scale, we used the official[ FoodAdvisor](https://github.com/strapi/foodadvisor) example app. This app features nested relations between restaurants, categories, and reviews, making it a perfect candidate for performance testing. ## **Growing with Strapi** The Strapi architecture is flexible, but like any ORM based system, it can face challenges with complex data. As noted in the[ official documentation](https://docs.strapi.io/dev-docs/api/rest/guides/understanding-populate#performance-issues-and-the-maximum-populate-depth), using broad population can lead to many database queries. Specifically, the ORM can generate one SQL query per relation per row. A single request for a restaurant list might issue **150 or more queries** to hydrate all the related fields. Since Strapi does not have a[ built in database query cache](https://docs.strapi.io/cloud/getting-started/caching), teams often look for external solutions. Common approaches include rewriting queries or manually managing Redis, but these add significant code complexity. We wanted a solution that improves performance without adding a heavy maintenance burden. ## **How Readyset Helps** [Readyset](https://readyset.io/) is a **wire compatible caching proxy** for Postgres and MySQL. It sits between Strapi and your database. It uses logical replication to materialize the results of queries and keeps them fresh incrementally as writes stream in. The integration is a **zero code change**. Strapi continues to speak Postgres; it just speaks it to Readyset instead of directly to the database. The setup is two lines in your configuration: ``` DATABASE_HOST=readyset   # was: postgres DATABASE_PORT=5433       # was: 5432 ``` ### **Why this approach works:** - **Automatic Invalidation**: Readyset monitors the database replication stream. - **Instant Refresh**: When you edit content, the cache updates instantly without writing TTL logic. - **SQL Level Caching**: It caches by SQL structure (shape), not just the URL. - **Safe Fallbacks**: Writes, DDL, and transactions go straight to the main database automatically. ## **The Benchmark** We built a test stack with Postgres and Strapi, seeded with real data for **43,320 restaurants**. We tested the specific queries that Strapi emits for a restaurant list page, which you can view in our[ benchmark script](https://github.com/vgrippa/mydemos/blob/main/readyset-strapi-foodadvisor/scripts/bench_sql.py). **Query Type** **Postgres** **Readyset** **Speedup** **Pagination Count** 3.5 ms 0.33 ms **10x** **Sorted List Page** 8.6 ms 0.45 ms **19x** **Place Aggregation** 3.3 ms 0.33 ms **10x** **Honest Caveats:** - **Point Lookups**: Simple queries by ID are already very fast on Postgres (~0.3 ms) and do not see a massive boost. - **Application Overhead**: While the database layer becomes sub millisecond, the Strapi Node.js layer still handles JSON assembly and authentication. Overall HTTP response times typically see a **2 times** improvement. ## **Try It Yourself** The full demo is open source and reproducible on any machine with Docker. ``` git clone https://github.com/vgrippa/mydemos cd mydemos/readyset-strapi-foodadvisor ./demo.sh ``` The script sets up the stack, loads the data, and prints the performance table to your terminal. ## **How to Reach Out** - **Book a demo**:[ readyset.io/book-a-demo](https://readyset.io/book-a-demo) - **GitHub**: Check out the main[ Readyset project](https://github.com/readysettech/readyset) and our[ demo repo](https://github.com/vgrippa/mydemos). If you are scaling a Strapi project, we would love to hear from you. We want to build these tools in the open with real feedback from the community. --- ### Using PHP and Readyset for the First Time with MySQL - URL: https://anotherboringtechblog.com/2026/03/php-readyset-mysql-getting-started/ - Published: 2026-03-31 - Topic: MySQL **Speed up your PHP application without changing a single query.** A few days ago I set out to answer a simple question: how much faster can a PHP app go if you drop a SQL cache in front of MySQL — without touching application code? The answer is below. On a four-table join aggregating revenue by category and region, Readyset served the same result **60x faster** than MySQL. And all I did was change a port number. This post walks through exactly what I did, step by step, so you can reproduce it yourself. ## What Is Readyset? [Readyset](https://readyset.io/) is a SQL-aware caching engine that speaks the native MySQL wire protocol. Your PHP application connects to it the same way it connects to MySQL — same driver, same queries, same credentials. Underneath, Readyset proxies queries to your upstream database and, for the ones you choose to cache, serves results straight from memory. Unlike Redis or Memcached, you don’t serialize objects or manage keys. You just tell Readyset “cache this query” and it handles the rest. It supports two caching modes: - **Shallow caching** — stores results with a TTL, works with any SELECT, refreshes automatically in the background. This is what we’ll use here. - **Deep caching** — maintains materialized views updated incrementally via the MySQL binlog. Always-fresh, but limited to supported query patterns. Shallow caching is the fastest way to get going. Let’s start. ## Prerequisites - **PHP 8.1+** with the `pdo_mysql` extension - **Docker and Docker Compose** Tested with PHP 8.1.34, 8.2.30, 8.3.30, and 8.4.19. All versions produced consistent results. ## Step 1: Start Readyset with Docker Create a `docker-compose.yml`: ``` services: mysql: image: mysql:8.0 environment: MYSQL_ROOT_PASSWORD: readyset MYSQL_DATABASE: myapp expose: - 3306 healthcheck: test: "mysqladmin ping -h127.0.0.1 -P3306 -uroot -preadyset" interval: 5s timeout: 5s retries: 12 readyset: image: docker.io/readysettech/readyset:latest environment: UPSTREAM_DB_URL: mysql://root:readyset@mysql:3306/myapp QUERY_CACHING: explicit LISTEN_ADDRESS: 0.0.0.0:3307 ALLOW_UNAUTHENTICATED_CONNECTIONS: true DEPLOYMENT: docker_compose_deployment CACHE_MODE: shallow DISABLE_TELEMETRY: true ports: - "3307:3307" - "6034:6034" healthcheck: test: ["CMD", "curl", "--fail", "127.0.0.1:6034/health"] interval: 2s timeout: 1s retries: 30 start_period: 10s depends_on: mysql: condition: service_healthy ``` Bring it up: ``` docker-compose up -d ``` Give Readyset a moment to connect to MySQL and initialize. You can check when it’s ready: ``` curl http://localhost:6034/health ``` Expected output: ``` Adapter is in healthy state ``` ## Step 2: Connect Your PHP Application The only change: point your DSN at port 3307 instead of 3306. ``` // Before: connecting directly to MySQL // $dsn = 'mysql:host=127.0.0.1;port=3306;dbname=myapp'; // After: connecting through ReadySet $dsn = 'mysql:host=127.0.0.1;port=3307;dbname=myapp'; $username = 'root'; $password = 'readyset'; $pdo = new PDO($dsn, $username, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ]); ``` Set `PDO::ATTR_EMULATE_PREPARES` to `false` so PDO uses the binary protocol for prepared statements — Readyset handles these natively. One thing to know: Readyset admin commands (`SHOW PROXIED QUERIES`, `SHOW CACHES`, `CREATE CACHE`) need the text protocol. If you want to run those from PHP, use a separate connection with emulated prepares enabled: ``` $admin = new PDO('mysql:host=127.0.0.1;port=3307;dbname=myapp', 'root', 'readyset', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => true, ]); ``` ## Step 3: Set Up Some Test Data I wanted something realistic enough to show a meaningful difference — not just a single-table lookup. So I created a small e-commerce schema: customers, products, orders, and order items. ``` $pdo->exec(" CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, region VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) "); $pdo->exec(" CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) "); $pdo->exec(" CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ) "); $pdo->exec(" CREATE TABLE order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ) "); ``` Then I seeded it: 500 customers across 5 regions, 200 products in 5 categories, and 2,000 orders each with 1–5 line items. Nothing huge, but enough to make a join query actually work for its result. ## Step 4: The Slow Query Here’s the kind of query you’d find behind a dashboard — revenue broken down by product category and customer region, filtered by order status: ``` SELECT p.category, c.region, COUNT(DISTINCT o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id JOIN customers c ON o.customer_id = c.id WHERE o.status = ? GROUP BY p.category, c.region ORDER BY revenue DESC ``` Four tables, a GROUP BY, a DISTINCT count, and a SUM. Against MySQL directly, this ran in about **4ms** on average. Not terrible, but if you’re calling it hundreds of times a second on a dashboard or an API, it adds up. ## Step 5: Cache It First, run the query through Readyset so it can observe the shape. Then tell it to cache: ``` mysql -h 127.0.0.1 -P 3307 -u root -preadyset myapp ``` ``` SHOW PROXIED QUERIES; ``` Expected output: ``` +--------------------+-----------------------------------------------------------+--------------------+-------+ | query id | proxied query | readyset supported | count | +--------------------+-----------------------------------------------------------+--------------------+-------+ | q_4b2510ce90bdd3d8 | SELECT ... FROM `order_items` ... WHERE (`o`.`status`=$1) | pending | 0 | +--------------------+-----------------------------------------------------------+--------------------+-------+ ``` ``` CREATE CACHE FROM SELECT p.category, c.region, COUNT(DISTINCT o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id JOIN customers c ON o.customer_id = c.id WHERE o.status = ? GROUP BY p.category, c.region ORDER BY revenue DESC; ``` Verify: ``` SHOW CACHES; ``` Expected output: ``` +--------------------+--------------------+----------------------------+-----------------------------------------------------------+-------+ | query id | cache name | query text | properties | count | +--------------------+--------------------+----------------------------+-----------------------------------------------------------+-------+ | q_4b2510ce90bdd3d8 | q_4b2510ce90bdd3d8 | SELECT p.category, c.re... | shallow, ttl 10000 ms, refresh 5000 ms, coalesce 5000 ms | 0 | +--------------------+--------------------+----------------------------+-----------------------------------------------------------+-------+ ``` The properties column tells you exactly how this cache behaves. The `ttl 10000 ms` is the default time-to-live — cached results are considered stale after 10 seconds. The `refresh 5000 ms` means Readyset starts refreshing the cache in the background 5 seconds before it expires, so your users almost never hit a cold cache. The `coalesce 5000 ms` groups concurrent cache misses together to avoid hammering the upstream database. These are all Readyset defaults (configurable via `DEFAULT_TTL_MS`, `DEFAULT_COALESCE_MS` environment variables), independent of the `QUERY_CACHING` setting — which only controls whether caches are created explicitly (you run `CREATE CACHE`) or implicitly (Readyset caches queries automatically). Or do it from PHP: ``` $admin->exec("CREATE CACHE FROM SELECT p.category, c.region, COUNT(DISTINCT o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id JOIN customers c ON o.customer_id = c.id WHERE o.status = ? GROUP BY p.category, c.region ORDER BY revenue DESC "); ``` ## Step 6: The Benchmark I ran the same query 500 times against MySQL directly, then 500 times through Readyset cache. Here’s what I got on PHP 8.4.19: ``` MySQL direct: 4.125 ms avg (500 iterations) Readyset cached: 0.065 ms avg (500 iterations) Speedup: 63.3x faster ``` The results were consistent across every PHP version I tested: PHP Version MySQL direct Readyset cached Speedup 8.1.34 3.996 ms 0.067 ms 59.7x 8.2.30 4.008 ms 0.051 ms 78.0x 8.3.30 4.173 ms 0.067 ms 62.0x 8.4.19 4.125 ms 0.065 ms 63.3x That’s a four-table join with aggregation going from ~4ms to ~0.06ms. For a dashboard hitting this query on every page load, that’s the difference between your database sweating and barely noticing. Here’s what the query actually returns: ``` category region total_orders revenue ------------------------------------------------------- books eu-east 68 $78,619.79 sports eu-east 69 $72,150.55 books us-east 50 $69,442.39 books ap-south 54 $65,119.23 home eu-east 59 $63,388.10 electronics eu-east 53 $63,108.77 clothing eu-east 47 $54,604.23 sports eu-west 52 $53,354.70 sports ap-south 47 $51,671.39 home us-east 48 $51,549.50 ``` Here’s the complete script that sets up the data, creates the cache, and runs the benchmark. Save the code below as `benchmark.php` and run it with `php benchmark.php`. Make sure the file starts with a `tag (some browsers strip it when you copy from a web page):` ``` PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ]); $rs = new PDO('mysql:host=127.0.0.1;port=3307;dbname=myapp', 'root', 'readyset', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => false, ]); $admin = new PDO('mysql:host=127.0.0.1;port=3307;dbname=myapp', 'root', 'readyset', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_EMULATE_PREPARES => true, ]); // --- Create tables --- echo "Creating tables...\n"; $rs->exec("DROP TABLE IF EXISTS order_items"); $rs->exec("DROP TABLE IF EXISTS orders"); $rs->exec("DROP TABLE IF EXISTS products"); $rs->exec("DROP TABLE IF EXISTS customers"); $rs->exec(" CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, region VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) "); $rs->exec(" CREATE TABLE products ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, category VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) "); $rs->exec(" CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(id) ) "); $rs->exec(" CREATE TABLE order_items ( id INT AUTO_INCREMENT PRIMARY KEY, order_id INT NOT NULL, product_id INT NOT NULL, quantity INT NOT NULL, unit_price DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders(id), FOREIGN KEY (product_id) REFERENCES products(id) ) "); // --- Seed data --- echo "Seeding data...\n"; $regions = ['us-east', 'us-west', 'eu-west', 'eu-east', 'ap-south']; $categories = ['electronics', 'clothing', 'books', 'home', 'sports']; $statuses = ['completed', 'pending', 'shipped', 'cancelled']; $stmt = $rs->prepare("INSERT INTO customers (name, email, region) VALUES (?, ?, ?)"); for ($i = 1; $i <= 500; $i++) { $stmt->execute(["Customer $i", "customer$i@example.com", $regions[array_rand($regions)]]); } $stmt = $rs->prepare("INSERT INTO products (name, category, price) VALUES (?, ?, ?)"); for ($i = 1; $i <= 200; $i++) { $stmt->execute(["Product $i", $categories[array_rand($categories)], round(mt_rand(499, 29999) / 100, 2)]); } $stmtOrder = $rs->prepare("INSERT INTO orders (customer_id, status) VALUES (?, ?)"); $stmtItem = $rs->prepare("INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)"); for ($i = 1; $i <= 2000; $i++) { $stmtOrder->execute([mt_rand(1, 500), $statuses[array_rand($statuses)]]); $orderId = $rs->lastInsertId(); for ($j = 0; $j < mt_rand(1, 5); $j++) { $stmtItem->execute([$orderId, mt_rand(1, 200), mt_rand(1, 10), round(mt_rand(499, 29999) / 100, 2)]); } } echo "Seeded: 500 customers, 200 products, 2000 orders.\n\n"; // --- The query --- $query = " SELECT p.category, c.region, COUNT(DISTINCT o.id) AS total_orders, SUM(oi.quantity * oi.unit_price) AS revenue FROM order_items oi JOIN orders o ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id JOIN customers c ON o.customer_id = c.id WHERE o.status = ? GROUP BY p.category, c.region ORDER BY revenue DESC "; // Run once so Readyset sees it, then cache it $stmt = $rs->prepare($query); $stmt->execute(['completed']); $stmt->fetchAll(); $admin->exec("CREATE CACHE FROM $query"); echo "Cache created.\n"; // Warm the cache $stmt = $rs->prepare($query); $stmt->execute(['completed']); $stmt->fetchAll(); usleep(200000); // --- Benchmark --- $iterations = 500; $stmt = $db->prepare($query); $start = microtime(true); for ($i = 0; $i < $iterations; $i++) { $stmt->execute(['completed']); $stmt->fetchAll(); } $mysqlTime = (microtime(true) - $start) / $iterations * 1000; $stmt = $rs->prepare($query); $start = microtime(true); for ($i = 0; $i < $iterations; $i++) { $stmt->execute(['completed']); $stmt->fetchAll(); } $rsTime = (microtime(true) - $start) / $iterations * 1000; echo sprintf("\nMySQL direct: %.3f ms avg (%d iterations)\n", $mysqlTime, $iterations); echo sprintf("Readyset cached: %.3f ms avg (%d iterations)\n", $rsTime, $iterations); echo sprintf("Speedup: %.1fx faster\n", $mysqlTime / $rsTime); ``` ## What About Writes? Writes pass straight through Readyset to MySQL. No configuration needed — INSERTs, UPDATEs, and DELETEs go upstream automatically. The shallow cache refreshes on its TTL cycle, so new data shows up within seconds: ``` $stmt = $pdo->prepare("INSERT INTO products (name, category, price) VALUES (?, ?, ?)"); $stmt->execute(['New Widget', 'electronics', 39.99]); // After the TTL refresh (~10 seconds), the cached query includes the new row ``` I verified this in every test run — after the TTL window, `New Widget` consistently appeared in the cached results with no manual invalidation. If you need writes to appear instantly in cached results, switch to deep caching (`CACHE_MODE=deep`), which uses the MySQL binlog to update caches incrementally. The trade-off is that deep caching only supports a subset of query patterns, whereas shallow caching works with any SELECT. However, the **DEEP caching** is a topic for another blog post. ## Using Readyset with Laravel Update your `.env` and you’re done: ``` DB_HOST=127.0.0.1 DB_PORT=3307 DB_DATABASE=myapp DB_USERNAME=root DB_PASSWORD=readyset ``` Eloquent, the query builder, raw queries — they all work through Readyset without changes. Cache the expensive ones through `SHOW PROXIED QUERIES` and move on. ## A Few Things I Learned Along the Way **Start with the expensive queries.** The simple single-table lookup (`SELECT name, price FROM products WHERE category = ?`) only showed a 1.5–2x improvement because MySQL already handles it quickly (~0.1ms). The big wins come from joins, aggregations, and anything that makes the database do real work. **Readyset exposes Prometheus metrics.** Hit `http://localhost:6034/metrics` to get cache hit rates, latency distributions, and replication lag. Wire that into Grafana and you have full observability. **Shallow caching works with everything.** I didn’t hit a single query that shallow caching couldn’t handle. Deep caching has restrictions around certain functions and subqueries, but shallow just works. *Readyset is source-available under the [Business Source License 1.1 (BSL 1.1)](https://github.com/readysettech/readyset/blob/main/LICENSE) — you can use it freely in production at no cost. The code is available at [github.com/readysettech/readyset](https://github.com/readysettech/readyset).* *We’d love to see what you build with Readyset. If you run a proof of concept or benchmark with your own workload, share your results with us — real-world feedback is how we make this better. Reach out at [readyset.io](https://readyset.io) or open a discussion on [GitHub](https://github.com/readysettech/readyset/discussions).* --- ### Technical Analysis: Why 0.2ms Queries Can Still Result in Low QPS - URL: https://anotherboringtechblog.com/2026/03/technical-analysis-0-2ms-queries-low-qps/ - Published: 2026-03-29 - Topic: Linux It is a classic trap: you spend a week fine-tuning your cache and your database hits only to realize your application is essentially running with the handbrake on. I saw this with my tests: MySQL and Readyset were returning rows in **0.2 ms,** but the application throughput remained capped at 40 queries per second (QPS). The database isn’t the bottleneck, and the cache isn’t the bottleneck. Your application code is simply leaving performance on the table. While running some demos recently, I found that a Node.js service went from 40 QPS to over 6,000 QPS, which is a **150x improvement** without changing a single database setting or query plan. Here is a post-mortem of how application-layer bottlenecks can neutralize even the most aggressive infrastructure wins. ## The “Fairness” Trap: Event Loop Lag In Node.js and similar async runtimes we are taught to “be nice” to the event loop. We use `setImmediate()` or `yield` to ensure our heavy loops do not starve I/O or HTTP handlers. The intent is noble, but the result can be catastrophic when dealing with high-performance backends. In my demo the worker yielded after every single query. The problem is that `setImmediate` does not execute “immediately”; it schedules the callback for the next iteration of the event loop after all I/O polling and microtask queues are processed. If your event loop has any pressure from Prometheus metrics or health checks that yield might take **3.4 ms** to return. If your query takes 0.2 ms but your yield takes 3.4 ms you are spending **95% of your time waiting for the application to wake back up**. The math is simple: a 3.6 ms total iteration time (3.4 ms yield + 0.2 ms query) creates a theoretical ceiling that no amount of database tuning can break. **The Fix:** Do not yield on every iteration. Batch your work instead. Yielding every 64 or 128 iterations keeps the app responsive without paying the “Event Loop Tax” on every single row. ## The Defensive Sleep We have all written code that says if there is no work to do then sleep for 100 ms so we do not spin the CPU. It is a standard safety measure for pollers and background workers. But what happens when a configuration change or a specific filter makes that “empty” state common? In my debugging session a random 20% of iterations were hitting a “no work found” branch that triggered a `setTimeout(100)`. - 80% active work × 0.17 ms = 0.136 ms - 20% empty branch × 100 ms = 20 ms - Total average time per loop is ~20.14 ms which equals **~50 QPS max** This bug is a chameleon. It looks like a slow database because the throughput is low but the database is not even being called. It is a defensive “nothing to do” sleep that accidentally becomes the primary bottleneck. **The Fix:** Audit your `setTimeout` and `setInterval` calls. If a worker finds no work in its primary queue have it check a fallback or revalidate its state before it commits to a 100 ms nap. ## Connection Pool Friction Connection pooling is a best practice for a reason but it is not free. Every time you call `pool.getConnection()` the driver has to check the pool for an available connection and potentially validate it via a hidden `SELECT 1`. On a local network, this is negligible. But move your app to the cloud, where there is cross-AZ latency or TLS handshakes, and suddenly checking out a connection for a 0.2 ms query triples your total latency. **The Fix:** If you have a dedicated background worker, stop checking connections in and out of the pool for every single query. Hold a single connection for the duration of the worker lifecycle to eliminate the checkout overhead. ## Post-Mortem: How to Audit Your Own Stack If you suspect your app is the bottleneck, stop looking at the database dashboard and run these tests: - **Benchmark the Raw Path:** Run a tight loop with no yields and no sleeps against the DB from the app server. - **Measure Event Loop Lag:** Use `prom-client` to track `nodejs_eventloop_lag_seconds`. Anything above 1 ms means your yields are expensive. - **The Sleep Audit:** Search your codebase for `setTimeout` inside hot paths or worker loops. Ask what happens when that branch becomes the common case due to a configuration change. - **The AI Code Review:** If your logic was generated by an LLM, pay extra attention to async patterns. AI models are trained to be “safe” and often over-insert defensive yields or sleeps that prioritize system stability over maximum throughput. Always validate that AI-generated loops aren’t introducing accidental “dead time” between high-speed database calls. Optimization is a full-stack game. You can have the fastest database or the most aggressive cache in the world, but if your application logic introduces milliseconds of “dead time” between calls, your infrastructure ROI will stay near zero. Do not just tune the engine. Check if the brakes are rubbing. --- ### Determining the Correct innodb_buffer_pool_chunk_size - URL: https://anotherboringtechblog.com/2026/02/innodb-buffer-pool-chunk-size-calculator/ - Published: 2026-02-28 - Topic: MySQL A common source of instability in MySQL environments is the unexpected growth of memory usage at startup. You might set your `innodb_buffer_pool_size` to 16GB, only to find the process consuming 18GB or 20GB. This happens because MySQL enforces a strict geometric relationship between the **total size, the number of instances, and the chunk size**. If these parameters are not aligned, the storage engine automatically rounds the total size upward. To maintain control over your server’s RAM, you must define `innodb_buffer_pool_chunk_size` with precision. ### **The Alignment Logic** The InnoDB buffer pool is organized into a hierarchy: the pool is split into **instances** to improve concurrency, and each instance is composed of **chunks**. According to the MySQL technical specifications, the total buffer pool size must be a multiple of this specific calculation: > **Unit = innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances** If your configuration is misaligned, MySQL increases the total size until it becomes a multiple of that unit. If you are running on a cloud instance with tight RAM limits, this “rounding up” can trigger an OOM (Out of Memory) killer and crash your database. ### **The “Fixed-Grid Floor” Analogy** To visualize this, imagine you are tiling a floor, but with a catch: - **Buffer Pool Size** is the floor area you want to cover. - **Instances** represent the number of rows in your grid. - **Chunk Size** is the size of each tile. MySQL requires every row to be **identical**. If you pick a tile size that doesn’t fit your floor area perfectly across all rows, MySQL doesn’t “cut” the tiles to fit. Instead, it **expands the walls of the room** until the last full tile fits. To keep your database within its physical memory “walls,” you must pick a tile size (Chunk Size) that divides your floor area perfectly across your required rows (Instances). ### Interactive Setup Optimizer Use the tool below to solve for the **Chunk Size**. It will find the largest integer value that keeps your Buffer Pool Size exactly where you want it while ensuring you stay under the 1,000-chunk performance limit. ### InnoDB Chunk Optimizer Target Buffer Pool Size (GB) Buffer Pool Instances Optimize Configuration # Current Default (128MB Chunk) Risks: — — Recommended innodb_buffer_pool_chunk_size (MB) # Optimized System Interpretation Final Pool Size: — Total Chunk Count: — Generated my.cnf Settings --- ### MySQL 8.4 Memory Limits: tmp_table_size vs. temptable_max_ram - URL: https://anotherboringtechblog.com/2025/11/mysql-8-4-temptable-memory-limits/ - Published: 2025-11-20 - Topic: MySQL I recently got into a bit of a debate about standardizing MySQL 8.4’s internal temporary table configuration. We wanted to cap memory usage efficiently, but relying on “rules of thumb” isn’t enough when production stability is at stake. The main confusion was about how **per-query** limits fight with **global** limits. So, rather than guessing, I decided to break a sandbox environment to see exactly where the bytes go—and confirm the findings against the official documentation. ### The Theory: Individual vs. Collective Before I run the scripts, let’s establish the rules based on the MySQL 8.4 Reference Manual. There is a critical distinction between “individual” and “collective” limits. #### 1. `tmp_table_size` (The Cup) This is the limit for a single table. According to the [documentation](https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html): > *“Defines the maximum size of any individual in-memory internal temporary table created using the TempTable storage engine.”* If a single query tries to exceed this: > *“When the limit determined by tmp_table_size is reached, MySQL automatically converts the in-memory internal temporary table to an InnoDB on-disk internal temporary table.”* This confirms the first premise: this variable protects us from **individual** bad queries. #### 2. `temptable_max_ram` (The Bucket) This is the global limit. The [documentation](https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html) states: > *“Defines the maximum amount of RAM that can be used by the TempTable storage engine before it starts allocating space from memory-mapped files or before MySQL starts using InnoDB on-disk internal temporary tables…”* It also clarifies the relationship between the two: > *“The tmp_table_size limit is intended to prevent individual queries from consuming an inordinate amount of global TempTable resources…”* **The “Cup vs. Bucket” Interaction:** The manual explicitly defines what happens when these two interact: > *“If tmp_table_size is less than temptable_max_ram, it is not possible for an in-memory temporary table to use more than tmp_table_size.”* **The Theory:** If we set the global bucket (`temptable_max_ram`) to 2GB and the individual cup (`tmp_table_size`) to 256MB, queries shouldn’t go beyond that 2GB of RAM when using temp space collectively. **The Reality:** If you have 10 queries running, and each needs ~200MB, they fit in RAM (Total: 2GB). But if an 11th query shows up, the global `temptable_max_ram` limit is hit. Once that global limit is hit, the behavior depends on your configuration. ### The Setup To test this without needing terabytes of RAM, I set the limits ridiculously low to force the behavior immediately. **The Config:** ``` -- 1. Set the storage engine to TempTable (default in 8.4) SET GLOBAL internal_tmp_mem_storage_engine = TempTable; -- 2. Set the INDIVIDUAL table limit to 16MB SET GLOBAL tmp_table_size = 16777216; -- 16MB -- 3. Set the GLOBAL RAM limit to 32MB (So only ~2 maxed out tables can fit) SET GLOBAL temptable_max_ram = 33554432; -- 32MB -- 4. Turn OFF mmap to test the default behavior first SET GLOBAL temptable_use_mmap = OFF; ``` **The Query:** I used a CTE to generate some data (just enough to overflow the 16MB individual limit) and forced a temp table using `ORDER BY RAND()`. ``` SELECT * FROM ( WITH RECURSIVE cte (n, str) AS ( SELECT 1, REPEAT('X', 1000) UNION ALL SELECT n + 1, REPEAT('X', 1000) FROM cte WHERE n < 20000 ) SELECT * FROM cte ) AS t1 ORDER BY RAND(); ``` I ran this in parallel 50 times: ``` for i in {1..50}; do ( mysql -e "source script.sql" > /dev/null ) & done ``` ### The Results Checking the Performance Schema confirmed the behavior. ``` SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME LIKE 'Created_tmp%'; ``` **Output:** ``` +-------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------+ | Created_tmp_disk_tables | 39 | | Created_tmp_files | 120 | | Created_tmp_tables | 64 | +-------------------------+----------------+ ``` The `Created_tmp_disk_tables` count went up. This aligns with the docs: > *“When an internal temporary table is created on disk, the server increments the Created_tmp_disk_tables value.”* ### The “Invisible File” Mystery Next, I wanted to test `temptable_use_mmap = ON`. According to the documentation, this setting changes the overflow mechanism: > *“Controls whether the TempTable storage engine allocates space from memory-mapped files or MySQL uses InnoDB on-disk internal temporary tables when the limit determined by temptable_max_ram is exceeded.”* I enabled it and ran the test again. But here is the catch: **You can’t see the files.** The documentation explains exactly why `ls -la /tmp` shows nothing: > *“Temporary files are deleted immediately after they are created and opened, and therefore do not remain visible in the tmpdir directory. The space occupied by temporary files is held by the operating system while temporary files are open.”* Furthermore, standard metrics fail us here: > *“Due to a known limitation, Created_tmp_disk_tables does not count on-disk temporary tables created in memory-mapped files.”* To prove this was actually working, I had to use the Performance Schema instruments recommended by the manual (`memory/temptable/physical_disk`) and write a Linux-level script to monitor `/proc` file descriptors. **The Monitor Script:** ``` PID=4056647 TMP_PATH="sandboxes/msb_8_4_6/tmp" echo "Monitoring PID $PID for temp files in $TMP_PATH..." while true; do # 1. Find all File Descriptors (FD) pointing to deleted files in your tmpdir for fd_path in $(find /proc/$PID/fd -lname "*$TMP_PATH*deleted*" 2>/dev/null); do # Get the actual filename file_target=$(readlink "$fd_path") # Skip if the file is already gone (race condition) if [ -z "$file_target" ]; then continue; fi clean_name=$(echo "$file_target" | sed 's/ (deleted)//') # 2. Check if this specific file exists in the process's memory map if grep -Fq "$clean_name" /proc/$PID/maps; then echo "[$(date +%T)] FOUND: $clean_name is MEMORY MAPPED (mmap ON)" else echo "[$(date +%T)] FOUND: $clean_name is STANDARD DISK I/O (InnoDB/mmap OFF)" fi done sleep 0.5 done ``` **The Logs:** ``` [18:06:34] FOUND: [temp_file_handle] is MEMORY MAPPED (mmap ON) [18:06:34] FOUND: [temp_file_handle] is MEMORY MAPPED (mmap ON) [18:06:35] FOUND: /home/.../tmp/#32506356 is STANDARD DISK I/O (InnoDB/mmap OFF) [18:06:35] FOUND: /home/.../tmp/#32506359 is STANDARD DISK I/O (InnoDB/mmap OFF) ``` This output confirms the hybrid behavior we expected: - **Standard Disk I/O:** Queries exceeding `tmp_table_size` (16MB) were booted to InnoDB. - **Memory Mapped:** Queries that fit individually but hit the Global limit (32MB) spilled to mmap files. ### The Verdict We confirmed that `temptable_use_mmap` does exactly what it says—it changes the overflow mechanism for the global limit. However, you probably shouldn’t use it. The documentation is blunt: > *“temptable_use_mmap is deprecated; expect support for it to be removed in a future version of MySQL.”* Since it’s deprecated, standardization is a bad idea. **The recommendation:** - **Keep `temptable_max_ram` high (1GB+):** The docs define the default as *“3% of the total memory available on the server, with a minimum of 1 GB”*. Don’t starve the global pool. - **Tune `tmp_table_size` individually:** If you need to control memory, do it here. Let the bad queries spill to disk (InnoDB) individually without punishing the rest of the server. ### Wrapping Up The biggest insight here isn’t just about memory settings—it’s about understanding the scope of your tools. We proved that `temptable_max_ram` acts as a safety valve for the entire server, while `tmp_table_size` is your per-query disciplinarian. The recommendation is to stick to the defaults for the engine, keep your global limits healthy (1GB+), and focus on tuning your bad queries. See you next time! --- ### Checking Your MySQL Server Before an Upgrade with MySQL Shell - URL: https://anotherboringtechblog.com/2025/11/mysql-upgrade-checker-mysql-shell/ - Published: 2025-11-02 - Topic: MySQL Upgrading MySQL is often seen as straightforward — install the new binaries, restart, done. In reality, it’s more like upgrading a production jet engine mid-flight. Small changes in defaults, deprecated settings, or old privileges can lead to surprises you don’t want at 2:00 AM on maintenance night. Or even worse, see your performance deteriorating during the peak of business activity. Fortunately, MySQL Shell ships with a utility to help validate whether your server is actually ready before you pull the trigger: [util.checkForServerUpgrade()](https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-shell-utilities-upgrade.html). In this post, we’ll walk you through the installation of MySQL Shell and running the upgrade checker. ## **Installing MySQL Shell** If you don’t already have MySQL Shell available on your server, install it first. Packages are available at: [https://dev.mysql.com/downloads/shell/](https://dev.mysql.com/downloads/shell/) On RHEL-based systems, you can just download and install the RPM: ``` yum install https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.4.4-1.el9.x86_64.rpm ``` If your [MySQL repo](https://dev.mysql.com/downloads/repo/yum/) is already configured: ``` yum install mysql-shell ``` Nothing fancy here — just a standard installation. You can also install MySQL Shell using the [Percona repositories](https://docs.percona.com/percona-distribution-for-mysql/8.4/install-pdps.html#install-percona-distribution-for-mysql-packages). ## **Running the Upgrade Checker** With MySQL Shell installed, we can start validating our server. The checker looks at things like: - Deprecated features and settings - Schema definitions that won’t be compatible - Privileges that will disappear - System variables with new defaults - Storage engine checks - Authentication method changes To run it, connect and run the util.checkForServerUpgrade()  helper. Here are a few common examples. ### **Standard Syntax** ``` mysqlsh -- util checkForServerUpgrade root@localhost:3306 --target-version=8.0.40 --config-path=/etc/percona-server.conf.d/mysqld.cnf ``` Or with flags: ``` mysqlsh -- util check-for-server-upgrade --user=root --host=localhost --port=3306 --target-version=8.0.40 --config-path=/etc/percona-server.conf.d/mysqld.cnf ``` Or inline: ``` mysqlsh -e "util.checkForServerUpgrade('root@localhost:3306', {'targetVersion':'8.0.40','configPath':'/etc/percona-server.conf.d/mysqld.cnf'})" ``` If your environment prefers sockets — often the case for local admin checks — here’s an example: ``` MySQL localhost JS > util.checkForServerUpgrade( 'root@localhost?socket=(/mysql/data/mysqld.sock)', { "password":"msandbox", "targetVersion":"8.0.40", "configPath":"/etc/mysql/conf.d/mysqld.cnf" } ) ``` **Note:** Your target version must be ≤ the version of MySQL Shell you’re using. If you’re checking for 8.0.40, use Shell 8.0.40 or newer. ## **Example Output** Running the checker generates a report that summarizes issues and their severity. A typical result looks like: ``` Errors: 6 Warnings: 397 Notices: 2 ERROR: 6 errors were found. Please correct these issues before upgrading to avoid compatibility issues. ``` And an example of a warning: ``` 22) New default authentication plugin considerations (defaultAuthenticationPlugin) Warning: The default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file: [mysqld] default_authentication_plugin=mysql_native_password However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security. MySQL 8.4.0 removes the deprecated default_authentication_plugin option. The deprecated mysql_native_password authentication plugin is disabled by default as of MySQL 8.4.0, and is subject to removal in a future version. If you are using replication please take time to understand how the authentication plugin changes may impact you. More information: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html ``` Most warnings tend to involve system variables whose defaults change. For example: ``` 17) System variables with new default values (sysVarsNewDefaults) Warning: Following system variables that are not defined in your configuration file will have new default values. Please review if you rely on their current values and if so define them before performing upgrade. back_log - default value will change. character_set_server - default value will change from latin1 to utf8mb4. collation_server - default value will change from latin1_swedish_ci to utf8mb4_0900_ai_ci. event_scheduler - default value will change from OFF to ON. explicit_defaults_for_timestamp - default value will change from OFF to ON. innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to 2 (interleaved). innodb_flush_method - default value will change from NULL to fsync (Unix), unbuffered (Windows). innodb_flush_neighbors - default value will change from 1 (enable) to 0 (disable). innodb_max_dirty_pages_pct - default value will change from 75 (%) 90 (%). innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10 (%). innodb_undo_log_truncate - default value will change from OFF to ON. innodb_undo_tablespaces - default value will change from 0 to 2. log_bin - default value will change from OFF to ON. log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning). log_slave_updates - default value will change from OFF to ON. master_info_repository - default value will change from FILE to TABLE. max_allowed_packet - default value will change from 4194304 (4MB) to 67108864 (64MB). max_error_count - default value will change from 64 to 1024. optimizer_trace_max_mem_size - default value will change from 16KB to 1MB. performance_schema_consumer_events_transactions_current - default value will change from OFF to ON. performance_schema_consumer_events_transactions_history - default value will change from OFF to ON. relay_log_info_repository - default value will change from FILE to TABLE. server_id - default value will change from 0 to 1. slave_rows_search_algorithms - default value will change from 'INDEX_SCAN, TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'. table_open_cache - default value will change from 2000 to 4000. transaction_write_set_extraction - default value will change from OFF to XXHASH64. binlog_transaction_dependency_tracking - default value will change from COMMIT_ORDER to WRITESET. group_replication_consistency - default value will change from EVENTUAL to BEFORE_ON_PRIMARY_FAILOVER. group_replication_exit_state_action - default value will change from READ_ONLY to OFFLINE_MODE. innodb_adaptive_hash_index - default value will change from ON to OFF. innodb_buffer_pool_in_core_file - default value will change from ON to OFF. innodb_buffer_pool_instances - default value will change from 8 (or 1 if innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4). innodb_change_buffering - default value will change from all to none. innodb_doublewrite_files - default value will change from innodb_buffer_pool_instances * 2 to 2. innodb_doublewrite_pages - default value will change from innodb_write_io_threads to 128. innodb_flush_method - default value will change from fsynch (unix) or unbuffered (windows) to O_DIRECT. ``` And the example of an error: ``` 36) Check for deprecated temporal delimiters in table partitions. (deprecatedTemporalDelimiter) Error: The following columns are referenced in partitioning function using custom temporal delimiters. Custom temporal delimiters were deprecated since 8.0.29. Partitions using them will make partitioned tables unaccessible after upgrade. These partitions need to be updated to standard temporal delimiters before the upgrade. [...] More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-29.html#mysqld-8-0-29-deprecation-removal https://dev.mysql.com/doc/refman/en/datetime.html ``` ## **Takeaways** A few key notes before pushing the upgrade button: - Always run the upgrade checker before major changes - Match or exceed the target version with your MySQL Shell version - Review warnings — especially defaults that change under the hood - Fix real blockers before your maintenance window - Test in a staging environment when possible This tool doesn’t replace a full upgrade plan — backups, rollback path, replication considerations, plugin compatibility, and app testing still matter — but it’s a solid early line of defense.   See you next time! --- ### My Impressions from Oracle AI World 2025 – MySQL, AI, and Its Open Source Future - URL: https://anotherboringtechblog.com/2025/10/mysql-ai-world-2025-impressions/ - Published: 2025-10-17 - Topic: MySQL **Note: The opinions below represent my personal perspective only. They don’t include any confidential information or represent the views of Oracle or my current employer, Percona.**   The question I brought home after this conference is: **Was this an AI conference because everyone is already using AI, or because they want you to use AI?****** For context, I’ve worked with MySQL databases for more than 12 years, mainly focused on the open-source ecosystem. However, as an [Oracle ACE Director](https://ace.oracle.com/ords/ace/profile/vgripp987), I also keep an eye on what’s happening with the enterprise and cloud offerings. Understanding what happens outside — even in the PostgreSQL world — helps me see how MySQL differentiates itself and maintains its position as the leading open-source database, although it has lost ground to Postgres in recent years. Below are my impressions from **Oracle AI World 2025** and what I expect for MySQL’s future (both open-source and commercial). * ### **The event** I’ve just finished my journey at Oracle AI World, and while the experience is still fresh, I decided to write down my thoughts. It was my first time at a conference of this scale — more than 20,000 attendees, great energy, and positivity. I really loved it, and I recommend that everyone go. On the technical side, multiple simultaneous sessions covering every part of Oracle’s ecosystem — all under one main focus: **AI**. If you look at the press coverage, most of it echoes Oracle’s message: - [Analyst takeaways – Oracle AI World](https://finance.yahoo.com/news/analyst-takeaways-oracle-ai-world-121444901.html) - [NVIDIA and Oracle to Accelerate Enterprise AI and Data Processing](https://blogs.nvidia.com/blog/nvidia-oracle-accelerate-enterprise-ai-data-processing/) - [Oracle confirms Meta Cloud deal](https://www.cnbc.com/2025/10/16/oracle-confirms-meta-cloud-deal-.html) - [Oracle Advances Enterprise AI with New Agents Across Fusion Applications](https://www.oracle.com/news/announcement/ai-world-oracle-advances-enterprise-ai-with-new-agents-across-fusion-applications-2025-10-15/) - [MySQL HeatWave feature announcements at Oracle AI World](https://blogs.oracle.com/mysql/post/mysql-heatwave-feature-announcements-at-oracle-ai-world) Billions of dollars are being invested in datacenter infrastructure and AI. The close relationship between Oracle and Nvidia stands out — a kind of symbiosis where one is both supplier and customer of the other. From the outside, it’s hard to tell who benefits more. The same applies to Nvidia and OpenAI — in fact, almost every major AI conversation today seems to have Nvidia’s influence behind it. ### **MySQL and AI — where do we stand?** Now, back to what interests us: **MySQL**. Given Oracle’s clear strategic focus on AI, it’s no surprise that MySQL followed that direction too. And I was not wrong — we saw several MySQL/Heatwave sessions during the event, as shown in the picture below. *(fig. MySQL HeatWave schedule)* One of the key announcements was **MySQL AI for on-premises**, available only in the Enterprise Edition. It’s an interesting solution because it allows AI to run on CPUs instead of GPUs — not the fastest option, but it’s good to see flexibility in deployment. Unfortunately, the **community edition** doesn’t include support for this feature, and current vector datatype support remains limited. There’s a noticeable gap between community and enterprise versions, and an even wider one when comparing with [**HeatWave**](https://www.oracle.com/heatwave/) on **OCI**. I also participated in a session titled “**MySQL Panel: Experts and Rock Stars,**”* where we discussed open challenges MySQL users face today. It was a refreshing conversation with attendees about upgrades, optimization, and how the community can collaborate to improve the technology. ### **Natural Language and AI integration** Many sessions showcased MySQL’s ability to interact with AI agents and process **natural language queries**, including the new [NL2SQL](https://blogs.oracle.com/mysql/post/natural-language-to-sql-available-with-mysql) feature. It’s promising technology, though I’m still unsure about real-world adoption at the current moment. I spoke to a few companies using it for customer support and educational use cases — mainly to enhance their knowledge base and offer a personalized experience to their users. Yet, all confirmed a common pattern: **AI assists, but humans verify.** The goal remains productivity, not replacement. ### **The open-source gap** The elephant in the room is the growing distance between the **enterprise/Heatwave** and **community** editions. Oracle’s commercial focus is understandable — large investments need to generate returns — but this separation limits community innovation. It’s understandable that Oracle, like any company, focuses on profitability — these are massive investments. Still, some companies have consistently contributed to the ecosystem, such as **Percona**, **Uber**, and **Facebook**, while others like **AWS** simply replace MySQL with **Aurora**, apply their own changes, and contribute little back. It’s not easy to balance, and I don’t claim to have a clear answer. But as a user, ACE Director, and advocate for the technology, I believe narrowing the gap between the **Community Edition** and **OCI/HeatWave** would benefit everyone. Developers are often the ones who truly drive technology forward, relying on open-source environments to test ideas, learn, and experiment freely. Even with OCI’s free tier, the experience isn’t the same. Ideally, most companies should have the ability to access **MySQL AI features** locally to explore and test the technology — even if their infrastructure isn’t powerful enough to run AI workloads efficiently. This experimentation phase is essential for technological maturity within any organization; as the company evolves, its needs and workloads naturally move toward more optimized environments. That’s where users and organizations ultimately gravitate toward cloud providers offering dedicated AI infrastructure. It’s the natural order of things — yet this is precisely where the **MySQL Community Edition** is losing momentum and breaking the innovation chain. ### **Business reality vs. open source potential** From what I see, Oracle’s aggressive quarterly goals and financial expectations suggest the **community edition** might not receive the same level of attention as before. I don’t expect any drastic structural changes, such as transferring MySQL to a neutral foundation (like the Linux Foundation). Still, that could be a strategic move — maintaining a strong user base, reducing costs by encouraging community contributions, and continuing to profit from the **enterprise and OCI** versions. Maybe this is an idealistic view, but I see more long-term value and sustainable business in a more open approach. When we look at **PostgreSQL**, it’s clear that its openness drives innovation. The **pgvector** extension ([https://github.com/pgvector/pgvector](https://github.com/pgvector/pgvector)) is a good example — much more advanced today than MySQL’s community current vector implementation. In practice, the real competitor of Postgres would be MySQL Enterprise in the AI field. And it is a fact that once an ecosystem becomes mature and self-sustaining inside a company, switching becomes hard, as it would require rewriting the application and building knowledge on the new database technology. So in the end, it is unlikely a company that opted for the Postgres open source version would migrate to MySQL Heatwave. ### **Final reflections** Despite these challenges, the **MySQL community** remains vibrant, with over **1 billion installations worldwide**. I’m still very enthusiastic about the product and confident about its future. MySQL can reach another golden era — it only requires Oracle to listen more closely to the community and bridge both worlds. For now, my next stop is **FOSDEM 2026** ([https://fosdem.org/2026/](https://fosdem.org/2026/)), where we’ll have another strong lineup for MySQL. See you there! --- ### Benchmarking MySQL Server With mysqlslap: Estimating Capacity, Not Absolute Limits - URL: https://anotherboringtechblog.com/2025/04/mysql-server-benchmarking-guide/ - Published: 2025-04-25 - Topic: MySQL When I’m working with MySQL, I care not just about whether a single query is slow, but how much total load the server can handle before performance starts to drop. Tools like `mysqlslap` help by generating synthetic workloads to simulate multiple clients and measure the server’s Queries Per Second (QPS). In this post, I’ll walk through a script I used to benchmark MySQL server performance with `mysqlslap`, and discuss when it makes sense to use it over more advanced tools like sysbench. I originally used this script in a blog post published at ReadySet: [Vertical Scaling of MySQL vs. Horizontal Scaling with ReadySet](https://readyset.io/blog/vertical-scaling-of-mysql-vs-horizontal-scaling-with-readyset-a-cost-and-performance-analysis-in-the-cloud). ## **Why Benchmark a MySQL Server?** Benchmarking gives a rough idea of how a MySQL server performs under load, but it’s not a guaranteed measure of full capacity. It can help you estimate: - How many concurrent connections the server can handle before performance starts to drop. - The approximate QPS limit before queries get slower. - The potential gains from adding more CPU, memory, or better hardware. - How much impact tuning settings like `innodb_buffer_pool_size` have on overall performance. Keep in mind: these tests provide guidance, but real-world workloads are often more complex, and results may vary outside of synthetic benchmarks. ## **Benchmark Setup** For this example, we used: - **Database**: Employees sample dataset (~300,000 rows) - **Server**: MySQL 8.0 running on a regular VM - **Benchmarking Tool**: mysqlslap - **Automation**: A Bash script running 10 different query files at once The idea is simple: - Simulate 200 concurrent client connections - Split that load evenly across 10 different queries - Measure total QPS the server can deliver ## **How the Benchmark Works** The Bash script provided: - Starts one mysqlslap process per query - Runs each query with a controlled concurrency and iteration count - Collects how long it takes to execute - Calculates approximate Queries Per Second (QPS) for each query - Sums up the QPS to estimate server throughput Example run: ``` Running queries with 20 concurrency each... ================== QPS Results ================== q1 : 150.12 QPS q2 : 145.77 QPS q3 : 160.21 QPS q4 : 7.45 QPS ... ------------------------------------------------- Total estimated QPS (sum): 782.00 ================================================= ``` This **782 QPS** number is a rough idea of what the server can handle under this type of load. ## **What mysqlslap Tells You (and What It Doesn’t)** `mysqlslap` gives you a basic sense of: - Overall server throughput (QPS) - How well your server handles concurrency - Whether a bigger instance or more tuning is needed But `mysqlslap` **does not**: - Simulate complex application behavior (mixed reads/writes, transactions) - Show latency percentiles (like 95th, 99th percentiles) - Analyze detailed performance metrics like CPU or IO usage It’s a **basic** benchmarking tool, but sometimes that’s all you need to get useful numbers quickly. ## **mysqlslap vs sysbench** Here’s a straight comparison: **Feature** **mysqlslap** **sysbench** Goal Quick QPS measurement Detailed load testing Setup Very easy, no config needed Needs installation and Lua scripts Types of tests Mostly simple queries Read, write, OLTP, custom transactions Metrics Total time, QPS QPS, latency stats, errors, TPS Best Use Case Quick server capacity check Full performance and stress testing **Summary**: - Use `mysqlslap` if you just want a **quick and rough estimate** of server performance. - Use `sysbench` if you want **more detailed analysis** or **simulate real workloads**. ## **Practical Tips When Using mysqlslap** - **Warm up the server** before benchmarking. Run some queries first so caches are populated. - **Use realistic queries**. If your real application does joins or aggregations, test with those. - **Measure under different settings**. Try different `innodb_buffer_pool_size`, `max_connections`, and so on. - **Monitor server metrics separately**. Use `top` , `htop`, or MySQL’s `SHOW GLOBAL STATUS` during tests to catch CPU or IO bottlenecks. Tools like [pt-stalk](https://docs.percona.com/percona-toolkit/pt-stalk.html) can help you to gather data while the load is going on. ## **Final Thoughts** Benchmarking helps me understand what kind of load a MySQL server can realistically handle. `mysqlslap` won’t give me deep profiling or real-world modeling, but it’s quick, scriptable, and good enough for estimating QPS and spotting performance trends. It tells me: - How much concurrent load the server handles before slowing down - If tuning changes had any real effect - When it might be time to scale up or scale out If I need to go deeper, I bring in `sysbench`. But for many use cases, a quick test with `mysqlslap` gives me what I need to move forward. # **Example Bash Script to Benchmark** Here’s the basic script used to run 10 queries in parallel: ``` #!/bin/bash # mysqlslap benchmark script for MySQL server USER="sysbench" PASS="sysbench" HOST="172.25.31.248" PORT="3306" DB="employees" QUERIES=("q1.sql" "q2.sql" "q3.sql" "q4.sql" "q5.sql" "q6.sql" "q7.sql" "q8.sql" "q9.sql" "q10.sql") TOTAL_CONCURRENCY=200 CONCURRENCY_PER_QUERY=$(( TOTAL_CONCURRENCY / ${#QUERIES[@]} )) ITERATIONS=10 TMP_DIR="./mysqlslap_results" mkdir -p "$TMP_DIR" for QUERY_FILE in "${QUERIES[@]}"; do BASENAME=$(basename "$QUERY_FILE" .sql) OUTPUT_FILE="$TMP_DIR/${BASENAME}.out" mysqlslap -u "$USER" -p"$PASS" -h "$HOST" -P "$PORT" \ --concurrency="$CONCURRENCY_PER_QUERY" \ --iterations="$ITERATIONS" \ --query="$(cat "$QUERY_FILE")" \ --delimiter=";" \ --create-schema="$DB" > "$OUTPUT_FILE" 2>&1 & done wait sed -i '/Using a password on the command line interface can be insecure/d' "$TMP_DIR"/*.out echo "================== QPS Results ==================" TOTAL_QPS=0 for OUTPUT in "$TMP_DIR"/*.out; do QUERY_NAME=$(basename "$OUTPUT" .out) AVG_TIME=$(grep -i 'Average number of seconds to run all queries' "$OUTPUT" | awk -F ':' '{print $2}' | xargs) if [[ -n "$AVG_TIME" ]]; then QPS=$(awk "BEGIN {printf \"%.2f\", ($CONCURRENCY_PER_QUERY * $ITERATIONS) / $AVG_TIME}") printf "%-10s : %10s QPS\n" "$QUERY_NAME" "$QPS" TOTAL_QPS=$(awk "BEGIN {print $TOTAL_QPS + $QPS}") else echo "$QUERY_NAME: QPS not available or query failed" fi done echo "-------------------------------------------------" echo "Total estimated QPS (sum): $TOTAL_QPS" echo "=================================================" ``` I hope you liked it, and see you in the next post! --- ### MySQL Brazil Meetup 2025 - Aniversário de 30 Anos! - URL: https://anotherboringtechblog.com/2025/04/mysql-brazil-meetup-2025/ - Published: 2025-04-14 - Topic: MySQL Parece mentira, mas o **MySQL está completando 30 anos** em 2025. Três décadas de código aberto, performance, confiabilidade (sim, a gente sabe dos bugs também 😅) e uma comunidade incrível que cresce a cada ano. E, claro, **isso merece ser comemorado!****** ### **📍 Onde e quando?** Vamos nos reunir em **São Paulo, no dia 4 de outubro**, para celebrar essa história que mistura tecnologia, comunidade e um pouquinho de teimosia (afinal, quem nunca ficou horas debugando um EXPLAIN que não fazia sentido?). O evento será uma ótima oportunidade para: - Reencontrar a galera da comunidade, - Trocar ideias sobre MySQL, Percona, MariaDB e outras tecnologias open source, - Assistir a palestras técnicas, histórias de guerra de produção e boas práticas, - E, claro, celebrar! ### **🐬 E o golfinho?** Sim, o nosso mascote oficial também estará presente (de alguma forma). Já até arrumou as malas e está a caminho do Brasil — porque comemoração boa é aquela que a gente faz juntos! ### **🚀 Vem mais por aí…** Ainda não podemos revelar tudo, mas já posso adiantar que estamos preparando **muitas surpresas legais**. Fica de olho por aqui, nas redes sociais e nos grupos da comunidade — em breve vamos divulgar a agenda completa, palestrantes e como se inscrever. ### **💬 Participe!** Se você já teve que ajustar o innodb_buffer_pool_size, apagou aquele ibdata1 sem backup (força!), ou simplesmente ama banco de dados, esse evento é pra você. Marca aí na agenda: **🗓 4 de outubro de 2025 – São Paulo****** E bora celebrar juntos esses 30 anos de muita história. Nos vemos lá! 🇧🇷🐬 --- ### Black Friday or Dark Friday? Depends If MySQL Stands the Rush - URL: https://anotherboringtechblog.com/2024/11/mysql-black-friday-survival-checklist/ - Published: 2024-11-26 - Topic: Linux, MySQL ## **Black Friday or Dark Friday? Tips to Ensure Your MySQL Instance Survives****** Black Friday is one of the most demanding days of the year for e-commerce and online services, and your database is at the heart of it all. A slow or offline MySQL instance can turn your Black Friday into a Dark Friday, costing revenue and reputation. Here’s a checklist to prepare your MySQL instance to handle the surge. ### **1. Check Your Slow Query Log and Performance Schema****** Slow queries are a major contributor to performance bottlenecks during periods of high traffic. To identify and address these issues, you can use the **Slow log**, **Performance Schema,** and **sys schema**. The sys schema provides a collection of views designed to simplify query performance analysis. Here are some particularly useful views to focus on: **Sys Schema Tables to Examine:****** •x$statements_with_full_table_scans: Identifies queries causing table scans that slow down performance. •x$statements_with_runtimes_in_95th_percentile: Flags the slowest 5% of queries that might cripple your system under load. •x$statements_with_sorting: Highlights queries that require sorting operations, which can consume resources. •x$statements_with_temp_tables: Points to queries creating temporary tables, often a sign of suboptimal design. For the Slow log analysis, I suggest you look at the [pt-query-digest](https://docs.percona.com/percona-toolkit/pt-query-digest.html). This tool provides a detailed report summarizing the queries in the slow log, helping to identify the queries that consume the most time and resources, allowing for targeted performance improvements. **Action:** Optimize these queries by adding appropriate indexes, revising query structure, or reducing unnecessary data fetching. ### **2. Check Swap Usage****** High swap usage is a red flag. When MySQL starts swapping, performance degrades as disk I/O is significantly slower than RAM. •Use vmstat or free -m to monitor swap usage. You can also use PMM to check swap usage: • If you notice excessive swapping, it may be necessary to fine-tune your MySQL instance. Review the number of connections and session settings that impact memory usage, such as tmp_table_size, join_buffer_size, and sort_buffer_size. Additionally, consider evaluating and adjusting the size of your buffer pool to optimize memory allocation and reduce swapping. In some cases, it might be necessary to increase the memory of the server. **Action:** Ensure swap usage is minimal during stress testing. ### **3. Monitor Critical Metrics****** Having a **monitoring tool** is non-negotiable. Ensure you monitor both **OS-level** and **MySQL-specific** metrics: **Key OS Metrics:****** •**CPU usage**: Watch for spikes that might indicate bottlenecks. •**Memory usage**: Ensure there’s enough free memory to avoid OOM killing the MySQL process. •**Disk I/O**: High disk utilization can signal storage issues. **Key MySQL Metrics:****** •**Threads Running**: Tracks the number of active threads. •**Threads Connected**: Monitors overall database connections. •**Long Running Queries**: Identify queries that exceed acceptable execution times. ### PMM screenshot of the top command counters. Check the demo –> [PMM Demo](https://pmmdemo.percona.com/graph/d/pmm-home/home-dashboard?orgId=1&refresh=1m) **Action:** Tools like [**Percona Monitoring and Management (PMM)**](https://www.percona.com/software/database-tools/percona-monitoring-and-management) or **Zabbix** can help visualize these metrics in real-time. Additionally, you can use **Dolphie**—an open-source project—running in daemon mode to replay workloads and analyze metrics. For instance, Dolphie allows you to replay the process list during peak periods, providing valuable insights. [Learn more about Dolphie](https://anotherboringtechblog.com/2024/11/testing-dolphie/). ### **4. Resize Your Redo Log****** High write workloads on Black Friday can overwhelm the redo log. MySQL 8.0.30 introduced dynamic redo log resizing, making it easier to adjust the size. •Follow the steps in [this guide](https://anotherboringtechblog.com/2023/09/dynamic-redo-log-sizing-mysql-8030/) to resize the redo log dynamically. •Monitor `Innodb_os_log_written` to estimate redo log usage. **Action:** Ensure the redo log is adequately sized to handle your expected transaction volume. ### **5. Prepare for Instance Scaling****** If you’re using VMs, **Amazon EC2**, or **RDS**, you should plan for instance resizing: • Predict traffic spikes based on previous years’ data. • **Proactively scale** your instance before the rush. Downtime during scaling can cost more than the price of a temporary upgrade. **Action:** Scale up before Black Friday, even if you’re unsure. The cost of a larger instance is often negligible compared to the losses from downtime. ### **6. Have a Reliable Backup****** A robust backup strategy is your safety net. During Black Friday, the risk of data corruption or failure increases due to high activity. •Use tools like [**MySQL Shell utilities**](https://dev.mysql.com/doc/mysql-shell/8.4/en/mysql-shell-utilities.html), [**Percona XtraBackup**](https://www.percona.com/mysql/software/percona-xtrabackup), or [**mydumper/myloader**](https://github.com/mydumper/mydumper). •Schedule incremental backups to minimize downtime. •Test your backups before Black Friday to ensure they work. **Action:** Implement a recovery plan and ensure your backups are accessible and tested. Preparation is key to surviving the high demand on Black Friday. By addressing slow queries, monitoring critical metrics, optimizing configurations, and planning for scalability, you can ensure your MySQL instance performs reliably under pressure. Don’t let a lack of preparation turn your Black Friday into a Dark Friday—start today! --- ### How to Identify Processes Using Swap Memory on Linux - URL: https://anotherboringtechblog.com/2024/11/identify-processes-using-swap/ - Published: 2024-11-23 - Topic: Linux Identifying which processes have pages swapped out of memory when troubleshooting performance issues on a Linux system is often helpful. Swapping can impact system performance, so knowing which processes are affected is important in diagnosing and resolving these issues. ### **Checking Swap Usage by Process****** Linux provides detailed process information in the `/proc` directory. By leveraging this, you can extract each process’s swap memory usage with a simple bash command. ### **The Command****** Run the following command in your terminal to display the amount of swap memory used per process: ``` for file in /proc/*/status; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -nr | head -20 ``` **Explanation****** 1. **Iterating through **/proc: The command processes every status file under /proc, which contains metadata about each running process. 2. **Extracting Relevant Information**: The awk command searches for lines containing VmSwap (swap usage in kilobytes) and Name (process name). It prints the process name and its swap usage on the same line. 3. **Sorting Results**: The sort command organizes the output numerically in descending order based on the second column (swap usage). 4. **Filtering Results**: The head command filters the top 20 processes using swap **Sample Output****** Here’s an example of what the output might look like: ``` # for file in /proc/*/status; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -nr | head -20 sssd_kcm 3163528 kB mongod 184936 kB rpc.gssd 184332 kB mongod 163300 kB mysqld 97240 kB mongod 94412 kB mongod 52744 kB mongod 51904 kB mongod 42560 kB mongod 42412 kB mongod 39540 kB mysqld 25768 kB clickhouse-serv 10948 kB dockerd 8112 kB clickhouse-serv 6492 kB containerd 3856 kB datacollector 3036 kB docker-proxy 2616 kB docker-proxy 2412 kB docker-proxy 2152 kB ``` ### **Conclusion****** The above command quickly identifies processes that are consuming swap memory and addresses potential performance bottlenecks. This lightweight, native approach is particularly useful for Linux administrators and developers seeking to optimize system performance without relying on additional tools.   --- ### Testing Dolphie For the First Time: Features and Feedback - URL: https://anotherboringtechblog.com/2024/11/testing-dolphie/ - Published: 2024-11-21 - Topic: MySQL In this blog post, I will test [Dolphie](https://github.com/charles-001/dolphie), an open-source project by [Charles Thompson](https://www.linkedin.com/in/charles-thompson-a27a4661/). Dolphie is an open-source tool that provides real-time analytics for MySQL, MariaDB, and ProxySQL. As a first-time user, my goal is to go from zero to hero, exploring how easy it is to start using the tool while getting familiar with its key features and statistics. ### **Installation** [Dolphie](https://github.com/charles-001/dolphie) offers four installation methods: PyPi, Poetry, Brew, and Docker. There’s also a fifth option if you prefer to compile it yourself. Since I’m using a MacBook, I opted for the Brew installation. My setup is a MacBook M3 running macOS Sequoia 15.1. Installation is straightforward: ``` $ brew install dolphie ``` After installation, you can check the version: ``` $ dolphie --version 6.5.3 ``` The help menu is very comprehensive: ``` $ dolphie --help usage: dolphie [--help] [--tab-setup] [-C ] [-u ] [-p ] [-h ] [-P ] [-S ] [-c ] [-m ] [-l ] [-r ] [--host-cache-file ] [--tab-setup-file ] [--heartbeat-table ] [--ssl-mode ] [--ssl-ca ] [--ssl-cert ] [--ssl-key ] [--panels ] [--graph-marker ] [--pypi-repo ] [-H ] [-R] [-D] [--daemon-log-file ] [--replay-file ] [--replay-dir ] [--replay-retention-hours ] [--exclude-notify-vars ] [--show-trxs-only] [--additional-columns] [--debug-options] [-V] [uri] Dolphie, an intuitive feature-rich top tool for monitoring MySQL in real time [...] ``` ### **Testing** To test Dolphie, I configured MySQL using Docker. This was a standard installation with no customizations: ``` $ docker run -d -p 3306:3306 -it -e MYSQL_ROOT_PASSWORD=vinnie --name mysql mysql:8.0.39 ``` Verifying the setup: ``` $ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES cbe6b8b6d23b mysql:latest "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql ``` To add some workload, I used sysbench: ``` // Prepare the environment $ sysbench --db-driver=mysql --mysql-user=sysbench --mysql-password='sysbench' \ --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-db=test --range_size=100 \ --table_size=10000 --tables=5 --threads=5 --events=0 --time=60 \ --rand-type=uniform /usr/share/sysbench/oltp_read_only.lua --mysql_storage_engine=InnoDB prepare // Running the synthetic workload sysbench --db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \ --mysql-port=3306 --mysql-host=127.0.0.1 --mysql-db=test --range_size=100 \ --table_size=10000 --tables=5 --threads=1 --events=0 --time=6000 \ --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua --db-ps-mode=auto --report-interval=1 run ``` To connect to MySQL with Dolphie, I used the command line: ``` $ dolphie -h 127.0.0.1 -u root -pvinnie ``` ### **My Impressions** When Dolphie runs on the same host as the MySQL database, it’s possible to visualize OS metrics. In my case, since everything is running on my laptop, I used the loopback address to monitor OS resources. Here’s how the overall interface looks: * And here are the queries displayed below: *(fig. Dolphie query details and activity)* Dolphie has many advanced features. Pressing `?` displays an extensive list of commands. For example, pressing `o` reveals the SHOW ENGINE INNODB STATUS (SEIS): *(fig. Dolphie INNODB status panel)* You can even kill a MySQL thread directly from Dolphie: *(fig. Killing MySQL threads using Dolphie)* One of the most interesting features for me is the ability to enable graph metrics panels. These visualizations are impressive: *(fig. Graph metrics panel in Dolphie)* I could spend hours exploring Dolphie’s features, but I’ll leave some for you to discover. From what I’ve experienced, Dolphie is highly customizable. It’s not just a visualization tool; it also allows database interactions, such as killing connections. Unfortunately, I didn’t get the chance to explore its replication metrics, but I’m curious to see how it performs. ### **Conclusion and the Future** It’s worth emphasizing that this tool comes from the community and is fully open-source. Charles has put a lot of effort into this project and actively listens to user feedback. The release cadence is impressively fast—this version was released just four days ago! As proof of his attention to the community (apologies if this was a secret, Charles! 😄), we had a conversation where he shared this insight: Your blog post about investigating fragmented tables inspired me to create this command 🙂* More features are on the way! If I had a wish for Dolphie, I’d love to see the ability to modify dynamic parameters. Coupled with the existing metrics panels, this feature would let users monitor changes in real-time, enabling better performance decisions. ### **Additional Resources****** If you’re interested in learning more about Dolphie, check out this insightful blog post by Frederic Descamps – [Welcome to Dolphie](https://lefred.be/content/welcome-to-dolphie/) Thanks for all your hard work, [Charles](https://www.linkedin.com/in/charles-thompson-a27a4661/)! Long live [Dolphie](https://github.com/charles-001/dolphie)! --- ### Understanding MySQL DDL Space Requirements - URL: https://anotherboringtechblog.com/2024/11/understanding-mysql-ddl-storage-req/ - Published: 2024-11-20 - Topic: MySQL This blog post will explore MySQL’s file storage behavior during DDL operations, particularly with InnoDB tables. Using a practical example, we’ll discuss where and how MySQL stores data during different types of DDL operations. **Setting the Stage: Table Structure****** Consider a table `sbtest1` with 30 million rows, created as follows: ``` CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=30000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ``` This table is actively used in DML operations (e.g., inserts, updates, and deletes), as shown by `sysbench`: ``` [ 22s ] thds: 10 tps: 157.96 qps: 3136.24 (r/w/o: 2188.47/631.85/315.92) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00 ``` **Example 1: Modifying a Column****** Suppose you execute the following DDL operation to modify a column: ``` ALTER TABLE sbtest1 MODIFY COLUMN k BIGINT NOT NULL DEFAULT 0; ``` This **online DDL** operation uses the `COPY` algorithm, creating a temporary table to store the rows during the DDL process. Concurrent DMLs are not allowed during this process. **Observing Files****** A temporary file is created in the same directory as the table: ``` -rw-r-----. 1 user vgrippa 7.0G Nov 19 19:07 sbtest1.ibd -rw-r-----. 1 user vgrippa 4.8G Nov 19 19:53 '#sql-3461b9_13.ibd' ``` The file `#sql-3461b9_13.ibd` is the intermediate table created to perform the COPY operation. **Example 2: Performing a NULL Rebuild operation** Another operation is to rebuild the table in place, which is a very common operation to defragment the table: ``` ALTER TABLE sbtest1 ENGINE=InnoDB; ``` This operation creates a temporary file for the rebuild process: ``` -rw-r-----. 1 user vgrippa 27M Nov 19 20:15 '#sql-ib1068-957745685.ibd' -rw-r-----. 1 user vgrippa 7.1G Nov 19 20:15 sbtest1.ibd ``` The temporary file `#sql-ib1068-957745685.ibd`  is an **intermediate table file** created during the rebuild, requiring space proportional to the table’s size plus its indexes. **Example 3: Adding an Index****** Adding an index with the following DDL allows concurrent DMLs: ``` ALTER TABLE sbtest1 ADD INDEX idx_new_k (k); ``` Temporary files for this **online DDL** are stored in the directory specified by the `--tmpdir` option. These files might not appear in standard directory listings but can be observed using tools like `lsof`. **Observing Temporary Files****** Using `lsof` while the operation runs: ``` mysqld 3432889 user 94u REG 253,8 65536 32908240 /home/user/sandboxes/msb_8_0_30/tmp/ibZzJNo7 (deleted) ``` After completion, the files are automatically deleted. **Customizing Temporary File Storage****** You can specify a custom directory for temporary files using the `innodb_tmpdir` variable. For example: ``` SET GLOBAL innodb_tmpdir = '/path/to/custom/tmpdir'; ``` Performing the same index addition: ``` ALTER TABLE sbtest1 ADD INDEX idx_new_k (k); ``` Temporary files will now appear in the custom directory: ``` mysqld 3432889 user 15u REG 253,8 7651328 /path/to/custom/tmpdir/ibmcFP3W (deleted) ``` **Considerations****** If you encounter issues due to insufficient disk space for temporary sort files during online DDL operations, there are a few strategies to mitigate them: 1. Use `innodb_tmpdir`**:****** Specify a path for temporary files on another mount with sufficient space: ``` SET GLOBAL innodb_tmpdir = '/path/to/large/disk'; ``` 2. Perform the DDL operation “*offline*“ You can use options like `ALGORITHM=COPY` and `LOCK=EXCLUSIVE`. While these operations are blocking, they eliminate the need for temporary sort files and reduce disk space requirements during the operation. Like in **Example 1**, a temporary file will be created in the same table directory. **Key Insights****** 1. **Intermediate Table Files:** Temporary files are often created during DDL operations, such as modifying columns or changing storage engines. These files typically begin with the #sql-ib prefix. 2. **Temporary Sort Files:** When adding indexes, MySQL stores temporary files in the directory specified by `--tmpdir` or `innodb_tmpdir`. 3. **Disk Space Requirements:** Operations that involve rebuilding or copying data require additional disk space equal to or similar to the original table size. **Takeaway****** Understanding MySQL’s file management during DDL operations helps in planning disk space and locating intermediate files during troubleshooting. For more details, refer to the [MySQL Documentation on Online DDL](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-space-requirements.html). --- ### Understanding Latency Through a 100m Race: From CPU to NVMe - URL: https://anotherboringtechblog.com/2024/11/understanding-latency-from-cpu-to-nvme/ - Published: 2024-11-14 - Topic: Linux In the world of computing, latency is everything. Whether you’re optimizing a database query or fine-tuning a high-performance application, understanding how quickly different components of your system respond can make or break performance. But let’s put these abstract nanoseconds and microseconds into a more relatable scenario—a 100m sprint race. 🏃‍♂️🏃‍♀️ ### **The Runners in the Race****** Let’s imagine a 100m race where each participant represents a different component of your system. The finish line? Your application receives the data it needs. **L1 Cache****** Think of this as Usain Bolt, the fastest sprinter on the planet. It takes **0.5 seconds** to complete the race. In computing terms, this represents a **latency of around 0.5 nanoseconds**—very fast! **L2 Cache****** This runner is a strong contender but not quite as fast. It takes **7 seconds** to cross the finish line, equivalent to **7 nanoseconds** in latency. Still, it is incredibly quick and ideal for slightly less critical data. **L3 Cache****** This runner’s **15-20 seconds** of latency represents **15-20 nanoseconds**. While slower, it’s still a high-speed competitor compared to others on the track. **RAM (Main Memory)****** Now we’ve got a middle-distance runner, completing the 100m in **1 minute and 40 seconds**. This translates to **100 nanoseconds** of latency. It’s slower, but for larger data sets, it’s still much faster than storage devices. **NVMe SSD****** Here’s our jogger. Depending on their pace, they finish the race in **30 seconds to 1 minute and 40 seconds**—equivalent to **30,000-100,000 nanoseconds (30-100 microseconds)**. While slower compared to RAM and caches, they’re still leagues ahead of the old-school hard drive (which, let’s be honest, might take all day to finish). ### **Why This Matters for Your Database****** Let’s say you’re running a complex SQL query, and it needs to scan through a large dataset stored on disk. If your database has to fetch data from NVMe instead of RAM or cache, the response time increases significantly. ### **Key Takeaways****** •**Know your runners**: Just like sprinters, joggers, and long-distance runners, different components of your system have different latency levels. •**Optimize your queries**: Keep your most frequent and critical data in fast, low-latency storage (e.g., cache or RAM). •**Design for performance**: Leverage database features like caching, query tuning, and data partitioning to minimize expensive disk I/O operations. In the race for performance, every millisecond counts. Understanding where your data lives and how quickly it can be accessed can help you design systems that are not only fast but also efficient. ### **Balancing Speed and Cost****** While it’s tempting to aim for the fastest possible solution, speed comes at a cost. L1 cache is lightning-fast but extremely expensive and limited in size. RAM offers more capacity at a lower cost but can’t compete with cache for speed. NVMe, while slower, provides a great balance of cost and storage for less frequently accessed data. For more detailed latency numbers, check out this resource: [Common Performance Numbers](https://specbranch.com/posts/common-perf-numbers/).   **🏁 And you, ****who’s winning the latency race in your system?** --- ### How to Add an Invisible Primary Key to an Existing Table in MySQL - URL: https://anotherboringtechblog.com/2024/11/add-invisible-primary-key/ - Published: 2024-11-04 - Topic: MySQL With MySQL 8.0.23, the concept of invisible columns was introduced. Columns marked as invisible do not appear in standard SELECT *  queries and don’t require changes to existing application code. Later, in MySQL 8.0.30, support for automatically generated invisible primary keys (GIPKs) was added for InnoDB tables created without an explicit primary key. This enhancement, controlled by the sql_generate_invisible_primary_key  server variable, allows MySQL to add a GIPK to any table lacking a primary key. However, there are scenarios where an invisible primary key must be manually added to an existing table. **Procedure for Adding an Invisible Primary Key****** We’ll apply a Data Definition Language (DDL) command to the table to add an invisible primary key. Let’s walk through an example. 1. **Inspect the Table Structure****** First, inspect the table to understand its current structure. ``` mysql > SHOW CREATE TABLE vinnie\G ``` Example Output: ``` *************************** 1. row *************************** Table: vinnie Create Table: CREATE TABLE `vinnie` ( `i` int DEFAULT NULL, `c` varchar(50) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ``` 2. **Add the Invisible Primary Key****** Run the following command to add an invisible primary key: ``` ALTER TABLE vinnie ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY INVISIBLE, ALGORITHM=inplace, LOCK=shared; ``` Example Output: ``` Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 ``` 3. **Verify the Change****** After running the command, verify the new structure of the table to ensure the invisible primary key was successfully added: ``` mysql > SHOW CREATE TABLE vinnie\G ``` Example Output: ``` *************************** 1. row *************************** Table: vinnie Create Table: CREATE TABLE `vinnie` ( `i` int DEFAULT NULL, `c` varchar(50) DEFAULT NULL, `id` int NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ``` 4. **Confirm the Data Remains Unaffected****** Ensure the table data remains unchanged: ``` mysql > SELECT * FROM vinnie; ``` Expected Output: ``` +------+------+ | i | c | +------+------+ | 1 | b | | 1 | b | | 1 | b | +------+------+ 3 rows in set (0.00 sec) ``` **Important Considerations****** Adding an invisible primary key has some performance and operational considerations: •**In-Place Rebuild**: When a primary key is added, MySQL rebuilds the table in place, a resource-intensive process. This operation uses ALGORITHM=INPLACE, which can reduce locking time but may not be supported if the columns need conversion to NOT NULL. •**Clustered Index Impact**: Changing the clustered index requires copying table data, making it ideal to define primary keys at table creation rather than altering them later. •**Parameter Adjustments for Heavy Write Workloads**: If the table receives high write traffic, consider increasing the innodb_online_alter_log_max_size parameter to accommodate larger temporary log files during the DDL operation. **Using pt-online-schema-change****** The pt-online-schema-change tool from Percona Toolkit is widely used for making online schema changes with minimal locking. However, adding an invisible primary key with this tool is not recommended due to potential compatibility issues. An attempt to add an invisible primary key using pt-online-schema-change may result in errors, as shown below: ``` $ pt-online-schema-change --alter "ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY INVISIBLE" \ --user=root --password=msandbox --host=127.0.0.1 --port=8033 --execute \ D=test,t=vinnie; ``` Example Output: ``` The new table index PRIMARY would be used for the DELETE trigger, but it uses column id which does not exist in the original table and the original table does not have a PRIMARY KEY or a unique index to use for the DELETE trigger. Dropping new table... Dropped new table OK. ``` **Making the Primary Key Visible****** If you decide to make the invisible primary key visible, use this command: ``` ALTER TABLE vinnie ALTER COLUMN id SET VISIBLE; ``` Confirm the visibility change: ``` mysql > SELECT * FROM vinnie; ``` Expected Output: ``` +------+------+----+ | i | c | id | +------+------+----+ | 1 | b | 1 | | 1 | b | 2 | | 1 | b | 3 | +------+------+----+ ``` **References****** For further details, consult: •[MySQL 8.0 Invisible Columns Documentation](https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html) •[InnoDB Online DDL Operations Documentation](https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html) This guide should equip you to add and manage invisible primary keys in MySQL 8.0+, improving table flexibility while maintaining application compatibility. --- ### How to Monitor and Optimize Fragmented Tables in MySQL - URL: https://anotherboringtechblog.com/2024/09/optimizing-fragmented-tables-in-mysql/ - Published: 2024-09-10 - Topic: MySQL Random insertions or deletions in a secondary index can lead to fragmentation, where the physical order of index pages on disk no longer aligns with the logical order of records. A common sign of fragmentation is that a table occupies more space than expected, though determining the exact amount is challenging. InnoDB stores data and indexes in [B-trees](https://dev.mysql.com/doc/refman/8.4/en/glossary.html#glos_b_tree), with [fill factor](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_fill_factor) ranging from 50% to 100%, contributing to this variability. ### Monitoring Fragmentation Fragmented tables typically consume more disk space than expected. They can also cause query slowness. The following query can help identify fragmentation in tables along with the OPTIMIZE command to be run: ``` SELECT ENGINE, TABLE_SCHEMA, TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024) AS data_length, ROUND(INDEX_LENGTH / 1024 / 1024) AS index_length, ROUND(DATA_FREE / 1024 / 1024) AS data_free, ( ROUND(DATA_FREE / 1024 / 1024) / (ROUND(DATA_LENGTH / 1024 / 1024) + ROUND(INDEX_LENGTH / 1024 / 1024)) ) * 100 AS Fragmentation_Ratio, CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') AS Optimize_Command FROM information_schema.tables WHERE DATA_FREE > 0 AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema') ORDER BY data_free; ``` ### Defragmenting Tables To defragment tables, use the OPTIMIZE TABLE command or rebuild the table using ALTER TABLE. For example: ``` ALTER TABLE . ENGINE=InnoDB; OPTIMIZE TABLE .; ``` **Note:** These operations can be time-consuming. If concurrent DML operations are modifying the table, the size of the temporary online log may exceed the [innodb_online_alter_log_max_size](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_online_alter_log_max_size) configuration. When this limit is reached, a DB_ONLINE_LOG_TOO_BIG error occurs, interrupting the operation. It’s essential to monitor and adjust this setting as needed to prevent issues during large table alterations. ### Alternative Approach: pt-online-schema-change To avoid potential errors during table alterations, consider using the [pt-online-schema-change](https://docs.percona.com/percona-toolkit/pt-online-schema-change.html) tool. This tool modifies a copy of the table while allowing the original table to remain accessible for reads and writes. It works by creating a new table, applying the desired changes, and then migrating data from the original table to the new one. Once the process is complete, the original table is swapped with the new version. By default, the original table is dropped. Here’s an example: ``` pt-online-schema-change --user=root --password=your_password --alter "ENGINE=InnoDB" D=test,t=your_table --execute ``` ### Conclusion Regularly monitoring and optimizing fragmented tables is essential to maintaining MySQL performance and avoiding wasting disk usage. ### References - [InnoDB File Defragmentation](https://dev.mysql.com/doc/refman/8.4/en/innodb-file-defragmenting.html) - [InnoDB Online DDL Operations](https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html#online-ddl-primary-key-operations) - [InnoDB Fill Factor](https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_fill_factor) --- ### MySQL Brazil Meetup 2024 - Informações do Evento e Agenda - URL: https://anotherboringtechblog.com/2024/07/mysql-brazil-meetup-2024/ - Published: 2024-07-14 - Topic: MySQL Salve a todos os nerds! O nosso meetup de MySQL de 2024 já tem uma data! **Data**: 05 de Outubro, Sábado, 2024** Horário**: 9:00 AM – 5:30 PM (Horário de Brasilia)** Local**: [Oracle do Brasil Sistemas, Rua Dr. José Áureo Bustamante 455, bl. 110, São Paulo, SP, BR, 04710-090](https://maps.app.goo.gl/KTy872sMxZUYmgNU6) Teremos um dia inteiro com palestras sobre os mais diversos tópicos relacionados ao MySQL, que vão desde ferramentas open-source até a nuvem Oracle. E, no final do dia, encerraremos com uma visita ao data center da Oracle para aqueles que queiram conhecer mais sobre os Exadatas e a nuvem Oracle. ## **Inscrições Encerradas!**   Os sponsors confirmados dessa edição:** [](https://www.oracle.com/br/) [](https://www.percona.com/) [](https://readyset.io/) ## ## Agenda do Evento** - **Morning Coffee and Registration** – 20 mins 08:40-9:00 am - **Welcome** 09:00-9:25 am - [**Explore Generative AI with MySQL using HeatWave GenAI**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/Narciso_GenAI.pdf) **Horário:** 9:25 – 10:15 am – 40mins + 10 mins Q&A - **Speaker**: [Narciso Oliveira Júnior](https://www.linkedin.com/in/narcisooliveira/) - **Descrição:  **See how HeatWave can revolutionize application development with Artificial Intelligence and Machine Learning features. You will discover how to use LLM and Vector Store directly on the platform, without the need to call external services, ensuring the security of your data and a cost-effective solution.** ** - **Coffee Break:** 15mins 10:15-10:30 - [**Otimização de Performance no MySQL utilizando Cache**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/ReadySet_Marcelo.pdf) **Horário:** 10:30-11:20 am – 40mins + 10 mins Q&A - **Speaker**: [Marcelo Altmann](https://www.linkedin.com/in/marcelo-altmann/) - **Descrição: **Nesta palestra, exploraremos as melhores práticas eestratégias para otimizar a performance de bancos de dados MySQL utilizando soluções de cache. Focaremos no [Readyset](https://readyset.io/), uma inovadora ferramenta que simplifica a implementação de caching em bancos de dados MySQL ou PostgreSQL, sem a necessidade de alterações na aplicação. Você aprenderá como o Readyset pode ser integrado ao seu ambiente MySQL existente, como ele funciona para armazenar e servir resultados de consultas de maneira eficiente, como ele se difere de outras ferramentas de cache e como que ele elimina a necessidade de criação de cache por TTL, mantendo os resultados sempre atualizados.** Discutiremos os benefícios de performance, e daremos uma visão prática sobre como configurar e utilizar o [ReadySet](https://readyset.io/) para obter ganhos significativos em desempenho. Esta palestra é ideal para desenvolvedores, DBAs e arquitetos de sistemas que buscam maneiras de melhorar a eficiência de seus bancos de dados MySQL, especialmente em aplicações de alto desempenho e grande escala. Venha descobrir como o [ReadySet](https://readyset.io/) pode ser a chave para levar seu MySQL ao próximo nível. - [Percona Toolkit na vida do DBA**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/JeanPerconaToolkitVidaDBA.pdf) **Horário:** 11:20 – 12:10 – 40mins + 10 mins Q&A - **Speaker**: [Jean da Silva](https://www.linkedin.com/in/jenunes/) - **Descrição: **No dia a dia de um DBA, muitas tarefas são executadas; algumas são rotineiras, como a melhoria ou análise de queries, enquanto outras não são tão frequentes, como a modificação de estrutura ou a verificação de integridade do dados.** Nesta apresentação, utilizaremos o Percona Toolkit e demonstraremos como ele pode ajudar no seu dia a dia, facilitando a administração do seu banco MySQL. ** - **Lunch Break & Discussion** 12:10-13:20 – 1h10m - [**MySQL Performance Troubleshooting com PMM**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/Beto_Performance_TroubleshootingPMM.pdf) **Horário:** 13:20-14:10 – 40mins + 10 mins Q&A - **Speaker**: [Roberto Garcia de Bem](https://www.linkedin.com/in/roberto-de-bem/) - **Descrição: **O PMM (Percona Monitoring and Management) é uma ferramenta gratuita e open source para monitoramento e gerenciamento de banco de dados. Possui métricas para os bancos MySQL, MongoDB, e Postgres e métricas de utilização do sistema operacional (Linux). Nesta apresentação iremos verificar como podemos fazer uma análise e encontrar problemas de performance no MySQL utilizando essa ferramenta de monitoramento. - [**Introducing JavaScript support in MySQL**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/Ana_Introducing_JavaScriptMySQL.pdf) **Horário:** 14:10-15:00 – 40mins + 10 mins Q&A - **Speaker**: [Ana Paula Sales](https://www.linkedin.com/in/anasalesaraujo/) - [](https://www.linkedin.com/in/anasalesaraujo/)**Descrição:**O MySQL continua a inovar, agora oferecendo capacidades avançadas de programação procedural dentro do banco de dados. Com a introdução do MySQL-JavaScript, os desenvolvedores podem criar programas armazenados em JavaScript e executá-los diretamente no servidor MySQL utilizando o runtime GraalVM. Isso permite evitar a movimentação de dados e implementar lógicas avançadas de processamento de dados de maneira simples e eficiente. Exemplos de uso incluem extração, formatação, busca aproximada, validação, compressão/codificação e transformação de dados. - **Coffee break:** 15mins 15:00-15:15 - [**MySQL Security**](https://github.com/vgrippa/MySQLMeetupBR/blob/main/Meetup_20241005/MySQLSecurityBianchi.pdf) **Horário:** 15:15-16:05 – 40 mins + 10mins Q&A - **Speaker**: [Wagner Bianchi](https://www.linkedin.com/in/wagnerbianchi/) - **Descrição: **Esta apresentação abordará a segurança no MySQL, desde conceitos básicos até avançados. Inclui gerenciamento de contas, autenticação, criptografía de dados, componentes e plugins de segurança, além de políticas de para controle de acesso remoto aos bancos de dados e como gerar credenciais dinâmicas com o Hashicorp Vault. O objetivo é fornecer uma visão abrangente para proteger os ambientes de banco de dados MySQL, desde sua instalação até a sua utilização em ambiente de produção. - **Closing session** – 25mins (what are the group plans for the future, where and how people can join…etc.) 16:05-16:30 - **LAB visit** – 60-90 mins 16:30-17:15 - **ReadySet Sponsored Happy Hour** --- ### How to Sort MySQL Tables by Size and Why It Matters - URL: https://anotherboringtechblog.com/2024/06/sorting-mysql-tables-by-size/ - Published: 2024-06-06 - Topic: Linux, MySQL ### **Introduction** Efficient database management ensures optimal performance, especially as your data grows. One important aspect of managing a MySQL database is monitoring and managing the size of your tables. Knowing which tables are the largest can help optimize queries, plan storage, and identify potential performance bottlenecks. ### **Why Table Size Matters** 1. **Performance Optimization**: Large tables can slow down query performance. You can improve query response times by identifying and indexing large tables or archiving old data. 2. **Storage Management**: Understanding table sizes helps plan storage needs. This is particularly important for databases hosted on servers with limited storage capacity. 3. **Backup and Recovery**: Larger tables take longer to back up and restore. Knowing table sizes helps in planning backup strategies and minimizing downtime during recovery. ### **How to Sort Tables by Size in MySQL** To sort tables by size in MySQL, you can use a simple query on the information_schema database. Here’s how: ``` SELECT table_schema AS `Database`, table_name AS `Table`, ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS `Size (GB)` FROM information_schema.TABLES ORDER BY (data_length + index_length) DESC; ``` ### **Explanation** •**table_schema**: This column represents the database name. •**table_name**: This column represents the table name. •**data_length**** and ****index_length**: These columns represent the size of the data and indexes, respectively. By summing these, you get the total size of the table. •**ROUND((data_length + index_length) / 1024 / 1024, 2)**: This part of the query converts the size from bytes to megabytes (MB) and rounds it to two decimal places. •**ORDER BY (data_length + index_length) DESC**: This sorts the tables in descending order based on their size. ### **Conclusion** Regularly monitoring table sizes in MySQL is a best practice for maintaining database performance and planning for future storage needs. Using the provided query, database administrators can quickly identify the largest tables and take appropriate actions to optimize their database systems. --- ### How to Run ANALYZE in MySQL - URL: https://anotherboringtechblog.com/2024/05/analyze-in-mysql/ - Published: 2024-05-14 - Topic: MySQL #### **Introduction** In database management, keeping your database running smoothly is essential. [`ANALYZE`](https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html) is a command in MySQL that helps update statistics for table indexes. These statistics help the query optimizer choose the most efficient way to execute queries. #### **How to Use the `ANALYZE` Command** Here’s how you can use the `ANALYZE` command to improve your database’s performance: - **Basic Command**: ``` ANALYZE TABLE tablename; ``` Replace `tablename` with your actual table name. - **Multiple Tables**: If you need to update more than one table, you can do it in a single command: ``` ANALYZE TABLE table1, table2, table3; ``` - **Automating ANALYZE for all tables**: To create the ANALYZE command for all tables in a specific database, you can use the following script: ``` -- Set the database name SET @dbName = 'your_database_name'; -- Create a prepared statement to run ANALYZE TABLE for each table in a given database SET @sql = CONCAT('SELECT CONCAT("ANALYZE TABLE ", table_schema, ".", table_name, ";") AS sqlCommand FROM information_schema.tables WHERE table_schema = "', @dbName, '"'); -- Prepare and execute the statement PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; ``` Replace `'your_database_name'` with the name of your database. This script dynamically generates the `ANALYZE TABLE` commands for every table in the specified database. This script only generates the ANALYZE commands; it **DOES NOT** execute them. #### **Special Considerations for Percona Server and MySQL Community** For users of the Percona server, it’s important to note some specific improvements. Percona addressed a significant issue in the bug report [PS-2503](https://jira.percona.com/browse/PS-2503), which made running `ANALYZE TABLE` safer. Percona removed an unnecessary table definition cache lock that improved the command’s efficiency. Since the release of versions 5.6.38 and 5.7.20 and continuing into Percona Server for MySQL version 8.0, this command has been optimized to run without these issues. You can find more details in the [release notes](https://docs.percona.com/percona-server/5.7/release-notes/Percona-Server-5.7.20-18.html#bugs-fixed). This issue also affected MySQL Community versions up to MySQL 8.0.23, which was resolved in the release of version [8.0.24](https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-24.html) onwards. #### **Conclusion** Using the `ANALYZE` command is a straightforward way to update index statistics in your MySQL database. Additionally, regularly updating your database ensures access to new features, security patches, and important bug fixes, enhancing both functionality and safety. --- ### [PT-BR] Desativar o Transparent Huge Pages(THP) no Ubuntu 22.04 - URL: https://anotherboringtechblog.com/2024/05/desativar-transparent-huge-pages-ubuntu2204/ - Published: 2024-05-09 Desativar as Transparent Huge Pages (THP) de forma permanente no Ubuntu 22.04 exige a modificação dos arquivos de configuração do sistema para que a alteração seja aplicada durante o boot. Existem diversas maneiras de fazer isso, mas um método comum é utilizar o `rc.local` ou criar um serviço `systemd` personalizado, já que o Ubuntu pode não habilitar o `rc.local` por padrão em versões mais recentes. A seguir, explicarei como configurar um serviço `systemd` para desativar as THP. **Passo 1: Criar um Arquivo de Serviço systemd** Você deve começar utilizando o seu editor de texto para criar um novo arquivo de serviço systemd. Vamos usar o `vi` para isso: ``` sudo vi /etc/systemd/system/disable-thp.service ``` Copie e cole o seguinte conteúdo no arquivo: ``` [Unit] Description=Disable Transparent Huge Pages [Service] Type=oneshot ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag' [Install] WantedBy=multi-user.target ``` Salve e feche o arquivo ao terminar. **Passo 2: Recarregar o `systemd` e Ativar o Serviço** Depois de criar o arquivo do serviço, é necessário recarregar as configurações do gerenciador `systemd`, ativar o serviço para que ele seja iniciado durante o boot e iniciar o serviço imediatamente para efetivar a mudança sem necessidade de reiniciar o sistema. Execute os seguintes comandos no terminal: ``` sudo systemctl daemon-reload sudo systemctl enable disable-thp.service sudo systemctl start disable-thp.service ``` **Passo 3: Verificar as Mudanças** Para confirmar que as Transparent Huge Pages estão desativadas, você pode usar o comando cat para checar as configurações atuais: ``` cat /sys/kernel/mm/transparent_hugepage/enabled cat /sys/kernel/mm/transparent_hugepage/defrag ``` Ambos os comandos devem mostrar `never`, indicando que as Transparent Huge Pages estão desativadas. **Conclusão** Seguindo esses passos, você configurou um serviço `systemd` para desativar as Transparent Huge Pages de maneira permanente no Ubuntu 22.04. Esta configuração será mantida após reinicializações do sistema, garantindo que as THP permaneçam desativadas cada vez que o sistema for iniciado. Este método é recomendado para sistemas onde as THP possam interferir no desempenho de certas aplicações, especialmente bancos de dados como MongoDB, MySQL, Redis, etc., que sugerem a desativação das THP para melhorar o desempenho e a eficiência. --- ### Releem: MySQL Performance Tool - URL: https://anotherboringtechblog.com/2024/04/releem-mysql-performance-tool/ - Published: 2024-04-28 - Topic: MySQL Who wouldn’t love a reliable database free from issues and allowing us to sleep peacefully? That’s what [Roman Agabekov](https://www.linkedin.com/in/roman-agabekov/), the CEO of [Releem](https://releem.com/), aims to provide. As a tool specializing in MySQL Performance Tuning, Releem helps developers and database administrators by automating MySQL tuning, enhancing application performance, and reducing server costs. In this blog post, I’ll explain how to use the product and provide some examples of the issues it can effectively address. ### **Introduction** Releem is a MySQL Performance Tuning Tool that is available as a service, which means you’ll need to sign up on their website. We all understand that signing up can be as tedious as slogging through a long blog post (wink), but fortunately, Releem keeps it simple—just your email, name, and password are needed. Once registered, we gain access to the interface to begin setting up the tool for monitoring our MySQL databases. For this demonstration, I’m using two MySQL instances with asynchronous replication and a default InnoDB configuration to explore the types of tuning insights the tool can offer. ### **Installing Releem ** The installation process is quite simple, following Releem’s friendly guide. To start, you click on “Add new server”. This action leads you to a Wizard page where you select the installation type. In my case, I chose the “Automatic Agent Installation”. This choice prompts you to enter the root password of the MySQL database, after which a shell script is generated for you to copy and execute on your server: Executing this script automates the entire setup process. Here’s a breakdown of what happens: - Dependencies are installed. - The Releem Agent is downloaded and configured. - Directives are added to the MySQL configuration files. - A new MySQL user `releem` is created for data collection. - The Releem Agent configures memory limits and cron jobs. - Metrics are collected and recommendations are fetched from Releem’s cloud platform. Upon successful completion, the installation prints: ``` # RELEEM_MYSQL_MEMORY_LIMIT=0 RELEEM_API_KEY=3897ade6-1159-45e0-a7bf-ab7f867aede1 RELEEM_MYSQL_ROOT_PASSWORD='XXXXX' RELEEM_CRON_ENABLE=1 bash -c "$(curl -L https://releem.s3.amazonaws.com/v2/install.sh)" % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 20304 100 20304 0 0 295k 0 --:--:-- --:--:-- --:--:-- 295k * Installing dependences... * Downloading Releem Agent, architecture x86_64... % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 21673 100 21673 0 0 255k 0 --:--:-- --:--:-- --:--:-- 255k % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 19.9M 100 19.9M 0 0 76.9M 0 --:--:-- --:--:-- --:--:-- 76.9M * Configure the application... * Detected service name for appling config * Configure catalog for copy recommend config * The /etc/mysql/my.cnf file is used for automatic Releem settings. * Adding directive includedir to the MySQL configuration /etc/mysql/my.cnf. * Configure MySQL user for collect data * Using MySQL root user Connect to MySQL - successful mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. Created new user `releem` Connecting to MySQL with user `releem` - successfull * Configure mysql memory limit * Saving variables to Releem Agent configuration - Adding API key to the Releem Agent configuration: /opt/releem/releem.conf - Adding Releem Configuration Directory /opt/releem/conf to Releem Agent configuration: /opt/releem/releem.conf - Adding user and password mysql to the Releem Agent configuration: /opt/releem/releem.conf - Adding MySQL restart command to the Releem Agent configuration: /opt/releem/releem.conf - Adding MySQL include directory to the Releem Agent configuration /opt/releem/releem.conf. - Adding autodetected hostname to the Releem Agent configuration: /opt/releem/releem.conf * Configure crontab... * Executing Releem Agent for first time... INFO : [Main] - 2024/04/28 21:12:54 main.go:85: Starting releem-agent of version is 1.15.0 INFO : [Main] - 2024/04/28 21:12:54 config.go:41: Loading config /opt/releem/releem.conf INFO : [Main] - 2024/04/28 21:12:54 main.go:155: InstanceType is Local INFO : [Main] - 2024/04/28 21:12:54 main.go:184: Connect Success to DB via tcp 127.0.0.1 INFO : [Worker] - 2024/04/28 21:12:54 runner.go:73: * Collecting metrics to recommend a config... INFO : [ReleemRepeaterConfigurations] - 2024/04/28 21:12:54 releemConfiguration.go:25: * Sending metrics to Releem Cloud Platform... INFO : [ReleemRepeaterConfigurations] - 2024/04/28 21:12:55 releemConfiguration.go:62: * Downloading recommended MySQL configuration from Releem Cloud Platform... INFO : [ReleemRepeaterConfigurations] - 2024/04/28 21:12:55 releemConfiguration.go:80: 1. Recommended MySQL configuration downloaded to /opt/releem/conf INFO : [ReleemRepeaterConfigurations] - 2024/04/28 21:12:55 releemConfiguration.go:81: 2. To check MySQL Performance Score please visit https://app.releem.com/dashboard?menu=metrics INFO : [ReleemRepeaterConfigurations] - 2024/04/28 21:12:55 releemConfiguration.go:82: 3. To apply the recommended configuration please read documentation https://app.releem.com/dashboard INFO : [Main] - 2024/04/28 21:12:55 main.go:85: Starting releem-agent of version is 1.15.0 INFO : [Main] - 2024/04/28 21:12:55 config.go:41: Loading config /opt/releem/releem.conf INFO : [Main] - 2024/04/28 21:12:55 main.go:155: InstanceType is Local INFO : [Main] - 2024/04/28 21:12:55 main.go:184: Connect Success to DB via tcp 127.0.0.1 INFO : [Worker] - 2024/04/28 21:12:58 runner.go:51: Exiting * Installing and starting Releem Agent service to collect metrics.. Installing Releem Agent - successful Restarting Releem Agent - successful * Enabling Performance schema and SlowLog to collect metrics... To apply changes to the mysql configuration, you need to restart the service Please confirm restart MySQL service? (Y/N) y Restarting service with command ' /usr/bin/systemctl restart mysql'... Waiting for mysql service to start 1200 seconds - PID 79152 terminated with exit code 0 The MySQL service restarted successfully! Performance schema and Slow Log are enabled. * Releem Agent is successfully installed. * To view Releem recommendations and MySQL metrics, visit https://app.releem.com/dashboard ``` With the agent installed and running on the server, we can now see Releem actively monitoring our instances: The tool will initiate workload monitoring, and after 24 hours, we can examine the recommended adjustments to enhance our database: *Screenshot* Applying changes in the Releem interface is as easy as clicking a button. However, certain MySQL changes require restarting the database, so use caution when implementing them in a production environment. ### **Extras** Releem delivers detailed information on MySQL and the operating system, presented in a clear and structured summary: And it offers query analytics to help us identify and optimize slow queries: ### **Conclusion** Releem proves to be a lifesaver for those who either lack the budget for dedicated DBAs to manage MySQL or are more focused on automation and overseeing hundreds of databases rather than meticulously tuning MySQL. It spares DBAs from analyzing database metrics (using tools like pt-stalk, OS watcher, PMM, etc.) to pinpoint optimization opportunities. Let’s face it: Any tool that makes a DBA’s day less boring is a friend. You might be curious about the features I think could enhance Releem. It would be helpful to briefly explain each suggested modification to my database. Also, I would like to see the displayed metrics be a bit more interactive, perhaps with the ability to zoom in and out and set specific date ranges. As the tool evolves, I would like to see recommendations for replication settings in the future—not only for asynchronous replication scenarios (between source and replica) but also for setups like InnoDB Cluster. And hey, wouldn’t it be fantastic if Releem expanded to include other tools from the MySQL ecosystem, like ProxySQL and Galera Cluster? A bored engineer can dream, right? Lastly, this space is open for [Roman](https://www.linkedin.com/in/roman-agabekov/) to share his comments, feedback, and his visions for the future of Releem. Here’s wishing him all the best on his journey! Happy databasing to all! --- ### Improving Database Performance with ReadySet: A MySQL Caching Solution - URL: https://anotherboringtechblog.com/2024/04/readyset-a-mysql-caching-solution/ - Published: 2024-04-10 - Topic: MySQL Many developers face the challenge of achieving performance without compromising on real-time application functionality in database management. This brings us to today’s spotlight technology: [**ReadySet**](https://readyset.io/). ### What is ReadySet? According to its [GitHub repository](https://github.com/readysettech/readyset), ReadySet is designed to enhance the performance of real-time applications by providing a powerful SQL caching engine that requires no changes to existing code or the need to switch databases. It’s a solution that promises efficiency and ease of integration, making it a compelling option for developers looking to speed up their applications. ReadySet is based on Jon Gjengset’s MIT Ph.D. thesis, [*Partial State in Dataflow-Based Materialized Views*](https://jon.thesquareplanet.com/papers/phd-thesis.pdf), and CEO [Alana Marzoev](https://www.linkedin.com/in/alanamarzoev/) leads the company’s innovative journey. The person who presented the tool to me is [Marcelo Altmann](https://www.linkedin.com/in/marcelo-altmann/), my colleague and a developer celebrated for his contributions to Percona XtraBackup. He is now a member of the ReadySet development team. ### Setting Up the Stage for Testing I set up two EC2 instances for this demo, each armed with 4 CPUs and 16GB of RAM, running Ubuntu 22.04 (Jammy Jellyfish). For clarity, these instances are designed to be one dedicated to ReadySet and one for MySQL. The goal? To install and configure ReadySet from scratch and evaluate its performance enhancements on MySQL databases. ### MySQL Installation and Configuration #### **Adding the Percona Repository** Access the official [Percona documentation](https://docs.percona.com/percona-server/8.0/apt-repo.html) with instructions on how to add the repository to your Ubuntu system. This step ensures you can install the latest version of MySQL directly from Percona’s repository. #### **System Update and Upgrade** We will upgrade the system packages to ensure our system is up-to-date and minimize potential compatibility issues. ``` # apt update; # apt upgrade -y; ``` #### **Installing Percona Server for MySQL** With the Percona repository added and the system updated, the next step is to install the Percona Server for MySQL. This can be done by following the specific [instructions in the Percona documentation](https://docs.percona.com/percona-server/8.0/apt-repo.html#install-percona-server-for-mysql-using-apt), which guides you through installing the server package. As of this writing, the installed version is 8.0.36-28. ``` # mysqld --version /usr/sbin/mysqld Ver 8.0.36-28 for Linux on x86_64 (Percona Server (GPL), Release '28', Revision '47601f19'$) ``` #### **Optimizing InnoDB Settings** To minimize disk I/O and enhance performance for the purpose of this test, adjustments are made to the InnoDB settings in the MySQL configuration file (`/etc/mysql/my.cnf`). The parameters adjusted include: ``` [mysqld] innodb_flush_log_at_trx_commit = 0 sync_binlog = 0 innodb_redo_log_capacity = 6G innodb_buffer_pool_size = 10G ``` #### **Creating a High-Privilege User** For ease of testing and to avoid permission-related issues during the ReadySet evaluation, a MySQL user with broad privileges is created (note: in a production environment, stronger password practices are recommended): ``` mysql> CREATE USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'app'; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* to app@'%'; Query OK, 0 rows affected (0.00 sec) ``` *Note: The general guide for MySQL is in the [ReadySet documentation](https://docs.readyset.io/reference/configure-your-database/mysql/generic-db-directions). It provides detailed information on the necessary parameters and the minimum privileges required for operation.* ### ReadySet Installation ReadySet offers different installation options, including binaries and a Docker version. For this test, I opted for the binary installation to closely monitor and control the environment, aligning with our “hands-on” approach. #### **Downloading the ReadySet Binary** Navigate to the [ReadySet releases page](https://github.com/readysettech/readyset/releases) to download the latest stable version of the ReadySet binary. At the time of this writing, the version selected was `readyset_1.2.0-1_amd64.deb`. ``` # wget https://github.com/readysettech/readyset/releases/download/stable-240328/readyset_1.2.0-1_amd64.deb ``` #### **Installation Process** Once the binary is downloaded, installation is straightforward. Utilize the Debian package manager with the command `sudo dpkg -i readyset_1.2.0-1_amd64.deb`. This command unpacks and installs ReadySet, registering it as a system service and simplifying the process of managing its operation through standard `systemd` commands. ``` # dpkg -i readyset_1.2.0-1_amd64.deb Selecting previously unselected package readyset. (Reading database ... 96135 files and directories currently installed.) Preparing to unpack readyset_1.2.0-1_amd64.deb ... Unpacking readyset (1.2.0-1) ... Setting up readyset (1.2.0-1) ... Created symlink /etc/systemd/system/multi-user.target.wants/readyset.service → /lib/systemd/system/readyset.service. Notice: The UPSTREAM_DB_URL and LISTEN_ADDRESS values must be set in /etc/readyset/readyset.conf before starting the readyset service. Press to acknowledge this message. ``` #### **Configuring ReadySet for Performance Testing** With ReadySet installed, the configuration phase begins. - **Configuring the Database Connection**: The configuration file, `readyset.conf`, located in `/etc/readyset/`, holds the key to linking ReadySet with the MySQL database. Within this file, the `UPSTREAM_DB_URL` parameter requires adjustment to reflect the MySQL instance’s credentials and address. An example configuration would resemble: ``` UPSTREAM_DB_URL="mysql://app:app@172.31.16.153:3306/vinnie_test" ``` - **Listening Address Configuration**: Additionally, the `LISTEN_ADDRESS` parameter in the `readyset.conf` file is set to `0.0.0.0:3306`, enabling ReadySet to listen on all network interfaces of the host machine at the specified port. ``` LISTEN_ADDRESS=0.0.0.0:3306 ``` - **Finalizing the Setup**: With the configuration accurately pointing to the MySQL database and the listening address set, the final step involves restarting the ReadySet service to apply the changes. This is achieved through the command: ``` $ sudo systemctl restart readyset ``` #### **Validating ReadySet’s Operational Status** To confirm ReadySet’s activity and to confirm a successful connection to MySQL, we check the service logs: ``` # tail -400f **/var/lib/readyset/readyset.log.2024-04-11** [...] 2024-04-11T00:14:23.657997Z INFO replicators::noria_adapter: MySQL connected 2024-04-11T00:14:23.658017Z INFO replicators::noria_adapter: binlog_position=binlog.000003:861 ``` Directly interacting with ReadySet provides a definitive confirmation of its operational status: ``` $ mysql -uapp -papp -h 54.160.157.28 -P 3306 mysql> SHOW READYSET STATUS; +----------------------------+-------------------------+ | Variable_name | Value | +----------------------------+-------------------------+ | Database Connection | Connected | | Connection Count | 4 | | Snapshot Status | Completed | | Maximum Replication Offset | binlog.000004:4 | | Minimum Replication Offset | binlog.000004:4 | | Last started Controller | 2024-04-11 00:21:02 UTC | | Last completed snapshot | 2024-04-11 00:21:02 UTC | | Last started replication | 2024-04-11 00:21:02 UTC | +----------------------------+-------------------------+ 8 rows in set (0.17 sec) ``` ### Evaluating ReadySet’s Performance Enhancement This evaluation focuses on the impact of ReadySet’s caching on query execution times using a specifically designed test scenario. #### Creating the Test Database and Table The first step involves creating a database and table within the MySQL instance to serve as the basis for our performance tests. A table named `joinit` was created, containing approximately 16 million rows, populated with a mix of integer, varchar, and time fields to simulate a real-world dataset. The script to create the table and populate it: ``` USE vinnie_test; DROP TABLE IF EXISTS `joinit`; CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 ))); INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; [...]  INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; ``` #### **Baseline Performance Measurement** To establish a baseline, queries were executed directly against the MySQL database, measuring the completion time. I executed the query multiple times to avoid the data being read from the disk, achieving a consistent execution time of approximately 7.5 seconds after stabilization: ``` mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.54 sec) [...] mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.61 sec) ``` **Introducing ReadySet into the Workflow** Switching the query execution to route through ReadySet initially mirrored the baseline performance, as the caching mechanism had yet to be activated: ``` $ mysql -uapp -papp -h 54.160.157.28 -P 3306 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.26-readyset Percona Server (GPL), Release '28', Revision '47601f19'$ mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.74 sec) mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.72 sec) ``` This step was important in understanding the initial overhead and behavior of ReadySet without caching. #### **Activating and Assessing Caching Benefits** Upon executing the `SHOW PROXIED QUERIES` command, I observed a list of queries ReadySet had processed, including one that particularly interested me due to its potential for caching: ``` mysql> SHOW PROXIED QUERIES; +--------------------+------------------------------------------------+--------------------+-------+ | query id | proxied query | readyset supported | count | +--------------------+------------------------------------------------+--------------------+-------+ **| q_ac193aeddd293eac | SELECT count(1) FROM `joinit` WHERE (`g` = $1) | yes | 0 | **| q_95844e527a191a7b | show databases | unsupported | 0 | | q_21d7e9ff50fcbf0e | SELECT DATABASE() | unsupported | 0 | | q_e4add1bc5b3d07c | show global variables like '%timeout%' | unsupported | 0 | +--------------------+------------------------------------------------+--------------------+-------+ 4 rows in set (0.16 sec) ``` Noticing that ReadySet had successfully identified and assigned an ID to the query of interest, I proceeded to cache this specific query using the command: ``` mysql> CREATE CACHE FROM q_ac193aeddd293eac; Query OK, 0 rows affected (0.16 sec) ``` With the cache now set, I reran the query: ``` mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (7.71 sec) ``` This timing was anticipated, given the known behavior of caching mechanisms, which require a query to be run once before the cache is effectively utilized. To evaluate the true impact of caching, I executed the same query once more: ``` mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (3.71 sec) ``` The result showed a 50% reduction in execution time. Curious about the consistency of this improvement, I ran the query a third time: ``` mysql> select count(1) from joinit where g=5 ; +----------+ | count(1) | +----------+ | 560097 | +----------+ 1 row in set (0.16 sec) ``` The execution time dramatically dropped to 0.16 seconds, indicating a nearly 98% increase in speed compared to the original run. To confirm the caching effect was not isolated to a single parameter, I tested another value, `g=6`, inserted a new row, and observed a similar pattern of performance enhancement: ``` mysql> select count(1) from joinit where g=6 ; +----------+ | count(1) | +----------+ | 559382 | +----------+ 1 row in set (0.15 sec) mysql> INSERT INTO joinit VALUES (NULL, uuid(), time(now()), 6); Query OK, 1 row affected (0.15 sec) mysql> select count(1) from joinit where g=6 ; +----------+ | count(1) | +----------+ | 559383 | +----------+ 1 row in set (0.15 sec) ``` *When inserting a new row, we see that ReadySet does not flush and reload the entire data set.* #### Concluding Thoughts The initial tests revealed that ReadySet’s effectiveness becomes notably apparent after the first query execution when caching mechanisms can fully engage. The observed reduction in query execution times—from several seconds to a fraction of a second in subsequent runs—illustrates the potential benefits of implementing ReadySet in environments where database query performance is critical. Furthermore, while ReadySet shows promise for enhancing query efficiency, its adoption should be weighed against other factors, such as system complexity, maintenance overhead, and compatibility with existing technologies and workflows. As with any technology solution, the ultimate value of ReadySet will depend on its alignment with organizational needs, technical requirements, and strategic goals. In summary, ReadySet emerges from this evaluation as a tool with the potential to significantly improve database query performance.   See you next time and happy databasing! --- ### MySQL: Identifying Tables Not Using InnoDB - URL: https://anotherboringtechblog.com/2024/03/mysql-identifying-tables-not-using-innodb/ - Published: 2024-03-24 - Topic: MySQL This blog post aims to spotlight tables within a MySQL database that do not employ the InnoDB storage engine. The essence of the query is to filter out tables that do not utilize the InnoDB engine, thereby enabling a focused analysis or migration plan for database optimization. Below is the query: ``` SELECT table_schema AS 'database_name', table_name, engine FROM information_schema.tables WHERE engine NOT LIKE 'InnoDB' AND table_type = 'BASE TABLE' AND table_schema NOT IN ( 'information_schema', 'sys', 'performance_schema', 'mysql' ) ORDER BY table_schema, table_name; ``` Let’s dissect the query to understand its mechanics and objectives better: - **Column Selection**: The query targets three specific columns: `table_schema`, `table_name`, and `engine`. For clarity, `table_schema` is aliased as `database_name`. This trio of columns provides a concise yet comprehensive overview of each table’s database association, name, and the storage engine it employs. - **Data Source**: Information is fetched from the `information_schema.tables` table. This meta-database is a treasure trove of data about all other databases and tables within a MySQL environment. By querying this meta-database, users can gain insights into the structural metadata of their databases. - **Filters Applied**: **Engine Filter**: The primary condition `engine NOT LIKE 'InnoDB'` zeroes in on tables that do not use the InnoDB storage engine. This is crucial for identifying tables that might not be benefiting from the features and performance optimizations offered by InnoDB. - **Table Type Filter**: By specifying `table_type = 'BASE TABLE'`, the query ensures that only regular tables are considered, excluding views and other non-table database objects. - **Schema Exclusion**: The query excludes tables from system schemas such as `information_schema`, `sys`, `performance_schema`, and `mysql`. This focus ensures that the analysis is limited to user-created tables, which are more relevant for most optimization and migration tasks. - **Sorting**: Finally, the results are sorted by `table_schema` and `table_name`, facilitating an organized and readable output. This sorting aids in quickly identifying specific tables and their corresponding databases. #### Why Focus on InnoDB? InnoDB is renowned for its ACID compliance, support for foreign keys, and performance efficiency, especially in transaction-heavy applications. However, there are scenarios where other engines like MyISAM or MEMORY might be used for legacy reasons or specific use cases where these engines offer advantages. Identifying tables not using InnoDB can help assess potential optimizations, migrations, or adjustments needed to align with best practices or application requirements. In conclusion, this MySQL query is a powerful tool for database professionals aiming to optimize their database environments. Identifying tables not leveraging InnoDB opens up avenues for enhancements, ensuring that databases are configured for optimal performance and reliability. --- ### Introduction to the New Percona Everest Beta - URL: https://anotherboringtechblog.com/2024/02/percona-everest-beta-impressions/ - Published: 2024-02-28 In the rapidly evolving world of technology, database management remains a crucial, albeit challenging, aspect for many organizations. Percona, a leading provider of open-source database solutions, comes with its latest product, which is now in its beta phase: [Percona Everest](https://docs.percona.com/everest/index.html). This post aims to shed light on what Percona Everest is, how it works, and its potential impact on database management. ### What is this tool? What is Percona Everest? Percona has long been recognized for contributing to the open-source community, offering enhanced versions of MySQL, MongoDB, and Postgres. Solutions, including [Percona Xtrabackup](https://www.percona.com/mysql/software/percona-xtrabackup), [Percona Monitoring and Management(PMM)](https://pmmdemo.percona.com/graph/d/pmm-home/home-dashboard), and [Percona Toolkit](https://www.percona.com/software/database-tools/percona-toolkit), have aided database administrators (DBAs) by providing alternatives to proprietary solutions, thereby preventing vendor lock-in. The newest addition to Percona’s suite of tools, Percona Everest, aims to redefine the use and management of databases. Built on the foundation of Percona Operators, Everest simplifies the database lifecycle, encompassing provisioning, configuration, and management, with an overarching goal of saving time and resources traditionally dedicated to database management or Database as a Service (DBaaS) offerings. ### Getting Started with Percona Everest The initial step towards using Percona Everest involves setting up a Kubernetes cluster, a process supported on both Google Kubernetes Engine (GKE) and Amazon Elastic Kubernetes Service (EKS). For illustration, this post details the process of using GKE and the Everest client running on a MacOS Sonoma with Apple silicon, beginning with the use of the `gcloud` [CLI](https://cloud.google.com/sdk/docs/install) and proceeding to the Kubernetes cluster creation. The first step is to create the Kubernetes cluster in GKE: ``` $ gcloud container clusters create \ > --machine-type n1-standard-4 \ > --num-nodes 3 \ > --zone us-central1-b \ > --cluster-version latest vinnie1-k8s Default change: VPC-native is the default mode during cluster creation for versions greater than 1.21.0-gke.1500. To create advanced routes based clusters, please pass the `--no-enable-ip-alias` flag Default change: During creation of nodepools or autoscaling configuration changes for cluster versions greater than 1.24.1-gke.800 a default location policy is applied. For Spot and PVM it defaults to ANY, and for all other VM kinds a BALANCED policy is used. To change the default values use the `--location-policy` flag. Note: Your Pod address range (`--cluster-ipv4-cidr`) can accommodate at most 1008 node(s). Creating cluster vinnie1-k8s in us-central1-b... Cluster is being health-checked (master is healthy)...done. Created [https://container.googleapis.com/v1/projects/support-211414/zones/us-central1-b/clusters/vinnie1-k8s]. To inspect the contents of your cluster, go to: https://console.cloud.google.com/kubernetes/workload_/gcloud/us-central1-b/vinnie1-k8s?project=support-211414 kubeconfig entry generated for vinnie1-k8s. NAME LOCATION MASTER_VERSION MASTER_IP MACHINE_TYPE NODE_VERSION NUM_NODES STATUS vinnie1-k8s us-central1-b 1.29.0-gke.1381000 34.136.183.215 n1-standard-4 1.29.0-gke.1381000 3 RUNNING ``` ### Using Percona Everest With the installation complete, the focus shifts to [installing the Everest client](https://docs.percona.com/everest/install/installEverestCLI.html). It is simple as we need only to download the binary (and the documentation already provides the commands): ``` $ curl -sSL -o everestctl-darwin-arm64 https://github.com/percona/percona-everest-cli/releases/latest/download/everestctl-darwin-arm64 $ sudo install -m 555 everestctl-darwin-arm64 /usr/local/bin/everestctl $ everestctl version ProjectName: everestctl Version: 0.8.0 FullCommit: 2fe2cb9246c8e185a91e775fdf4855511def411a ``` With the client installed, we will perform the installation of the Percona Operators: ``` $ everestctl install ? Namespaces managed by Everest (comma separated) default ? What operators do you want to install? MySQL, MongoDB, PostgreSQL 2024-02-28T23:36:56Z info install/install.go:430 Installing Operator Lifecycle Manager {"component": "install"} 2024-02-28T23:37:25Z info install/install.go:435 OLM has been installed {"component": "install"} 2024-02-28T23:37:25Z info install/install.go:436 Installing Percona OLM Catalog {"component": "install"} 2024-02-28T23:38:09Z info install/install.go:441 Percona OLM Catalog has been installed {"component": "install"} 2024-02-28T23:38:09Z info install/install.go:419 Creating namespace everest-monitoring {"component": "install"} 2024-02-28T23:38:10Z info install/install.go:425 Namespace everest-monitoring has been created {"component": "install"} 2024-02-28T23:38:10Z info install/install.go:238 Preparing k8s cluster for monitoring {"component": "install", "action": "monitoring"} 2024-02-28T23:38:10Z info install/install.go:208 Creating operator group for everest {"component": "install"} 2024-02-28T23:38:10Z info install/install.go:212 Installing victoriametrics-operator operator {"component": "install"} 2024-02-28T23:38:36Z info install/install.go:228 victoriametrics-operator operator has been installed {"component": "install"} 2024-02-28T23:38:48Z info install/install.go:246 K8s cluster monitoring has been provisioned successfully {"component": "install", "action": "monitoring"} 2024-02-28T23:38:48Z info install/install.go:419 Creating namespace default {"component": "install"} 2024-02-28T23:38:49Z info install/install.go:425 Namespace default has been created {"component": "install"} 2024-02-28T23:38:49Z info install/install.go:311 Installing operators into default namespace {"component": "install"} 2024-02-28T23:38:49Z info install/install.go:478 Installing percona-xtradb-cluster-operator operator {"component": "install"} 2024-02-28T23:39:10Z info install/install.go:520 percona-xtradb-cluster-operator operator has been installed {"component": "install"} 2024-02-28T23:39:10Z info install/install.go:478 Installing percona-server-mongodb-operator operator {"component": "install"} 2024-02-28T23:39:38Z info install/install.go:520 percona-server-mongodb-operator operator has been installed {"component": "install"} 2024-02-28T23:39:38Z info install/install.go:478 Installing percona-postgresql-operator operator {"component": "install"} 2024-02-28T23:40:09Z info install/install.go:520 percona-postgresql-operator operator has been installed {"component": "install"} 2024-02-28T23:40:09Z info install/install.go:315 Creating role for the Everest service account {"component": "install"} 2024-02-28T23:40:10Z info install/install.go:321 Binding role to the Everest Service account {"component": "install"} 2024-02-28T23:40:12Z info install/install.go:419 Creating namespace everest-system {"component": "install"} 2024-02-28T23:40:12Z info install/install.go:425 Namespace everest-system has been created {"component": "install"} 2024-02-28T23:40:12Z info install/install.go:255 Creating operator group for everest {"component": "install"} 2024-02-28T23:40:13Z info install/install.go:478 Installing everest-operator operator {"component": "install"} 2024-02-28T23:40:36Z info install/install.go:520 everest-operator operator has been installed {"component": "install"} 2024-02-28T23:40:36Z info install/install.go:278 Deploying Everest to everest-system {"component": "install"} 2024-02-28T23:40:53Z info install/install.go:293 Updating cluster role bindings for everest-admin {"component": "install"} 2024-02-28T23:40:54Z info install/install.go:577 Creating token for Everest {"component": "install"} 2024-02-28T23:40:55Z info install/install.go:185 Here's your authorization token for accessing the Everest UI and API: k7DcNAJyCsUFgReBJOZ4dH43G8Bc864cZ23keHckPbcTOpYK58B7MAZYHiuAHRahFONUdOXLeY4ZBws5azQLqrb1Gpxjrg6UoBQJrEg67E1dAtOH1URvlamrJ87j99C Store this token securely as you will not be able to retrieve it later. If you ever need to reset it, use the following command: everestctl token reset {"component": "install"} ``` It is very important to save the token, as we will use it later in the UI interface. With the installation done, we need to enable the port forwarding so we can use the UI interface: ``` $ kubectl port-forward svc/everest 8080:8080 -n everest-system Forwarding from 127.0.0.1:8080 -> 8080 Forwarding from [::1]:8080 -> 8080 ``` And we open our browser using the forwarded port (8080) at localhost ([http://127.0.0.1:8080/login](http://127.0.0.1:8080/login)):   With the Everest UI, we can start managing our databases and creating our first instance. I’m not going into the details of every option(maybe in another opportunity), but it is similar to deploying a DBaaS service (RDS, for example):    With all steps completed, we will see our database initializing: And we can confirm that the pods are being installed: ``` $ kubectl get pods NAME READY STATUS RESTARTS AGE mysql-boring-haproxy-0 2/2 Running 0 2m59s mysql-boring-haproxy-1 2/2 Running 0 95s mysql-boring-haproxy-2 2/2 Running 0 75s mysql-boring-pxc-0 1/1 Running 0 3m mysql-boring-pxc-1 1/1 Running 0 98s mysql-boring-pxc-2 0/1 Running 0 27s percona-postgresql-operator-c5c44c589-5c6zg 1/1 Running 0 20m percona-server-mongodb-operator-668566bcbc-r5kqp 1/1 Running 5 (10m ago) 20m percona-xtradb-cluster-operator-56486d5b7d-pdrf6 1/1 Running 5 (10m ago) 21m ``` This overview provides insight into the user interface’s features, highlighting its intuitive design for creating and managing database instances. Nonetheless, as it currently stands in its beta phase, there is a noticeable limitation in the range of options available. This indicates the potential for a more comprehensive exploration as the tool matures and evolves in subsequent updates. ### Observations and Conclusion Reflecting on the journey from traditional database management practices to the modern, automated approaches facilitated by technologies like Kubernetes, this evolution highlights the industry’s shift towards efficiency and simplicity. The advent of containers and the introduction of orchestration and automation by Kubernetes have been game-changers. These innovations underscore the dynamic nature of technology, where continuous change and evolution are the only constants. This fast-paced development necessitates smarter, more streamlined ways to deploy databases, aligning with the perspective shared by Jensen Huang, Nvidia’s CEO, on the transformative nature of technology and the so-called “[death of coding](https://www.youtube.com/watch?v=MnT9hgFVtbc).” Huang’s vision suggests a paradigm shift where the essence of innovation and product creation is no longer tethered to deep technical expertise in specific areas but is accessible to those with the vision and ideas. Percona Everest is emblematic of this shift, offering a platform that democratizes database management by abstracting the complexities of traditional database administration. It empowers businesses, especially startups with limited resources, to focus on their core objectives rather than the intricacies of database and infrastructure management. By minimizing the need for specialized knowledge in database operations, Everest enables innovators and creators to reclaim their role at the forefront of product development and innovation. --- ### How to Permanently Disable Transparent Huge Pages (THP) on Ubuntu 22.04 - URL: https://anotherboringtechblog.com/2024/02/disabling-thp-ubuntu-22-04/ - Published: 2024-02-23 - Topic: Linux Disabling Transparent Huge Pages (THP) permanently on Ubuntu 22.04 requires editing system configuration files to apply the change at boot time. There are a few methods to achieve this, but one common approach is to use `rc.local` or a custom systemd service since Ubuntu may not have `rc.local` enabled by default in newer versions. Here, I’ll show you how to create a systemd service to disable THP. ### Step 1: Create a systemd Service File - Use your favorite text editor to create a new systemd service file. Here, we’ll use `vi`: ``` sudo vi /etc/systemd/system/disable-thp.service ``` - Add the following content to the file: ``` [Unit] Description=Disable Transparent Huge Pages [Service] Type=oneshot ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' ExecStart=/bin/sh -c 'echo never > /sys/kernel/mm/transparent_hugepage/defrag' [Install] WantedBy=multi-user.target ``` - Save and close the file. ### Step 2: Reload Systemd and Enable the Service After creating the service file, you need to reload the systemd manager configuration, enable the service to start at boot, and then start the service immediately to apply the change without rebooting. Execute the following commands in the terminal: ``` sudo systemctl daemon-reload sudo systemctl enable disable-thp.service sudo systemctl start disable-thp.service ``` ### Step 3: Verify the Changes To verify that Transparent Huge Pages have been disabled, you can use the `cat` command to check the current settings: ``` cat /sys/kernel/mm/transparent_hugepage/enabled cat /sys/kernel/mm/transparent_hugepage/defrag ``` Both commands should output `never`, indicating that Transparent Huge Pages are disabled. ### Conclusion By following these steps, you’ve created a systemd service to disable Transparent Huge Pages permanently on Ubuntu 22.04. This change will persist across reboots, ensuring that THP is disabled each time the system starts. This method is preferred for systems where THP may interfere with the performance of certain applications, especially databases like MongoDB, Redis, etc., that recommend disabling THP for better performance and efficiency. --- ### How to Create SSH Keys - URL: https://anotherboringtechblog.com/2024/02/creating-ssh-keys/ - Published: 2024-02-21 - Topic: Linux An SSH key consists of a pair of files: the private key and the public key. The private key is confidential and should never be shared, while the public key enables you to log into the servers, containers, and VMs you set up. When creating these keys using the `ssh-keygen` command, the keys are stored securely, allowing you to bypass the login prompt when connecting to your instances. To generate SSH keys, follow these steps: - Enter the following command in the shell. ``` ssh-keygen -t rsa ``` The `-t` option specifies the type of key to generate. Here, `rsa` is chosen due to its widespread use, though other types such as `dsa`, `ecdsa`, `ed25519`, or `rsa` for different security needs can be selected, depending on your requirements. From the `man` documentation: ``` $ man ssh-keygen -t dsa | ecdsa | ed25519 | rsa | rsa1 Specifies the type of key to create. The possible values are “rsa1” for protocol version 1 and “dsa”, “ecdsa”, “ed25519”, or “rsa” for protocol version 2. ``` This command initiates the key generation process. `ssh-keygen` will then prompt you to specify a storage location for the key. - Enter a passphrase for added security or press ENTER to proceed without one (not recommended for security reasons). - Confirm the passphrase by entering it again. Upon confirming the passphrase, the system generates the key pair and provides a summary of the operation, including the location of the saved key pair and a key fingerprint for verification purposes. The generated output will resemble the following: ``` $ ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/home/vinicius.grippa/.ssh/id_rsa): Enter passphrase (empty for no passphrase): Enter same passphrase again: Your identification has been saved in /home/vinicius.grippa/.ssh/id_rsa. Your public key has been saved in /home/vinicius.grippa/.ssh/id_rsa.pub. The key fingerprint is: SHA256:in5ES8pWGp43XMpjI76NX/fX7E6fhksM70h+8gljhWI vinicius.grippa@testing-server.com The key's randomart image is: +---[RSA 2048]----+ | | | | | | | . + . . | | o @ +SE o . | | O.%.. . = | | o.=.+. .= +.o.| | ..o.. .+o*ooo=| | ++o o=*o++| +----[SHA256]-----+ ``` Your private key (`id_rsa`) is stored in the `.ssh` directory and is critical for verifying your public key’s association with your user account. ### Never share your private key with anyone!  --- ### That's a Wrap: MySQL Belgian Days and FOSDEM 2024 - URL: https://anotherboringtechblog.com/2024/02/mysql-belgian-days-and-fosdem-2024/ - Published: 2024-02-06 Hey everyone! FOSDEM and MySQL Belgian days are over, and I wanted to share my impressions from an incredible 4-day journey filled with talks about databases and, of course, the famous Belgian beers. 🍻 ### 🛬 Touchdown in Brussels After a whopping 16-hour flight from Sao Paulo, I arrived in Brussels on Wednesday, a day before the MySQL Belgian days kicked off. It was all about unwinding, savoring local cuisine, getting used to the timezone, and gearing up for the events ahead. ### 🏨 Hotel Hopping A bit of a spontaneous traveler here – I ended up splitting my stay between Hygge and Marriott due to a last-minute trip decision. Pro tip: Book your stay early in Belgium! Both hotels were cozy and reasonably priced, but Hygge is better connected to the event locations – ICAB for MySQL Belgian days and ULB for FOSDEM. Uber also works in Brussels. * ##### ULB* * ##### ICAB* ### 🎤 Kicking Off MySQL Belgian Days Thursday brought the excitement of the MySQL Belgian days, initiated by [Frederic](https://www.linkedin.com/in/freddescamps/)‘s warm welcome. A big shoutout to [Frederic](https://www.linkedin.com/in/freddescamps/) for orchestrating this fabulous event – your efforts in bringing together sponsors and speakers and setting up all the logistics were nothing short of amazing! * We dived into a day filled with insights from names like Percona, ReadySet, Booking, AWS, Ubuntu Canonical, Cash App, PingCAP, and PlanetScale. The talks ranged from best practices to innovative products, enhancing our MySQL experience. ##### [Dave Stokes](https://www.linkedin.com/in/davidmstokes/) from Percona* ** ##### *[ Peter Zaitzev](https://www.linkedin.com/in/peterzaitsev/), Percona founder* * ##### [Marcelo Altmann](https://www.linkedin.com/in/marcelo-altmann/), from ReadySet* ### 🌟 Day Two: A Blend of MySQL and Oracle The second day was a mix of Oracle’s finest – [Miguel](https://www.linkedin.com/in/miguelgaraujo/), [Frederic](https://www.linkedin.com/in/freddescamps/), [Luis Soares](https://www.linkedin.com/in/losoares/), and more, sharing their expertise on MySQL and Oracle Cloud services. The variety was wide, but I personally would’ve loved a more mixed approach across both days for a more community-centric vibe. *** And hey, I’m thrilled to share that I was named one of the MySQL Rockstars of 2023! 🌟 What an honor! ##### From left to right:** Alkin, Giuseppe, Frederic, Vinnie (Sveta was not able to join the event, and she was nominated rockstar as well).* ### 🍽️ Belgian Beers and Dinners The event ended with a delightful dinner for the MySQL community with a taste of authentic Belgian beers. The perfect way to celebrate the end of an enriching conference! ### 🌐 FOSDEM: A World of Tech Saturday marked the start of FOSDEM, and the Percona team represented the open-source community brilliantly. The event was a tech enthusiast’s paradise, with sessions spanning Machine Learning, Blockchain, Python, Databases, and more. The variety was overwhelming in the best way possible! *[](https://anotherboringtechblog.com/wp-content/uploads/2024/02/slonki2.heic) [Yevhen (Evgeniy) Patlan](https://www.linkedin.com/in/evgeniypatlan/) as Slonik!   And the Percona team: ##### [Edith](https://www.linkedin.com/in/edithpuclla/), [Yevhen](https://www.linkedin.com/in/evgeniypatlan/), [Kai Wagner](https://www.linkedin.com/in/kai-wagner-b1b661152/), Nikolas, [Aleks](https://www.linkedin.com/in/aleks-abramova/) and [Maria](https://www.linkedin.com/in/mazizova/).* A standout talk for me was from my colleague Charly Batista in the Postgres dev room: And [Edith](https://www.linkedin.com/in/edithpuclla/) in the containers room talking about operators: And who could forget the fun PacMan Lego raffle by Percona to wrap up Sunday? ### 🚀 Final Thoughts Being part of an event like this is truly a game-changer. It’s not just about the tech; it’s about breaking out of your comfort zone, networking, and getting a sneak peek into the future of technology. I’m already looking forward to Brussels 2025! A huge thanks to Percona for this opportunity and to Oracle and Frederic for making it happen. See you next time! --- ### How to Use MySQL Shell to Create an InnoDB Cluster - URL: https://anotherboringtechblog.com/2024/01/mysql-shell-create-innodb-cluster/ - Published: 2024-01-28 - Topic: MySQL ### Introduction The efficiency and reliability of database management systems are paramount. This blog post is your step-by-step guide to mastering the creation of an InnoDB Cluster from scratch using MySQL Shell. Let’s dive into the detailed steps. ### Prerequisites for Setting Up an InnoDB Cluster #### Laying the Groundwork: What You Need Before You Begin Before creating an InnoDB Cluster, it’s essential to ensure specific prerequisites are in place. These foundational steps are critical for a smooth and successful setup: - **MySQL Shell Installation**: The MySQL Shell must be installed and should be the same version as your MySQL instance. Installing it locally is unnecessary, but the server with MySQL Shell must have access to the database servers. - **Network Configuration**: Ensure that all servers are on a network that allows communication between them. This step is vital for the cluster to operate correctly. - **Security Considerations**: Proper security settings and firewall configurations are imperative. These measures safeguard your cluster from unauthorized access and potential vulnerabilities. - **Unused MySQL Database Instance**: The database instance you plan to use for the cluster should not currently be used for other purposes. This helps avoid conflicts and potential data integrity issues. ### Step-by-Step Guide #### Installation and User Permissions - **MySQL Installation**: Install MySQL on the server that will be part of the cluster. - **Ensuring User Permissions**: After the installation, it’s crucial to verify that the user has all the necessary permissions to create and manage the cluster. If permissions are lacking, you’ll encounter an error message. To check and confirm the user permissions, use the following command in MySQL Shell: ``` MySQL localhost:8031 ssl JS > dba.checkInstanceConfiguration() ``` If there are permission issues, it will provide a detailed error message and the required privileges. These privileges include a range of permissions from CLONE_ADMIN to SYSTEM_VARIABLES_ADMIN: ``` Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... ERROR: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster: GRANT CLONE_ADMIN, CONNECTION_ADMIN, CREATE USER, EXECUTE, FILE, GROUP_REPLICATION_ADMIN, PERSIST_RO_VARIABLES_ADMIN, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION_APPLIER, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SELECT, SHUTDOWN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT DELETE, INSERT, UPDATE ON mysql.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_bkp.* TO 'cluster_admin'@'%' WITH GRANT OPTION; GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, INDEX, INSERT, LOCK TABLES, REFERENCES, SHOW VIEW, TRIGGER, UPDATE ON mysql_innodb_cluster_metadata_previous.* TO 'cluster_admin'@'%' WITH GRANT OPTION; For more information, see the online documentation. Dba.checkInstanceConfiguration: The account 'cluster_admin'@'%' is missing privileges required to manage an InnoDB cluster. (RuntimeError) ``` - **Verifying MySQL Instance Parameters**: To ensure that your MySQL instance is ready for cluster initiation, use the `dba.checkInstanceConfiguration()` command in MySQL Shell: ``` MySQL localhost:8031 ssl JS > dba.checkInstanceConfiguration() Validating local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031 Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | | server_id | 1 | | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. NOTE: Please use the dba.configureInstance() command to repair these issues. { "config_errors": [ { "action": "server_update", "current": "COMMIT_ORDER", "option": "binlog_transaction_dependency_tracking", "required": "WRITESET" }, { "action": "server_update+restart", "current": "OFF", "option": "enforce_gtid_consistency", "required": "ON" }, { "action": "server_update+restart", "current": "OFF", "option": "gtid_mode", "required": "ON" }, { "action": "server_update+restart", "current": "1", "option": "server_id", "required": "" } ], "status": "error" } ``` - **Applying Necessary Parameters:** Apply the necessary parameters for your cluster manually or using the `dba.configureInstance()` command. This step configures your MySQL instance for InnoDB Cluster use and may require server updates or restarts. ``` MySQL localhost:8031 ssl JS > dba.configureInstance() Configuring local MySQL instance listening at port 8031 for use in an InnoDB cluster... This instance reports its own address as single-8031:8031 applierWorkerThreads will be set to the default value of 4. NOTE: Some configuration options need to be fixed: +----------------------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +----------------------------------------+---------------+----------------+--------------------------------------------------+ | binlog_transaction_dependency_tracking | COMMIT_ORDER | WRITESET | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | | server_id | 1 | | Update read-only variable and restart the server | +----------------------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. Do you want to perform the required configuration changes? [y/n]: Y Do you want to restart the instance after configuring it? [y/n]: Y Configuring instance... The instance 'single-8031:8031' was configured to be used in an InnoDB cluster. Restarting MySQL... NOTE: MySQL server at single-8031:8031 was restarted. ``` -  **Creating the Cluster: **Create the cluster using the `dba.createCluster()` command. This step involves adding the seed instance and ensuring the configuration is suitable for the cluster. ``` MySQL localhost:8031 ssl JS > dba.createCluster('myCluster', {'localAddress':'localhost:38031'}) A new InnoDB cluster will be created on instance 'localhost:8031'. Validating instance configuration at localhost:8031... This instance reports its own address as single-8031:8031 Instance configuration is suitable. Creating InnoDB cluster 'myCluster' on 'single-8031:8031'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure. ``` - **Cluster Validation: **Use the command to confirm the cluster’s creation and check its status. This step verifies that your cluster is operational and ready for use. ``` MySQL localhost:8031 ssl JS > cluster = dba.getCluster() MySQL localhost:8031 ssl JS > cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "single-8031:8031", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "single-8031:8031": { "address": "single-8031:8031", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": null, "role": "HA", "status": "ONLINE", "version": "8.0.31" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "single-8031:8031" } ``` ### **Conclusion** Creating an InnoDB cluster becomes more simplified and fluid using the MySQL shell. Following the detailed instructions in this blog post ensures that the process is carried out safely and efficiently. ### **Additional Resources** For further reading and detailed documentation, visit: - [Configuring Cluster Instance Ports](https://dev.mysql.com/doc/mysql-shell/8.0/en/configuring-cluster-instance-ports.html) - [Setting Up InnoDB Cluster and MySQL Router](https://dev.mysql.com/doc/mysql-shell/8.0/en/setting-up-innodb-cluster-and-mysql-router.html#creating-innodb-cluster) This blog post consolidates the documentation into an easily digestible format, covering all the essential steps and information needed for creating an InnoDB Cluster using MySQL Shell. Let me know if you need any further adjustments or additional sections! --- ### How to Add a New MySQL Server Node to an InnoDB Cluster - URL: https://anotherboringtechblog.com/2024/01/adding-innodb-cluster-nodes/ - Published: 2024-01-24 - Topic: MySQL ## Introduction It is possible to expand the capacity and the availability of an InnoDB Cluster by adding more MySQL servers. For example, a 5-node InnoDB cluster can tolerate up to 2 node failures, while a 3-node InnoDB cluster can tolerate only a single failure. ## Objective This guide will navigate you through the process of integrating a new MySQL server into an existing InnoDB Cluster, leveraging the MySQL Shell to execute the requisite commands. Initially, the cluster encompasses the following servers: - vinnie-mysql-8-01 - vinnie-mysql-8-02 The node to be introduced is: - vinnie-mysql-8-03 ## Preconditions Before the server integration, it’s important to establish the following prerequisites: - **Existing Cluster**: The InnoDB Cluster is operational, with all nodes functioning optimally. - **Network Configurations**: The new MySQL server is configured for seamless network integration with the existing cluster nodes. - **MySQL Versions**: The new server operates on the identical MySQL version as the other cluster nodes. - **Data Backup**: Data backups are conducted regularly and verified for integrity. ## Adding a New Node to the InnoDB Cluster ### Preparation - **MySQL Installation**: Verify MySQL’s installation and proper configuration on the new server, ensuring version consistency with the cluster’s existing nodes. - **Network Configuration**: Confirm the new server’s network compatibility and communication capabilities with the existing cluster nodes. ### Adding the node - **Connecting to the Cluster**: Establish a connection to the MySQL Shell on one of the existing cluster nodes: ``` # mysqlsh MySQL JS > shell.connect('vinicius.grippa@localhost:3306') MySQL localhost:3306 ssl JS > var cluster = dba.getCluster() MySQL localhost:3306 ssl JS > cluster.status() ``` - **Adding the New Server**: Seamlessly integrate the new server into the cluster: ``` MySQL localhost:3306 ssl JS > cluster.addInstance("vinicius.grippa@vinnie-mysql-8-03:3306") ``` And you should see a similar output: ``` The safest and most convenient way to provision a new instance is through automatic clone provisioning, which will completely overwrite the state of 'vinnie-mysql-8-03:3306' with a physical snapshot from an existing cluster member. To use this method by default, set the 'recoveryMethod' option to 'clone'. The incremental state recovery may be safely used if you are sure all updates ever executed in the cluster were done with GTIDs enabled, there are no purged transactions and the new instance contains the same GTID set as the cluster or a subset of it. To use this method by default, set the 'recoveryMethod' option to 'incremental'. Incremental state recovery was selected because it seems to be safely usable. Validating instance configuration at vinnie-mysql-8-03:3306... This instance reports its own address as vinnie-mysql-8-03:3306 Instance configuration is suitable. NOTE: Group Replication will communicate with other members using 'vinnie-mysql-8-03:3306'. Use the localAddress option to override. * Checking connectivity and SSL configuration... A new instance will be added to the InnoDB Cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Adding instance to the cluster... Monitoring recovery process of the new cluster member. Press ^C to stop monitoring and let it continue in background. State recovery already finished for 'vinnie-mysql-8-03:3306' The instance 'vinnie-mysql-8-03:3306' was successfully added to the cluster. ``` ## Conclusion Incorporating a new server into an InnoDB Cluster is a process that has been greatly facilitated by MySQL shell. The process is relatively straightforward and MySQL will handle the initial sync by itself. Please do not hesitate to reach out if you require additional insights, modifications, or further information. ## Useful Links For further guidance, refer to the MySQL documentation on adding instances to a cluster: [MySQL Documentation on Adding Instances to a Cluster](https://dev.mysql.com/doc/mysql-shell/8.0/en/add-instances-cluster.html). --- ### How to Execute a Switchover in InnoDB Cluster - URL: https://anotherboringtechblog.com/2024/01/executing-switch-over-in-innodb-cluster/ - Published: 2024-01-22 ## Introduction The process of executing a switchover operation is important for an InnoDB cluster. It encompasses not merely routine maintenance but extends to recovery from system failures. The core of this operation involves promoting a replica node to the PRIMARY role methodically and securely. ## Objective This blog post is a comprehensive guide for database engineers and system administrators. It delineates the steps in executing a switchover by employing MySQL Shell within an InnoDB Cluster environment. The cluster under consideration comprises the following nodes: - **vinnie-mysql-8-01 – 10.110.3.43** - **vinnie-mysql-8-02 – 10.110.3.79** - **vinnie-mysql-8-03 – 10.110.3.197** ## Pre-conditions Before commencing the switchover process, it is imperative to establish certain pre-conditions to ascertain that the environment is primed for a successful operation: - **Cluster Health**: Ensuring operational status and synchronization across all nodes (`vinnie-mysql-8-01`, `vinnie-mysql-8-02`, `vinnie-mysql-8-03`). - **Data Backup**: Availability of comprehensive, validated backups for each node. - **Documentation and Knowledge**: Proper documentation of current configurations, MySQL versions, and detailed understanding of the network architecture peculiarities. - **Monitoring Tools**: Deployment of tools configured to monitor the cluster’s status, performance metrics, and log files. - **Maintenance Window**: Schedule an appropriate maintenance window to carry out the switchover. ## Switchover Procedure The execution of a switchover using MySQL Shell encompasses the following procedural steps: ### Preparation: - **Cease Write Operations**: Ascertain that no applications are executing write operations on the current PRIMARY. During switch over, the node transitions to read mode, potentially causing write operations to fail. For instance: ``` FATAL: mysql_stmt_execute() returned error 1290 (The MySQL server is running with the --super-read-only option so it cannot execute this statement) for query 'INSERT INTO sbtest20 (id, k, c, pad) VALUES (?, ?, ?, ?)' FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:488: SQL error, errno = 1290, state = 'HY000': The MySQL server is running with the --super-read-only option so it cannot execute this statement ``` - **Target Server Identification**: Determine the target server for promotion, such as vinnie-mysql-8-02. - **PRIMARY Identification and Connection**: ``` shell.connect('vinnie@localhost:3306') var cluster = dba.getCluster() ``` - **Cluster Status Review**: ``` MySQL localhost:3306 ssl JS > cluster.status() ``` ### Execution of Switch Over Initiate the switchover process: ``` MySQL localhost:3306 ssl JS > cluster.setPrimaryInstance('vinnie-mysql-8-02:3306'); ``` System feedback will confirm the new PRIMARY and delineate the status of the other instances: ``` Setting instance 'vinnie-mysql-8-02:3306' as the primary instance of cluster 'mycluster'... Instance 'vinnie-mysql-8-03:3306' remains SECONDARY. Instance 'vinnie-mysql-8-02:3306' remains PRIMARY. Instance 'vinnie-mysql-8-01:3306' remains SECONDARY. The instance 'vinnie-mysql-8-02:3306' was successfully elected as primary. ``` ### Post-Switch Over - **Verification of New PRIMARY**: ``` var cluster = dba.getCluster() cluster.status() ``` ``` MySQL localhost:3306 ssl JS > cluster.status() { "clusterName": "mycluster", "defaultReplicaSet": { "name": "default", "primary": "vinnie-mysql-8-02:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "vinnie-mysql-8-01:3306": { "address": "vinnie-mysql-8-01:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.34" }, "vinnie-mysql-8-02:3306": { "address": "vinnie-mysql-8-02:3306", "memberRole": "PRIMARY", "mode": "R/W", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.34" }, "vinnie-mysql-8-03:3306": { "address": "vinnie-mysql-8-03:3306", "memberRole": "SECONDARY", "mode": "R/O", "readReplicas": {}, "replicationLag": "applier_queue_applied", "role": "HA", "status": "ONLINE", "version": "8.0.34" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "vinnie-mysql-8-02:3306" } ``` - Alternatively: ``` MySQL localhost:3306 ssl JS > \sql SHOW STATUS LIKE 'group_replication_primary_member'; ``` ``` MySQL localhost:3306 ssl JS > \sql SHOW STATUS LIKE 'group_replication_primary_member'; +----------------------------------+--------------------------------------+ | Variable_name | Value | +----------------------------------+--------------------------------------+ | group_replication_primary_member | a5d3a627-9e1e-11ee-ac21-42010adc014f | +----------------------------------+--------------------------------------+ 1 row in set (0.0026 sec) ``` - **Error Log Review**: Meticulously inspect the error logs for the servers, with particular emphasis on the new PRIMARY and other members, to confirm operational stability. For example: ``` 2024-01-22T23:01:12.349328Z 228 [System] [MY-013214] [Repl] Plugin group_replication reported: 'Starting group operation local execution: Primary election change' 2024-01-22T23:01:12.377228Z 0 [System] [MY-011507] [Repl] Plugin group_replication reported: 'A new primary with address vinnie-mysql-8-02:3306 was elected. The new primary will execute all previous group transactions before allowing writes. Enabling conflict detection until the new primary applies all relay logs.' 2024-01-22T23:01:12.485458Z 229 [System] [MY-011565] [Repl] Plugin group_replication reported: 'Setting super_read_only=ON.' 2024-01-22T23:01:12.486126Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_disable_super_read_only_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "1" will be run.' 2024-01-22T23:01:12.486215Z 181 [System] [MY-011566] [Repl] Plugin group_replication reported: 'Setting super_read_only=OFF.' 2024-01-22T23:01:12.486344Z 181 [System] [MY-013731] [Repl] Plugin group_replication reported: 'The member action "mysql_start_failover_channels_if_primary" for event "AFTER_PRIMARY_ELECTION" with priority "10" will be run.' 2024-01-22T23:01:12.786657Z 229 [System] [MY-011510] [Repl] Plugin group_replication reported: 'This server is working as primary member.' 2024-01-22T23:01:12.789131Z 0 [System] [MY-013213] [Repl] Plugin group_replication reported: 'Configuration operation 'Primary election change' terminated. Primary server switched to: a5d3a627-9e1e-11ee-ac21-42010adc014f' ``` ## Conclusion The switchover process within an InnoDB Cluster is critical for maintenance and effective failure recovery. Your inquiries, as modifications or additional detail requests regarding any sections, are welcome. ## Additional Resources For an extensive understanding and adherence to best practices, refer to the official MySQL documentation on [Group Replication in Single-Primary Mode](https://dev.mysql.com/doc/refman/8.0/en/group-replication-single-primary-mode.html). --- ### MySQL Brazil Meetup 2023 - Informações do Evento e Agenda - URL: https://anotherboringtechblog.com/2023/10/mysql-meetup-brazil-informacoes-do-evento-e-agenda/ - Published: 2023-10-05 ***Temos definido nosso MySQL Meetup!***   Salve a todos os nerds! Temos definido nosso dia de banco de dados MySQL! **Data**: 02 de Dezembro, Sábado, 2023** Horário**: 9:00 AM – 5:30 PM (Horário de Brasilia)** Local**: [Oracle do Brasil Sistemas, Rua Dr. José Áureo Bustamante 455, bl. 110, São Paulo, SP, BR, 04710-090](https://maps.app.goo.gl/KTy872sMxZUYmgNU6) Teremos um dia todo com palestras sobre os mais diversos tópicos sobre MySQL que vão desde ferramentas opensource até nuvem Oracle. E no final do dia, encerraremos com uma visista ao data center da Oracle para aqueles que queiram conhecer mais sobre os Exadatas e a nuvem Oracle. **Não perca essa chance!** Se inscreva aqui: ### [Formulário de Inscrição](https://docs.google.com/forms/d/e/1FAIpQLScvr0x4QoZGXdSz-bjbmfMmiD5X13A0FoT9tCat6ZuG4cWnFA/viewform) E a agenda do nosso evento segue a todo vapor. No momento da publicação deste blog temos duas talks já definidas! Abaixo a agenda do evento: - **Morning Coffee and Welcome** **Horário: **9-9:10** - Avaliando grandes cargas e análises de dados com MySQL HeatWave Lakehouse** **Horário: **9:10-10:05 - **Speaker**: [Narciso Oliveira Júnior](https://www.linkedin.com/in/narcisooliveira/) - **Descrição: **À medida que o número de dados e dispositivos conectados cresce, aumenta a procura por uma melhor experiência do usuário e por serviços de valor agregado. A IA desempenha um papel cada vez mais importante nesta procura. O desenvolvimento de modelos de IA requer um pré-processamento eficiente da grande quantidade de dados gerados pelos mais diversos dispositivos. O MySQL HeatWave, especificamente o recurso Lakehouse, fornece uma solução eficiente, aprimorando as operações de E/S de dados, carregando dados diretamente do armazenamento de objetos na memória. Aprenda como o MySQL HeatWave Lakehouse é utilizado para carregar, analisar e executar algoritmos de Machine Learning com eficiência em grandes quantidades de dados heterogêneos, enquanto discutimos as implicações para a construção de uma plataforma flexível de análise de dados utilizando MySQL na OCI.**** - **Coffee Break: 15mins** **Horário: **10:05 – 10:20 - **MySQL Shell para Database Engineers: A melhor ferramenta de administração do MySQL** **Horário: **10:20 – 11:15am - **Speaker: **[Herbert Rogério Bezerra de Menezes](https://www.linkedin.com/in/herbertrbmenezes/) - **Descrição:** O MySQL Shell é um cliente avançado e editor de código para MySQL. Esta apresentação mostrará os principais recursos do MySQL Shell. Além da funcionalidade SQL, semelhante ao client mysql, o MySQL Shell fornece recursos de script para JavaScript e Python e inclui APIs para trabalhar com MySQL X DevAPI permite que você trabalhe com dados relacionais e de documentos, AdminAPI permite que você trabalhe com InnoDB Cluster, InnoDB ClusterSet e InnoDB ReplicaSet, além de ferramentas de backup e restore extremamente rápidos e seguros! - - **ProxySQL no MySQL: Apenas um load balancer?** **Horário:** 11:15 – 12:10 - **Speaker**: [Roberto Garcia de Bem](https://www.linkedin.com/in/roberto-de-bem/) - *(fig. Profile photo of Roberto Garcia de Bem)***Descrição:** ProxySQL é um proxy Open-Source projetado para o MySQL e suas variantes, capaz de se comunicar atraves do protocolo do MySQL, utilizado mais frequentemente como balanceador de carga. Além de suas funcionalidades como balanceador de carga, o ProxySQL oferece grande quantidade de recursos(Multiplexing,Regras de Query, Reescrita Query, detecção de Failover, Cluster ProxySQL entre outros) que podem ser de grande utilidade em nossas atividades diárias. Durante a apresentação, mostraremos algumas das suas capacidades e potenciais usos, explorando sua arquitetura e camadas de configuração. Além disso, demonstraremos o processo de instalação e configuração em diferentes arquiteturas possíveis(Replica simples, PXC/Galera, Group Replication). - **Lunch Break & Discussion ** **Horário: **12:10-13:20 - **Ferramentas e métodos para análise de performance em MySQL** **Horário: **13:20-14:15 - **Speaker**: [Leonardo Bacchi Fernandes](https://www.linkedin.com/in/leonardo-bacchi-fernandes-3221b7141/) - **Descrição: **Nessa apresentação iremos discutir por onde começar e o que temos a nossa disposição para fazer uma análise de performance em MySQL. Utilizando como base casos reais, revisaremos alguns comandos chaves de Linux (vmstat, iostat, top, etc) e como a utilização de recursos no sistema operacional direciona nossa análise do banco de dados. Veremos alguns comandos e ferramentas que o MySQL possui para análise (SHOW PROCESSLIST, SHOW ENGINE INNODB STATUS, mysqladmin, pt-stalk, slow query log, etc), e quando utiliza-las. Por fim, discutiremos algumas ferramentas mais avançadas que podemos recorrer pra problemas complexos, quando os comandos de MySQL não são suficientes para entendermos o problema (perf, flamegraphs, pt-pmp, coredumps). - **Migração de Data Center para Oracle OCI Brasil: Otimização do MySQL 8** **Horário: **14:15-15:10 - **Speaker**: [Ricardo Rodrigues](https://www.linkedin.com/in/ricardo-rodrigues-45386525/) -  **Descrição:**Este case destaca a migração bem-sucedida para a Oracle Cloud Infrastructure (OCI) no Brasil, onde desafios incluíam longos tempos de backup, interrupções devido a problemas de roteamento e bloqueios no ambiente de produção. Com a adoção das ferramentas XtraBackup e MyDumper para otimização do backup, foi alcançada uma redução impressionante no tempo de backup, de 18 horas para apenas 2 horas. Além disso, a migração para OCI permitiu que todas as operações de backup fossem executadas eficientemente no ambiente standby, garantindo a continuidade dos serviços no ambiente de produção.   - **Coffee break: 15mins** **Horário: **15:10-15:25 - **MySQL: 7 Dicas de Ouro e 7 Erros a Evitar** **Horário: **15:25-16:20 - **Speaker**: [Vinicius Grippa](https://www.linkedin.com/in/vinicius-grippa/) - **Descrição: **Vamos conversar um pouco sobre o um conjunto de estratégias eficazes e erros comuns a serem evitados para usuários do MySQL. Foca em sete dicas cruciais para melhorar a eficiência e o desempenho do banco de dados, como a realização de backups regulares, otimização de consultas SQL e implementação de melhores práticas. Além disso, destaca sete erros frequentes, incluindo a negligência na segurança dos dados, uso inadequado de índices e má gestão de recursos do sistema. Este guia é focado para desenvolvedores e administradores de banco de dados que buscam otimizar a utilização do MySQL, garantindo alta performance e baixo custo. - **Closing session** **Horário: **16:20-16:30 - **LAB visit** **Horário: **16:30-17:15 Também aproveitar a oportunidade para agradecer as empresas e pessoas que estão colaborando com este evento: [*(fig. Open Source Database Software Support & Services | Percona)*](https://www.percona.com/software/mysql-database/percona-server) [*(fig. Ficheiro:Oracle Logo.svg – Wikipédia, a enciclopédia livre)*](https://www.oracle.com/br/)   --- ### [PT-BR] Rotacionando os logs de erro e consultas lentas do MySQL com logrotate - URL: https://anotherboringtechblog.com/2023/10/rotacionando-logs-mysql-logrotate/ - Published: 2023-10-02 Banco de dados geram toneladas de logs, e é essencial gerenciá-los corretamente. Os logs ajudam os DBAs a entender o que está acontecendo nos bastidores, rastrear erros e otimizar o desempenho de consultas lentas. No entanto, esses logs podem se tornar um problema ao longo do tempo, especialmente se consumirem muito espaço em disco e não forem gerenciados adequadamente. #### Por que rotacionar logs? Os logs são como o diário de um sistema; eles acompanham tudo. Os logs podem crescer bastante com o tempo e se tornar um problema para o espaço em disco (especialmente ao usar um [log_error_verbosity](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_verbosity) maior ou um [long_query_time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_long_query_time) mais baixo). Sem falar que, fazer uma pesquisa em um arquivo de logs enorme pode ser uma tarefa tediosa. É por isso que rotacionar os logs se torna essencial, especialmente em bancos de dados como o MySQL. #### Configurando o `logrotate` Embora o MySQL cuide de alguns de seus logs, certos logs, como os de erro e consultas lentas, não são rotacionados automaticamente. Felizmente, na maioria das distribuições Linux, existe uma ferramenta chamada `logrotate` projetada especificamente para essa tarefa. Configurar o `logrotate` para o MySQL é relativamente simples. Aqui está um guia passo a passo: - **Crie um Arquivo de Configuração**: Crie um arquivo de configuração específico para o MySQL. Use o comando: ``` $ sudo vi /etc/logrotate.d/mysql ``` - **Adicione o Conteúdo de Configuração**: Dentro deste arquivo, adicione o seguinte conteúdo: ``` /var/log/mysql/mysql_error.log /var/log/mysql/slow_query.log { compress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/bin/mysql -e 'FLUSH SLOW LOGS; FLUSH ERROR LOGS;' endscript rotate 30 } ``` **Nota**: Certifique-se de atualizar os nomes dos logs de erro do MySQL e de consultas lentas na primeira linha de acordo com a configuração do seu servidor. Você pode verificar seus caminhos de logs com a consulta: ``` SELECT @@global.log_error, @@global.slow_query_log_file; ``` - **Entendendo a Configuração**: - **compress**: Comprime os logs rotacionados para economizar espaço. - **create 660 mysql mysql**: Garante que os novos arquivos de logs tenham as permissões e propriedade corretas. - **size 1G**: Rotaciona o log uma vez que ele atinja 1 Gigabyte em tamanho. - **dateext**: Adiciona uma extensão de data ao nome do arquivo de log rotacionado. - **notifempty**: Não rotacione o arquivo de log se ele estiver vazio. - **missingok**: Se o arquivo de log estiver faltando, passe para o próximo passo sem emitir uma mensagem de erro. - **sharedscripts**: informa ao `logrotate` para verificar todos os logs antes de executar o script postrotate. O script é executado apenas uma vez se um ou ambos os logs forem rotacionados. Se nenhum dos logs for rotacionado, o script `postrotate` não é executado. - **postrotate**: Comandos para executar após a rotação do log. - **rotate 30**: Mantém 30 arquivos de logs rotacionados antes de excluir o mais antigo. - **Ative e Teste**: Depois de salvar e fechar o arquivo de configuração, o `logrotate` irá pegá-lo automaticamente durante sua próxima execução. Você também pode forçar uma execução para testar sua configuração: ``` $ logrotate --force /etc/logrotate.d/mysql ``` - **Leitura Adicional**: Como todas as ferramentas, há muito mais no `logrotate` do que a configuração básica. A documentação oferece um mergulho profundo: [[Documentação do Logrotate](https://manpages.ubuntu.com/cgi-bin/search.py?cx=003883529982892832976%3A5zl6o8w6f0s&cof=FORID%3A9&ie=UTF-8&titles=404&lr=lang_en&q=logrotate.8)] Em conclusão, embora os logs sejam inestimáveis para manter e solucionar problemas dos sistemas, gerenciá-los eficientemente garante que seus sistemas funcionem sem problemas. O `logrotate` oferece uma solução prática para manter seus logs do MySQL em ordem. --- ### How to Rotate MySQL Slow and Error Logs Using Logrotate - URL: https://anotherboringtechblog.com/2023/10/rotating-mysql-slow-and-error-logs-with-logrotate/ - Published: 2023-10-02 Databases generate tons of logs, and it’s essential to manage them correctly. Logs help DBAs understand what’s happening behind the scenes, trace errors, and optimize the performance of slow queries. However, these logs can become a hassle over time, especially if they consume too much disk space and are not appropriately managed. #### Why Rotate Logs? Logs are like the diary of a system; they keep track of everything. Logs can grow quite large over time and become an issue for disk space (especially when using higher [log_error_verbosity](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_verbosity) or low [long_query_time](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_long_query_time)). Not to mention, sifting through an enormous log file can be a tedious task. This is why rotating logs become essential, especially in databases like MySQL. #### Setting Logrotate While MySQL takes care of some of its logs, certain logs, like the error and slow query logs, aren’t automatically rotated. Thankfully, on most Linux distributions, a tool called `logrotate` is explicitly designed to handle this task. Setting up `logrotate` for MySQL is relatively straightforward. Here’s a step-by-step guide: 1. **Create a Configuration File**: Create a configuration file specifically for MySQL. Use the command: ``` $ sudo vi /etc/logrotate.d/mysql ``` 2. **Add Configuration Content**: Inside this file, add the following content: ``` /var/log/mysql/mysql_error.log /var/log/mysql/slow_query.log { compress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/bin/mysql -e 'FLUSH SLOW LOGS; FLUSH ERROR LOGS;' endscript rotate 30 } ``` Note: Ensure you update the MySQL error log and Slow Query log filenames in the first line according to your server’s configuration. You can check your log paths with the query: ``` SELECT @@global.log_error, @@global.slow_query_log_file; ``` 3. **Understanding the Configuration**: – `compress`: Compresses the rotated logs to save space.** – `create 660 mysql mysql`: Ensures the new log files have the correct permissions and ownership. – `size 1G` : Rotates the log once it reaches 1 Gigabyte in size. – `dateext`: Adds a date extension to the rotated log filename. – `notifempty` : Do not rotate the log file if it is empty. – `missingok`: If the log file is missing, go to the next one without issuing an error message. –`sharedscripts`: tells logrotate to check all the logs for that configuration block before running the `postrotate` script. The script runs only once if one or both logs are rotated. If none of the logs is rotated, the `postrotate` script doesn’t run. – `postrotate`: Commands to run after log rotation. – `rotate 30`: Keeps 30 rotated log files before deleting the oldest. 4. Activate and Test**: After you’ve saved and closed the configuration file, `logrotate` will automatically pick it up during its next run. You can also force a run to test your configuration: ``` $ logrotate --force /etc/logrotate.d/mysql ``` 5. **Further Reading**: As with all tools, there’s much more to logrotate than the basic setup. The documentation provides a deep dive: [[Logrotate Documentation](http://manpages.ubuntu.com/manpages/zesty/man8/logrotate.8.html)] In conclusion, while logs are invaluable for maintaining and troubleshooting systems, managing them efficiently ensures your systems run smoothly. `logrotate` offers a handy solution to keep your MySQL logs in check. --- ### Understanding and Creating a Deadlock in MySQL for Beginners - URL: https://anotherboringtechblog.com/2023/09/understanding-and-creating-a-deadlock-in-mysql-for-beginners/ - Published: 2023-09-26 Are you new to MySQL and hearing the term “deadlock” for the first time? Don’t worry! This article will explain a deadlock and provide a simple guide on simulating one. **What is a Deadlock?** A deadlock happens when two transactions block each other, waiting for the other to finish, but neither can proceed. Think of it like two cars stuck at a crossroad, each waiting for the other to move first. **Why Simulate a Deadlock?** Simulating a deadlock helps you understand how they occur and how to monitor or detect them. It’s like practicing fire drills; you hope it doesn’t happen, but you know what to expect and how to act if it does. **Steps to Simulate a Deadlock:** **1. Setting the Stage:** On one session (think of it as one user or terminal), run the following commands: ``` # session 1 CREATE TABLE t (i INT) ENGINE = InnoDB; INSERT INTO t (i) VALUES(1); START TRANSACTION; SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ``` **2. Introducing a Blocker:** On a second session, initiate a blocker with: ``` # session 2 START TRANSACTION; DELETE FROM t WHERE i = 1; ``` **3. Creating the Deadlock:** Now, go back to the first session and run: ``` # session 1 DELETE FROM t WHERE i = 1; ``` To see the deadlock in action, use the `SHOW ENGINE INNODB STATUS\G` command. If you’ve set up any specific deadlock monitoring settings like `innodb_print_all_deadlocks`, you’ll be able to view the deadlock in the MySQL error logs. Here is an example of the output: ``` 2023-09-27T01:26:36.606039Z 10 [Note] [MY-012468] [InnoDB] Transactions deadlock detected, dumping detailed information. 2023-09-27T01:26:36.606076Z 10 [Note] [MY-012469] [InnoDB] *** (1) TRANSACTION: TRANSACTION 1617, ACTIVE 5 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 11, OS thread handle 140109082932992, query id 98 localhost msandbox updating DELETE FROM t WHERE i = 1 2023-09-27T01:26:36.606115Z 10 [Note] [MY-012469] [InnoDB] *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1617 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606366Z 10 [Note] [MY-012469] [InnoDB] *** (2) TRANSACTION: TRANSACTION 1618, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 10, OS thread handle 140109084276480, query id 99 localhost msandbox updating DELETE FROM t WHERE i = 1 2023-09-27T01:26:36.606399Z 10 [Note] [MY-012469] [InnoDB] *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1618 lock mode S Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606592Z 10 [Note] [MY-012469] [InnoDB] *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 1618 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 6; hex 000000000203; asc ;; 1: len 6; hex 000000000650; asc P;; 2: len 7; hex 82000000930110; asc ;; 3: len 4; hex 80000001; asc ;; 4: SQL NULL; 2023-09-27T01:26:36.606744Z 10 [Note] [MY-012469] [InnoDB] *** WE ROLL BACK TRANSACTION (1) ``` ### Conclusion Deadlocks can initially seem intimidating, but with understanding and practice, they become more manageable. This simulation exercise is an excellent way for beginners to grasp the concept and practice handling such scenarios. Always be cautious and avoid running such tests on production databases to ensure data safety and integrity. --- ### Profiling MySQL using perf and Flame Graphs - URL: https://anotherboringtechblog.com/2023/09/profiling-mysql-using-perf-and-flame-graphs/ - Published: 2023-09-26 A common task in performance analysis is identifying which code sections consume the most CPU time. This often requires profiling stack traces. Enter Flame graphs – a game-changer in how we visualize these profiles. They provide an intuitive visual representation of the application’s most CPU-intensive parts. In this article, we’ll take a look at how to generate these flame graphs using `perf`. ## Process **1) Installing `perf`** Depending on your Linux distribution, the installation method may differ. ##### On Ubuntu: ``` $ sudo apt-get install linux-tools-$(uname -r) linux-tools-generic -y ``` ##### On RHEL and its clones: ``` $ sudo yum install -y perf ``` **2) Capturing Performance Data** For our example, we’ll be capturing performance data from the `mysqld` process for 60 seconds. ``` $ sudo perf record -a -g -F99 -p $(pgrep -x mysqld) -- sleep 60; ``` In this command: - The `-a` flag allows us to capture data across all CPU cores. - `-g` facilitates call-graph (or stack trace) recording in both user and kernel spaces. - With `-F99`, we’re instructing `perf` to sample data 99 times every second. **3) Converting Captured Data** Once data is captured, it’s initially in a binary format. To make sense of this data, we need to convert it into something more readable. ``` $ sudo perf script > perf.script ``` Although it’s now in a human-readable format, visualizing this data in the form of a Flame Graph is ideal to truly glean insights with ease. **4) Installing the FlameGraph Toolkit** We need the FlameGraph toolkit by Brendan Gregg to create our flame graphs. ``` $ mkdir -p ~/src $ cd ~/src $ git clone https://github.com/brendangregg/FlameGraph ``` **5) Producing the Flame Graph** We can produce our flame graph with the toolkit installed and our performance data ready. ``` ~/src/FlameGraph/stackcollapse-perf.pl perf.script | ~/src/FlameGraph/flamegraph.pl > flamegraph.svg ``` You can now open `flamegraph.svg` in your preferred web browser to interactively explore the graph. Here is an example of a flame graph (the image is static since WordPress does not allow SVG files): ## Further Reading For those interested in diving deeper into profiling with `perf` and Flame Graphs: - [CPU Flame Graphs](http://www.brendangregg.com/flamegraphs.html) ## In Conclusion Flame graphs provide an invaluable tool for performance analysis. Providing a visual representation of CPU consumption allows developers and system administrators to quickly identify and troubleshoot performance bottlenecks. Using `perf` in combination with the FlameGraph toolkit makes the process of generating and interpreting these graphs straightforward. Happy profiling! --- ### The Ins and Outs of innodb_flush_log_at_trx_commit - URL: https://anotherboringtechblog.com/2023/09/mysql-innodb-flush-log-performance/ - Published: 2023-09-24 Today, we’re going to take a journey through one of MySQL’s performance guardians – the `innodb_flush_log_at_trx_commit`. The `innodb_flush_log_at_trx_commit` has 3 valid values: 0, 1, and 2. Let’s see each of them. ### Option 1: The Safe Harbor (innodb_flush_log_at_trx_commit = 1) By default, MySQL is set up with a focus on safety. Imagine this as saving your work every time you make a change; that’s what MySQL does with this setting. It writes your transaction data safely on the disk whenever a change and a commit happens. So, even with a power outage, your data remains secure. It’s a bit slower, but it’s the best choice if you want to play it safe. ### Option 2: The Speed Racer (innodb_flush_log_at_trx_commit = 2) If you’re the kind who likes to live on the edge for a bit of speed, this one’s for you! This setting is faster because MySQL considers the write committed once it is present in the OS buffers. Then the OS will flush to disk roughly every 1 second. However, tread carefully, as choosing speed here means losing about a second’s worth of data if something goes wrong with the operating system. It’s a bit of a gamble, but it can be worth it if you’re after speed. ### Option 3: The Speed Demon (innodb_flush_log_at_trx_commit = 0) Taking it up a notch, this setting is all about speed, but with it comes a higher risk. Here, MySQL considers a transaction good once it’s in the MySQL log buffer. That write then flushes from the log buffer to the redo log file on disk every 1 second or when the OS flushes, which means you could lose some recent transactions in case of a MySQL or an OS crash. It’s the fastest option but requires a bit of bravery due to the risk involved. Here is a diagram to better illustrate the idea of each value: ### ### Which One to Choose? #### Playing it Safe (Option 1) If you’re running a standalone MySQL setup and cannot afford to lose your data at any cost, sticking to the default setting is your best bet. #### Daring Choices (Option 0/2) If you are okay with taking some risks for enhanced performance, you might consider options 0 or 2. They can be handy when you have backup measures or if some data loss is acceptable. They can also be very useful for replicas and Galera or InnoDB clusters. ### Wrapping Up Think of the `innodb_flush_log_at_trx_commit` setting as a lever, you can pull to balance speed and safety according to your comfort level. If you’re just starting out, it’s great to know that MySQL has got your back with a safe default. As you get more comfortable, feel free to test out the faster settings to see what works best for your environment. Remember, every setting has pros and cons, so choose the one that best fits your needs. Happy databasing! --- ### How To Estimate Redo Log Capacity - URL: https://anotherboringtechblog.com/2023/09/dynamic-redo-log-sizing-mysql-8030/ - Published: 2023-09-21 With MySQL 8.0.30 comes a feature that is a game changer in ensuring optimal MySQL performance – the dynamic redo log sizing. ### Overcoming Previous Limitations Before version 8.0.30 came into play, redo log configuration was static, set once during database setup and rarely touched thereafter due to the MySQL restart required to effect any changes — a restart many chose to avoid to sidestep downtime. The launch of MySQL 8.0.30 changed the game, introducing the much-anticipated flexibility in redo log sizing, enabling alterations without needing a MySQL restart. ### How It Works Implementing resizing is relatively straightforward. All that is needed is to input the following MySQL command: ``` mysql> SET GLOBAL innodb_redo_log_capacity = 2*1024*1024*1024; ``` ### Estimating the Redo Log Capacity The fresh batch of status variables introduced in MySQL 8.0.30 has made it possible to effectively estimate the redo log size, enhancing database performance tuning. Here’s how you can do it: Start with executing the query: ``` mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_current_lsn'; SELECT SLEEP(60); SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_current_lsn'; ``` Next, use the results derived from the initial query to run the following: ``` mysql > SELECT ABS(20641693317 - 20903377487) / 1024 / 1024 AS MB_per_min; +--------------+ | MB_per_min | +--------------+ | 249.56147194 | +--------------+ 1 row in set (0.00 sec) ``` In this instance, we observe a writing rate of approximately 250MB per minute in the redo log files. Therefore, a reasonable value for the redo log size would be the product of this rate and the number of minutes in an hour, yielding a size of 15GB. This approach aids in optimizing the redo log size to suit your database’s transaction rate, enhancing its performance. ### Conclusion MySQL 8.0.30 introduces a relevant feature by introducing dynamic redo log sizing, allowing hassle-free adjustments without system restarts. Alongside this, the update provides tools for accurate redo log capacity estimations, paving the way for optimized performance. It’s a step forward worth embracing for smoother MySQL operations. --- ### Rethinking the 80% InnoDB Buffer Pool Rule: Digging into Server RAM Allocation - URL: https://anotherboringtechblog.com/2023/09/server-optimization-80-percent-rule-analysis/ - Published: 2023-09-21 In server optimization, various rules of thumb have guided professionals in making informed decisions about resource allocation. One such directive is the 80% rule, which suggests allocating 80% of a server’s RAM to InnoDB’s buffer pool, leaving the remaining 20% for other operations. Initially conceived as a general guideline to get servers up and running quickly, the rule is often adopted in situations with limited knowledge about the system workload. Let’s scrutinize the nuances of this rule to understand its implications and efficacy in modern server environments. #### **Breaking Down the 80% Rule** To better understand, we may consider scenarios where the total server RAM varies significantly. Here’s how the rule plays out with different RAM sizes: ``` | Total Server RAM | Buffer pool with 80% rule | Remaining RAM | |------------------|---------------------------|---------------| | 1G | 800MB | 200MB | | 8G | 6G | 2G | | 16G | 13G | 3G | | 32G | 26G | 6G | | 64G | 51G | 13G | | 128G | 102G | 26G | | 256G | 205G | 51G | | 512G | 409G | 103G | | 1024G | 819G | 205G | ``` #### **The Disproportionate Growth Dilemma** For the 80% rule to stand its ground, it necessitates a proportionate increase in workload memory consumption relative to the buffer pool size. In reality, this is rarely the case. A server with 1TB of RAM might not require a gargantuan 205G to manage connections and queries — a threshold that MySQL might find overwhelming to handle regarding active connections and queries. #### **The 20% Tax on Resources** It brings us to an essential question: does it make sense to adhere to a rule that essentially levies a 20% “tax” on your server resources, particularly when you have invested a substantial amount of dollars in procuring a high-performance server? There’s no denying that setting aside a generous portion of RAM for unforeseen spikes in workload can be a prudent strategy. Yet, the arbitrary adherence to the 80% rule may result in underutilization of resources, leading to economic inefficiencies. #### **Towards a More Tailored Approach** A deeper understanding of how the buffer pool performs can be gleaned through constant monitoring of several parameters in MySQL’s InnoDB status reports. Let’s look at some key indicators that help in assessing the efficiency of the buffer pool: ``` ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 52747567104; in additional pool allocated 0 Total memory allocated by read views 6696 Internal hash tables (constant factor + variable factor) Adaptive hash index 1264159888 (815987752 + 448172136) Page hash 3188312 (buffer pool 0 only) Dictionary cache 218963057 (203998672 + 14964385) File system 1623040 (812272 + 810768) Lock system 127518328 (127499608 + 18720) Recovery system 0 (0 + 0) Dictionary memory allocated 14964385 Buffer pool size 3145712 Buffer pool size, bytes 51539345408 Free buffers 16385 — IF NEAR 0 - BUFFER POOL IS FULL Database pages 3101973 Old database pages 1144736 Modified db pages 0 Pending reads 0 — IF CONSISTENTLY GREATER THAN 0 - BUFFER MAY BE TOO SMALL Pending writes: LRU 0, flush list 0, single page 0 — IF CONSISTENTLY GREATER THAN 0 - BUFFER MAY BE TOO SMALL OR DISK IS SLOW FOR AMOUNT OF WRITES YOU HAVE Pages made young 6420249, not young 2000664705 0.13 youngs/s, 0.00 non-youngs/s Pages read 21996954, created 7754528, written 10606607 — IF written CONSISTENTLY GREATER THAN read - BUFFER IS TOO SMALL 0.00 reads/s, 0.00 creates/s, 0.33 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 25 / 1000 not 0 / 1000 — IF hit rate CONSISTENTLY NOT 1000 / 1000 - BUFFER IS TOO SMALL, IF young-making rate CONSISTENTLY NOT 0 / 1000 - BUFFER IS TOO SMALL Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s — IF evicted without access CONSISTENTLY GREATER THAN 0 - BUFFER IS TOO SMALL LRU len: 3101973, unzip_LRU len: 0 I/O sum[720]:cur[0], unzip sum[0]:cur[0] ``` #### **Conclusion** While the 80% rule offers a simplified starting point for server RAM allocation, its practicality diminishes as we scale up the server resources. It calls for reconsidering this rule of thumb, steering towards strategies grounded in the demands and characteristics of your server workloads. --- ## About - Edited by Vinicius Grippa. - Voice: deadpan, technical, self-aware. Headlines declarative. Deks one sentence. No emoji, no superlatives ("unlock", "unprecedented", "revolutionary" are banned). - Published irregularly. - Typeset in Source Serif 4 (body) and JetBrains Mono (labels, code). - Site lives at https://anotherboringtechblog.com/