BestHub
Sign in
Tagged articles
5000 articles
Page 29 of 50
Liangxu Linux
Liangxu Linux
Jul 19, 2022 · Databases

Master MySQL Stored Procedures, Events, and Triggers for Automated Data Workflows

This guide explains how to use MySQL stored procedures, events (timers), and triggers—including their creation, variable handling, control structures, parameter passing, loops, scheduling options, and practical examples—to automate reporting, data monitoring, and business logic directly at the database layer.

Event SchedulerStored Proceduredatabase automation
0 likes · 11 min read
Master MySQL Stored Procedures, Events, and Triggers for Automated Data Workflows
IT Services Circle
IT Services Circle
Jul 18, 2022 · Databases

Migrating MySQL Dual-Master High Availability to Master‑Slave Architecture: Lessons Learned and Simple Conversion Steps

After a month of testing a MySQL dual‑master high‑availability setup, the author details the numerous pitfalls encountered—including primary key collisions, sync failures, and data inconsistencies—and explains why they switched to a simpler master‑slave configuration, providing step‑by‑step instructions for the conversion.

Database ReplicationMaster‑Slavehigh availability
0 likes · 8 min read
Migrating MySQL Dual-Master High Availability to Master‑Slave Architecture: Lessons Learned and Simple Conversion Steps
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 18, 2022 · Databases

Analysis of Commit Blocking and SHOW MASTER STATUS Contention in MySQL 5.7 Replication

The article examines how a large transaction in MySQL 5.7 can block the SHOW MASTER STATUS command by holding the lock_log during the commit's flush stage, analyzes the commit and SHOW MASTER STATUS code paths, and proposes mitigation strategies such as avoiding big transactions and using SELECT @@global.gtid_executed.

Binlogcommitlarge transaction
0 likes · 7 min read
Analysis of Commit Blocking and SHOW MASTER STATUS Contention in MySQL 5.7 Replication
Architecture Digest
Architecture Digest
Jul 18, 2022 · Databases

Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It

The article analyzes a slow MySQL query that uses an IN subquery on a massive user table, explains how the optimizer’s semi‑join and materialized temporary tables cause full‑table scans, and demonstrates practical fixes such as disabling the semi‑join optimizer switch or rewriting the SQL to regain index usage.

SQL OptimizationSemi-Joinexecution plan
0 likes · 8 min read
Why MySQL IN Subquery with Semi‑Join Is Slow and How to Optimize It
FunTester
FunTester
Jul 18, 2022 · Databases

Why MySQL’s MysqlConnectionPoolDataSource Isn’t a Real Connection Pool (And How to Use It)

The article examines the MySQL class com.mysql.cj.jdbc.MysqlConnectionPoolDataSource, revealing that despite its name it creates new connections each request and only recycles idle ones, provides a practical Groovy example, highlights pitfalls such as duplicate setURL methods, and concludes it’s unsuitable as a true pool for testing.

Connection PoolGroovyPerformance Testing
0 likes · 5 min read
Why MySQL’s MysqlConnectionPoolDataSource Isn’t a Real Connection Pool (And How to Use It)
Top Architect
Top Architect
Jul 16, 2022 · Databases

Understanding MySQL Locks: Table, Row, and Gap Locks in InnoDB and MyISAM

This article explains MySQL locking mechanisms—including table‑level, row‑level, shared and exclusive locks, as well as gap locks—covers the differences between MyISAM and InnoDB engines, shows how to monitor lock statistics, and provides practical optimization tips for reducing lock contention.

Database OptimizationInnoDBLocks
0 likes · 9 min read
Understanding MySQL Locks: Table, Row, and Gap Locks in InnoDB and MyISAM
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 15, 2022 · Databases

SQLE 1.2207.0‑pre1 Release Notes: New OceanBase MySQL Support, Feature Updates, and Bug Fixes

The SQLE 1.2207.0‑pre1 pre‑release introduces OceanBase MySQL‑mode audit plugins, exclusive OceanBase rules, enhanced table‑name case checks, count(*) recommendations, adds TiDB audit‑log support, fixes several MySQL‑related bugs, and outlines future plans for more OceanBase and Oracle‑mode auditing capabilities.

Database AuditingOceanBaseRelease Notes
0 likes · 5 min read
SQLE 1.2207.0‑pre1 Release Notes: New OceanBase MySQL Support, Feature Updates, and Bug Fixes
Programmer DD
Programmer DD
Jul 15, 2022 · Databases

Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them

This article examines frequent MySQL performance problems—such as inefficient LIMIT usage, implicit type conversion, suboptimal joins, mixed ordering, EXISTS subqueries, condition pushdown limitations, early range reduction, and intermediate result handling—and provides concrete rewrites and execution‑plan analyses that dramatically improve query speed.

Query RewritingSQL Optimizationdatabase indexing
0 likes · 16 min read
Boost MySQL Performance: 8 Common SQL Pitfalls and How to Fix Them
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 14, 2022 · Backend Development

Introducing and Evaluating the DBLE Split Command for Accelerated Data Import

This article explains the DBLE split feature that partitions large mysqldump files according to sharding configuration, provides command syntax and usage examples, and presents a performance test showing that split‑based import can be up to 18 times faster than direct DBLE import while preserving data correctness.

DBLEPerformance Testingdata import
0 likes · 12 min read
Introducing and Evaluating the DBLE Split Command for Accelerated Data Import
Programmer DD
Programmer DD
Jul 14, 2022 · Databases

Why MySQL Joins Lag Behind PostgreSQL and How to Optimize Multi‑Table Queries

This article examines MySQL’s limited join capabilities compared to PostgreSQL, explains why multi‑table queries over three tables can be inefficient, and explores strategies such as service‑layer joins, query decomposition, and caching to improve performance and scalability in database‑centric applications.

JOIN optimizationQuery DecompositionService Layer
0 likes · 9 min read
Why MySQL Joins Lag Behind PostgreSQL and How to Optimize Multi‑Table Queries
Programmer DD
Programmer DD
Jul 14, 2022 · Big Data

Master Fast Data Synchronization with Alibaba DataX: A Step‑by‑Step Guide

This article explains why traditional mysqldump and file‑based methods struggle with massive tables, introduces Alibaba DataX as a high‑performance offline data integration tool, details its architecture, and provides comprehensive installation and configuration steps for full and incremental MySQL‑to‑MySQL synchronization using JSON job files.

Big DataDataXETL
0 likes · 15 min read
Master Fast Data Synchronization with Alibaba DataX: A Step‑by‑Step Guide
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 13, 2022 · Databases

Bypassing GTID Restrictions on CREATE TABLE … SELECT and Temporary Tables in MySQL 5.6/5.7 and Their Removal in MySQL 8.0

The article explains the strict GTID‑based replication limits on CREATE TABLE … SELECT and temporary‑table statements in MySQL 5.6/5.7, demonstrates practical work‑arounds such as splitting statements or using CREATE TABLE LIKE, and shows how MySQL 8.0’s native DDL atomicity eliminates these constraints.

DDLGTIDMySQL8.0
0 likes · 12 min read
Bypassing GTID Restrictions on CREATE TABLE … SELECT and Temporary Tables in MySQL 5.6/5.7 and Their Removal in MySQL 8.0
dbaplus Community
dbaplus Community
Jul 11, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them

This article explains why MySQL queries can become sluggish, covering the full query execution flow, profiling techniques, index pitfalls, connection‑pool limits, buffer‑pool sizing, and additional performance tricks, while providing concrete commands and code examples for each optimization step.

Connection PoolIndex OptimizationProfiling
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them
Wukong Talks Architecture
Wukong Talks Architecture
Jul 11, 2022 · Databases

Migrating from MySQL Dual-Master to Master‑Slave: Lessons Learned and Simple Conversion Steps

After a month of operating a MySQL dual‑master high‑availability cluster, the author details the numerous pitfalls encountered and explains a step‑by‑step manual process for converting the setup to a simpler master‑slave architecture, including configuration changes and Keepalived adjustments.

Dual MasterMaster‑Slavedatabase migration
0 likes · 9 min read
Migrating from MySQL Dual-Master to Master‑Slave: Lessons Learned and Simple Conversion Steps
Aikesheng Open Source Community
Aikesheng Open Source Community
Jul 11, 2022 · Databases

Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist

This article explains how the default MySQL SHOW PROCESSLIST command can create a global mutex that slows down busy systems, demonstrates the problem with slow INSERTs reproduced via mysqlslap, and shows how enabling the performance_schema_show_processlist variable in MySQL 8.0.22+ eliminates the contention while providing best‑practice recommendations.

Database PerformanceSHOW PROCESSLISTSQL Optimization
0 likes · 6 min read
Avoiding Global Mutex Contention in MySQL SHOW PROCESSLIST with performance_schema_show_processlist
Liangxu Linux
Liangxu Linux
Jul 9, 2022 · Databases

How Indexes Transform MySQL Subquery Performance: A Deep Dive

This article walks through a MySQL 5.6 scenario where a subquery that originally took over eight hours is accelerated by orders of magnitude through strategic index creation, query rewriting, and join optimization, illustrating the impact of single‑column, composite, and covering indexes on execution plans.

Index OptimizationJOINSubquery
0 likes · 13 min read
How Indexes Transform MySQL Subquery Performance: A Deep Dive
Programmer DD
Programmer DD
Jul 8, 2022 · Databases

Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive

This article investigates MySQL’s recommendation against UUIDs, comparing auto‑increment, UUID, and Snowflake‑style random keys through insertion and query benchmarks, analyzing their index structures, performance impacts, and trade‑offs, and concludes why sequential primary keys generally outperform non‑sequential identifiers.

auto_incrementdatabaseindex
0 likes · 12 min read
Why Auto‑Increment Beats UUID in MySQL: Performance Test & Deep Dive
Top Architect
Top Architect
Jul 7, 2022 · Databases

Understanding InnoDB Logical Storage Structure and B+Tree Indexes in MySQL

The article explains MySQL InnoDB's logical storage architecture, including tablespaces, pages, segments, and B+‑tree indexes, demonstrates how primary and secondary indexes are organized and accessed, and shows how to calculate index tree height and data capacity using SQL queries and hexdump analysis.

B+TreeInnoDBdatabase
0 likes · 10 min read
Understanding InnoDB Logical Storage Structure and B+Tree Indexes in MySQL
IT Services Circle
IT Services Circle
Jul 6, 2022 · Databases

Understanding MySQL COUNT() Performance and Strategies for Large Tables

This article explains how MySQL COUNT() works under different storage engines, why counting rows becomes slow on large InnoDB tables, and presents practical methods such as using EXPLAIN rows, auxiliary count tables, batch processing, and transaction‑based updates to obtain approximate or exact row counts efficiently.

InnoDB_countdatabase
0 likes · 12 min read
Understanding MySQL COUNT() Performance and Strategies for Large Tables
Java Captain
Java Captain
Jul 5, 2022 · Frontend Development

Music Website Project – Full‑Stack Implementation with Vue 3 and Spring Boot

This article introduces a music website built with Vue 3 on the client side and Spring Boot + MyBatis on the server side, detailing its features, technology stack, development environment, and step‑by‑step instructions for downloading, configuring, and running the full‑stack application.

Full-StackSpring BootTutorial
0 likes · 7 min read
Music Website Project – Full‑Stack Implementation with Vue 3 and Spring Boot
dbaplus Community
dbaplus Community
Jul 4, 2022 · Databases

Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up

The article explains why a MySQL query with a large LIMIT offset becomes extremely slow, demonstrates the underlying I/O behavior, and shows how rewriting the query with a sub‑query that fetches primary keys first can cut execution time from seconds to milliseconds while also reducing buffer‑pool pollution.

InnoDBLIMITSubquery
0 likes · 9 min read
Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up
Liangxu Linux
Liangxu Linux
Jul 3, 2022 · Databases

What’s the Optimal Batch Size for MySQL Inserts? A Deep Performance Test

This article investigates how many rows should be inserted per batch in MySQL by measuring the impact of packet size limits, buffer pool usage, insert buffers, transaction handling and index structures, and it provides practical recommendations based on tests with millions of rows.

Batch Insertbuffer poolmax_allowed_packet
0 likes · 16 min read
What’s the Optimal Batch Size for MySQL Inserts? A Deep Performance Test
dbaplus Community
dbaplus Community
Jul 3, 2022 · Databases

Master MySQL Clone Plugin: Installation, Usage, and Optimization

This guide explains MySQL 8.0.17's Clone Plugin—how to install it, perform local and remote clones, monitor progress via performance_schema, build replicas from cloned data, understand its internal stages, configure parameters, and compare it with XtraBackup.

BackupClone PluginDatabase Replication
0 likes · 19 min read
Master MySQL Clone Plugin: Installation, Usage, and Optimization
Programmer DD
Programmer DD
Jul 3, 2022 · Databases

What Happens When MySQL Auto‑Increment IDs Hit Their Limit?

This article explains how MySQL’s various auto‑increment identifiers—table AUTO_INCREMENT, InnoDB row_id, Xid, trx_id, and thread_id—behave when they reach their maximum values, the resulting errors or data overwrites, and the underlying mechanisms that cause these edge‑case behaviors.

InnoDBXidauto_increment
0 likes · 13 min read
What Happens When MySQL Auto‑Increment IDs Hit Their Limit?
Top Architect
Top Architect
Jul 1, 2022 · Databases

Analyzing the Time Cost of Establishing MySQL Database Connections in Java Web Applications

This article examines how a MySQL connection is created from a Java web application, breaking down the TCP handshake, authentication, and variable‑setting steps with Wireshark captures, measuring latency, and demonstrating why connection pooling is essential to avoid hundreds of milliseconds of overhead per request.

Connection PoolDatabase ConnectionTCP
0 likes · 7 min read
Analyzing the Time Cost of Establishing MySQL Database Connections in Java Web Applications
Open Source Linux
Open Source Linux
Jul 1, 2022 · Databases

Understanding MySQL Buffers, Cache, and Zero‑Copy I/O: A Deep Dive

This article explains the differences between buffers and caches, explores MySQL’s buffer pool architecture, details write‑through/write‑back strategies, and reviews key InnoDB parameters such as innodb_flush_log_at_trx_commit and innodb_flush_method for optimizing data durability and performance.

CacheFlushInnoDB
0 likes · 12 min read
Understanding MySQL Buffers, Cache, and Zero‑Copy I/O: A Deep Dive
Top Architect
Top Architect
Jun 30, 2022 · Databases

Database Sharding and Partitioning Strategies with Practical Implementation

This article explains why high‑traffic systems need database sharding, compares table‑level and database‑level partitioning methods, presents concrete MySQL table‑creation scripts and Java service code, and discusses the advantages, drawbacks, and common middleware solutions such as Sharding‑JDBC and Mycat.

MycatSharding-JDBCTable Partitioning
0 likes · 12 min read
Database Sharding and Partitioning Strategies with Practical Implementation
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 30, 2022 · Databases

Handling Replication Anomalies in MySQL Slave IO Thread

This article analyzes MySQL replication anomalies caused by master failures or network interruptions that lead to incomplete transaction replay on slaves, demonstrates a reproducible experiment using network delay and iptables, and provides practical guidance for both recovering and permanently handling stalled slave IO threads.

GTIDReplicationdatabase
0 likes · 6 min read
Handling Replication Anomalies in MySQL Slave IO Thread
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 29, 2022 · Databases

Understanding Multi-Column Partition Tables in MySQL: Implementation, Performance Comparison, and Best Practices

This article explains MySQL's support for multi‑column partition tables, provides a stored‑procedure to create them, compares performance against single‑column partitions under various query conditions, discusses partition column order impact, and offers practical guidance for when and how to use multi‑column partitions.

Multi-ColumnPartitioningdatabase
0 likes · 10 min read
Understanding Multi-Column Partition Tables in MySQL: Implementation, Performance Comparison, and Best Practices
Open Source Linux
Open Source Linux
Jun 29, 2022 · Databases

Top MySQL Interview Questions & Answers Every DBA Should Know

This article compiles essential MySQL interview questions covering primary and candidate keys, date functions, trigger limits, index column counts, data copying techniques, core MySQL concepts, feature highlights, transaction ACID properties, heap versus clustered tables, numeric type differences, and objects creatable with CREATE statements.

Heap TableTriggersdatabase
0 likes · 9 min read
Top MySQL Interview Questions & Answers Every DBA Should Know
MaGe Linux Operations
MaGe Linux Operations
Jun 28, 2022 · Databases

How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive

This article walks through a MySQL scenario with large tables, demonstrates why a sub‑query that took over eight hours runs in just a second after adding appropriate single‑column and composite indexes, and explains how to read and interpret execution plans for further tuning.

Index Optimizationdatabasemysql
0 likes · 11 min read
How Indexes Can Speed Up MySQL Queries by 50,000×: A Deep Dive
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 28, 2022 · Databases

Quick Guide to Enabling Read‑Write Splitting in Dble (v3.20.10+)

This article explains how to configure Dble's read‑write splitting feature by preparing MySQL master‑slave instances, defining dbGroup and rwSplitUser in XML configuration files, reloading the settings, and verifying that write statements go to the primary node while reads are load‑balanced across replicas.

ConfigurationDatabase Middlewareload balancing
0 likes · 6 min read
Quick Guide to Enabling Read‑Write Splitting in Dble (v3.20.10+)
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 27, 2022 · Databases

SQLE 1.2206.0-pre2 Release Notes: New Features, Full Release Information, and Future Plans

The preview release SQLE 1.2206.0-pre2 introduces enterprise‑level SQL drill‑down analysis, a refreshed login page, detailed release notes with new features and bug fixes, and outlines future plans to support more database types, while providing links to documentation, demos, and previous versions.

Bug FixesDatabase AuditingRelease Notes
0 likes · 4 min read
SQLE 1.2206.0-pre2 Release Notes: New Features, Full Release Information, and Future Plans
FunTester
FunTester
Jun 27, 2022 · Backend Development

Implementing a Custom MySQL Connection Pool with Apache Commons Pool2 in Java

The article explains how to build a reusable MySQL connection pool in Java using Apache Commons Pool2, detailing the creation of a poolable object, a factory class, the pool configuration, and a simple API wrapper for borrowing and returning connections while providing full source code examples.

Connection Poolcommons-pool2java
0 likes · 7 min read
Implementing a Custom MySQL Connection Pool with Apache Commons Pool2 in Java
dbaplus Community
dbaplus Community
Jun 26, 2022 · Information Security

How to Build a Lightweight MySQL/MariaDB Audit Log Platform with LogAnalyzer

This guide explains how to set up a lightweight database audit log platform using MariaDB's audit plugin, Rsyslog, and the LogAnalyzer web interface, covering environment preparation, plugin installation, syslog forwarding, MySQL schema creation, and LogAnalyzer configuration for real‑time monitoring and compliance reporting.

Database SecurityLogAnalyzerMariaDB
0 likes · 11 min read
How to Build a Lightweight MySQL/MariaDB Audit Log Platform with LogAnalyzer
IT Services Circle
IT Services Circle
Jun 25, 2022 · Databases

Understanding Optimistic and Pessimistic Locks in MySQL

Optimistic and pessimistic locks are conceptual concurrency control strategies used across databases and caching systems; the article explains their principles, MySQL implementations with SELECT ... FOR UPDATE and version checks, compares their trade‑offs, and advises preferring optimistic locking in high‑concurrency scenarios.

Concurrency Controlmysqloptimistic lock
0 likes · 6 min read
Understanding Optimistic and Pessimistic Locks in MySQL
Architecture & Thinking
Architecture & Thinking
Jun 25, 2022 · Databases

Why and How to Split Databases: Vertical & Horizontal Sharding Explained

This article explains the reasons for database sharding, compares vertical (scale‑up) and horizontal (scale‑out) partitioning methods, illustrates vertical database/table splits and horizontal partition strategies such as HASH, RANGE, LIST, KEY and composite, and discusses the challenges like distributed transactions, cross‑database joins and increased management overhead.

PartitioningVertical Partitioningdatabase sharding
0 likes · 14 min read
Why and How to Split Databases: Vertical & Horizontal Sharding Explained
ITPUB
ITPUB
Jun 24, 2022 · Databases

How to Build MySQL Master‑Master HA with Keepalived: A Step‑by‑Step Guide

This article walks through the complete process of creating a highly available MySQL master‑master cluster using Docker containers, MySQL replication, and Keepalived for automatic health checks, virtual IP failover, and seamless traffic switching, while also sharing common pitfalls and their solutions.

Database operationsDockerLinux
0 likes · 24 min read
How to Build MySQL Master‑Master HA with Keepalived: A Step‑by‑Step Guide
php Courses
php Courses
Jun 24, 2022 · Databases

Comprehensive List of MySQL Interview Questions

This article compiles 66 MySQL interview questions covering fundamentals such as joins, data types, indexes, storage engines, transaction properties, locking mechanisms, replication, sharding, performance tuning, and high‑availability concepts for interview preparation.

Replicationdatabaseindexing
0 likes · 3 min read
Comprehensive List of MySQL Interview Questions
dbaplus Community
dbaplus Community
Jun 23, 2022 · Databases

How to Detect and Resolve Blocked DDL Operations in MySQL

Learn how to identify when a MySQL DDL statement is blocked, use SHOW PROCESSLIST and the sys.schema_table_lock_waits view to pinpoint the blocking session, and apply targeted KILL commands or transaction queries to safely unblock operations in both MySQL 5.7 and 8.0.

DDLkillmetadata lock
0 likes · 9 min read
How to Detect and Resolve Blocked DDL Operations in MySQL
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 23, 2022 · Information Security

SSL Overview and DBLE SSL Configuration Guide

This article introduces the SSL protocol, explains why encryption is needed for database communication, and provides step‑by‑step instructions for generating self‑signed certificates with OpenSSL, converting them for Java, and configuring both DBLE server and client to use SSL, including experimental verification of encrypted versus unencrypted connections.

DBLEInformation SecurityOpenSSL
0 likes · 15 min read
SSL Overview and DBLE SSL Configuration Guide
Zhuanzhuan Tech
Zhuanzhuan Tech
Jun 23, 2022 · Databases

Resolving Connection Invalidations in MySQL Read/Write Splitting with Druid and ReplicationConnection

This article analyzes why MySQL connections become invalid when using Druid with ReplicationConnection for read/write splitting, explains the underlying proxy mechanics, and presents a practical solution by extending MySqlValidConnectionChecker to correctly validate both master and slave connections.

Connection ValidationDruidReplicationConnection
0 likes · 10 min read
Resolving Connection Invalidations in MySQL Read/Write Splitting with Druid and ReplicationConnection
Laravel Tech Community
Laravel Tech Community
Jun 22, 2022 · Databases

Understanding MySQL SELECT ... FOR UPDATE: When It Locks Rows vs. Tables

This article explains how a plain SELECT does not lock data, while SELECT … FOR UPDATE adds a pessimistic lock whose scope—row or table—depends on whether the query uses an indexed or primary‑key column, and demonstrates the behavior with SQL examples and transaction scenarios.

Database TransactionsSELECT FOR UPDATElocking
0 likes · 4 min read
Understanding MySQL SELECT ... FOR UPDATE: When It Locks Rows vs. Tables
Top Architect
Top Architect
Jun 22, 2022 · Databases

Understanding SELECT ... FOR UPDATE Locking in MySQL: Row vs. Table Locks

This article explains how the SELECT ... FOR UPDATE statement in MySQL adds a pessimistic lock, describes when it results in a row lock versus a table lock based on index usage, provides verification SQL, example scenarios, and practical tips for transaction handling.

SELECT FOR UPDATEdatabaselocking
0 likes · 5 min read
Understanding SELECT ... FOR UPDATE Locking in MySQL: Row vs. Table Locks
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 22, 2022 · Databases

Diagnosing Excessive data_free Values in MySQL and Managing ibtmp1 Temporary Tablespace Bloat

This article walks through a real‑world case where a MySQL instance showed an abnormal 13 GB data_free value, explains how the ibtmp1 temporary tablespace caused the growth, demonstrates verification steps with sysbench and SQL queries, and provides configuration and operational solutions to prevent and resolve the issue.

data_freeibtmp1mysql
0 likes · 9 min read
Diagnosing Excessive data_free Values in MySQL and Managing ibtmp1 Temporary Tablespace Bloat
Programmer DD
Programmer DD
Jun 22, 2022 · Databases

Why Did My Spring @Transactional Rollback Fail? Uncovering a MySQL Connector Bug

An in‑depth investigation reveals that Spring’s @Transactional rollback can silently fail due to a bug in mysql‑connector‑java 8.0.28 when useLocalSessionState=true, causing auto‑commit to stay enabled; the article explains the root cause, debugging steps, and how upgrading to 8.0.29 resolves the issue.

Connector/Jautocommitmysql
0 likes · 14 min read
Why Did My Spring @Transactional Rollback Fail? Uncovering a MySQL Connector Bug
IT Services Circle
IT Services Circle
Jun 21, 2022 · Databases

MySQL High‑Availability Incident Review and Resolution in a Dual‑Master Setup with Keepalived

This article recounts a MySQL high‑availability incident in a dual‑master environment, explains how missing binary‑log index files caused replication failures, and details step‑by‑step troubleshooting, directory recreation, binlog position correction, and configuration improvements to restore reliable database operation.

Replicationdatabaseshigh availability
0 likes · 8 min read
MySQL High‑Availability Incident Review and Resolution in a Dual‑Master Setup with Keepalived
Ops Development Stories
Ops Development Stories
Jun 21, 2022 · Databases

Inside MySQL UPDATE: How Undo, Redo, and Binlog Work

This article explains in detail what happens inside MySQL when an UPDATE statement is executed, covering the execution flow, the roles of undo log, redo log, and binlog, buffer pool mechanics, two‑phase commit, group commit, and practical optimization tips.

Binlogbuffer poolmysql
0 likes · 40 min read
Inside MySQL UPDATE: How Undo, Redo, and Binlog Work
Open Source Linux
Open Source Linux
Jun 20, 2022 · Databases

Understanding Buffer vs Cache and MySQL’s Flush Strategies

This article explains the fundamental differences between buffers and caches, details MySQL’s multi‑layer buffer architecture, and examines how write‑through, write‑back, and Direct I/O affect data durability and performance through various InnoDB flush parameters.

CacheDatabase PerformanceFlush
0 likes · 12 min read
Understanding Buffer vs Cache and MySQL’s Flush Strategies
dbaplus Community
dbaplus Community
Jun 19, 2022 · Databases

Master MySQL Upgrades with pt‑upgrade: Detect Compatibility Issues Seamlessly

This article explains why MySQL upgrades can cause syntax, semantic and performance incompatibilities, introduces the Percona Toolkit's pt‑upgrade tool, details its detection logic and common usage patterns, provides a full demo with real query results, and lists essential parameters and precautions for safely migrating from MySQL 5.7 to 8.0.

compatibility testingmysqlpercona-toolkit
0 likes · 17 min read
Master MySQL Upgrades with pt‑upgrade: Detect Compatibility Issues Seamlessly
Selected Java Interview Questions
Selected Java Interview Questions
Jun 18, 2022 · Backend Development

A Quick Guide for Developers to Install and Use Docker: From Hello World to MySQL and Custom Images

This tutorial walks developers through installing Docker on Ubuntu, explains core concepts such as images, containers and repositories, and demonstrates practical tasks including pulling a hello‑world image, running nginx and MySQL containers, configuring network bridges, handling common errors, and building a custom Spring Boot image with a Dockerfile.

DockerfileNginxmysql
0 likes · 14 min read
A Quick Guide for Developers to Install and Use Docker: From Hello World to MySQL and Custom Images
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 17, 2022 · Databases

SQLE 1.2206.0-pre1 Release Notes and New Feature Overview

The preview release SQLE 1.2206.0-pre1 introduces TiDB audit plugin support, TiDB‑specific rules, enhanced SQL syntax checks, and recommendations on primary‑key usage, while providing full release details, documentation links, and references to prior versions for database administrators and developers.

Database AuditingRelease NotesSQLE
0 likes · 6 min read
SQLE 1.2206.0-pre1 Release Notes and New Feature Overview
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 17, 2022 · Databases

MySQL Database High‑Availability Architecture Exploration – Highlights from the 2022 Gdevops Global Agile Operations Summit

The 2022 Gdevops Global Agile Operations Summit in Guangzhou featured a technical session by Zheng Zengquan of Aikesheng on MySQL high‑availability architecture, covering data consistency, production‑ready solutions, and a comparison of MGR versus traditional master‑slave setups, with the full PPT available for download.

Database ArchitectureGdevops SummitPPT
0 likes · 3 min read
MySQL Database High‑Availability Architecture Exploration – Highlights from the 2022 Gdevops Global Agile Operations Summit
Su San Talks Tech
Su San Talks Tech
Jun 16, 2022 · Databases

Master MySQL Index Design: Essential Principles for Optimal Performance

This article explains MySQL index fundamentals, covering clustered and secondary indexes, best‑practice design rules such as using auto‑increment primary keys, avoiding large or low‑cardinality columns, respecting the left‑most prefix, and common scenarios that cause index loss, with practical SQL examples.

index designmysqlsql
0 likes · 12 min read
Master MySQL Index Design: Essential Principles for Optimal Performance
Sanyou's Java Diary
Sanyou's Java Diary
Jun 15, 2022 · Databases

How to Build MySQL Master‑Master HA with Keepalived and Docker

This tutorial walks through setting up a highly available MySQL master‑master cluster using Docker containers, configuring MySQL replication, and employing Keepalived for automatic failover and virtual IP management, complete with step‑by‑step commands, configuration files, and troubleshooting tips.

DockerLinuxMaster-Master Replication
0 likes · 23 min read
How to Build MySQL Master‑Master HA with Keepalived and Docker
Laravel Tech Community
Laravel Tech Community
Jun 14, 2022 · Operations

LNMP One-Click Installation Package V1.9 Release Notes and Usage Guide

The LNMP one‑click installation package V1.9 adds support for Rocky Linux, AlmaLinux, CentOS Stream 9 and several Chinese Linux distributions, introduces PHP 8.1, new PHP extensions, MySQL 5.7/8.0 binaries, IPv6 and ZeroSSL options, and provides detailed management commands for deploying and maintaining Nginx, MySQL, PHP and related services.

LNMPLinuxOperations
0 likes · 7 min read
LNMP One-Click Installation Package V1.9 Release Notes and Usage Guide
Wukong Talks Architecture
Wukong Talks Architecture
Jun 14, 2022 · Databases

MySQL High‑Availability Incident Review and Recovery Steps

The article recounts a production‑like MySQL dual‑master HA setup using Keepalived, describes how a missing binary‑log index caused replication failure, and details step‑by‑step troubleshooting commands, configuration fixes, and preventive measures to restore reliable database synchronization.

databaseshigh-availabilitykeepalived
0 likes · 9 min read
MySQL High‑Availability Incident Review and Recovery Steps
ITPUB
ITPUB
Jun 14, 2022 · Databases

12 Common MySQL Slow‑Query Causes and How to Fix Them

This article enumerates twelve typical reasons why MySQL queries become slow—such as missing or ineffective indexes, deep pagination, massive tables, excessive joins, large IN lists, dirty pages, file‑based ORDER BY, and hardware limits—and provides concrete SQL examples and step‑by‑step optimization techniques to resolve each issue.

Database PerformanceIndex Optimizationmysql
0 likes · 33 min read
12 Common MySQL Slow‑Query Causes and How to Fix Them
Top Architect
Top Architect
Jun 14, 2022 · Databases

Understanding MySQL Execution Process: Connectors, Cache, Parser, Optimizer, and Executor

This article explains MySQL's internal execution process—including the connector, cache, parser, optimizer, and executor—describes privilege tables and verification, discusses query caching removal, outlines logical SQL execution order, and provides practical tips for ordering WHERE conditions to improve performance.

Database InternalsQuery Optimizerdatabase
0 likes · 12 min read
Understanding MySQL Execution Process: Connectors, Cache, Parser, Optimizer, and Executor
php Courses
php Courses
Jun 14, 2022 · Operations

Permission Configuration for Nginx, PHP‑FPM, and MySQL on Linux Servers

This article explains how to properly set file and process permissions for Nginx, PHP‑FPM, and MySQL on Linux servers, recommending the use of a dedicated www user group, showing configuration snippets, and illustrating how to verify running processes to enhance security and avoid permission‑related errors.

LinuxNginxPermissions
0 likes · 5 min read
Permission Configuration for Nginx, PHP‑FPM, and MySQL on Linux Servers
Top Architect
Top Architect
Jun 13, 2022 · Databases

Comprehensive MySQL Performance Optimization Guide

This article provides a detailed guide on optimizing MySQL performance, covering SQL statement tuning, index usage, table design normalization, system configuration, hardware choices, and the internal architecture of MySQL to help developers write faster and more efficient queries.

databaseindexingmysql
0 likes · 19 min read
Comprehensive MySQL Performance Optimization Guide
dbaplus Community
dbaplus Community
Jun 12, 2022 · Databases

12 Common MySQL Slow‑Query Triggers and Practical Fixes

This article enumerates twelve typical reasons why MySQL queries become slow—ranging from missing or ineffective indexes, type conversions, OR/LIKE patterns, composite‑index misuse, built‑in functions, large tables, deep pagination, join overload, oversized IN clauses, dirty‑page flushing, order‑by filesort, lock contention, delete‑in pitfalls, and group‑by temp tables—and provides concrete SQL examples, execution‑plan explanations, and optimization techniques for each case.

Database PerformanceSQL Tuningmysql
0 likes · 28 min read
12 Common MySQL Slow‑Query Triggers and Practical Fixes
ITPUB
ITPUB
Jun 12, 2022 · Databases

10 Proven Ways to Prevent Dangerous SQL Mistakes in Production

This article presents ten practical techniques—ranging from avoiding chat‑tool SQL transmission to using logical deletes, backups, and temporary tables—to help developers and DBAs safely execute and modify data in production environments while minimizing accidental data loss.

Data Safetybest practicesmysql
0 likes · 14 min read
10 Proven Ways to Prevent Dangerous SQL Mistakes in Production
ITPUB
ITPUB
Jun 11, 2022 · Databases

How Meituan Optimized a Non‑Intrusive MySQL Packet Capture for Full‑SQL Auditing

This article explains Meituan's non‑intrusive, Go‑based packet‑capture solution for full‑SQL database auditing, details the performance challenges encountered at high QPS, and describes a series of systematic analyses and optimizations—including probe redesign, CPU profiling, data‑masking, scheduling, GC pressure reduction, and packet filtering—that dramatically lowered loss rates and CPU usage.

Database AuditingGoPacket Capture
0 likes · 12 min read
How Meituan Optimized a Non‑Intrusive MySQL Packet Capture for Full‑SQL Auditing
Big Data Technology Architecture
Big Data Technology Architecture
Jun 9, 2022 · Databases

Building a Real‑Time Data Warehouse with Apache Doris: Architecture, Benefits, and Lessons Learned

This article details how a fast‑growing supply‑chain platform migrated from MySQL and Hive to Apache Doris for real‑time analytics, describing the architectural evolution, the advantages of the new design, practical implementation steps, encountered challenges, and the performance and cost benefits achieved.

Apache DorisData IntegrationFlink CDC
0 likes · 12 min read
Building a Real‑Time Data Warehouse with Apache Doris: Architecture, Benefits, and Lessons Learned
Aikesheng Open Source Community
Aikesheng Open Source Community
Jun 7, 2022 · Databases

Implementing MySQL Auditing with init_connect, MariaDB Audit Plugin, and McAfee Audit Plugin

This article explains three practical methods for adding audit capabilities to MySQL 5.7—using the init_connect parameter to log user connections, installing the MariaDB server_audit plugin for detailed event logging, and deploying the McAfee audit plugin—while providing step‑by‑step commands, configuration examples, code snippets, and a comparison of advantages and drawbacks.

AuditingMariaDBMcAfee
0 likes · 27 min read
Implementing MySQL Auditing with init_connect, MariaDB Audit Plugin, and McAfee Audit Plugin
政采云技术
政采云技术
Jun 7, 2022 · Databases

Understanding MySQL Auto‑Increment Locks and Their Behaviors

This article explains the concept of MySQL auto‑increment locks, describes different insertion methods and lock modes, analyzes how InnoDB allocates and manages auto‑increment IDs—including simple, bulk, and mixed inserts—and provides source‑code insights, practical experiments, and mitigation strategies for ID continuity issues.

InnoDBauto_incrementdatabase
0 likes · 21 min read
Understanding MySQL Auto‑Increment Locks and Their Behaviors
Su San Talks Tech
Su San Talks Tech
Jun 6, 2022 · Databases

Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them

This article explores the various reasons MySQL queries become slow—including index misuse, insufficient connection pools, small InnoDB buffer pools, and query cache limitations—while providing practical solutions such as optimizing indexes, adjusting max_connections, enlarging buffer pools, and configuring connection pools for better performance.

Connection PoolIndex OptimizationInnoDB
0 likes · 16 min read
Why Is MySQL Query Slow? Hidden Factors Beyond Indexes and How to Fix Them
Ops Development Stories
Ops Development Stories
Jun 5, 2022 · Databases

What Really Happens Inside MySQL When You Run a SELECT Query?

This article walks through the complete lifecycle of a MySQL SELECT statement, from establishing the TCP connection and authentication, through query cache, parsing, optimization, and execution, detailing how the server and storage engine collaborate and explaining concepts like index usage, connection handling, and memory management.

Database ArchitectureIndex OptimizationQuery Execution
0 likes · 20 min read
What Really Happens Inside MySQL When You Run a SELECT Query?
Top Architect
Top Architect
Jun 5, 2022 · Databases

Why Store IPv4 Addresses as UNSIGNED INT in MySQL: Benefits, Drawbacks, and Java Conversion

The article explains that using a 32‑bit UNSIGNED INT to store IPv4 addresses in MySQL saves space and improves indexing and range queries, outlines the conversion functions and their performance, discusses readability drawbacks, and provides Java code for bidirectional conversion between string and integer representations.

IP addressUNSIGNED INTdata storage
0 likes · 6 min read
Why Store IPv4 Addresses as UNSIGNED INT in MySQL: Benefits, Drawbacks, and Java Conversion
Liangxu Linux
Liangxu Linux
Jun 5, 2022 · Databases

MySQL IPv4 Storage: Unsigned INT vs String – Benefits, Drawbacks & Java Example

The article explains why MySQL’s high‑performance guide recommends storing IPv4 addresses as a 32‑bit UNSIGNED INT instead of VARCHAR, detailing space savings, faster range queries, conversion functions (INET_ATON/INET_NTOA), associated drawbacks, and provides Java code to convert between string and integer representations.

IPv4Unsigned Integerdata storage
0 likes · 5 min read
MySQL IPv4 Storage: Unsigned INT vs String – Benefits, Drawbacks & Java Example
ITPUB
ITPUB
Jun 4, 2022 · Databases

Understanding MySQL Architecture: From Storage Engines to InnoDB Internals

This article explains MySQL’s core architecture, detailing components such as connection pools, SQL interface, optimizer, and storage engine plugins, then compares MyISAM and InnoDB, and dives deep into InnoDB’s memory structures, buffer pool, adaptive hash index, tablespaces, and redo log mechanisms.

Database ArchitectureInnoDBStorage Engine
0 likes · 18 min read
Understanding MySQL Architecture: From Storage Engines to InnoDB Internals