This article walks through the principles of database connection pools, common causes of pool exhaustion such as leaks, mis‑configuration, slow SQL and DB limits, and provides a step‑by‑step methodology with monitoring, thread‑stack analysis, and concrete configuration and SQL optimizations to restore stability.
The article explains that performance tuning should start with pinpointing bottlenecks using response time, throughput, concurrency and resource utilization metrics, then choose appropriate measures—caching, async processing, database tuning, horizontal scaling, and rate‑limiting—rather than blindly adding a cache.
This article examines ten typical situations where MySQL indexes become ineffective, explains the underlying reasons such as function usage, implicit type conversion, wildcard LIKE patterns, OR conditions, left‑most prefix violations, small tables, stale statistics, NULL handling, GROUP BY/DISTINCT, and ORDER BY, and provides concrete SQL examples and step‑by‑step remediation techniques to restore optimal query performance.
The article walks through Oracle response‑time analysis, compares service and wait time, presents two real‑world cases (Statspack pre‑10g and AWR post‑10g) that show I/O waits often contribute less than 6% of total time, and then details step‑by‑step methods, parameters, and storage techniques for diagnosing and reducing I/O‑related performance problems.
This article systematically examines why MySQL indexes can become ineffective—covering function calls, implicit casts, LIKE patterns, OR conditions, composite‑index left‑most rules, data distribution, and ORDER/GROUP mismatches—while providing concrete code examples, production case studies, and actionable optimization techniques.
A real‑world incident of soaring QPS reveals six common MySQL indexing pitfalls—type mismatches, function usage, left‑most prefix violations, implicit charset conversion, range query side effects, and optimizer mis‑selection—and provides concrete SQL fixes and verification tools to keep queries fast and reliable.
This guide walks through the full MySQL performance optimization workflow, covering slow‑query logging, pt‑query‑digest analysis, EXPLAIN interpretation, index design (including covering indexes and index‑condition pushdown), InnoDB buffer‑pool tuning, connection‑pool settings, real‑time diagnostics, monitoring metrics, and best‑practice recommendations for large‑scale production databases.
The paper presents CMA+DB, a hierarchical multi‑agent framework that automatically tunes database parameters across diverse workloads by combining classification‑based collaboration, layered training, and joint action selection, achieving superior performance, faster convergence, and strong generalization compared with existing tuning methods.
This guide walks you through real‑world MySQL slow‑query problems, showing how to enable logging, analyze execution plans, design single‑ and composite indexes, use partitioning, rewrite queries, and set up monitoring scripts, achieving performance jumps from 800 ms to just 8 ms.
This guide presents a comprehensive, step‑by‑step roadmap for accelerating slow SQL queries without altering the original statements, covering index creation, database parameter tuning, table partitioning, caching layers, read‑write splitting, sharding, statistics updates, hardware choices, and middleware routing.
This article explains how MySQL indexes work, enumerates eight common reasons they become ineffective, adds six advanced pitfalls, and provides concrete diagnostic steps and production‑grade optimization techniques to keep queries fast and reliable.
This guide presents 30 practical MySQL index optimization techniques, covering creation principles, usage tips, performance enhancements, and maintenance strategies, with concrete SQL examples for each rule to help developers improve query efficiency and reduce write overhead.
By systematically applying connection pooling, batch processing, WAL tuning, table partitioning, query refactoring, and OS-level tweaks, we transformed a PostgreSQL‑Node.js stack to sustain 50,000 transactions per second with sub‑100 ms latency, while maintaining data consistency on modest hardware.
MySQL 8.0 (especially versions 8.0.20+) suffers a performance regression in table‑rebuilding operations like ALTER TABLE … FORCE, running up to three times slower than 5.7 due to the default innodb_doublewrite_pages setting, but the issue can be mitigated by adjusting the parameter or upgrading to 8.4+.
This guide walks you through identifying MySQL bottlenecks, using EXPLAIN, optimizing indexes, rewriting queries, tuning configuration, applying architectural patterns, and learning from real‑world cases to dramatically speed up your database.
Learn how MySQL’s optimizer decides between indexes, why the same query can suddenly use a different index with dramatically slower performance, and practical steps—including analyzing execution plans, updating statistics, checking index coverage, and rebuilding fragmented indexes—to diagnose and resolve index selection issues.
This article presents a comprehensive set of MySQL optimization techniques, covering the avoidance of SELECT *, proper use of specific fields, index-friendly query patterns, join strategies, handling of NULL and default values, efficient use of UNION, batch inserts, and numerous other performance‑enhancing tips for database developers.
This article explains how to troubleshoot and resolve the MySQL "Too many connections" error by using GDB to modify the max_connections parameter on a running MySQL 5.7 instance without restarting, including step‑by‑step commands, sysbench load testing, and two practical methods.
This article explains why Java performance matters, outlines fundamental principles, and provides concrete code, memory, concurrency, I/O, and database optimization techniques, along with profiling tools, common pitfalls, real‑world case studies, and emerging trends to help developers build faster, more stable applications.
This article walks through a MySQL performance case study, showing how a slow sub‑query can be accelerated by adding single‑column and composite indexes, rewriting the query with EXISTS or JOIN, and applying best‑practice indexing strategies such as covering indexes and left‑most prefix rules.
This article explains why MySQL subqueries can hurt performance, outlines common pitfalls such as temporary tables, index loss, and optimizer complexity, and presents ten concrete examples that replace subqueries with IN, EXISTS, JOIN, indexes, temporary tables, window functions, and LIMIT to achieve faster, more maintainable queries.
Facing tight schedules and diverse coding habits, many Chinese projects struggle with API latency; this guide walks through requirement analysis, acceptance standards, common pitfalls, and a comprehensive set of optimization tactics—from configuration and code tweaks to caching, async processing, and observability tools—to dramatically improve API performance.
This comprehensive guide explains how to identify and resolve slow web page performance by addressing hardware resources, network configuration, and both front‑end and back‑end optimizations such as server upgrades, CDN usage, HTTP/2, image compression, code minification, caching, and efficient database queries.
This article provides a comprehensive guide to optimizing Kunpeng‑based servers, covering hardware characteristics, matrix multiplication benchmarks, Von Neumann architecture insights, soft and hard acceleration, compiler and JDK tweaks, NUMA tuning, Nginx and OpenSSL acceleration, disk and network optimizations, application‑level tuning, and a step‑by‑step MariaDB performance‑tuning checklist.
A client complained about a 35‑second MySQL query that ignored indexes; by analyzing column selectivity and rewriting the condition to use the auto‑increment primary key instead of a secondary index, the execution time dropped to 2.55 seconds, illustrating why primary‑key access is often faster.
This article details how a large‑scale lottery service was architected and tuned for extreme traffic spikes by applying server‑side rate limiting, application‑level throttling, behavior‑based filtering, caching strategies, database optimizations, and hardware upgrades, resulting in a ten‑fold performance improvement.
This article walks through generating tens of millions of rows in MySQL, then demonstrates a series of optimizations—including simple and covering indexes, data reduction, small‑table‑driven joins, and forced indexes—showing how each technique impacts query performance and EXPLAIN output.
This guide covers practical MySQL performance improvements, detailing SQL-level optimizations such as limit pagination, proper LIKE usage, UNION ALL, EXISTS vs IN, TRUNCATE, batch inserts, early filtering, function placement, data type minimization, CHAR/VARCHAR choices, indexing strategies, force index, as well as server parameter tuning for buffer pool and redo log settings.
This article reviews MySQL's execution process and presents a comprehensive list of practical SQL optimization strategies—including indexing, query rewriting, avoiding costly operators, using batch operations, and leveraging EXPLAIN—to improve database performance and maintainability.
GPTuner, an LLM‑driven system for PostgreSQL knob tuning developed by researchers at Sichuan University, demonstrates that knowledge processing, parameter selection, search‑range optimization, and a two‑stage Bayesian framework each significantly improve performance, while costing roughly 880 000 GPT‑4 tokens (≈ $30) with reusable knowledge.
This guide presents ten practical MySQL performance optimization methods—including index creation, query rewriting, limiting result sets, avoiding SELECT *, reducing joins, using proper data types, caching with Redis, and server configuration tweaks—each illustrated with concrete SQL code examples to help you speed up your database operations.
This article explains how to tune MySQL's InnoDB settings—including buffer pool size, log file configuration, I/O threads, flush behavior, and timeout values—by providing detailed explanations and exact configuration snippets to improve performance and resource usage.
This announcement introduces Huawei Cloud’s DTT live session on August 29, where expert Lv Pengbo will explain GaussDB(DWS) resource control principles and demonstrate practical techniques—such as CPU usage analysis, memory tuning, and queue issue resolution—to help developers efficiently manage data‑warehouse resources and boost performance.
This comprehensive guide explains 20 practical MySQL index optimization rules—including when NOT to use indexes, how to choose the right index type, and techniques such as prefix indexes, covering indexes, and query rewriting—to dramatically improve SQL query efficiency and reduce database load.
This article walks through nine common MySQL performance pitfalls—such as inefficient LIMIT usage, implicit type conversion, sub‑query updates, mixed sorting, EXISTS clauses, condition push‑down, early range reduction, intermediate result push‑down, and more—showing how to rewrite each query and its execution plan to achieve dramatic speed improvements.
This article presents practical MySQL query optimization techniques—including pagination, index‑based join improvements, subquery replacement with joins, order‑by and group‑by indexing, and count() efficiency—illustrated with real‑world examples, execution‑plan analysis, and ready‑to‑run SQL code.
Facing a surge in MySQL slow queries, Ctrip’s senior database engineers detail a comprehensive approach—including refined SQL release processes, deep execution‑plan analysis, index tuning, query rewriting, and resource management—to systematically identify, diagnose, and eliminate performance bottlenecks in large‑scale hotel services.
This article walks through a MySQL scenario where a nested sub‑query runs for hours, demonstrates how adding single‑column and multi‑column indexes, rewriting the query as a join, and analyzing execution plans can reduce execution time from thousands of seconds to a few milliseconds.
This article explains the purpose and mechanics of PostgreSQL's VACUUM and AUTOVACUUM processes, demonstrates practical examples of table bloat, transaction ID handling, and provides step‑by‑step guidance for precise triggering and performance optimization of vacuum operations.
This article examines why MySQL benchmark results often show low transactions per second on seemingly powerful servers, exploring hidden factors such as network bottlenecks, SSL overhead, and inappropriate sort_buffer_size settings, and provides practical troubleshooting steps and configuration tweaks to achieve optimal performance.
This article outlines a comprehensive process for analyzing, diagnosing, and optimizing performance issues in production business systems, covering hardware, database, middleware, JVM tuning, code-level problems, testing limitations, scaling considerations, and the role of APM monitoring.
The article outlines a comprehensive process for diagnosing and optimizing performance problems in production business systems, covering causes such as high concurrency, data growth, hardware constraints, and detailing analysis of hardware, OS, database, middleware, JVM settings, code inefficiencies, and the role of monitoring and APM tools.
This article outlines a comprehensive process for analyzing, diagnosing, and optimizing performance issues in production business systems, covering hardware, OS, database, middleware, JVM tuning, and APM monitoring to identify root causes and implement effective solutions.
This article outlines a comprehensive approach to diagnosing and optimizing performance problems in production business systems, covering analysis processes, hardware, OS, database, middleware, JVM tuning, code inefficiencies, and monitoring techniques to identify root causes and improve system reliability.
This article documents a step‑by‑step performance optimization of a high‑traffic web module, covering requirement analysis, bottleneck identification, caching, message‑queue offloading, Linux TCP tuning, and the resulting capacity increase to 50,000 QPS with sub‑70 ms latency.
This article explores TiDB performance optimization by examining layered approaches—hardware, software, and SQL—and scenario‑specific tactics such as read/write hotspot handling, OLAP challenges, and distributed transaction tuning, offering practical insights and real‑world case studies from industry experts.
This guide details practical server‑side and client‑side techniques for improving HBase read and write throughput, covering rowkey design, BlockCache configuration, HFile management, compaction tuning, scan cache sizing, bulkload usage, WAL policies, and SSD storage options.
This article explains how MySQL, when run by a non‑root user with limited system resources, automatically adjusts the open_files_limit, max_connections, and table_open_cache parameters by calculating required file descriptors, comparing them to system limits, and correcting the configuration values accordingly.
This article examines a slow MySQL query that scans millions of rows, explains why the optimizer chose a low‑selectivity index, demonstrates how forcing the correct index dramatically improves performance, and shows how to create a covering composite index to resolve the issue permanently.
This article outlines a comprehensive process for diagnosing and optimizing performance problems in production business systems, covering hardware, OS, database, middleware, JVM tuning, code inefficiencies, monitoring tools, and the limitations of pre‑release testing, with practical guidelines and visual references.
This article outlines a step‑by‑step approach for identifying root causes of performance bottlenecks in production business systems, covering common scenarios such as high concurrency, data growth, hardware limits, database and middleware tuning, code inefficiencies, and the role of monitoring and APM tools.
This article walks through a MySQL 5.6 case study where a nested sub‑query for finding students scoring 100 in a specific subject runs for hours, then demonstrates how adding single‑column and composite indexes, rewriting the query as a join, and analyzing execution plans can shrink the runtime from tens of thousands of seconds to under a second.
This article provides a detailed, practical guide to optimizing HBase in production, covering table pre‑splitting, RowKey design, JVM memory and GC settings, MSLAB and BucketCache configuration, read‑side client and server tuning, write‑side strategies, and additional tips such as compression and scan caching.
This article presents a comprehensive collection of MySQL performance‑tuning techniques—including proper use of EXPLAIN, limiting IN lists, selecting explicit columns, using LIMIT 1, avoiding costly ORDER BY RAND(), choosing between IN and EXISTS, preferring UNION ALL, applying full‑text indexes, and optimizing joins, pagination, and index usage—to help developers write faster, more efficient queries.
This article experimentally demonstrates how MySQL's table cache works, showing that it reduces file‑system reads of table definition files, operates per thread, and explains the recommended table_open_cache sizing formula based on concurrency and join complexity.
This article provides an in‑depth overview of Java application performance optimization, covering a four‑layer model (application, database, framework, JVM), on‑site and post‑mortem analysis methods, OS and JVM diagnostic tools, common code and GC issues, database deadlock handling, and practical tuning recommendations.
This article presents a thorough overview of Java application performance optimization, covering a four‑layer model (application, database, framework, JVM), practical OS and Java diagnostic tools, JVM and GC tuning, application‑level code improvements, and database deadlock mitigation strategies.
Learn how to dramatically improve Redis performance by shortening key-value sizes, enabling lazy free, setting expirations, disabling costly commands, using slowlog, leveraging pipeline, preventing mass expirations, optimizing client connections, limiting memory, running on physical servers, configuring persistence, disabling THP, and adopting distributed architectures.
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.
This article introduces Oracle's SQLT tool, explains its seven diagnostic methods, demonstrates how to generate and analyze diagnostic files, shows direct and replace plan binding with SQL PROFILE, and details using the XPLORE feature to pinpoint parameter or bug issues affecting SQL performance.
The article outlines five key layers—front‑end optimization, middle‑layer load distribution, gateway control, service‑layer improvements, and database tuning—detailing practical techniques such as static resource caching, Nginx load‑balancing methods, request rate limiting, message‑queue buffering, and extensive MySQL best‑practice guidelines to build robust high‑concurrency architectures.
This article details how a high‑traffic MongoDB sharded cluster exceeding one million TPS was optimized through software‑level tweaks, configuration changes, WiredTiger storage‑engine tuning, and hardware upgrades, resulting in latency reductions from hundreds of milliseconds to a few milliseconds and significantly improved stability.
This article shares practical experiences on diagnosing PHP interface performance bottlenecks using ThinkPHP's G function and profiling tools like Xhprof, then presents concrete code‑level, database‑level, and hardware‑level optimization techniques to improve execution speed and reduce resource consumption.
The report summarizes SIGMOD/PODS 2019 in Amsterdam, covering a blockchain-focused keynote, the CDBTune paper on end‑to‑end automatic cloud database tuning using deep reinforcement learning, modern hardware research, award‑winning systems, and related industry activities, providing a comprehensive academic overview.
This article walks through a real‑world MySQL scenario, showing how a slow sub‑query can be accelerated by adding single‑column indexes, converting the query to a join, and finally creating a composite index, with performance numbers and execution‑plan analysis to illustrate each optimization step.
This article presents thirty practical recommendations for deploying, configuring, and maintaining Pika—a high‑capacity, RocksDB‑backed Redis‑compatible storage system—covering version selection, thread settings, hardware choices, key design, memory management, replication, backup, compaction, security, and monitoring to achieve reliable and high‑performance operation.
This article compiles 52 practical SQL performance optimization strategies—including indexing best practices, query rewriting, use of temporary tables, storage engine selection, data type choices, backup procedures, and diagnostic tools—to help developers write faster, more efficient database queries.
This guide presents four practical MySQL performance‑boosting techniques—selecting optimal column types, replacing sub‑queries with JOINs, using UNION instead of temporary tables, and employing transactions with BEGIN/COMMIT/ROLLBACK—to make queries faster and ensure data integrity.
This article shares Meituan's practical performance‑optimization techniques—including code analysis, database tuning, caching strategies, asynchronous processing, JVM adjustments, multithreading, and monitoring—illustrated with real case studies that reduced job runtimes from over 40 minutes to under 15 minutes.
This guide explains why MySQL performance drops when a single table grows huge and provides a comprehensive set of optimization steps—including field type choices, index design, query tuning, engine selection, system parameters, hardware scaling, read/write splitting, caching, partitioning, vertical and horizontal sharding, and architecture recommendations—to keep MySQL fast and reliable at scale.
This guide outlines a complete performance optimization strategy covering web front‑end tuning (reducing HTTP requests, compressing assets, avoiding redirects), database optimization (SQL, architecture, connection pools), caching solutions, asynchronous processing, and JVM tuning with monitoring tools to improve overall system efficiency.
This article explains Oracle cursor concepts, the key V$SQL and V$SQLAREA views with their most important columns for tuning, how open‑cursor limits and the CURSOR_SHARING parameter affect execution plan sharing, and provides practical guidance for DBA performance optimization.
This article presents a comprehensive set of MySQL optimization techniques—including query‑cache tuning, proper use of EXPLAIN, indexing strategies, avoiding costly functions, and leveraging prepared statements—to help developers eliminate database bottlenecks and boost web‑application performance.
This article outlines the most frequent SQL performance mistakes—such as misusing UPDATE, over‑selecting columns, unnecessary double queries, and improper use of GUIDs—and provides concrete, actionable techniques like CASE expressions, temporary tables, batch operations, and table‑valued functions to dramatically improve query speed and concurrency.
By switching to the InnoDB storage engine, allocating about 80% of system memory to its buffer pool, and configuring multiple buffer pool instances, you can significantly improve MySQL performance, with practical commands and guidelines for various memory sizes.
This guide explains MySQL's configuration fundamentals, how MySQL reads options, variable scopes, dynamic settings, common pitfalls, recommended habits, and provides a minimal yet comprehensive example my.cnf with detailed explanations for each section.
This guide details practical MySQL performance enhancements across hardware BIOS settings, disk I/O choices, filesystem and kernel tuning, and MySQL configuration—including version selection, key innodb parameters, schema design rules, and operational best‑practices—to achieve substantial IOPS and query speed gains.
This article outlines practical strategies for optimizing the LNMP stack—including load balancing, server tuning, caching, and database improvements—to enhance performance, scalability, and reliability of web applications across various programming languages.
The article recounts a performance bottleneck in a Windows service that processed only about 12 records per second, analyzes why a simple SELECT TOP 1 query without proper indexes caused 89 ms latency, and demonstrates step‑by‑step how adding appropriate clustered and non‑clustered indexes reduced the execution time to virtually zero, while also discussing broader indexing strategies and common LEFT JOIN pitfalls.
This guide presents comprehensive Oracle database performance optimization techniques—including data structure design, application architecture, SQL tuning, memory and CPU adjustments, I/O balancing, and essential tools—providing actionable steps and example queries for DBAs to improve system responsiveness and efficiency.
This guide explains how to calculate and interpret key Oracle hit ratios—including library cache, shared pool, buffer cache, data buffer, sort memory, and PGA—provides exact SQL statements for each metric, and offers tuning thresholds and actions to improve database performance.
This article presents a comprehensive collection of practical SQL performance tuning guidelines, covering index usage, query rewriting, avoiding costly operators, proper data types, efficient joins, and view creation to help developers write faster, more resource‑efficient database queries.
The article explains the internal steps Oracle follows to acquire buffers, identifies the conditions that trigger buffer busy waits such as hot blocks and freelist limits, and provides practical tuning methods and diagnostic SQL queries to mitigate these performance issues.
A front‑end login slowdown was traced to a MySQL UPDATE that ran for 40 seconds because a varchar column’s charset conversion forced a full‑table scan, and the article walks through the logs, tests, and the fix of unifying the column charset to UTF‑8.
This guide compares Percona Server, MariaDB, and MySQL, outlines practical MySQL performance tuning steps across hardware, storage, filesystem, and server parameters, and presents several high‑availability architectures such as master‑slave replication, MMM/MHA, Heartbeat/SAN, and Heartbeat/DRBD with real‑world deployment examples.
This article examines how enabling MySQL 5.6 performance_schema with extremely high max_connections causes excessive virtual memory usage, swap pressure, and eventual out‑of‑memory errors, presents test data, references the related bug, and offers practical recommendations and discussion questions.
This article examines Oracle's guidance on placing redo logs on SSDs, explains sector size and 4K redo log support, describes a controlled experiment comparing PCIe flash cards with SAS disks using TPCC workloads, and presents performance data showing reduced log‑file‑sync waits and higher throughput on flash storage.
This guide explains the essential MySQL cache parameters—including key_buffer_size, table_cache, sort_buffer_size, read_buffer_size, join_buffer_size, thread_cache_size, query_cache_size, and tmp_table_size—detailing their impact on performance and providing recommended values based on server memory to help DBAs optimize MyISAM and InnoDB workloads.
Expert DBAs discuss critical Oracle 11.2.0.4 parameters to watch before production, covering generic, performance, deprecated, and OS‑level settings for AIX and Linux, with concrete examples such as shared pool sub‑pools, mutex tuning, cursor thresholds, and real‑world incidents like streams_pool_size misconfiguration.
This comprehensive guide explores MySQL's unique characteristics, hardware and OS tuning, configuration tweaks, schema and SQL optimization techniques, and provides actionable recommendations and tools to identify and resolve common performance bottlenecks.
This guide explains the most important MySQL configuration variables—max_connections, max_user_connections, net_buffer_length, max_allowed_packet, and back_log—detailing how each affects concurrency, network efficiency, and overall database performance, with practical sizing recommendations.
This article compiles expert insights from a high‑efficiency operations forum, covering MySQL performance optimization techniques such as read‑write separation, sharding, schema and parameter tuning, hardware and SSD enhancements, as well as a detailed FAQ addressing replication lag, Aurora challenges, and backup tools.
This guide explains how the thread_cache_size setting in MySQL caches idle threads, how to monitor thread creation with Thread_created, and why increasing the cache size can reduce resource consumption and improve response times.
