This article walks through diagnosing slow‑query problems on a massive MySQL table, reveals index misuse, proposes dropping the old composite index, adding targeted indexes, using online DDL or pt‑osc for schema changes, and applying batch‑delete techniques to reduce latency and storage fragmentation.
This article details a comprehensive performance investigation of a massive MySQL table, using pt‑query‑digest to analyze slow queries, exposing index inefficiencies, proposing index redesign, demonstrating backup/restore with mydumper, comparing online DDL and pt‑osc for index rebuilding, and recommending small‑batch deletions to reduce latency and maintenance overhead.
This article explains the common reasons MySQL queries become sluggish—beyond missing indexes—including connection limits, buffer pool size, and query cache, and provides practical solutions such as profiling, adjusting connection pools, tuning InnoDB settings, and using force index.
This article analyzes a MySQL large‑table performance issue caused by slow queries and full‑table scans, demonstrates using pt‑query‑digest, explains execution plans, proposes index redesign, shows backup and restore with mydumper, compares online DDL and pt‑osc, and presents batch delete strategies to reduce latency.
This article demystifies MySQL’s processing pipeline by tracing a simple UPDATE statement through the application, service, and storage engine layers, detailing connection handling, SQL parsing, optimization, execution, buffer pool management, and the roles of undo and redo logs in ensuring data integrity.
Facing SLA alerts due to slow queries on a billion‑row MySQL table, the article analyzes slow‑query logs, reveals index misuse, and demonstrates how removing a poor composite index, adding targeted indexes, employing online DDL or pt‑osc, and using batch deletions dramatically reduce query times and maintenance overhead.
This guide explains how to enable MySQL slow query logging, set appropriate thresholds, locate inefficient SQL statements via logs and EXPLAIN, and optimize queries by using indexes and analyzing execution plans, with practical examples and code snippets.
This guide walks through deploying a MySQL primary‑replica cluster on Kubernetes using Helm charts, configuring persistent volumes, exposing services, and adding Prometheus and Grafana monitoring, while also covering installation, testing, and clean‑up steps.
This article explains why using LIMIT with large offsets slows MySQL queries, analyzes the execution flow, and presents four practical optimization techniques—including subqueries, INNER JOIN, bookmark (tag‑record) method, and BETWEEN range scans—backed by real‑world performance data and code examples.
This tutorial explains how to deploy a primary‑replica MySQL cluster on Kubernetes using Helm, configuring persistent volumes, setting up Prometheus monitoring, and provides commands for installation, verification, and clean removal, all with detailed code snippets.
This weekly roundup presents a curated selection of high‑quality technical articles and resources on MySQL, database error‑log analysis, big‑data task optimization, SQL injection case studies, and upcoming SQLE development plans, offering readers up‑to‑date insights into database engineering and performance best practices.
The SQLE 1.2209.0-pre3 preview release introduces CloudBeaver integration, unified login, automated SQL compliance checks, and several bug fixes, while providing project background, detailed feature demonstrations, full release notes, and links to documentation, demos, and community resources.
This article explains how to set up MySQL master‑slave replication for read‑write splitting, covering configuration of master and slave servers, binary log usage, troubleshooting common issues, and demonstrates implementing the setup in a Java project using Sharding‑JDBC.
A large‑scale user‑push system suffers from minutes‑long MySQL queries because the IN subquery materializes a temporary table and triggers a semi‑join that forces full table scans, but disabling the semi‑join optimizer or rewriting the query restores sub‑second performance.
This article demonstrates how to configure the open-source Orchestrator tool for automatic MySQL cluster failover, explains key parameters, and presents three test cases covering normal failover, lag‑induced prevention, and the effect of disabling global recoveries.
This guide explains why regular MySQL health checks are crucial and introduces four open‑source performance tools—mysqltuner.pl, tuning-primer.sh, pt‑variable‑advisor, and pt‑query‑digest—detailing how to download, run, and interpret their reports to optimize configuration, detect bottlenecks, and improve security.
This article explains the background, concept, advantages, applicable scenarios, technical implementation, and performance evaluation of Elastic Parallel Query (ePQ) in PolarDB MySQL, showing how multi‑node parallelism leverages idle CPU resources, adaptive scheduling, and cloud‑native architecture to accelerate large‑scale analytical workloads while maintaining HTAP capabilities.
This article explains MySQL's four transaction isolation levels, illustrating how concurrent transactions can cause dirty reads, non‑repeatable reads, and phantom reads through detailed examples of two transactions interacting via SELECT, UPDATE, and INSERT operations, and summarizes the consistency issues each scenario creates.
This article walks through a systematic analysis of MySQL slow‑log entries, correlating them with error‑log flush events, disk‑I/O monitoring, and semi‑synchronous replication traces to pinpoint performance bottlenecks and provide practical troubleshooting steps.
This article explains how MySQL's SELECT … FOR UPDATE adds a pessimistic lock, and demonstrates that using an indexed column or primary key results in a row lock, while lacking an index causes a table lock, with step‑by‑step verification using multiple transaction examples.
This article presents a scalable design for selecting N winners from over ten million users during a flash‑sale event, comparing naive SQL approaches with Redis‑based solutions that guarantee uniqueness, atomicity, and sub‑millisecond latency.
This article explains why locating a record in a B‑Tree index is crucial for MySQL InnoDB operations, describes scan intervals, index page structures, and details the step‑by‑step binary and sequential search process used to find the first matching record, including performance optimizations.
This article details a comprehensive analysis of a large MySQL table's performance issues, including slow query diagnostics with pt‑query‑digest, index evaluation, backup and restore procedures, online DDL versus pt‑osc, and practical strategies for index redesign and batch deletions to reduce latency and maintenance overhead.
This weekly digest curates top community technical articles and release notes, highlighting PostgreSQL vs MySQL comparisons, architecture diagram tips, a six‑hour pagination incident, MySQL core‑dump handling, new DBLE features, DTLE performance boosts, and SQLE multi‑source audit enhancements.
The article investigates MySQL InnoDB lock‑wait timeouts caused by hanging transactions that lack proper commit or rollback, explains ACID and lock mechanisms, shows how stale ThreadLocal bindings let pooled threads reuse unfinished transactions, and recommends using @Transactional with robust try‑catch handling to prevent such deadlocks.
Amid the current tech hiring slowdown, this guide outlines a comprehensive learning roadmap for software testers, covering fundamentals, Linux, shell scripting, web fundamentals, databases, packet‑capture tools, API testing, automation frameworks, mobile testing, TestOps, and performance and security testing to help engineers stay competitive and advance their careers.
This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.
This comprehensive guide covers MySQL fundamentals such as the three normal forms, differences between MyISAM and InnoDB, redo log versus binlog, when queries bypass indexes, join types, various index structures, covering indexes, back‑row lookups, transaction properties and isolation levels, common performance bottlenecks, replication mechanics, lag mitigation, storage behavior after deletes, VARCHAR limits, lock types, systematic SQL tuning steps, the purpose and trade‑offs of indexes, B+‑tree advantages, MVCC internals, and the Snowflake distributed ID algorithm.
The DTLE 4.22.07.2 release introduces performance‑boosting pipeline enhancements for large transactions, continuous insert optimizations, data chunking for massive loads, a new DumpEntryLimit setting, and bug fixes, while providing repository links, documentation, and recommended articles for further exploration.
This guide explains how to identify duplicate rows in a MySQL table using GROUP BY and HAVING, how to delete the extra rows while keeping the earliest entry, and how to handle duplicate detection across multiple columns with correct query patterns and common pitfalls.
This article explains why a single MySQL instance can become a bottleneck, introduces asynchronous master‑slave replication using binary logs, provides detailed configuration commands for both master and slave servers, and shows how to achieve read‑write splitting in Java projects with ShardingJDBC, including dependency setup, YAML configuration, and testing procedures.
This article presents a comprehensive list of MySQL optimization best practices, covering SELECT field selection, WHERE clause design, data type choices, index usage, join strategies, batch operations, UNION handling, and many other techniques to reduce resource consumption, improve query speed, and ensure efficient database management.
This article walks through a real‑world incident caused by MySQL deep pagination, explains how the slow queries and CPU spikes were diagnosed, and presents several concrete optimization techniques—including ID‑based queries, range scans, sub‑queries and cursor‑based pagination—culminating in a stable production fix.
This article explains how to collect comprehensive MySQL performance metrics using only native SHOW commands, covering connections, buffer pool, locks, SQL statements, throughput, server variables, and slow‑query analysis, while also offering practical tips for interpreting and optimizing the results.
This article investigates MySQL's recommendation against using UUIDs as primary keys by creating three tables (auto‑increment, UUID, and random snowflake IDs), benchmarking insert speeds with Spring Boot/JdbcTemplate, analyzing index structures, and discussing the advantages and drawbacks of each key strategy.
The preview release SQLE 1.2209.0-pre1 introduces OceanBase TopSQL auditing, Alibaba Cloud RDS MySQL audit‑log support, and a sub‑query nesting rule, while providing project background, download links, demo environments, and a detailed list of new features and release notes for database professionals.
This article explains the evolution from single‑node MySQL to master‑slave replication, various replication modes, high‑availability solutions such as MHA and MGR, and the challenges and techniques of vertical and horizontal sharding for scaling large‑scale internet applications.
The article explains the three primary MySQL data removal commands—DELETE, TRUNCATE, and DROP—detailing their execution speed, underlying mechanisms, impact on disk space for InnoDB and MyISAM engines, and best‑practice usage such as running OPTIMIZE TABLE after DELETE.
This guide explores five essential dimensions of MySQL performance tuning—including connection configuration, architectural choices such as caching and read‑write splitting, optimizer settings, storage engine selection, and schema design—providing concrete commands, formulas, and best‑practice recommendations to accelerate query execution and reduce load.
The article compares MySQL's limited join capabilities with PostgreSQL's richer options, explains why multi‑table joins can be slower than separate single‑table queries performed in the service layer, and outlines how decomposing joins improves caching, scalability, and overall system performance.
This article describes a step‑by‑step investigation of a MySQL remote‑connection failure where the server reports port 0, explains how the skip‑grants‑tables option implicitly enables skip‑networking, and provides the simple fix of commenting out the option and restarting the service.
This article provides a comprehensive tutorial on installing and configuring the MySQL Orchestrator high‑availability tool, covering required host environments, software download, extraction, backend MySQL user setup, configuration file editing, service startup, and web UI access.
This article walks through a real‑world large‑scale MySQL sharding project, covering business refactoring, storage architecture design, data migration, incremental upgrades, best‑practice tips, and stability safeguards, while sharing concrete steps, pitfalls, and lessons learned from start to production rollout.
This article explains how MySQL performs crash recovery by using Redo logs, doublewrite buffers, undo tablespace handling, transaction subsystem initialization, and the processing of active, prepared, and committed transactions to restore data pages and ensure consistency after an unexpected shutdown.
This article walks through installing and using the golang-migrate CLI to manage database schema changes, covering supported drivers, creating migration files, applying and rolling back migrations in PostgreSQL and MySQL, handling errors, and advanced scenarios such as multiple sequential migrations.
The article examines the Chengdu COVID‑19 testing system failure, analyzes its root causes such as oversized MySQL tables and insufficient load handling, and then presents a step‑by‑step high‑concurrency roadmap—including single‑machine, service‑database separation, caching, load balancing, read/write splitting, sharding, hardware and DNS load balancing—to help backend developers design scalable systems.
The September 2023 DB-Engines ranking update shows Oracle’s steep decline, while MySQL and MongoDB gain points, and presents the top ten databases across relational, key‑value, document, time‑series, and graph categories along with the five metrics used to calculate popularity.
This article investigates why DBLE repeatedly logs "no handler" messages by analyzing DBLE and MySQL logs, using Arthas to trace the call chain, reproducing the scenario with timeout settings, capturing network traffic, and explaining the MySQL 8.0.24 error‑packet behavior that leads to the observed logs.
The September DB‑Engines ranking shows Oracle’s steep decline while MySQL and MongoDB climb, presents top‑10 lists for relational, key‑value, document, time‑series and graph databases, explains the five metrics behind the scores, and advises professionals to choose databases based on business needs rather than pure popularity.
This article examines why single-node databases no longer meet modern internet workloads, explores MySQL replication models (master‑slave, asynchronous, semi‑synchronous, group replication), discusses high‑availability solutions such as MHA, MGR and Orchestrator, and outlines vertical and horizontal sharding techniques along with their trade‑offs.
This weekly digest curates top MySQL and database articles, highlights the new SQLE 1.2208.0 release with its features and upcoming plans, and invites community feedback on DBLE, DTLE, SQLE, and TXLE middleware usage.
The SQLE 1.2208.0 release introduces intelligent scanning for Alibaba Cloud RDS MySQL slow logs, adds numerous MySQL audit rules, opens enterprise‑level rules to the community, provides new OceanBase for MySQL audit rules, a reporting statistics page, PostgreSQL analysis support, and several scenario‑based audit enhancements along with many optimizations and bug fixes.
An in‑depth guide explains why using MyBatis’s foreach for bulk inserts can cause severe performance degradation, especially with large tables, and demonstrates how switching to ExecutorType.BATCH or limiting batch sizes to 20‑50 rows dramatically reduces insertion time from minutes to seconds.
This article explains MySQL’s binlog, how it enables master‑slave replication, and explores three practical business uses—data heterogeneity, cache synchronization, and task dispatch—showing how a middleware that pretends to be a slave can leverage binlog events to improve system architecture and performance.
This article explains the features, advantages, drawbacks, and step‑by‑step installation process of a MySQL high‑availability Percona XtraDB Cluster (PXC), covering configuration, firewall settings, SELinux disabling, node initialization, clustering parameters, and verification commands.
This article details a step‑by‑step optimization of a Java backend that imports massive Excel files into MySQL, covering library switches, data caching, batch INSERT statements, and parallel stream insertion to cut processing time from minutes to under two minutes.
This article investigates why MySQL treats strings with trailing spaces as equal during "=" comparisons, explains the underlying PAD SPACE collation rule, and demonstrates how to perform exact matches using LIKE and BINARY operators with practical examples and code snippets.
This article compares the MySQL binlog parsing tools my2sql and binlog2sql, detailing their parameters, performance benchmarks on 2 GB binlog files with varying thread counts, feature differences, usage constraints, and provides practical guidance for selecting the appropriate tool.
When trying to change a table’s primary key from int to bigint, a 500‑million‑row MySQL table required data archiving and a bulk MODIFY, but parallel DELETE statements caused lock‑wait timeouts; experiments reveal that under REPEATABLE READ the range scan locks the boundary row, leading to contention.
This guide demonstrates how to perform asynchronous CRUD operations on MySQL, PostgreSQL, and Redis using Python libraries such as aiomysql, asyncpg, and SQLAlchemy, covering connection setup, querying, inserting, updating, deleting, and handling result objects with practical code examples.
The weekly SQLE newsletter curated by community reporter Yongzheng highlights recent MySQL audit rule additions, Alibaba Cloud RDS slow‑log support, links to several in‑depth technical articles, open‑source progress, and outlines development plans for the coming week.
The SQLE 1.2208.0-pre4 preview release introduces smart scanning for Alibaba Cloud RDS MySQL slow logs, opens a comprehensive set of enterprise MySQL audit rules to the community version, and provides full release information including download links, documentation, and detailed feature lists.
This comprehensive guide explains why distributed locks are needed, outlines their three essential properties, compares common implementations such as Redis, MySQL, ZooKeeper, and Redlock, discusses pitfalls like non‑atomic operations and lock expiration, and presents correct patterns using atomic commands, Lua scripts, watchdogs, and fencing tokens.
The article provides a comprehensive guide to relational database design using MySQL, covering ER modeling, normalization (BCNF and 3NF), storage engine selection, character set and data type choices, as well as index design principles illustrated with a student course selection example.
The article explains why MyBatis foreach‑based batch inserts can cause extreme CPU usage and long execution times, demonstrates how combining multiple VALUES into a single INSERT or using ExecutorType.BATCH dramatically improves performance, and provides practical code examples and sizing recommendations.
This article explains the principles of MySQL semi‑synchronous replication, analyzes how data consistency is maintained during high‑availability failover, presents detailed step‑by‑step transaction flow, discusses scenarios causing GTID divergence, and offers testing methods and remediation techniques for DBA practitioners.
This article investigates a puzzling 13‑hour time discrepancy caused by the MySQL 8.x JDBC driver interpreting the ambiguous CST timezone as America/Chicago, explains the underlying driver code and JDK timezone mappings, and provides multiple practical solutions including server settings, driver parameters, and official fixes.
This article details Huolala's challenges and solutions in governing hybrid‑cloud databases, covering background, pain points, platform architecture, MySQL/Redis/Kafka implementations, automation, cost optimization, and the evolving role of DBAs in the cloud era.
This article explains the fundamentals of relational database design, covering E‑R modeling with a student‑course example, normalization through BCNF and 3NF, selection of MySQL storage engines, character set and data type choices, and best practices for index design.
This article examines common pitfalls and six practical strategies for maintaining data consistency between MySQL and Redis caches, comparing naive approaches with optimal solutions such as cache double‑delete, asynchronous serialization via message queues, and binlog‑driven eventual consistency, and offers recommendations for real‑time and eventual consistency scenarios.
This article explains the limitations of MySQL's max_execution_time for SELECT statements inside stored procedures and provides practical solutions—including external kill scripts and the Percona Toolkit's pt‑kill—to enforce query timeouts while warning against use in production environments.
This article explains why timestamps in MySQL binary logs can appear out of order, detailing how event times are derived from the thread start time, how long‑running transactions and binlog rotation affect ordering, and provides code examples and an experiment to illustrate the behavior.
This article examines the DBLE distributed database middleware cache, reproduces a primary‑key update query that returns incomplete results due to stale routing cache, analyzes cache contents via the management console, explains the cache’s behavior and limitations, and shows how disabling the primary‑key cache restores correct query routing.
This article explains how Flink's Broadcast State feature can be used to dynamically update processing rules and enrich streaming events with user information from MySQL, showing configuration, code examples, key considerations, and runtime results that demonstrate real‑time adaptability without restarting the job.
This article explains why a MySQL UNIQUE index does not prevent duplicate rows when indexed columns contain NULL, explores the complications of logical‑delete tables, and presents practical solutions such as incrementing delete status, adding timestamps, delete‑id keys, hash fields, and proper batch inserts.
This comprehensive guide walks you through MySQL's core architecture, explains why SQL optimization matters, details index types and creation methods, demystifies EXPLAIN output, and provides practical examples and best‑practice tips to dramatically improve query performance.
This article reviews popular free MySQL client tools—including MySQL Workbench, phpMyAdmin, HeidiSQL, Sequel Pro, DBeaver, and command‑line utilities—highlighting their key features, platform support, and download links, helping users choose a cost‑effective solution for database management.
This article explains relational database design using MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine and charset choices, data type selection, and index design, illustrated with examples and practical guidelines for building a student course selection system.
The SQLE 1.2208.0-pre3 preview release introduces several new MySQL audit rules, scenario‑based audit mode enhancements, a detailed release notes list, bug fixes, and links to documentation and previous versions, providing a comprehensive overview for database users and administrators.
This article explains how MySQL's auto‑increment primary keys behave when they reach their maximum value, the internal InnoDB row_id mechanism, the generation of Xid and trx_id for transactions, and the handling of thread identifiers, including practical examples and code snippets.
This guide presents comprehensive MySQL naming standards covering database objects, schemas, tables, columns, indexes, views, stored procedures, functions, triggers, constraints, and users, along with design principles for storage engines, character sets, table structures, and SQL query best practices.
This article compares two approaches for keeping MySQL and Redis in sync—using MySQL triggers combined with a UDF to write directly to Redis, and parsing MySQL binlogs (e.g., with Alibaba's Canal) to propagate changes—detailing their workflows, advantages, limitations, and implementation details.
The article investigates why the MySQL client defaults to a non‑root user (zhenxing) despite configuration files showing root, detailing the discovery of the hidden .mylogin.cnf file, the effect of --no-defaults, and how MySQL reads option files in a specific order.
This article demonstrates how Spring's @Transactional rolls back only RuntimeException subclasses, shows the failure to roll back a checked Exception, and explains how to use rollbackFor to ensure proper transaction rollback for all exception types.
This article explains why Percona Xtrabackup backups of MySQL 5.7 can fail with "Too many open files" errors, demonstrates how to reproduce the issue under both non‑root and root users, analyzes the open‑files‑limit settings, and provides practical solutions to adjust system and user limits for successful backups.
This article explains how to collect comprehensive MySQL performance metrics—including connections, buffer cache, locks, statement counts, throughput, server variables, and slow‑query analysis—using only MySQL's native SHOW commands, providing a fast, low‑overhead monitoring solution.
This article explains how the height of MySQL InnoDB B+‑tree indexes determines the number of page I/O operations for different query types, provides theoretical calculations of index height, and shows practical methods using information_schema and hexdump to inspect the actual index height of tables.
This article explores a common MySQL InnoDB pitfall where unique indexes fail to prevent duplicate rows, explains how NULL values and logical deletion affect uniqueness, and presents practical strategies—including composite keys, delete status counters, timestamps, hash fields, and Redis locks—to enforce true uniqueness.
A MySQL 5.6+ optimizer bug causes queries that filter by uid and order by id with a small LIMIT to use a costly full table scan instead of the appropriate idx_uid_stat index, and the article explains the root cause, shows optimizer_trace output, and offers two practical work‑arounds.
This article introduces Python's DB‑API, lists supported databases, demonstrates how to install and use PyMySQL for basic CRUD operations, shows techniques for preventing SQL injection, and explains two connection‑pooling models with DBUtils, including code examples and a brief promotional note at the end.
This article explains MySQL's internal architecture and provides twelve practical SQL optimization strategies—including avoiding subqueries, using IN instead of OR, efficient pagination, minimizing ORDER BY, batch inserts, selective column retrieval, proper JOIN handling, and index best practices—to dramatically improve database query performance.
This article explains how MySQL 8.0's InnoDB generates Redo logs, groups them into mini‑transactions, writes them concurrently to the log buffer, and uses log_writer and log_flusher threads together with LSN, slots, and recent_written structures to ensure durable, ordered persistence.
This article explains how MySQL processes LIMIT with an offset, why large offsets cause slower queries and deep‑pagination problems, and presents practical optimization techniques such as id‑based pagination, sub‑queries, and index‑aware strategies to improve performance.
The preview release SQLE 1.2208.0-pre2 introduces a comprehensive report statistics page for the enterprise edition, details the project's purpose, provides resource links, enumerates new feature items such as work‑order metrics and license usage, and lists bug fixes and links to prior versions.
This article demonstrates how MySQL 8.0.29’s ALGORITHM=INSTANT enables instant column addition, deletion, and row‑size checks at any position in large tables, compares performance with previous versions, and discusses related XtraBackup backup considerations.
This article demonstrates how Spring's @Transactional annotation rolls back transactions only for RuntimeException subclasses, shows practical MySQL update tests that trigger ArithmeticException, and explains how to configure rollback for checked exceptions using rollbackFor, with complete code examples.
This article examines various multi‑table join scenarios in MySQL, demonstrating when partition tables can accelerate queries and when they degrade performance, supported by concrete examples, execution times, and explain plans to guide proper partition key usage.
This article explains the risks of misusing the sql_slave_skip_counter parameter in MySQL replication, demonstrates a step‑by‑step test environment with both transactional and non‑transactional tables, analyzes the resulting binlog events, and provides practical methods to reset the counter safely.
This article demystifies MySQL's Multi‑Version Concurrency Control (MVCC) by explaining its Undo Log and Read View mechanisms, showing how InnoDB implements read‑committed and repeatable‑read isolation without locking, and illustrating the process with clear examples and diagrams.
This article examines the challenges of running MySQL in Docker containers, highlighting data safety concerns, I/O performance bottlenecks, statefulness issues, and resource isolation limits, while also outlining scenarios where containerization can work and offering practical mitigation strategies.
