check performance of memory allocation of big blocks under windows

Under windows allocation more than 16KB could be expensive.

Benchmark stop growing memory blocks more than 16KB in MEM_ROOT.

There is an alternative system allocator on Windows, called Segment heap, which in my tests always beat the default "low fragmentation" heap, which also does not have problems with larger 16K blocks. It is relatively easy to enable via application manifest (which we already use for some purposes, e.g enabling utf8 codepage by default).

This should be investigated, and benchmarked, to compare to alternative( to reducing some default MEM_ROOT sizes)

The motivation for this research is a very noticeable collapse of throughput with many clients on performance benchmarks. Profiling suggested

Benchmark setup

3 alternatives were tested

1. main baseline with standard allocator (called in following, possible incorrectly LFH, LFH is actually a heap frontend used for small blocks)
2. main baseline with SegmentHeap allocator
3. patch that changes sql/sql_const.h to use smaller values for preallocated blocks (QUERY_ALLOC_BLOCK_SIZE, QUERY_ALLOC_PREALLOC_SIZE, TMP_TABLE_BLOCK_SIZE, TMP_TABLE_PREALLOC_SIZE,SHOW_ALLOC_BLOCK_SIZE all set to 8K, rather than 16K or 32K, to provide for space overhead for my_malloc allocations)

The test setup was an Alder Lake machine i9-12900K. The machine uses heterogenous cores with different performance characteristics, so the server was CPU affinitized on 8 faster P-cores, and the client to 8 slower E-cores.

Sysbench tests

The sysbench tests ran via runner script, for N threads where N is 1,2,4,8,16,32,64,128,256,512,1024, for OLTP tests oltp_read_only,oltp_read_write,oltp_point_select.

Sysbench invocation

sysbench.exe --db-driver=mysql --mysql-host=. --mysql-user=root --mysql-db=mysql --tables=10 --table-size=100000 --rand-type=uniform <test> --threads=<N> --time=60 --events=0 --percentile=99 --report-interval=1 --db-ps-mode=auto run

for each OLTP test, restart and "sysbench prepare" is done, and 11 tests with increasing client count are run back-to-back.

Note, that host="."

Server parameters

--innodb-buffer-pool-size=10G
--innodb-log-file-size=20G
--innodb-flush-log-at-trx-commit=2
--console --enable-named-pipe
--max-connection=10000
--max-prepared-stmt-count=1000000

Benchmark Results

OLTP point selects

`oltp_point_select` is a useful control workload. Both allocators scale normally. Segment heap is modestly faster, but there is no qualitative difference. Small-alloc patch does not make a big difference either

OLTP readonly

The standard allocator shows a clear scalability collapse at high concurrency: throughput drops sharply between 512 and 1024 threads. Segment heap behaves much more robustly. It reaches the saturations, then degrades gracefully instead of falling off a cliff.
Note, in all tests segment heap is winning in TPS in all cases. small-blocks patch is normally slower than baseline, but helps a little with very high concurrency.

OLTP read-write

The baseline allocator collapses between 256 and 512 threads. Segment heap remains stable and degrades gracefully. The small-allocation workaround does not solve the issue: it collapses even earlier, already at 256 threads.

ETW context-switch analysis

Capture method

Context-switch ETLs were collected with UIForETW while running sysbench oltp_read_only at 512 users

The traces were converted into Brendan Gregg-style flamegraphs using xperf_to_collapsedstacks.py from UIForETW kit.

The flamegraphs show stacks associated with context-switch and ready-thread activity. The sample weights are inclusive: a parent frame includes the weights of its descendants. Percentages therefore overlap and must not be added together.

Flamegraphs

Variant	Flamegraph
Baseline	baseline_context_switches.svg
Small alloc	small-blocks_context_switches.svg
Segment heap	segment_heap_context_switches.svg

Dominant heap-related stacks

Dominant baseline allocation path

A large part of the baseline flamegraph is under the legacy NT heap path:

... -> heap_write

-> hp_get_new_block

-> my_malloc

-> ucrtbase.dll!_malloc_base

-> ntdll.dll!RtlAllocateHeap

-> ntdll.dll!RtlpAllocateNTHeapInternal

-> ntdll.dll!RtlpAllocateHeap

-> ntdll.dll!RtlpHpAllocVirtBlockCommitFirst

-> ntdll.dll!NtAllocateVirtualMemory

-> ntoskrnl.exe!NtAllocateVirtualMemory

-> ntoskrnl.exe!MiAllocateVirtualMemory

A second substantial path is associated with filesort buffer allocation:

... -> filesort

-> Filesort_buffer::alloc_sort_buffer

-> my_malloc

-> ucrtbase.dll!_malloc_base

-> ntdll.dll!RtlAllocateHeap

-> ntdll.dll!RtlpAllocateNTHeapInternal

-> ntdll.dll!RtlpAllocateHeap

The baseline flamegraph also shows considerable critical-section activity below the legacy heap allocator:

ntdll.dll!RtlEnterCriticalSection      28.68%

ntdll.dll!RtlLeaveCriticalSection      21.08%

Small-allocation experiment

Limiting preallocation to blocks smaller than 16 KiB does not much help in this case, since dominant stacks do not use preallocation. The only visible effect of the patch is eliminating init_sql_alloc() call stack, which has 7.41% inclusive weight in the baseline flamegraph.

The dominant legacy heap stacks remain almost unchanged.This indicates that reducing the preallocated block size is not a complete fix. It changes the symptom, but the same legacy NT heap contention and virtual-memory-commit paths remain prominent.

Segment-heap behavior

The segment-heap flamegraph is qualitatively different:

• Legacy NT heap frames such as RtlpAllocateNTHeapInternal, RtlpAllocateHeap, and RtlpHpAllocVirtBlockCommitFirst are no longer visible.
• RtlEnterCriticalSection drops from approximately 29–30% to 0.42%.
• my_malloc drops from approximately 51% to 2.37%.
• hp_get_new_block drops from approximately 27–29% to 0.26%.
• Filesort_buffer::alloc_sort_buffer drops from approximately 13–15% to 0.33%.

Segment-heap-specific allocation frames are visible, but remain small:

Once the allocator bottleneck is removed, a larger relative share of the remaining context-switch activity is associated with normal named-pipe response completion, where write to named pipe wakes up sysbench client thread, and so contributes to context switch activity.

Interpretation

The combined ETW evidence indicates a workload-dependent scalability problem in the legacy Windows heap path:

• The baseline and small-allocation variants spend a large proportion of context-switch flamegraph weight below RtlAllocateHeap.

• Both variants show substantial critical-section activity in the legacy NT heap allocator.

• Both variants also show virtual-memory-commit activity below RtlpHpAllocVirtBlockCommitFirst.

• small-allocation patch does not remove these paths.

• Enabling segment heap almost completely removes the dominant allocator-related stacks.

Additional test on Windows Server 2022

The result was reproduced on a second, substantially older machine:

Windows Server 2022

Intel Xeon E3-1230 V2 @ 3.30 GHz

4 physical cores / 8 logical processors

MariaDB 10.11

For this run:

MariaDB used --thread-handling=one-thread-per-connection.
--innodb-flush-log-at-trx-commit=2 was removed.
The server was pinned to 6 logical processors, corresponding to 3 physical cores.
Sysbench was pinned to the remaining physical core.
Concurrency was tested up to 128 clients.

OLTP read-only

OLTP read-write

HammerDB benchmark (courtesy of Steve Shaw)

Steve ran comparison benchmark on his Windows (24 core Alder Lake), and provided graph with results, showing better results
with segment heap (about 7% TPS increase in peak performance, but improvements are also seen on non-peak concurrency levels)

Summary

The same pattern is visible on the Windows Server 2022 machine with the one-thread-per-connection scheduler: the NT heap reaches its peak at low concurrency and then collapses as the client count increases. Segment heap continues scaling until saturation and then degrades gracefully.