This comprehensive guide outlines five common MySQL performance problems, a step‑by‑step investigation methodology, detailed Java middleware and database analyses, practical tuning tactics such as index optimization and sharding, and a governance framework for sustainable performance management.
This article walks through a simulated MySQL 3307 cluster failure, examines Orchestrator's source code to explain the ContinuousDiscovery loop, discovery queues, health ticks, caretaking tasks, raft coordination, topology snapshots, and the logic distinguishing UnreachableMaster from DeadMaster states.
This guide explains how to enable and read MySQL's slow query log, use EXPLAIN, profiling, and optimizer trace to pinpoint inefficient SQL, and presents ten classic problem patterns—such as implicit conversion, left‑most prefix violations, deep pagination, large IN lists, and file‑sort order‑by—with concrete code examples and practical optimization steps.
The article explains why a recent write may be invisible when reading from a MySQL replica, analyzes the replication threads and latency causes, and presents practical solutions such as forcing master reads, lag detection, semi‑sync mode, and GTID‑based waiting, with examples from Sharding‑JDBC, MyCat and MaxScale.
This article explains the fundamentals of MySQL indexing, detailing how InnoDB pages, B+‑tree structures, clustered and non‑clustered indexes, and disk prefetching work together to reduce I/O operations and dramatically improve query performance.
This article provides a comprehensive technical overview of MySQL's underlying index structures, SQL execution plan analysis, transaction isolation levels, MVCC concurrency control, and the InnoDB buffer pool mechanism, illustrating each concept with diagrams and practical SQL examples.
This article explains how MySQL performs implicit type conversion during queries, demonstrates the unexpected results caused by joining mismatched column types, outlines the conversion rules, and provides practical tips to avoid performance degradation and incorrect data retrieval.
This guide explains how to detect MySQL deadlock exceptions, retrieve and interpret InnoDB deadlock logs and binlog files, and use the information to pinpoint conflicting SQL statements and fix the underlying lock issues in live systems.
This article explains MySQL's native data encryption capabilities, detailing the AES_ENCRYPT/AES_DECRYPT functions, their parameters, storage considerations, example usage, and best‑practice recommendations for secure and efficient encryption of sensitive fields.
This article describes the layered architecture of Qunar's next‑generation database automation platform, outlines the design goals for secure Agent/Plugin‑Server communication, compares communication and encryption methods, and details the authentication and request flow using symmetric encryption and token‑based security.
This article examines why a MySQL InnoDB table with a unique index can still store duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies such as adjusting delete status, adding timestamp or auxiliary ID fields, using hash columns, and leveraging proper bulk‑insert techniques to enforce uniqueness effectively.
The article walks through a MySQL MGR failover incident, analyzes the InnoDB assertion failure shown in the error log, pinpoints the problematic code at row0pread.cc line 727, compares the fix in MySQL 8.0.28, and references the related bug report for future troubleshooting.
This article explains how MySQL uses indexes during JOIN operations, compares indexed and non‑indexed join execution, introduces Index Nested‑Loop Join and Block Nested‑Loop Join algorithms, discusses the role of the join buffer, and provides practical guidance on choosing the optimal driving table for efficient query performance.
This article investigates a recurring MySQL CPU‑100% alarm caused by a slow SELECT with ORDER BY id ASC LIMIT, explains why the optimizer mistakenly chooses the primary‑key index over a suitable composite index, and presents six practical experiments—including force‑index, ORDER BY tweaks, and LIMIT adjustments—that reliably restore optimal index usage.
The e‑commerce platform’s order‑query alerts were caused by MySQL master‑slave replication lag—specifically, delayed redo‑log flushing let the slave receive binlog events before the master committed—so queries returned empty, a condition mitigated by retry logic, delayed MQ delivery, or emitting post‑commit business events.
This article details the investigation and resolution of a MySQL 8.0.21 client segmentation fault on CentOS 8.4, covering missing ncurses libraries, implicit function declaration warnings, pointer truncation on 64‑bit systems, core‑file generation, gdb analysis, and the steps required to rebuild the client without crashes.
This article explains when read/write splitting is needed, details MySQL master‑slave replication mechanics, outlines middleware design choices, and provides practical monitoring and mitigation strategies for replication lag, helping developers decide and implement effective database scaling.
This article explains MySQL’s three‑tier logical architecture—from client services to core query processing and storage engines—and details the four ANSI SQL transaction isolation levels, highlighting their behavior, advantages, and trade‑offs in MySQL.
The SQLE 1.2210.0 release introduces integrated CloudBeaver support for multiple database types, new MySQL audit rules, enhanced smart‑scan filtering, independent menus for scan tasks, along with numerous optimizations and bug fixes, and provides full release information and acknowledgments.
The article explains why a seemingly non‑conflicting UPDATE on a secondary index can cause lock‑wait alerts under RC isolation, detailing the missing end‑range condition push‑down, the InnoDB row‑search flow, semi‑consistent read behavior, and differences between primary‑key and secondary‑index locking.
Shopee’s scalable fund‑routing decision engine combines a configurable Go‑based rule engine with Redis‑backed, Lua‑driven quota control to dynamically match loan orders to external capital providers, enforce multi‑dimensional volume limits, achieve tens of thousands TPS, and provide extensible risk‑management capabilities across markets.
MVCC (Multiversion Concurrency Control) in MySQL InnoDB uses undo logs and read views to provide non‑locking snapshot reads, managing read‑write, write‑write, and read‑read concurrency, with implicit row fields and transaction IDs determining visibility across isolation levels.
The article explains how a MySQL 5.7 service fails to start on a CentOS 7.9 server because SELinux blocks write access to err.log, walks through log inspection, strace tracing, SELinux context analysis, and provides a simple fix by disabling SELinux.
This article documents practical experiments comparing four MySQL bulk‑insert techniques—single‑row loop, concatenated SQL, MyBatis‑Plus saveBatch, and loop with batch mode—in a SpringBoot environment, revealing the most efficient approach and key configuration tips.
This article explains how to achieve database read‑write separation in high‑concurrency Java applications by configuring master and slave data sources, implementing a routing DataSource, managing context with ThreadLocal, defining a custom @DataSourceSwitcher annotation, and applying AOP to switch between read and write operations.
This article explains the functional differences between the run_sql and sql commands in MySQL Shell, demonstrates how to execute DDL/DML statements, bind variables, and retrieve results using both commands, and provides guidance on selecting the appropriate function for various use cases.
This article introduces Debezium, an open‑source low‑latency change data capture platform that streams database row changes via Kafka, explains its architecture and common scenarios such as cache invalidation and CQRS, and provides step‑by‑step Docker commands to install ZooKeeper, Kafka, MySQL and the Debezium connector.
This article presents a comprehensive set of MySQL optimization techniques, covering everything from avoiding SELECT * and OR conditions to proper index usage, join strategies, batch operations, and query planning, all aimed at reducing resource consumption, improving execution speed, and ensuring reliable database design.
The article examines three approaches to handling inventory deduction in e‑commerce—single‑field MySQL, sharded MySQL rows, and Redis incrby—analyzes their concurrency drawbacks, and presents a robust Redis‑Lua script solution with distributed locking and Java implementation details.
Facing a surge in MySQL slow queries, Ctrip’s senior database engineers detail a comprehensive approach—including refined SQL release processes, deep execution‑plan analysis, index tuning, query rewriting, and resource management—to systematically identify, diagnose, and eliminate performance bottlenecks in large‑scale hotel services.
This article explains MySQL's three concurrency types—read‑read, read‑write, and write‑write—describes how InnoDB uses shared/exclusive locks, MVCC snapshot reads, and ReadView metadata to ensure transaction isolation, and provides practical examples and lock‑based solutions for avoiding update loss.
After a write to the MySQL master, reads from a slave can return old or missing data due to replication lag, and this article explains the underlying asynchronous replication mechanisms, the involved threads, and presents practical solutions such as forcing master reads, delay detection, semi‑sync mode, GTID waiting, and middleware implementations like Sharding‑JDBC, MyCat, and MaxScale.
This article explains why a unique index in MySQL can still allow duplicate rows when indexed columns contain NULL, explores the challenges of adding unique indexes to logically deleted tables, and presents practical solutions such as incremental delete status, timestamps, extra IDs, hash fields, and proper batch insertion techniques.
This article explains how to implement fast nearby POI queries by calculating distances with the Haversine formula, designing MySQL tables and indexes, applying GeoHash for dimensionality reduction, and leveraging Redis Geo for high‑concurrency scenarios, while also providing Go code examples for each step.
The SQLE 1.2210.0-pre2 release introduces CloudBeaver integration for multiple database types, adds new MySQL audit rules, provides several UI optimizations, and resolves critical bugs, offering a more comprehensive and secure SQL auditing experience for both community and enterprise users.
This article presents a detailed performance test of inserting massive data into MySQL with Java, comparing three strategies—MyBatis lightweight insertion without transactions, direct JDBC handling with and without transactions, and JDBC batch processing—showing timing results for millions of rows and offering practical recommendations for high‑throughput data loading.
This article describes Ctrip's practical approach to identifying and optimizing MySQL slow queries, covering background, improved SQL review workflow, execution plan analysis, common index problems, query rewriting, pagination issues, resource contention, and best‑practice recommendations for long‑term performance.
This article explains how to choose storage options, design sharding keys, apply partitioning strategies, modify business code, handle historical data migration, and plan future scaling for large‑scale e‑commerce systems using MySQL, NoSQL, and NewSQL solutions.
This article explains the evolution from single‑node MySQL databases to master‑slave replication, various replication modes, high‑availability strategies, and both vertical and horizontal sharding techniques, while discussing the associated challenges such as distributed transactions, routing, and operational complexity.
This article explains why single‑node databases cannot keep up with rapid business growth and describes MySQL replication methods, high‑availability solutions such as MHA and MGR, and the challenges and techniques of vertical and horizontal sharding for large‑scale systems.
The article explains MySQL's continued dominance in the database market, presents recent developer survey statistics, describes its three‑layer logical architecture, and introduces the new edition of the "High Performance MySQL" book as a guide for modern database professionals.
This article explains MySQL collations and character sets, shows how to view and set them, and demonstrates through test cases that while data insertion is unaffected by collation, query results are influenced by the collation defined on columns or the connection.
This article presents a comprehensive set of MySQL best‑practice guidelines—including avoiding SELECT *, steering clear of OR in WHERE clauses, preferring numeric over string types, using VARCHAR instead of CHAR, limiting DELETE/UPDATE, leveraging proper JOIN types, and many other indexing and query‑execution tricks—to dramatically improve query speed, reduce resource consumption, and maintain healthy database design.
This weekly newsletter curates top community tech shares, featuring MySQL 8.0 updates, database localization insights, concurrency consistency strategies, a new SQL auditing tool release, development progress, upcoming plans, and invites community feedback on DBLE, DTLE, SQLE, and TXLE middleware.
In an exclusive interview, MariaDB founder Michael "Monty" Widenius discusses the rapid growth of China’s database industry, the rise of open‑source adoption, competition with cloud providers, and strategic plans for expanding MariaDB’s presence in the Chinese market.
The SQLE 1.2210.0-pre1 preview release introduces new MySQL audit rules prohibiting rename/change operations and requiring LIMIT clauses, adds filter options to the intelligent scan task list, refines work‑order progress styling, and provides a detailed list of features, optimizations, and bug fixes.
The article explains why transaction isolation is needed, describes common concurrency problems such as lost updates, dirty reads, non‑repeatable reads and phantom reads, outlines the ACID properties of a transaction, and details MySQL's four isolation levels with their effects and default settings.
This article explains how MySQL 8.0's prefer_ordering_index optimizer switch can be toggled to improve LIMIT queries that involve ORDER BY and GROUP BY, demonstrates the effect with test data, and shows why manual parameter control can yield better execution plans.
This article explains the different layers—encoding, framework, driver, proxy, and implementation—through which database sharding can be introduced, compares driver‑level and proxy‑level middleware, outlines their characteristics, common constraints, and provides a step‑by‑step process for planning, preparing, and executing a sharding project.
This article explains MySQL's execution order and presents eight practical optimizations—including smarter LIMIT usage, implicit conversion handling, join‑based updates, mixed sorting, EXISTS rewriting, condition push‑down, early range reduction, and intermediate result push‑down—each illustrated with SQL examples and performance measurements.
This article explains the logical execution order of SQL statements, highlights common performance pitfalls such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, and demonstrates how to rewrite queries with JOINs, early range reduction, CTEs, and other optimizations to achieve dramatically faster execution times.
This article explains how MySQL 8.0.31 introduces native INTERSECT and EXCEPT set operators, demonstrates how to achieve the same intersection and difference results in MySQL 5.7 using joins, and shows usage of the ALL modifier for preserving duplicate rows.
This guide walks through installing NFS on Kubernetes nodes, creating a shared directory, configuring exports, setting up PersistentVolume and PersistentVolumeClaim objects, and deploying a MySQL instance that uses the NFS-backed storage.
This guide explains how to deploy and configure MariaDB MaxScale as a database proxy to achieve MySQL read/write splitting, covering topology design, user creation, installation steps, configuration files, service setup, and verification of routing behavior.
The article analyzes why MySQL LIMIT pagination becomes slower as the offset grows, demonstrates the performance impact with sample queries on a 500,000‑row table, and presents three optimization strategies—using ordered primary keys, subqueries, and join‑based pagination—to reduce full‑table scans and improve query speed.
This guide explains how to gather extensive MySQL monitoring data—including connections, buffer cache, locks, SQL activity, statement counts, throughput, server variables, and slow‑query analysis—solely with MySQL's native SHOW statements, providing low‑overhead, real‑time insight for database administrators.
This article explains MySQL's two‑layer storage engine architecture, details how the server layer prepares record metadata, and walks through a concrete InnoDB table example to show the exact memory layout and data‑exchange process between the server and storage engine.
The article analyzes a MySQL MGR cluster failover incident caused by a VM pause and system time jump, explains the underlying detection mechanisms, presents detailed source code excerpts, and demonstrates through debugging that time changes alone do not trigger MGR failover.
This article explains how MySQL determines default values for TIMESTAMP columns, the effect of the explicit_defaults_for_timestamp parameter, when zero‑date values are permitted via sql_mode, and demonstrates the behavior through four test scenarios with corresponding SQL statements and results.
This article analyzes why MySQL LIMIT pagination becomes slower on large tables, demonstrates the performance impact with real query timings, explains the underlying full‑table scan behavior, and presents three practical optimization strategies—including ordered primary‑key scans, sub‑queries, and join‑based approaches—to improve query speed.
The article explains why MySQL users created with passwords containing special characters like "$" or "#" may fail to log in, analyzes how shell quoting affects the stored password, demonstrates the problem with scripts and login‑path, and provides practical work‑arounds and version notes.
This article provides a comprehensive guide on using Flink CDC with Debezium to capture MySQL changes, covering serialization, adding dependencies, configuring SQL client and Java/Scala APIs, creating source and sink tables, enabling checkpoints, and streaming data into a Hudi data lake.
This article explains how MySQL’s InnoDB storage engine, page size, B+‑tree index structure, and primary‑key limits combine to make roughly 20 million rows the practical ceiling for a single table, and shows how to generate massive test data and tune server settings.
This article provides a systematic deep‑dive into MySQL table‑lock mechanics, comparing storage engines, explaining the advantages of table locks, detailing lock acquisition and release rules, showing how to monitor lock statistics, and revealing why MyISAM can deliver superior performance for heavy concurrent insert and select workloads despite using only table‑level locking.
This article analyzes a large MySQL table with frequent SLA alerts, demonstrates how to identify slow queries using pt‑query‑digest, proposes index redesign and online DDL versus pt‑osc methods, and shows testing, implementation, and batch‑delete techniques to dramatically reduce query time and maintenance overhead.
This article explains how to perform full, incremental, and binlog‑based data migrations, compares their trade‑offs, and introduces distributed transaction models such as XA, BASE, TCC, and AT, helping developers choose the right strategy for consistency and performance.
This article explains why a MySQL InnoDB table with a unique index can still contain duplicate rows—especially when indexed columns contain NULL values or when logical deletion is used—and presents several practical strategies, including schema changes and alternative locking mechanisms, to enforce true uniqueness.
This article explains how to enable and configure MySQL's slow query log, set appropriate thresholds, use EXPLAIN to analyze execution plans, and apply index optimizations to dramatically reduce query execution time for large tables.
This guide presents 21 practical MySQL table design guidelines covering naming conventions, field types, primary key strategies, indexing, logical deletion, common columns, storage engines, character sets, time types, relationship modeling, sharding considerations, and SQL optimization tips for robust backend development.
The article outlines common database and cache failures—such as instance outages, replication lag, data corruption, and cache avalanches—while detailing Bilibili’s mitigation strategies including high‑availability architectures, scaling, multi‑active designs, proxy controls, slow‑query alerts, fault‑injection drills, and ongoing resilience improvements.
This article explains how MySQL's count() works across different storage engines, compares the performance of various count() forms, and offers practical strategies—such as using EXPLAIN rows, auxiliary count tables, and batch processing—to obtain accurate or approximate row counts efficiently even on massive tables.
The article details the refactoring of memory monitoring in MySQL 8.0.28, introducing a new PFS metadata structure for precise tracking of memory allocations and deallocations across various components like Buffer Pool and user variables.
This article explains the difference between ON and WHERE clauses in MySQL LEFT JOIN, illustrates why a proposed optimization for DBLE sharding does not work, and provides practical examples and pseudo‑code to clarify how MySQL processes left joins.
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.
