MariaDB Sort Buffer Size & Eliminating Disk-Based Temporary Sort Merge Passes in Pakistan

In complex, query-heavy relational database applications across Pakistan—such as e-commerce product catalogs with dynamic price/review filtering, banking transaction history statements, ERP reporting dashboards, and CRM pipelines—users frequently execute SQL queries featuring ORDER BY, GROUP BY, and DISTINCT clauses.

When MariaDB executes a query that cannot resolve ordering using an existing B-tree index, it invokes the Filesort algorithm. Filesort allocates a session-level memory chunk known as the Sort Buffer (sort_buffer_size) to order the resulting row keys.

However, if sort_buffer_size is too small to contain the dataset being sorted, MariaDB cannot complete the operation in RAM. Instead, it segments the sorted rows into multiple temporary chunks, writes them to disk in /tmp, and performs a multi-pass merge sort (Sort_merge_passes). On a busy production server, disk-based sort merge passes generate severe disk I/O thrashing, spiking query execution times from 15ms to over 2,500ms!

Conversely, naively setting sort_buffer_size = 64M globally causes explosive memory consumption: because this buffer is allocated per thread for every active sorting query, 500 concurrent connections can instantly trigger Linux kernel Out-Of-Memory (OOM) crashes!

By deploying on high-memory bare-metal Dedicated Servers and mathematically tuning sort_buffer_size, read_rnd_buffer_size, and max_length_for_sort_data, database administrators eliminate on-disk sort merges while maintaining rock-solid memory stability.

---

In-Memory Filesort vs. Multi-Pass Disk Sort Merge Architecture

The diagram below illustrates how insufficient sort buffer sizing triggers expensive disk spills compared to tuned in-memory execution:

+-----------------------------------------------------------------------------------+
|               IN-MEMORY FILESORT vs. DISK-BASED SORT MERGE PASSES                 |
+-----------------------------------------------------------------------------------+
| Query: SELECT id, name, price FROM products WHERE category = 12 ORDER BY price;   |
| Dataset to sort: 8,000 matching rows (Total sort payload: ~2.4MB)                 |
|                                                                                   |
| 1. Default Configuration (`sort_buffer_size = 256K`):                             |
|    - 2.4MB dataset exceeds 256KB buffer ceiling!                                  |
|    - MariaDB divides sort into 10 separate chunks of 256KB each.                  |
|    - Writes all 10 chunks to temporary files on disk (/tmp/#sql_xxx).             |
|    - Performs multi-stage merge sort reading and rewriting temporary files!       |
|    - Global status increments: `Sort_merge_passes += 10`.                         |
|    - Result: Heavy disk I/O contention; query execution latency: ~1,850ms!        |
|                                                                                   |
| 2. Tuned Architecture (`sort_buffer_size = 4M`):                                  |
|    - Entire 2.4MB payload fits comfortably within single in-memory buffer.        |
|    - MariaDB executes quicksort purely in RAM in under 4 milliseconds!            |
|    - Zero temporary files written to disk; `Sort_merge_passes = 0`!               |
|    - Result: Blazing fast e-commerce catalog page rendering in under 15ms!        |
+-----------------------------------------------------------------------------------+

---

Step 1: Auditing Sort Merge Passes in the Running MariaDB Instance

Check how frequently your database is spilling sort operations to disk:

SHOW GLOBAL STATUS LIKE 'Sort_%';

Sample output from an un-tuned production database:

+-------------------+----------+
| Variable_name     | Value    |
+-------------------+----------+
| Sort_merge_passes | 148290   |
| Sort_range        | 34210    |
| Sort_rows         | 48192038 |
| Sort_scan         | 89410    |
+-------------------+----------+

Notice Sort_merge_passes = 148290: Every single merge pass represents an instance where MariaDB had to create temporary files on disk to complete an ORDER BY operation. If Sort_merge_passes is climbing continuously, your database is suffering from sort buffer starvation.

---

Step 2: Calculating Safe, High-Performance Session Sizing

Because sort_buffer_size is allocated per sorting thread (not globally shared like the InnoDB Buffer Pool), calculate your safe upper bound: $$\text{Max Sorting RAM} = \text{max_connections} \times (\text{sort_buffer_size} + \text{read_rnd_buffer_size})$$

For an enterprise bare-metal server with 64GB of RAM and max_connections = 500:

• Allocating 2MB to 4MB per sort buffer requires at most 1GB to 2GB of aggregate RAM under peak sorting load—completely safe and within system headroom.

---

Step 3: Configuring Optimized Sort Directives in my.cnf

Edit /etc/my.cnf.d/server.cnf (or /etc/mysql/mariadb.conf.d/50-server.cnf):

[mysqld]
# 1. Expand per-thread sort buffer to keep typical queries in RAM
# 2MB to 4MB accommodates 99% of e-commerce catalog and reporting sorts
sort_buffer_size = 4M

# 2. Tune random read buffer (used after sorting to fetch matching rows in order)
read_rnd_buffer_size = 2M

# 3. Sequential read buffer for full table scans
read_buffer_size = 1M

# 4. Join buffer size for queries without indexes on joined tables
join_buffer_size = 2M

# 5. Tune max_length_for_sort_data (Filesort algorithm threshold)
# Determines whether MariaDB uses "Modified" filesort (sorts full rows) 
# or "Original" filesort (sorts row IDs and re-fetches from tablespace)
max_length_for_sort_data = 2048

# 6. Mount MySQL temporary tables on high-speed RAM disk (tmpfs) or NVMe
tmpdir = /var/tmp/mysql_tmp

Mount /var/tmp/mysql_tmp as a tmpfs RAM disk in /etc/fstab to ensure that any unavoidable massive analytical sorts execute at memory speed:

tmpfs /var/tmp/mysql_tmp tmpfs rw,noexec,nosuid,size=8G,uid=mysql,gid=mysql 0 0

Apply runtime parameters immediately without restarting MariaDB:

SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;
SET GLOBAL read_rnd_buffer_size = 2 * 1024 * 1024;
SET GLOBAL max_length_for_sort_data = 2048;

---

Step 4: Measuring Sort Acceleration with EXPLAIN and Status Profiling

Verify that sorting occurs in-memory by analyzing a heavy ORDER BY query:

-- Enable query profiling
SET profiling = 1;

SELECT id, title, price, stock 
FROM catalog_products 
WHERE category_id = 45 
ORDER BY price DESC 
LIMIT 50;

SHOW PROFILE FOR QUERY 1;

Inspect execution status:

• In an un-tuned environment: converting HEAP to on-disk, filesort, and multiple disk I/O waits consume 85% of total query duration.
• In the tuned environment: filesort executes instantaneously in RAM, reducing query elapsed time from 1,450ms down to 12ms!

Check that Sort_merge_passes stops climbing:

FLUSH STATUS;
-- Run production workload for 1 hour
SHOW STATUS LIKE 'Sort_merge_passes';
-- Result should remain at 0 or near-zero!

---

Uncompromising Database Scalability on Dedicated Pakistani Infrastructure

Operating large-scale relational databases with multi-megabyte per-thread sort buffers, expansive InnoDB caches, and thousands of concurrent client connections requires unshared, enterprise physical hardware. Multi-tenant cloud VPS instances share host memory buses, causing memory allocation throttling that triggers unexpected database crashes during peak flash sales.

Deploying on bare-metal Dedicated Servers in Pakistan equips your MariaDB environment with pure DDR5 ECC memory channels, dedicated AMD EPYC / Intel Xeon multi-core processors, PCIe Gen5 NVMe arrays, and low-latency domestic fiber connectivity at PKIX.