This article provides a step‑by‑step tutorial on setting up Prometheus and Grafana for 24/7 monitoring of Linux servers and MySQL databases, covering installation, configuration, data visualization, alerting with onealert, and common troubleshooting tips for reliable operations.
Facing a sudden MySQL table lock? This guide walks through real‑world troubleshooting steps—from checking table usage and process lists to inspecting InnoDB transaction and lock tables, identifying lock‑wait relationships, and safely killing offending sessions, while also explaining lock types and common metadata‑lock scenarios.
After deploying a project, a 500 Server Error appears the next day due to MySQL closing idle connections after the default 8‑hour wait_timeout, and the article explains how to verify the issue, adjust wait_timeout, use autoReconnect, and properly configure the HikariCP connection pool to prevent the failure.
This guide explains what MySQL binlog is, how to enable it in a Docker container, common binlog commands for inspection and maintenance, and demonstrates a step‑by‑step data recovery scenario using mysqlbinlog to restore a mistakenly dropped database.
A recent poll revealed that MySQL with the InnoDB engine can support up to four billion tables, a limit dictated by the underlying file system rather than MySQL itself, and the official documentation confirms this massive capacity.
This article explains the structures of B+ trees and skip lists, compares their insertion and search behaviors, and shows why MySQL prefers B+ trees for disk‑based indexing while Redis adopts skip lists for in‑memory operations.
This article recounts a production incident caused by a MySQL query with a huge OFFSET, explains why large offsets lead to full‑table scans and severe latency, and presents three practical solutions—including index‑covering subqueries, start‑position redefinition, and offset throttling—along with data‑generation scripts and performance test results.
This article introduces a full‑stack food‑delivery project built with Spring Boot, Vue.js, MySQL and MongoDB, detailing its architecture, module breakdown, technology choices, database setup, and step‑by‑step instructions for launching the backend API, admin portal, and mobile client, along with download links for data and images.
This article compiles eight classic MySQL error scenarios—including forgotten passwords, case‑sensitivity mismatches, Windows service startup failures, export/import restrictions, excessive connections, full binary logs, and primary‑key replication conflicts—along with detailed troubleshooting steps, configuration tweaks, and command‑line solutions to help database practitioners resolve them efficiently.
MySQL 8.0.29, released on April 26 2022, brings 160 bug fixes, several feature refinements—including string handling, stricter time formats, replication defaults, instant‑algorithm support for InnoDB, and new clone variables—while signaling the final phase of the 8.0 lifecycle and urging users of older versions to upgrade for security.
This article walks through generating a heavy‑load MySQL workload, using top, iostat, and iotop to pinpoint high disk I/O, mapping OS thread IDs back to MySQL sessions, and safely killing the offending query to relieve pressure.
The article asks how many tables can be created in a single MySQL database using the InnoDB storage engine, prompting readers to provide the correct limit without consulting external sources and discuss the theoretical maximum based on MySQL specifications.
The article describes how a large‑scale membership system achieves high performance and fault tolerance by deploying a dual‑center Elasticsearch cluster, isolating traffic with multiple ES clusters, adding a Redis cache with distributed locks, migrating the primary relational store from SQL Server to a partitioned MySQL cluster, and implementing fine‑grained flow‑control and degradation strategies.
The article explains MySQL's metadata lock (MDL) mechanism, shows how to enable its instrumentation, demonstrates various lock scenarios with SELECT, INSERT and ALTER statements, and provides guidance on diagnosing and resolving MDL‑related blocking issues.
This article explains MySQL's lack of a hard row limit, the industry rule of thumb around 5 million rows, how InnoDB buffer size and hardware affect performance, and demonstrates a simple insertion test that reveals slowdown when a table grows too large.
This article analyzes a MySQL InnoDB lock wait timeout error, explains how gap locks cause blocked inserts and phantom reads under repeatable‑read isolation, and offers optimization strategies such as primary‑key deletions, while concluding with a recruitment invitation for the Zero technology team.
Yearning is a lightweight, self‑contained MySQL SQL audit platform that offers query auditing, SQL review, permission management, and automated rollback, with simple installation steps and no reliance on third‑party tools, making it ideal for small and medium enterprises.
This article analyzes MySQL batch insertion performance, explains how row size, max_allowed_packet, buffer pool, transaction handling, and index design affect throughput, and presents empirical tests that suggest using roughly half of the max_allowed_packet size as the optimal batch size for large data loads.
This weekly roundup from the DBLE open‑source community shares the latest MySQL‑related news, feature releases, bug fixes, and upcoming plans for DBLE, DTLE, and SQLE, providing developers with concise technical updates and links to detailed articles.
The article explains how Meituan uses MySQL's cost‑based optimizer to analyze slow queries, generate virtual index candidates, evaluate their costs with detailed statistics, and deploy a recommendation system that validates, tracks, and governs index suggestions to reduce CPU/IO waste and prevent database failures.
This article outlines several frequent MySQL query pitfalls—including missing table aliases in multi‑table operations, unsupported FULL JOIN syntax, unexpected REPLACE INTO side effects, auto‑increment edge cases, data‑type mismatches, nondeterministic functions, and warning‑driven query behavior—providing examples and best‑practice recommendations to prevent data loss and performance issues.
The article analyzes why a MySQL 5.7.32 instance’s monitoring thread remains in the Opening table state when querying information_schema.tables, discovers the involvement of the Federated storage engine, reproduces the issue with a non‑existent remote server, and confirms the root cause through controlled experiments.
This article provides a comprehensive guide to relational database design with MySQL, covering ER modeling, BCNF and 3NF normalization, storage engine selection, character set and data type choices, and index design principles, illustrated through a student‑course selection example.
This article explains the components of MySQL InnoDB Cluster, introduces the InnoDB Cluster Set (MICS) for disaster‑recovery, outlines its limitations, and provides a step‑by‑step demonstration with code on how to create, monitor, and fail over a MICS deployment.
This article presents eight common MySQL error scenarios—including forgotten passwords, password policy violations, case‑sensitivity issues, service startup failures, export/import restrictions, connection limits, binary log overflow, and primary‑key replication errors—along with detailed troubleshooting steps and configuration commands to resolve each problem.
This article explains the structures of B+ trees and skip lists, compares their read and write performance, and details why MySQL chooses B+ trees for indexing while Redis and RocksDB prefer skip lists, highlighting trade‑offs in disk I/O, fan‑out, and balancing overhead.
This article explains how to retrieve sales statistics for multiple business units and their associated products in MySQL by comparing rows, discusses alternative approaches such as loop queries, OR concatenation, and mixed filtering, and demonstrates why row‑by‑row comparison is the most suitable solution.
The DTLE 4.22.04.0 release introduces UTF‑32 support, Chinese table name handling, enhanced ColumnMapFrom conversion, expanded SqlFilter capabilities, additional logging, and numerous bug fixes including procedure handling, DDL panic prevention, and task list display issues, with links to documentation and related articles.
This article examines frequent SQL anti‑patterns—such as inefficient LIMIT usage, implicit type conversion, suboptimal UPDATE/DELETE joins, mixed ordering, unnecessary EXISTS clauses, condition push‑down failures, early limiting, and intermediate result handling—and provides rewritten queries that dramatically improve performance.
This article evaluates the open‑source SQLE SQL auditing platform against MySQL 8.0.28, demonstrating how DDL, DML, DCL statements and their contextual relationships are checked, highlighting successful detections, mismatches caused by new MySQL 8.0 features, and concluding on overall compatibility.
The article examines how slow‑query‑induced avalanches arise in MySQL, explains why naïvely killing slow queries is fraught with accuracy, automation and responsibility issues, and proposes a signature‑based timeout mechanism that lets applications safely abort problematic queries.
The article presents a kindergarten management system featuring three user roles, outlines its Spring Boot and Thymeleaf technology stack, details required runtime and database environments, provides step‑by‑step deployment instructions, and includes complete backend controller source code for user, teacher, student, and school management functionalities.
This article explains how to replace inefficient recursive MySQL tree queries with the nested‑set (preorder traversal) technique, covering descendant retrieval, subtree counting, leaf detection, insertion, deletion, direct‑child lookup, ancestor path queries, and a JavaScript example for building a tree structure.
This article analyzes the full lifecycle of a MySQL connection from Java code to TCP handshake using Wireshark, measures each step’s latency, calculates the total cost per request, and demonstrates why connection pooling is essential for high‑traffic web applications.
This tutorial presents a complete Python + Django e‑commerce mini‑project, outlining its main features, required environment, step‑by‑step installation commands, configuration changes, database setup, and how to run the application locally for self‑learners.
After prolonged Zabbix monitoring generates massive history tables, this guide provides a Bash script that stops Zabbix services, disables foreign key checks, truncates and optimizes various history tables in MySQL, backs up the database, and restarts the services.
This article explains how MySQL stores indexes using B+ trees, how to evaluate index selectivity and prefix length, the importance of the leftmost principle for composite indexes, and practical techniques such as covering indexes, index condition pushdown, and avoiding common pitfalls that render indexes ineffective, all illustrated with real‑world query examples and performance measurements.
This article explains how MySQL can implement GROUP BY using either a compact index scan that reads rows sequentially or a loose index scan that jumps to the needed rows, compares their costs, and shows when each method is optimal, including a hybrid sequential‑loose scan.
This article investigates how much time a simple Java program spends establishing and closing a MySQL connection, measures the latency of each TCP and MySQL handshake step with Wireshark, and demonstrates why connection pooling is essential for high‑traffic web services.
This article demonstrates how a MySQL pagination query on a 9.5‑million‑row table that originally took over 16 seconds can be accelerated to under 0.4 seconds by moving the LIMIT condition into a subquery that selects only primary‑key IDs and then joining to retrieve the remaining columns, with detailed code and experimental verification.
This article explains typical MySQL index failure cases such as left‑most matching truncation, implicit conversion, IN + ORDER BY, range queries, leading wildcard, OR conditions, function usage, NOT IN/!=, low selectivity, and index fragmentation, and provides practical optimization strategies including index redesign, covering indexes, and pagination improvements.
This document describes the background, problems, requirements, analysis, and detailed design of a high‑performance, highly available, and scalable log‑analysis pipeline for MySQL that leverages Kafka, ClickHouse, MySQL, and custom services to aggregate, enrich, and visualize massive query logs.
This article explains how DTLE supports pt-osc table schema changes by describing pt-osc's underlying mechanism, DTLE's DDL capabilities, and step‑by‑step instructions for deploying DTLE, preparing MySQL data, configuring a DTLE job, running pt‑online‑schema‑change, and verifying successful synchronization.
This tutorial walks you through setting up a complete music‑streaming web application built with Vue on the front end and Spring Boot, MyBatis, and MySQL on the back end, covering features, installation, database configuration, and launch commands.
This article explains why MySQL 8.0’s JDBC driver processes executeBatch() calls as separate statements, demonstrates the root cause in the driver’s rewriteBatchedStatements logic, and shows how configuring rewriteBatchedStatements=true (and optionally allowMultiQueries=true) enables genuine batch execution.
This article explains how MySQL implements indexes using B+ trees in InnoDB and MyISAM, compares primary and secondary indexes, demonstrates performance gains from proper indexing on large tables, and provides practical commands for creating, viewing, and dropping indexes.
This article investigates MySQL's recommendation against UUID primary keys by creating three tables—auto‑increment, UUID, and random (snowflake) keys—running insert and query benchmarks with SpringBoot/JdbcTemplate, analyzing index structures, and discussing the performance trade‑offs and security considerations of each key strategy.
This article evaluates several PHP approaches—including plain inserts, unsigned mode, MySQL transactions, file locks, and Redis queues—to prevent product inventory overselling under high concurrency, presenting test setups, results, and a final recommendation favoring Redis for its superior performance.
This article walks through the complete process of creating a custom SQL audit rule in the open‑source SQLE platform, covering API authentication, request construction, response analysis, rule insertion into the database, and the necessary Go backend code implementation.
This article describes a seat‑temporary‑leave management system built with Java Spring Boot and Vue, detailing its front‑end and back‑end modules, technology stack, functional pages, and provides a representative controller code snippet illustrating the back‑end API design.
This article explains how to use the nested set (left‑right) model to efficiently query, count, and manipulate hierarchical department data in MySQL, providing SQL examples for retrieving descendants, ancestors, leaf detection, as well as insert, delete, and JavaScript tree‑building techniques.
This article explains why SQL performance degrades as data grows, outlines a step‑by‑step optimization workflow—including slow‑query detection, EXPLAIN analysis, profiling, optimizer tracing, and corrective actions—while detailing type and extra fields, index usage, and practical scenarios such as large pagination, IN‑order‑by, range queries, and complex joins.
This comprehensive guide covers MySQL fundamentals including the three normal forms, permission tables, ACID properties, index design principles, SQL statement categories, sharding, deadlock resolution, isolation anomalies, view characteristics, SQL lifecycle, primary key choices, performance troubleshooting, replication, GTID, backup tools, and backup planning.
This article explores the precise moment a Spring @Transactional transaction is committed relative to a surrounding lock release, revealing that commits occur after the method finishes, and demonstrates how to verify this through source code inspection, debugging techniques, and proper lock‑usage to prevent overselling.
This article explains how to use the nested set model to efficiently query, count, and manage hierarchical department data in MySQL, covering recursive retrieval, leaf detection, insertion, deletion, and traversal techniques with practical SQL examples and JavaScript code for building tree structures.
The article analyzes why running MySQL in Docker containers can cause data loss, performance bottlenecks, state management problems, and insufficient resource isolation, while also offering scenarios and strategies where containerizing MySQL might still be feasible.
This article explains how MySQL single‑table row limits are determined by primary‑key size, InnoDB page structure, and B+Tree fan‑out, showing why a table with 100 million rows can still query quickly when the index depth remains low.
This article explains how to install and configure MySQL 8.0's component_log_filter_dragnet, describes its core filter fields and actions, and provides practical examples for customizing error‑log output such as dropping, throttling, setting, and unsetting log entries based on time, priority, error codes, symbols, and subsystems.
This article explains MySQL high‑availability concepts, defines HA, RPO and RTO, outlines common HA architectures such as master‑slave+VIP, MHA and MGR+Proxy, and details AutoHome's evolution from simple master‑slave setups to a container‑based MGR solution with automated failover and monitoring platforms.
This article explains how to retrieve sales statistics for multiple business units and their selected products in MySQL, compares loop‑based, OR‑concatenated, mixed‑filter, and SQL‑92 row‑comparison approaches, and shows why the row‑comparison technique was ultimately chosen for its index usage and simplicity.
This article describes how a finance team tackled a 500 million‑row MySQL transaction table by analyzing pre‑split issues, defining sharding goals, selecting sharding‑jdbc, addressing multi‑datasource transaction and pagination challenges, designing a hybrid data‑migration plan, and executing a three‑stage rollout to ensure system stability and performance.
This article details the design and implementation of a highly available, high‑performance membership system serving over a billion users, covering Elasticsearch dual‑center clusters, traffic isolation, Redis caching, MySQL migration, and fine‑grained flow‑control and degradation strategies.
This tutorial demonstrates how to add Create, Read, Update, and Delete functionality to a SpringBoot application using MyBatis XML mappers, configure the datasource in application.yml, and expose REST endpoints through a service and controller layer.
Sequel Pro is a user‑friendly MySQL client that lets you connect via standard, socket or SSH methods, configure connection details, test and save them, and then explore databases through panels for selecting schemas, viewing table structures, contents, relationships, triggers, table info, and running queries.
This article examines common reasons why MySQL queries become slow, including index misuse, insufficient connections, and small InnoDB buffer pools, and provides practical solutions such as profiling, using EXPLAIN, adjusting connection limits, and tuning buffer pool size.
Explore MySQL's comprehensive index guide, covering basic concepts, B‑tree and B+‑tree structures, storage engine differences, index types, optimization strategies, and practical tips for designing effective primary, secondary, and composite indexes to boost query performance and reduce I/O overhead.
This comprehensive guide explores MySQL's architecture, explains how indexes work, details the use of EXPLAIN to analyze query execution, and provides practical optimization strategies—including index creation, avoiding common pitfalls, and improving single- and multi-table queries—to dramatically boost database performance.
This article outlines a systematic approach to identifying and fixing slow MySQL queries, explains how to interpret EXPLAIN output and profiling information, and presents multiple real‑world cases illustrating index usage, implicit conversion, large pagination, IN queries, and other optimization techniques.
This beginner-friendly tutorial walks through creating a Spring Boot project, adding MySQL and MyBatis dependencies, configuring the application.yml file, and implementing UserMapper, User, and UserController classes to demonstrate a complete MySQL query demo.
This guide walks through systematic MySQL query optimization—from identifying slow statements via logs and EXPLAIN analysis, to profiling, tracing, and applying targeted fixes such as index tuning, query rewrites, and handling special cases like left‑most matching, implicit conversion, large pagination, and IN‑order‑by pitfalls.
This article details the design and implementation of a highly available, high‑performance membership system serving billions of users, covering Elasticsearch dual‑center clusters, traffic‑isolated architectures, deep ES optimizations, Redis caching with distributed locks, dual‑center MySQL partitioning, migration strategies, abnormal account handling, and future fine‑grained flow‑control and degradation policies.
The article explains why MySQL's HEX and UNHEX functions sometimes return hexadecimal representations instead of plain strings, demonstrates the issue with example queries, clarifies the meaning of related functions, and shows how to disable the client’s binary‑as‑hex option to get correct output.
This article breaks down how MySQL's InnoDB engine guarantees the four ACID properties—atomicity, consistency, isolation, and durability—by leveraging undo and redo logs, the binary log, hidden transaction metadata, and MVCC with ReadView, illustrating each mechanism with concrete examples.
This article explains why MySQL queries can become sluggish beyond missing indexes, covering the full query execution flow, profiling tools, index pitfalls, connection‑pool limits, InnoDB buffer‑pool sizing, and practical steps to diagnose and accelerate database performance.
This article explains why a naïve Python‑based row‑deletion approach is slow for large MySQL tables and provides step‑by‑step SQL techniques—including identifying duplicate names, handling MySQL’s update‑from‑same‑table limitation, and deleting duplicates while preserving a single record per group—complete with executable code examples.
This article explores memory management concepts from both operating system and MySQL perspectives, covering virtual vs physical memory, process memory usage analysis using tools like top and /proc, and experimental verification of memory allocation behaviors.
This article evaluates single‑threaded bulk‑insert performance in MySQL by testing various batch sizes, analyzes memory usage and transaction merging, and provides practical recommendations for efficiently generating millions of records.
This guide presents a comprehensive set of MySQL optimization techniques—including proper use of EXPLAIN, limiting IN clause size, avoiding SELECT *, using LIMIT 1, minimizing sorting, replacing OR with UNION ALL, preferring UNION ALL over UNION, eliminating ORDER BY RAND(), distinguishing IN versus EXISTS, applying efficient pagination, segmenting large scans, avoiding NULL checks and leading wildcards, rewriting expressions, preventing implicit type conversion, respecting composite index leftmost rules, forcing indexes, handling range queries, and optimizing JOINs—to dramatically improve query performance.
This comprehensive guide explains MySQL's architecture, storage engines, and the critical role of indexes, walks through the SQL parsing process, details how to read and interpret EXPLAIN output, and provides practical optimization examples for single‑table and multi‑table queries.
GitHub experienced multiple service outages over recent weeks due to resource contention in its MySQL1 cluster, leading to prolonged downtimes, and the company disclosed detailed timelines, root causes, and ongoing mitigation measures such as load audits, traffic shifting, and infrastructure scaling to prevent future incidents.
This article presents a detailed experiment comparing graceful and kill‑9 shutdown methods on a MySQL 5.7 semi‑synchronous master‑slave cluster, analyzes their impact on data consistency, and outlines the official shutdown sequence with practical observations and recommendations for production environments.
This article presents a Java Spring Boot vaccination management system that supports user registration, vaccine record handling, case history, nucleic‑acid test reporting, travel tracking, risk‑area management, and epidemic‑prevention knowledge, detailing its roles, technology stack, database design, UI screenshots, and core backend source code.
This article compiles essential MySQL interview topics, covering normal forms, privilege tables, ACID properties, SQL statement categories, sharding, deadlocks, isolation anomalies, view characteristics, statement lifecycle, primary key choices, CPU troubleshooting, replication, GTID, backup tools, and backup planning strategies.
This article walks through optimizing a slow MySQL batch import by first inserting a list directly, then using grouped inserts, and finally applying multithreaded batch processing with MyBatis, showing code examples and practical tips to reduce import time from over a minute to under ten seconds.
Learn how to identify and fix slow MySQL queries by analyzing slow‑query logs, using EXPLAIN, profiling, and optimizer trace, understanding index types and extra flags, and applying practical case‑by‑case optimizations such as index tuning, query rewriting, pagination strategies, and handling large data sets.
This article explains MySQL's various character sets and collations, describes important system variables such as character_set_client and character_set_connection, demonstrates how to query supported encodings, and provides practical examples for avoiding and fixing garbled text caused by mismatched encodings.
This article walks through generating a 10‑million‑row MySQL table, inserting data with a fast batch script, measuring plain LIMIT pagination performance, and applying several optimization techniques—including sub‑queries, ID‑range filtering, and column selection—to dramatically reduce query time on large data sets.
This article explains the usage of four MySQL 8.0 window functions—first_value, last_value, nth_value, and ntile—by describing their purpose, the required window frame syntax, and providing concrete SQL examples with sample data.
This article walks through why SQL queries become bottlenecks as data grows and presents a step‑by‑step guide—including slow‑query analysis, EXPLAIN interpretation, profiling, optimizer tracing, index tuning, and scenario‑specific tricks—to dramatically improve MySQL performance.
This article details the design and implementation of a high‑availability membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis multi‑center caching, MySQL dual‑center partitioning, data migration strategies, and refined flow‑control and degradation mechanisms to ensure stable, low‑latency service under massive concurrent load.
This article explains the purpose and principles of database indexes, compares various tree structures such as binary search trees, AVL trees, B‑Trees and B+Trees, and shows how MySQL uses B+Tree indexes to minimize disk I/O and boost query performance.
This article provides a set of Bash scripts for Linux server administration, including DDoS IP blocking, email alert configuration, MySQL backup (single and multi-loop), Nginx log rotation and analysis, network interface traffic monitoring, system initialization, and disk usage monitoring across multiple servers.
This article provides a step‑by‑step tutorial on deploying a DBLE cluster, configuring sharding.xml, db.xml and user.xml, creating test tables, installing DTLE, performing full MySQL‑to‑DBLE data migration, then switching to GTID‑based incremental synchronization with detailed troubleshooting tips and best‑practice recommendations.
This article explains how to design a MySQL table for product sales statistics and compares several query strategies—including loop queries, OR concatenation, mixed filtering, and the SQL‑92 row‑by‑row comparison—to efficiently retrieve sales data for dynamic sets of business institutions and their products in a single indexed query.
This guide walks through essential MySQL performance tuning methods, from identifying slow queries via logs and EXPLAIN analysis to interpreting Extra information, using profiling and optimizer trace, and applying concrete index, query, and schema adjustments for common scenarios such as left‑most matching, implicit conversion, large pagination, IN‑order‑by, and complex queries.
The article details a comprehensive high‑availability solution for a large‑scale membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster designs, deep ES optimizations, Redis caching with consistency safeguards, MySQL partitioned migration, and fine‑grained flow‑control and degradation mechanisms.
This guide explains how to prevent data loss in MySQL by enabling row‑based binlog, performing full restores with mysqldump or xtrabackup, executing point‑in‑time recovery, restoring individual tables, skipping erroneous SQL statements using binlog or GTID, and leveraging flashback tools such as binlog2sql and MyFlash.
This article explains MySQL index fundamentals—including B‑tree, B+‑tree, hash, and ordered array structures—covers primary versus secondary indexes, describes index maintenance such as page splits and merges, examines the InnoDB change buffer, and discusses optimizer choices and cases where indexes become ineffective.
This article details the design and implementation of a high‑performance, highly available membership system, covering Elasticsearch dual‑center master‑slave clusters, traffic‑isolated three‑cluster ES architecture, Redis cache strategies, MySQL dual‑center partitioning, seamless migration, abnormal member handling, and fine‑grained flow‑control and degradation policies.
This article analyzes a production incident where massive OFFSET values in MySQL pagination caused severe query slowdown, demonstrates the problem with simulated data, benchmarks the impact, and presents three practical solutions—including index‑covering subqueries, remembering the last primary key, and limiting offset size—to dramatically improve performance and protect the database.
