This guide shows how to configure Filebeat and Logstash to ingest, parse, and index multi‑line MySQL slow query logs in Elasticsearch, covering filebeat setup, Logstash input, grok filter, and output configurations.
This step‑by‑step guide shows how to download the FIFA 18 player dataset from GitHub, create a MySQL database and table, read the CSV with Python’s csv.DictReader, generate INSERT statements, batch‑load the data efficiently, and query Argentine players, illustrating a complete end‑to‑end data‑import workflow.
This article explains how to inspect and clear Linux memory buffers, interprets the fields shown by the free command, and then dives into SQL join types, performance‑impacting factors such as join_buffer_size, and practical optimization techniques like indexing and denormalization.
In this interview, senior DBA Ge Haoqiang from Tiancheng Financial discusses the scale of their database clusters, the efficiency gains from automation, DevOps culture, language choices, and how they adapt to new database features, offering insights into modern database operations automation.
This article demonstrates how to quickly generate large volumes of test data for a MySQL table using the open‑source mysql_random_load_data tool, describing the setup, performance, and advantages over traditional stored procedures or custom scripts.
This article outlines core database architecture principles—high availability, high performance, consistency, and scalability—examines four common deployment schemes (primary‑standby, dual‑primary, primary‑replica with read/write separation, and a combined dual‑primary/replica model), and presents multiple consistency and cache‑integration solutions.
Meituan’s DBA team discovered that Intel Skylake CPUs dramatically increased PAUSE‑instruction latency, causing MySQL’s ut_delay spin‑wait loops to consume most CPU cycles and reduce write throughput, and they restored performance by back‑porting MySQL 8.0’s spin_wait_pause_multiplier patch to 5.7, upgrading to CentOS 7, and moving to Cascadelake hardware.
This article demonstrates how to capture and analyze MySQL traffic on both unencrypted MySQL 5.7 and TLS‑encrypted MySQL 8.0 using tcpdump and Wireshark, explains the differences in packet contents, and walks through the TLS handshake process in detail.
This article explains MySQL's large object data types—TEXT, BLOB, and JSON—covering their storage formats, performance implications, indexing rules, partition restrictions, relevant server parameters, and practical examples of creating tables, inserting data, and querying JSON values.
This guide explains the fundamentals of MySQL master‑slave replication, the advantages of using GTID‑based replication, and provides detailed configuration steps, command examples, and troubleshooting tips for setting up and managing a reliable, read‑scalable database environment.
This article provides an in-depth analysis of MySQL's Metadata Lock (MDL) system, detailing lock types, data structures, acquisition, upgrade, downgrade processes, compatibility matrices, and practical debugging via custom print functions, with code examples and performance schema observations.
This article explains InnoDB's logical storage hierarchy of tablespaces, segments, extents, and pages, then details MySQL's partitioning concepts, types, creation syntax, and performance considerations, providing practical examples and guidance for effective database design.
This article explains how DBLE, a PaaS middleware, implements MySQL view capabilities by first describing MySQL view concepts and the MERGE and TEMPTABLE algorithms, then detailing DBLE's push‑down and non‑push‑down approaches, the criteria for each, and providing illustrative diagrams.
This tutorial walks through downloading the Renren‑Fast open‑source admin framework, integrating it into the PassJava project, configuring MySQL and Docker, adjusting application settings, launching the backend service, building the Vue front‑end, and performing end‑to‑end login verification, providing a complete step‑by‑step guide for developers.
This guide introduces four open‑source MySQL tuning utilities—mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download and run each tool, and shows how to interpret their AWR‑style reports for concrete performance and security improvements.
This extensive guide covers MySQL fundamentals and advanced concepts, including storage engines, data types, indexes, transactions, isolation levels, locking mechanisms, views, stored procedures, triggers, SQL optimization techniques, replication, backup strategies, and sharding approaches for high‑availability and scalability.
This article walks through constructing a comprehensive MySQL monitoring solution using Prometheus and Grafana, covering exporter installation, configuration, key performance metrics, alert rule definitions, and practical tips for visualizing and troubleshooting MySQL instances in production environments.
This article demonstrates a step‑by‑step experiment using sysbench and performance_schema to measure how different values of innodb_buffer_pool_instances affect buffer‑pool lock latency, analyzes the collected data, and discusses the observed trends and limitations.
This guide explains how to install, configure, and use the open‑source os_monitor tool to collect CPU, memory, and disk metrics, store them in MySQL, and trigger email or WeChat alerts through a web dashboard with simple SQL‑based setup.
This article explains the complete disk‑write journey of a MySQL INSERT statement, detailing the roles and order of redo log, binlog, double‑write, insert buffer, and data files, and clarifying InnoDB’s buffer‑pool and flushing mechanisms.
This article explains how InnoDB organizes data with B+ trees, calculates the maximum number of rows a single tree can hold (around 20 million), and shows how page size, pointer count, and tree height affect MySQL index performance.
This tutorial explains how to download the MySQL Docker image, create and run a MySQL container with volume mappings and environment variables, verify the container, connect to the database via Workbench, and access the container’s shell and host files.
This article explains the purpose of DBLE's heartbeat detection, including high‑availability switching, read load balancing, and idle connection management, and provides a detailed walkthrough of the module's source code—from the Scheduler initialization to the MySQLHeartbeat implementation—illustrating how the system monitors MySQL instance status.
This article explains why MySQL requires locking, compares table and row lock types, details MyISAM and InnoDB lock mechanisms—including optimistic vs. pessimistic locks, lock algorithms, and deadlock handling—while providing practical SQL examples and prevention strategies.
This article explains how to use Linux commands like free and sync to view and clear buffer/cache memory, describes the information shown by free, and then dives into SQL join types, their performance impact, and optimization techniques such as indexing, join_buffer tuning, and join algorithms.
This article walks through setting up a production‑grade MySQL monitoring solution using Prometheus and Grafana, covering exporter installation, MySQL user configuration, systemd service setup, Prometheus job definition, key MySQL performance metrics, and basic alerting rules.
This article presents a collection of interview questions and resource links covering system design, distributed sessions, Java pitfalls, MySQL indexing, and other backend topics, encouraging developers to keep learning and offering QR code access for further content.
This article explains why large OFFSET values in MySQL LIMIT queries cause severe performance degradation and demonstrates how rewriting the query to fetch primary keys first and then joining reduces execution time from seconds to milliseconds, supported by buffer‑pool analysis and practical test results.
This article walks through diagnosing severe SLA alerts caused by slow queries on a huge MySQL table, using pt‑query‑digest and tcpdump to uncover index misuse, then proposes new composite and single‑column indexes, demonstrates online DDL versus pt‑osc, and shows how batch deletions and efficient backup/restore dramatically reduce execution time and replication lag.
This weekly newsletter summarizes the latest DBLE, DTLE, and TXLE open‑source middleware updates, including new feature developments, bug fixes, community‑submitted articles on MySQL topics, and upcoming plans, while inviting community feedback and participation.
Through a hands‑on experiment, this article demonstrates how MySQL’s internal temporary tables, governed by tmp_table_size and max_heap_table_size, transition from in‑memory heap storage to on‑disk storage, showing the resulting I/O behavior, disk usage statistics, and the impact of engine configuration.
This article explains why B+ trees are the dominant data structure for MySQL indexes, compares them with hash tables, linked lists, and skip lists, and details page splits, merges, and how index values map to row records, helping readers master high‑frequency interview questions.
This article provides an in‑depth analysis of MySQL's filesort mechanism, explaining the original and modified algorithms, how sort and addon fields are calculated, memory allocation strategies, the role of temporary files, and practical tips to reduce excessive disk usage during sorting operations.
This article presents a detailed MySQL performance benchmark of Alibaba Cloud RDS, Tencent Cloud RDS, and AWS RDS using sysbench, analyzes configuration differences, shows results across mixed, read‑only, and no‑index update workloads, and offers practical recommendations for cloud database tuning.
This article presents thirty actionable SQL performance recommendations—including selecting specific columns, using LIMIT, avoiding OR and functions in WHERE clauses, proper indexing, batch inserts, and query planning—each illustrated with clear examples and explanations to help developers write faster, more efficient queries.
This article provides a comprehensive overview of MySQL data types—including numeric, floating‑point, decimal, string, date/time, binary, bit, enum, and set types—explaining their storage characteristics, appropriate use cases, and performance implications through practical examples and SQL demonstrations.
This article explains the principles of pessimistic concurrency control in relational databases, details the conditions under which MySQL's SELECT … FOR UPDATE works, compares it with optimistic locking, provides concrete SQL examples, and demonstrates lock behavior through multiple transaction test scenarios.
This guide explains how to install Percona XtraBackup 2.4, perform hot full backups of MySQL 5.7 databases, compress and transfer them, and configure master‑slave replication using innobackupex and standard MySQL commands.
This guide explains what the MySQL Test framework is, how to set up the required environment on Ubuntu, install dependencies and MySQL source, configure and compile the test suite, and demonstrates creating and running a simple test case with detailed command‑line examples.
When a MySQL table grows to millions of rows, query, insert, update and delete performance degrades sharply, but by applying proper field design, index strategies, SQL best practices, engine selection, system parameters, caching, partitioning, vertical and horizontal sharding, and choosing suitable client‑side or proxy‑side architectures, the database can efficiently handle tens of millions of records.
Yearning is an open‑source MySQL audit platform that offers query auditing, SQL review, and workflow automation, providing a web‑based interface for managing tickets, permissions, and notifications, with detailed installation and configuration instructions for quick deployment.
This guide introduces dtle, an open‑source MySQL data‑transfer middleware, covering download, installation on three nodes, detailed dtle.conf configuration, startup commands, creating source and target users, preparing job.json, using HTTP API to create and monitor migration jobs, and verifying data migration results.
This article examines why a MySQL query with a large OFFSET is extremely slow, demonstrates the performance gap with a join‑based rewrite, and validates the findings by comparing InnoDB buffer‑pool page usage, highlighting how unnecessary random I/O pollutes the cache.
This article explains how to identify slow MySQL queries using the slow‑query log and SHOW PROCESSLIST, configure logging parameters, and analyze query performance with EXPLAIN, covering key output fields, common values, and practical code examples for effective SQL optimization.
This guide introduces the open‑source macOS MySQL client Sequel Pro, explains its interface and SSH security options, and provides two practical solutions—including a test‑build download—to resolve the frequent crashes when connecting to MySQL 8, enabling stable database management.
Yearning is an open‑source MySQL SQL audit platform that offers query auditing, workflow‑driven review, execution, rollback, fine‑grained permission control and notification integrations, with detailed installation steps and a public Gitee repository for easy deployment.
This weekly community roundup presents the latest DBLE and TXLE open‑source developments, highlights new MySQL features and diagnostic tools, shares bug‑fixes and upcoming plans, and invites readers to contribute feedback and questions about the distributed database middleware ecosystem.
Through a series of experiments using MySQL's performance_schema and explicit MEMORY tables, this article demonstrates how internal temporary tables consume memory, reveals that they allocate significantly more space than raw data size, and discusses the implications for accurate memory estimation.
This article explains how MySQL computes replication delay using show_slave_status, analyzes the relationship between binlog write timestamps and event generation, enumerates common causes of lag, and provides a detailed examination of the system‑lock state in the slave SQL thread with code examples.
This article examines various MySQL pagination methods—including simple LIMIT, offset impact, sub‑query, ID‑range, and temporary‑table strategies—provides detailed performance measurements on a table with over five million rows, and offers practical recommendations for speeding up large‑scale data retrieval.
This article explores the design of a high‑performance short‑link system, covering its benefits, redirection mechanics, hash‑based and auto‑increment generation methods, database schema, Bloom filter optimization, and a scalable OpenResty‑based architecture for handling massive traffic.
This article explains InnoDB's logical storage hierarchy—tablespace, segment, extent, page—then details MySQL partitioning concepts, types, example DDL, performance impacts, and best‑practice tips for OLTP and OLAP workloads.
MySQL 8.0.19 adds two novel DML statements—TABLE for convenient full‑table scans and sub‑query replacements, and VALUES for generating in‑memory row sets—each with clear syntax, practical examples, and execution‑plan insights that help developers quickly adopt these features.
This article introduces BPF/eBPF fundamentals, explains the bcc toolkit, provides installation steps for Ubuntu and CentOS, and demonstrates how to use bcc's dbstat and dbslower utilities to trace MySQL query latency and slow queries on Linux systems.
This guide introduces four open‑source MySQL diagnostic utilities—MySQLTuner, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explaining how to download, run them against a live server, interpret the generated AWR‑style reports, and apply the recommended configuration changes for better stability and speed.
Learn step‑by‑step how to set up multiple MySQL data sources in Spring Boot 2.2.2, configure corresponding JdbcTemplate beans, define entity and service layers, and run unit tests, with full Maven dependencies, property settings, Java configuration, and SQL DDL examples.
This article explains how Linux memory statistics (free, top) report buffer/cache usage, how to clear it with sync and drop_caches, and then dives into SQL join concepts, types, optimization techniques, and MySQL join algorithms such as nested loop and block nested loop, highlighting their impact on performance.
A detailed reference covering MySQL service management, connection commands, database and table operations, data types, indexes, joins, subqueries, transactions, backup, user privileges, and common SQL functions, complete with practical code examples.
This article explains key Linux operations concepts such as gray release deployment, DNS resolution steps, RabbitMQ messaging, Keepalived high‑availability mechanisms, LVS load‑balancing modes, MySQL lock troubleshooting, replication lag mitigation, root password reset, backup tools, and compares Nginx, LVS, and HAProxy.
When deploying a Spring Boot application on CentOS, MySQL connections may throw CommunicationsException or SSLHandshakeException due to JDK mismatches, socket path errors, SSL settings, timeout defaults, or configuration issues, and this guide explains each cause and provides concrete fixes.
This guide introduces four key MySQL optimization utilities—mysqltuner.pl, tuning-primer.sh, pt-variable-advisor, and pt-query-digest—explains how to download, install, and run each tool, and shows how to interpret their health‑check reports to identify performance bottlenecks, configuration issues, and security risks.
This weekly DBLE community newsletter, authored by reporter Yongzheng, compiles curated MySQL and DBLE resources, outlines recent open‑source developments, bug fixes, community Q&A, and upcoming plans for both DBLE and TXLE middleware projects.
This comprehensive guide covers essential Linux fundamentals, common interview questions, and practical server administration techniques, including boot sequences, runlevels, file system differences, process management, packaging tools, Nginx optimization, MySQL replication, Docker usage, and useful shell scripts for real‑world operations.
This guide shows how to generate hourly slow‑query reports from MySQL slow‑log files by using pt‑query‑digest’s timeline feature, filtering with sed, importing the data into SQLite with termsql, and then querying the results to identify hotspot periods and recurring patterns.
The article examines why a 100 GB MySQL 5.6 transaction causes high CPU usage during lock_number_of_rows_locked calculation, compares the lock‑counting implementations of MySQL 5.6 and 5.7, and offers diagnostic steps and version‑upgrade recommendations to mitigate the bottleneck.
This article explains the distinctions between UNION and UNION ALL, TRUNCATE and DELETE, TIMESTAMP and DATETIME, introduces composite and covering indexes, describes InnoDB versus MyISAM storage, the left‑most prefix rule, monotonic primary keys, int display length, SHOW INDEX fields, handling LIKE patterns, and efficient pagination techniques.
This tutorial explains MySQL 8.0 role concepts, demonstrates how to create roles, assign them to multiple users, activate default roles, configure role parameters, and revoke roles, providing step‑by‑step examples with complete MySQL commands and explanations for effective privilege management.
This article explains MySQL storage engines, compares MyISAM and InnoDB, introduces the main index types—B‑Tree, Hash, Full‑text, R‑Tree—details the structures and characteristics of B‑Tree, B‑Tree and B+Tree, and outlines key principles for creating effective indexes.
This article walks through the end‑to‑end setup of a small‑scale data platform using Alibaba's Canal for MySQL binlog capture, covering the installation and configuration of MySQL, Zookeeper, Kafka, and Canal itself, and demonstrates real‑time change capture with sample DML operations.
This article explains MySQL index design principles, demonstrates the execution steps of a range query on a sample table, and discusses common index optimizations such as covering indexes, the left‑most prefix rule, index condition push‑down, implicit type conversion, as well as why the optimizer may choose a wrong index and how to correct it.
This article analyzes a MySQL InnoDB deadlock caused by concurrent batch inserts into a table with a composite unique index, explains the lock types involved, and offers practical strategies such as shortening transactions and lowering isolation levels to avoid similar deadlocks.
This article explains core MySQL concepts, comparing UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP and DATETIME, composite and covering indexes, the left‑most prefix rule, clustered vs non‑clustered indexes, prefix indexes, InnoDB vs MyISAM storage, monotonic primary keys, column length display, SHOW INDEX fields, LIKE pattern pitfalls, and efficient pagination techniques.
This article explains a MySQL replication experiment where a master is restored from an outdated backup, leading to missing GTID transactions due to server_id filtering, and provides analysis and solutions such as changing server_id or enabling replicate-same-server-id to prevent data loss.
This article explains the differences between MySQL table locks and row locks, describes how MyISAM and InnoDB handle locking, provides explicit lock syntax with examples, discusses gap locks, and offers practical recommendations for avoiding lock contention in concurrent applications.
The article reviews a Tencent Cloud TVP closed‑door forum where experts compared MySQL 8.0, PostgreSQL, Redis and the cloud‑native CynosDB, highlighting each product’s technical strengths, ecosystem support, and the broader industry trend toward cloud‑native, multi‑database solutions as the future of data management.
This guide explains essential MySQL concepts such as the distinction between UNION and UNION ALL, TRUNCATE versus DELETE, TIMESTAMP versus DATETIME, the purpose and benefits of composite indexes, index storage engines, prefix indexes, covering indexes, the left‑most prefix rule, and efficient pagination techniques.
This article provides a step‑by‑step tutorial on building a small‑scale data platform by installing MySQL, Zookeeper, Kafka and the open‑source Canal middleware, configuring Canal to capture MySQL binlog events, and forwarding the structured data to Kafka for downstream processing.
This article presents a comprehensive guide for synchronizing massive MySQL datasets to HBase, covering environment preparation, fast MySQL data loading techniques, and three practical pipelines—Sqoop, Kafka‑Thrift, and Kafka‑Flink—along with performance comparisons and optimization tips for large‑scale data processing.
This article demonstrates how to reproduce disk‑IO saturation in MySQL, enable performance_schema instruments and consumers, apply load, and use sys.x$latest_file_io together with events_waits_history_long to pinpoint whether binlog, redo log, or specific tables are causing the slowdown.
This article explains MySQL’s transaction isolation levels, clarifies the differences between dirty reads, non‑repeatable reads and phantom reads, and demonstrates through experiments why the REPEATABLE READ level can prevent phantom reads, while also discussing its limitations, MVCC, and locking mechanisms.
This article explains what MySQL indexes are, the different categories such as ordinary, unique, composite, clustered and non‑clustered, how B‑Tree, B+Tree and hash indexes are implemented in InnoDB and MyISAM, and why auto‑increment primary keys are recommended for optimal performance.
This tutorial explains MySQL fundamentals, the advantages of using MySQL, detailed concepts of indexes, how they work, step‑by‑step SQL examples for creating, viewing, and dropping indexes, best‑practice principles, and query‑optimization techniques to improve performance.
This tutorial walks through preparing the development environment, creating a Django project and app in PyCharm, configuring MySQL, adjusting Django's MySQL backend, setting up URL routing, and establishing a folder for file uploads, all illustrated with step‑by‑step screenshots.
The article documents a puzzling MySQL 5.7.25 bug where RAND() in a derived table is unexpectedly re‑evaluated in the outer query, explains the step‑by‑step reproduction, analyses the cause, and provides official work‑arounds for both MySQL 5.7 and 8.0.
This article compares two common approaches for encrypting user passwords in MySQL—using the built‑in AES_ENCRYPT/AES_DECRYPT functions and implementing encryption in the application layer—detailing their advantages, drawbacks, and providing concrete code examples for each method.
This article presents essential MySQL interview topics, comparing MyISAM and InnoDB storage engines, explaining INT‑Char implicit conversion nuances, demonstrating efficient methods to fetch random rows, and outlining various index types with their appropriate use cases.
This article analyses why the GTID information recorded by Xtrabackup’s xtrabackup_binlog_info file can differ from the GTID shown by SHOW MASTER STATUS after a restore, comparing the behavior of Xtrabackup 2.4 on MySQL 5.7 and Xtrabackup 8.0 on MySQL 8.0, and explains the underlying backup processes and MySQL internals that cause the discrepancy.
This article explains how MySQL’s character_set parameters such as character_set_client and character_set_results control the encoding and decoding of client commands and query results, illustrates common pitfalls with UTF‑8, GBK and Latin‑1, and provides practical commands to avoid garbled text.
This weekly newsletter summarizes the March DB‑Engines database ranking, explains its methodology, shares curated MySQL articles and tools, and reports the latest development, bug‑fix, and roadmap updates for the open‑source middleware projects DBLE, DTLE, and TXLE.
This article outlines essential principles for selecting and managing database indexes, covering unique indexes, indexing frequently sorted or filtered columns, limiting index count, using short or prefix indexes, removing unused indexes, left‑most prefix matching, column cardinality, avoiding functions on indexed columns, and extending existing indexes.
This guide explains how to create a delayed‑disk device on Linux, place MySQL binlog files on it, and use tools like mysqlslap and pt‑ioprofile to observe the impact of artificially slow I/O on MySQL performance.
This article provides a comprehensive overview of MySQL InnoDB's internal architecture, covering its memory and disk structures, buffer pool, change buffer, adaptive hash index, log buffer, various tablespace types, page layout, undo and redo logs, double‑write buffer, transaction isolation levels, and how the engine satisfies ACID properties.
This comprehensive guide explains MySQL fundamentals, transaction handling, ACID properties, isolation levels, indexing strategies, storage engine differences, lock mechanisms, optimistic vs pessimistic locking, and techniques for optimizing large tables in production environments.
The article explains the shortcomings of traditional MySQL replication lag measurement, introduces the community heartbeat‑table approach and its limitations, and then details MySQL 8 enhancements (WL#7319, WL#7374) that embed transaction timestamps in binlog events and expose new performance_schema tables, providing precise SQL queries to monitor lag at various stages of the replication pipeline.
This tutorial explains how to integrate JDBC into a Spring Boot project, covering Maven dependencies, MySQL driver setup, application.yml datasource configuration, Spring Boot's auto‑configuration mechanism, JdbcTemplate usage, Swagger documentation, CRUD endpoint implementation, and solutions to typical connection errors.
This article introduces several MySQL performance‑tuning utilities—including mysqltuner.pl, tuning‑primer.sh, pt‑variable‑advisor, and pt‑query‑digest—explains how to download, install, run them, and interpret their reports to identify configuration issues and optimize database performance.
This article analyzes a slow MySQL aggregation query that joins a large table with a derived table built from many UNION ALL subqueries, explains the execution plan and derived‑table materialization, and presents two optimization approaches—adding taskname indexes and rewriting the query or using a temporary indexed table—to dramatically reduce execution time while preserving result semantics.
This tutorial explains how to create and operate a MySQL InnoDB ReplicaSet—deploying sandbox instances, configuring a replica set with primary and secondary roles, adding nodes, performing manual primary switches and failure recovery, and using MySQL Router to provide an automatic read/write endpoint, while highlighting current limitations.
This guide explains how to integrate JDBC into a Spring Boot project, covering Maven dependencies, datasource configuration, Spring Boot's auto‑configuration mechanism, JdbcTemplate usage, Swagger setup for API testing, CRUD examples on a MySQL department table, and solutions to typical connection errors.
This guide walks through integrating MyBatis into a Spring Boot project, covering dependency setup, MySQL configuration, entity and mapper creation, CRUD annotations, and unit testing to verify database operations, including transaction management and result mapping.
