Why MySQL Performance Benchmark Shows Low TPS on High-End Hardware

Your boss asks you to run a MySQL performance benchmark, but despite a high‑end machine (64 cores, 256 GB RAM, RAID‑10 15K disks) the TPS remains low. Common suspects like a small InnoDB buffer pool, tiny redo log, insufficient pre‑warming, or too few sysbench threads are obvious; this article looks at less obvious causes.

Network Bottleneck

One benchmark run produced the following result:

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308 \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果：
SQL statistics:
    queries performed:
        read:                            4682958
        write:                           1337988
        other:                           668994
        total:                           6689940
    transactions:                        334497 (2783.82 per sec.)
    queries:                             6689940 (55676.32 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

The TPS is only about 2,800. Observing system load shows CPU usage at just 36 % while the network interface transfers roughly 124 MB/s, essentially saturating a 1 Gbps NIC.

Checking the network interfaces with:

ifconfig -a|grep -B 1 inet

reveals two NICs: the test was using em3 (1 Gbps) and another interface vlan2 (10 Gbps). Verifying the speed:

ethtool {dev_name} |grep Speed

After switching the benchmark to the 10 Gbps vlan2 interface (changing --mysql-host ), TPS jumps to around 10 k. If a 10 Gbps NIC is unavailable, using the loopback interface locally also avoids the bottleneck.

SSL Overhead

MySQL 8.0 or 5.7 Enterprise editions enable SSL by default, which can cap TPS around 3,700. Example run:

sysbench oltp_read_write --mysql-host=10.18x.xx.104 --mysql-port=3308 \
--mysql-user=sysbench --mysql-password=sysbench --mysql-db=sbtest --tables=10 \
--table-size=10000000 --report-interval=5 --threads=200 --time=600 run

##结果：
SQL statistics:
    queries performed:
        read:                            6303388
        write:                           1800968
        other:                           900484
        total:                           9004840
    transactions:                        450242 (3747.71 per sec.)
    queries:                             9004840 (74954.25 per sec.)
Latency (ms):
    min:                               6.35
    avg:                              53.31
    max:                             542.71
    95th percentile:                 104.84
    sum:                         24004566.42

CPU usage climbs to 80 % with unusually high system time, and 95 % latency exceeds 100 ms because each connection must perform encryption. The fix is to disable SSL in my.cnf (add skip-ssl and restart) or set the sysbench option --mysql-ssl=off . Verifying the status can be done with:

select * from status_by_thread where VARIABLE_NAME like '%ssl%';

sort_buffer_size Impact

The MySQL sort_buffer_size variable can dramatically affect sysbench performance. The documentation warns about thresholds at 256 KB and 2 MB, but the author's tests show a sharp drop at 32 MB.

On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values.

Test results:

## sort_buffer_size=31M
SQL statistics:
    queries performed:
        read:        16358846
        write:        4673956
        other:        2336978
        total:        23369780
    transactions:  1168489 (11678.75 per sec.)
    queries:      23369780 (233574.94 per sec.)

## sort_buffer_size=32M
SQL statistics:
    queries performed:
        read:        392182
        write:        112052
        other:        56026
        total:        560260
    transactions:   28013 (930.07 per sec.)
    queries:        560260 (18601.38 per sec.)

When sort_buffer_size crosses the 32 MB threshold, MySQL switches its internal memory allocation strategy, causing slower allocation and a surge in CPU system time.

Memory allocation in glibc uses two mechanisms:

brk() for small blocks (<128 KB). The heap expands, and freed memory stays cached, reducing page faults but potentially causing fragmentation under heavy load.

mmap() for large blocks (>128 KB). Memory is returned to the kernel on free, avoiding OOM but incurring page faults each time the block is re‑allocated, increasing kernel‑mode CPU overhead.

Thus, a larger sort_buffer_size can trigger the mmap path, leading to the observed performance degradation.

Summary

When benchmark results are disappointing, first examine CPU utilization: low total usage or high iowait/system time signals abnormal conditions. Adjusting my.cnf according to a well‑tested template resolves many issues, and combining systematic analysis with experience enables you to achieve the expected TPS.