Another Boring Tech Blog -

Understanding MySQL DDL Space Requirements

Posted on November 20, 2024November 20, 2024 by Vinicius Grippa

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;

1

2

3

4

5

6

7

8

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…

Understanding Latency Through a 100m Race: From CPU to NVMe

Posted on November 14, 2024 by Vinicius Grippa

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…

Adding an Invisible Primary Key to an Existing Table in MySQL

Posted on November 4, 2024 by Vinicius Grippa

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…

Monitoring and Optimizing Fragmented Tables in MySQL

Posted on September 10, 2024September 16, 2024 by Vinicius Grippa

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…

MySQL Brazil Meetup 2024 – Informações do Evento e Agenda

Posted on July 14, 2024October 8, 2024 by Vinicius Grippa

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 Teremos um dia inteiro com palestras…

Sorting MySQL Tables by Size: Importance and How-To

Posted on June 6, 2024 by Vinicius Grippa

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…

Running ANALYZE in MySQL

Posted on May 14, 2024 by Vinicius Grippa

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 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…

[PT-BR] Desativar o Transparent Huge Pages(THP) no Ubuntu 22.04

Posted on May 9, 2024May 9, 2024 by Vinicius Grippa

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…

Releem: MySQL Performance Tool

Posted on April 28, 2024April 29, 2024 by Vinicius Grippa

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 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,…

Improving Database Performance with ReadySet: A MySQL Caching Solution

Posted on April 10, 2024April 11, 2024 by Vinicius Grippa

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? According to its GitHub repository, ReadySet is designed to enhance the performance of real-time applications by providing a powerful SQL caching engine that requires no changes to…