How Innodb_thread_concurrency Caused Database Abnormalities

Problem Phenomenon

Developers reported anomalies in a test application system. Despite verifying application logs, CPU/memory/disk IO metrics, and network connectivity, the root cause remained elusive. Further investigation confirmed the database was the bottleneck.

​Key Configuration

Preliminary Analysis

The issue manifested as 28 connections stuck in the executing state, yet CPU usage remained low (~5%). Error logs showed no obvious abnormalities.

​Key Observations​

1. ​Resource Utilization:
   • CPU and IO metrics were within normal ranges, contradicting the high number of "stuck" connections.
   • Simple queries (e.g., SELECT 1) also exhibited delays.
2. ​Thread State Analysis:
   • Affected threads were blocked in the nanosleep function, indicating forced waiting.
   • Source code analysis revealed threads entering a sleep loop while waiting for InnoDB concurrency tickets.

Root Cause Analysis

Source Code Insight

The innodb_thread_concurrency parameter governs thread entry into InnoDB:

1. ​Ticket-Based Concurrency Control:
   • Threads acquire "tickets" (trx->n_tickets_to_enter_innodb) to enter InnoDB.
   • When tickets are exhausted, threads enter a sleep-wait cycle (triggering nanosleep).
2. ​Flowchart of Blocking Logic:

If innodb_thread_concurrency > 0:
    If thread has tickets:
        Decrement ticket count and proceed.
    Else:
        Sleep until tickets become available OR timeout.

‍

1. • Scenario 4​ (exhausted tickets) matched the observed behavior:
     Threads were stuck in nanosleep while waiting for InnoDB concurrency slots.
2. ​Impact of Slow Queries:
   • Long-running queries depleted available tickets, forcing even simple queries into a sleep-retry loop.
   • This created a cascading effect, worsening latency and resource contention.

Solution Steps

1. ​Disable Concurrency Throttling:
Set innodb_thread_concurrency=0 to remove thread limits:

SET GLOBAL innodb_thread_concurrency = 0;
FLUSH PRIVILEGES;

‍

2. ​Optimize Problematic Queries:

1. Identify and rewrite slow SQL statements causing ticket exhaustion.
2. Use tools like EXPLAIN to analyze execution plans.

3. ​Adjust Concurrency Parameters:

1. If concurrency control is necessary, start with innodb_thread_concurrency=128 and gradually reduce it while monitoring performance.

Best Practices for innodb_thread_concurrency

1. ​Default Setting:
   • Leave innodb_thread_concurrency=0 (unlimited concurrency) for most workloads.
   • Enable it only under extreme load conditions.
2. ​Dynamic Adjustment:
   • Use SET GLOBAL to modify the parameter without restarting MySQL.
   • Monitor SHOW ENGINE INNODB STATUS for metrics like Queries inside InnoDB.
3. ​Avoid Pitfalls:
   • Do not combine innodb_thread_concurrency with innodb_adaptive_max_sleep_delay unless thoroughly tested.
   • Ensure sufficient innodb_concurrency_tickets to prevent premature throttling.

Common Misconceptions

• ​Myth: Lowering innodb_thread_concurrency always improves performance.
  ​Reality: Excessive limits can degrade performance by increasing context-switching overhead.
• ​Myth: innodb_thread_concurrency affects all query types equally.
  ​Reality: Long-running transactions disproportionately consume tickets, blocking shorter queries.

Conclusion

This case illustrates how misconfigured innodb_thread_concurrency can masquerade as hardware limitations or application issues. By understanding InnoDB’s concurrency control mechanisms and aligning parameters with workload characteristics, teams can avoid subtle bottlenecks and maintain stable performance.

‍