MariaDB 10.2并行查询性能异常求助：单查与并行查耗时差异分析

Hey Rene, let's break down why you're seeing this noticeable slowdown when running concurrent queries on your 4-core MariaDB 10.2 instance. Your observations line up perfectly with how database engines and operating systems handle parallel workloads, even with optimized queries that avoid disk temp tables.

Here's what's likely happening behind the scenes:

1. CPU Context Switch Overhead #

• When you run more queries than your available CPU cores (7 or 10 on a 4-core server), the OS has to constantly pause one query's execution, save its state, and load another query's state to keep all work progressing. This is called context switching.
• Each switch adds extra CPU overhead that doesn't directly contribute to query execution. As a result, each individual query gets less effective CPU time, leading to longer runtime.
• The 80-90% CPU load you're seeing includes this context switching overhead—your actual query execution is using a portion of that, with the rest going to managing the switches.

2. Internal Database Resource Contention #

Even without disk IO bottlenecks, MariaDB has shared internal resources that become bottlenecks under concurrency:

• InnoDB Buffer Pool Locks/Latches: If your queries frequently access data pages in the buffer pool, concurrent queries will compete for access locks (like page latches). Waiting for these locks adds to each query's total time.
• In-Memory Operation Competition: Even if you're not using disk temp tables, in-memory sorts or joins can compete for memory resources. Multiple queries trying to use the sort_buffer or join_buffer at the same time can lead to waits or slower execution.
• MariaDB 10.2 Scheduling Limitations: Older versions like 10.2 have less optimized query schedulers compared to newer releases. When handling more concurrent queries than cores, the scheduler's efficiency drops, leading to more idle wait time for queries.

3. Memory Bandwidth Saturation #

4-core CPUs typically share a single memory bus. When multiple queries are simultaneously reading from the buffer pool or performing in-memory operations, the memory bus can hit its bandwidth limit.

• Even if CPU usage isn't at 100%, slow data transfer from memory to CPU cache will drag down query speed. You can verify this with tools like sar -B or vmstat to check memory bandwidth utilization.

Here are steps you can take to confirm these bottlenecks and mitigate the slowdown:

• Cap Concurrent Queries at Core Count: Your 4 concurrent queries only added 0.5s of overhead—this is ideal because there's minimal context switching. If your workload allows, limit parallel queries to match your CPU core count (4) to keep execution efficient.
• Monitor Context Switches: Run vmstat during single vs. 10-concurrent query runs. Look at the cs column—if it spikes significantly during high concurrency, context switching is a major culprit.
• Check InnoDB Wait Events: Enable MariaDB's Performance Schema and monitor metrics like innodb_buffer_pool_wait_free and innodb_row_lock_waits. A jump in these values indicates buffer pool or lock contention.
• Upgrade MariaDB: If possible, move to 10.5 or later. Newer versions include better concurrency controls, improved query scheduling, and InnoDB optimizations that handle high parallelism more gracefully.
• Tune Memory Allocation: Adjust settings like sort_buffer_size (ensure it's large enough for your queries but not so big it causes memory pressure) and innodb_buffer_pool_size (make sure frequently accessed data fits in memory to reduce memory bus load).

内容的提问来源于stack exchange，提问作者Rene