Performance Analysis of DBLE vs MyCat: Identifying Sorting and Isolation Level Issues

Author Introduction: Lan Yin, leader of the open‑source distributed middleware DBLE project, focuses on database technologies, data replication, read/write separation, and sharding.

Problem Origin: Benchmarking MyCat‑1.6.1 and DBLE‑2.17.07 with benchmarksql_for_mysql showed DBLE achieving only about 70% of MyCat's performance.

Analysis Process: The investigation followed a systematic approach: observing the phenomenon, collecting data, forming hypotheses, and verifying them.

CPU Flame Graph Insight: DBLE consumed over 15% CPU on pure sorting, while MyCat showed no noticeable CPU usage for sorting, indicating a potential sorting bottleneck.

Bug in MyCat Test Case: A bug in MyCat caused an unfair comparison: MyCat sent the original SQL directly to nodes and summed results, whereas DBLE rewrote the query to SELECT DISTINCT s_i_id and performed deduplication in the middleware, inflating DBLE's sorting workload.

Sorting Impact Confirmation: Removing the buggy case did not improve DBLE performance, but eliminating the sorting case made the two systems' performance nearly identical, confirming that sorting was the primary issue.

Hypothesis 1 – Sorting Implementation: DBLE uses a multi‑way merge sort whose theoretical optimal initialization is O(N), but the actual implementation incurs O(N·logK·2). Replacing the cmp function to always return 1 improved performance by 10%, showing that the cmp function contributed to the slowdown, though other factors remained.

Hypothesis 2 – Sorting SQL: B‑SQL contains three sorting SQL statements; two have sorting columns not present in the SELECT list (a potential MyCat bug). Hard‑coded paths prevented these from executing, focusing the issue on the remaining sorting SQL.

Multi‑Concurrency Findings: Under load, DBLE's cmp function was called about five times more than MyCat's, and packet captures revealed DBLE issued sorting SQL roughly ten times more frequently than MyCat.

Root Cause – Delete‑Loop Bug: In B‑SQL, when a DELETE affected zero rows, a dead‑loop could be triggered, repeatedly sending sorting requests and drastically reducing DBLE performance. This issue manifested under REPEATABLE_READ isolation, while MyCat's implementation ignored the isolation level unless explicitly set.

Isolation Level Verification: Adjusting DBLE's configuration to <property name="txIsolation">2</property> (READ_COMMITTED) and aligning MySQL nodes to READ_COMMITTED yielded a performance ratio of 1:0.75, confirming the isolation‑level bug as a key factor.

Conclusion and Lessons: The performance gap stemmed from (1) an inefficient cmp initialization, (2) two bugs (MyCat isolation‑level bug and B‑SQL delete dead‑loop), and (3) the sorting algorithm's behavior under high concurrency. The study highlights the importance of synchronized test environments, proper use of performance analysis tools, repeated verification, and careful attention to contradictory results.

Recommendations: Ensure all MySQL nodes use READ_COMMITTED, set DBLE's txIsolation to 2, and avoid the B‑SQL delete‑zero‑row dead‑loop to achieve expected performance.