Ubuntu 24.04 Server Optimization for MySQL & MariaDB InnoDB Buffer Pool Tuning

Author: Mir Alamin (Principal Web Architect) | Published: July 15, 2026 at 09:15 AM | Reading Time: 23 min read | Category: Performance | Tags: Ubuntu Server Tune, MariaDB, Performance, Database, Linux

Executive Summary

Default MySQL and MariaDB package configurations installed on Ubuntu 24.04 allocate a conservative 128MB to the innodb_buffer_pool_size. For database instances holding millions of records, this results in constant disk I/O reads as query engines swap tables from NVMe storage into memory.

This database optimization guide covers tuning InnoDB buffer pools, log file sizing, thread concurrency, and Linux kernel memory page allocation to achieve sub-millisecond query performance.

1. Sizing `innodb_buffer_pool_size` for Production Databases

The InnoDB buffer pool caches table data, indexes, and write buffers in RAM.

Rule of Thumb:

Dedicated Database Server: Allocate 70% to 80% of total system RAM to innodb_buffer_pool_size.
Shared LEMP Server (Web + DB on same host): Allocate 40% to 50% of total system RAM.

# /etc/mysql/mariadb.conf.d/50-server.cnf (or /etc/mysql/mysql.conf.d/mysqld.cnf)

[mysqld]
# Allocate 8GB RAM on a 16GB shared server
innodb_buffer_pool_size = 8G

# Split buffer pool into 8 instances (1GB per instance) for multi-threaded concurrency
innodb_buffer_pool_instances = 8

# Redo log file sizing for fast write throughput
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# Flush log at transaction commit (Set to 2 for high write performance)
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

2. Tuning Database Thread & Connection Limits

# Maximum concurrent client connections
max_connections = 500

# Thread cache to avoid constant thread creation overhead
thread_cache_size = 64

# Table open cache
table_open_cache = 4096
table_definition_cache = 2048

# Temp Table Memory Limits before spilling to disk
tmp_table_size = 128M
max_heap_table_size = 128M

3. Disabling Linux Swap Aggressiveness (`vm.swappiness`)

Prevent Linux from swapping database RAM pages to disk:

# Reduce swappiness from default 60 down to 10
echo "vm.swappiness = 10" | sudo tee -a /etc/sysctl.d/99-db-performance.conf
sudo sysctl -p /etc/sysctl.d/99-db-performance.conf

4. Performance Optimization Benchmarks

Query performance on a 2,500,000-row InnoDB table:

For database query tuning and managed Linux administration:

⚙️ Managed Server Administration Plans
🚀 Website Speed Optimization Services

5. Frequently Asked Questions (FAQ)

Q1: Why is `innodb_flush_method = O_DIRECT` recommended?

O_DIRECT bypasses the Linux operating system page cache, preventing double-buffering of database data in both OS RAM and InnoDB RAM.

Q2: What happens if `innodb_buffer_pool_size` is set too high?

If memory allocation exceeds available physical RAM, the server exhausts memory, causing swapping or triggering the Linux OOM Killer to terminate MariaDB.

Ubuntu 24.04 Server Optimization for MySQL & MariaDB InnoDB Buffer Pool Tuning

Mir Alamin