OS Optimization for MySQL Performance

Optimizing the operating system (OS) for MySQL performance involves tuning various system parameters and configurations to ensure that MySQL can efficiently utilize the system's resources. Here’s a comprehensive guide to OS optimization for MySQL

Key Kernel Network Parameters to Optimize :

Optimizing the OS kernel network settings for MySQL performance involves tuning various parameters to reduce latency, handle high connection loads efficiently, and ensure reliable network communication. Here’s a detailed guide to optimizing kernel network settings for MySQL

1. Increase Maximum Backlog

The net.core.somaxconn parameter defines the maximum number of connections that can be queued for acceptance.

sysctl -w net.core.somaxconn=1024
echo "net.core.somaxconn = 1024" >> /etc/sysctl.conf        

2. Increase TCP Maximum SYN Backlog

The net.ipv4.tcp_max_syn_backlog parameter sets the maximum number of queued SYN requests.

sysctl -w net.ipv4.tcp_max_syn_backlog=4096
echo "net.ipv4.tcp_max_syn_backlog = 4096" >> /etc/sysctl.conf        

3. Reuse TCP Connections in TIME_WAIT State

The net.ipv4.tcp_tw_reuse parameter allows reusing sockets in TIME_WAIT state for new connections.

sysctl -w net.ipv4.tcp_tw_reuse=1
echo "net.ipv4.tcp_tw_reuse = 1" >> /etc/sysctl.conf        

4. Reduce TCP FIN Timeout

The net.ipv4.tcp_fin_timeout parameter controls the time the socket remains in FIN-WAIT-2 state before being closed.

sysctl -w net.ipv4.tcp_fin_timeout=30
echo "net.ipv4.tcp_fin_timeout = 30" >> /etc/sysctl.conf        

5. Increase the Number of File Descriptors

Ensure that the MySQL process can open a sufficient number of file descriptors.

ulimit -n 65536
echo "mysql soft nofile 65536" >> /etc/security/limits.conf
echo "mysql hard nofile 65536" >> /etc/security/limits.conf        

6. Increase TCP Keepalive Time

The net.ipv4.tcp_keepalive_time parameter controls the frequency of TCP keepalive messages.

sysctl -w net.ipv4.tcp_keepalive_time=600
echo "net.ipv4.tcp_keepalive_time = 600" >> /etc/sysctl.conf        

7. Increase Memory for Network Buffers

Adjust the network buffer sizes to handle higher loads.

sysctl -w net.core.rmem_max=16777216
sysctl -w net.core.wmem_max=16777216
sysctl -w net.ipv4.tcp_rmem="4096 87380 16777216"
sysctl -w net.ipv4.tcp_wmem="4096 65536 16777216"
echo "net.core.rmem_max = 16777216" >> /etc/sysctl.conf
echo "net.core.wmem_max = 16777216" >> /etc/sysctl.conf
echo "net.ipv4.tcp_rmem = 4096 87380 16777216" >> /etc/sysctl.conf
echo "net.ipv4.tcp_wmem = 4096 65536 16777216" >> /etc/sysctl.conf        

8. Enable TCP BBR (Bottleneck Bandwidth and Round-trip propagation time)

TCP BBR is a congestion control algorithm that can improve throughput and reduce latency.

echo "net.core.default_qdisc=fq" >> /etc/sysctl.conf
echo "net.ipv4.tcp_congestion_control=bbr" >> /etc/sysctl.conf        

Example Configuration in /etc/sysctl.conf

# Increase maximum backlog
net.core.somaxconn = 1024

# Increase TCP SYN backlog
net.ipv4.tcp_max_syn_backlog = 4096

# Reuse sockets in TIME_WAIT state
net.ipv4.tcp_tw_reuse = 1

# Reduce TCP FIN timeout
net.ipv4.tcp_fin_timeout = 30

# Increase file descriptors
fs.file-max = 100000

# Increase memory for network buffers
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_rmem = 4096 87380 16777216
net.ipv4.tcp_wmem = 4096 65536 16777216

# Increase TCP keepalive time
net.ipv4.tcp_keepalive_time = 600

# Enable TCP BBR
net.core.default_qdisc = fq
net.ipv4.tcp_congestion_control = bbr        

Applying the Changes

After updating /etc/sysctl.conf, apply the changes using:

sysctl -p        

Key Kernel Memory Parameters to Optimize

1. Adjust Swappiness

The vm.swappiness parameter controls the tendency of the kernel to swap out idle processes from RAM to swap space. For a MySQL server, it's beneficial to m mize swapping.

sysctl -w vm.swappiness=1
echo "vm.swappiness = 1" >> /etc/sysctl.conf        

2. Increase File Descriptors

MySQL might need a large number of file descriptors, especially under high load. Increase the limit for open files.

ulimit -n 65536
echo "mysql soft nofile 65536" >> /etc/security/limits.conf
echo "mysql hard nofile 65536" >> /etc/security/limits.conf        

3. Adjust Overcommit Memory Settings

The vm.overcommit_memory parameter controls the kernel’s behavior when allocating memory.

• 0: Heuristic overcommit handling.
• 1: Always overcommit.
• 2: Never overcommit.

For MySQL, setting this to 1 is often recommended to prevent out-of-memory issues.

sysctl -w vm.overcommit_memory=1
echo "vm.overcommit_memory = 1" >> /etc/sysctl.conf        

4. Set Overcommit Ratio

The vm.overcommit_ratio parameter defines the percentage of physical RAM considered when vm.overcommit_memory is set to 2.

sysctl -w vm.overcommit_ratio=80
echo "vm.overcommit_ratio = 80" >> /etc/sysctl.conf        

5. Disable Transparent Huge Pages (THP)

THP can cause performance issues with MySQL. It's often recommended to disable it.

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

echo "echo never > /sys/kernel/mm/transparent_hugepage/enabled" >> /etc/rc.local
echo "echo never > /sys/kernel/mm/transparent_hugepage/defrag" >> /etc/rc.local        

6. Increase Shared Memory and Semaphore Limits

Adjust the shared memory and semaphore settings for MySQL to handle large datasets and concurrent connections effectively.

sysctl -w kernel.shmmax=68719476736
sysctl -w kernel.shmall=4294967296
echo "kernel.shmmax = 68719476736" >> /etc/sysctl.conf
echo "kernel.shmall = 4294967296" >> /etc/sysctl.conf
sysctl -w kernel.sem="250 32000 100 128"
echo "kernel.sem = 250 32000 100 128" >> /etc/sysctl.conf        

Example Configuration in /etc/sysctl.conf

# Adjust swappiness
vm.swappiness = 1

# Adjust overcommit memory settings
vm.overcommit_memory = 1
vm.overcommit_ratio = 80

# Increase shared memory limits
kernel.shmmax = 68719476736
kernel.shmall = 4294967296

# Increase semaphore limits
kernel.sem = 250 32000 100 128        

Applying the Changes

After updating /etc/sysctl.conf, apply the changes using:

sysctl -p        

File System Optimization

1. Choose the Right File System

For MySQL, use file systems that handle large files and databases efficiently, such as ext4 or XFS. Both provide good performance and stability.

2. Mount Options

Use appropriate mount options to enhance performance:

• noatime and nodiratime: Prevents the file system from updating access times, reducing unnecessary I/O operations.
• data=writeback: Speeds up write operations by writing only metadata changes to the journal.?

mount -o noatime,nodiratime,data=writeback /dev/sdX /mount/point        

To make it persistent, add to /etc/fstab:

/dev/sdX /mount/point ext4 noatime,nodiratime,data=writeback 0 1        

3. File System Tuning

For ext4, you can tune additional parameters:

• Increase commit interval: Reduce the frequency of journal commits.

tune2fs -o journal_data_writeback /dev/sdX
tune2fs -O dir_index /dev/sdX
tune2fs -o commit=60 /dev/sdX        

Disk I/O Optimization

1. Use a High-Performance Storage Solution

SSD (Solid-State Drives) are preferred over traditional HDDs (Hard Disk Drives) due to their faster read/write speeds and lower latency.

2. I/O Scheduler

Select the appropriate I/O scheduler for your storage type:

• For SSDs, noop or deadline are generally recommended.

echo noop > /sys/block/sdX/queue/scheduler        

To make it persistent, add to /etc/rc.local:

echo "echo noop > /sys/block/sdX/queue/scheduler" >> /etc/rc.local?