Optimizing MySQL on Linux Server: Connection Limits, File Handles, and InnoDB Tuning

When running MySQL 5.7 on a high-performance server with 64GB of memory, the default configurations are often too conservative. Parameters like max_connections, open_files_limit, and InnoDB memory settings can significantly impact database performance under heavy workloads.

In this guide, we’ll walk through how to:

1. Increase the system-level file descriptor and process limits.
2. Adjust MySQL-specific configurations.
3. Tune InnoDB settings for optimal performance.

1. System limits Configuration

By default, Linux distributions often set relatively low values for file descriptors and process limits, which can bottleneck MySQL under high concurrency.

Update the limits in /etc/security/limits.conf:

1
2
3
4
5
6
7
8
9

* soft     nproc          262144
* hard     nproc          262144
* soft     nofile         524288
* hard     nofile         524288

root soft  nproc          262144
root hard  nproc          262144
root soft  nofile         524288
root hard  nofile         524288

Also, configure systemd limits specifically for MySQL:

Create or edit the override file:

1

sudo systemctl edit mysql

Add the following:

1
2
3

[Service]
LimitNOFILE=524288
LimitNPROC=262144

Reload and restart:

1
2

sudo systemctl daemon-reexec
sudo systemctl restart mysql

This ensures MySQL can actually use the increased file handle and process limits.

2. MySQL Configuration (my.cnf)

Next, tune MySQL parameters in /etc/my.cnf (or /etc/mysql/my.cnf depending on your distribution).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

[mysqld]
# Increase maximum connections
max_connections = 2000

# Increase file handle limit
open_files_limit = 524288

# Optimize table cache
table_open_cache = 20000
table_definition_cache = 20000

# InnoDB Core Configuration (64GB server)
# ---------------------------------------
# Memory Pool
innodb_buffer_pool_size = 40G          # ~75% of physical memory
innodb_buffer_pool_instances = 8       # Reduce contention under high concurrency

# Logs
innodb_log_file_size = 1G              # Redo log size per file
innodb_log_files_in_group = 2          # Total redo log = 2G
innodb_log_buffer_size = 256M          # Log buffer

# Flush Strategy
innodb_flush_log_at_trx_commit = 1     # Safest, durability guaranteed
innodb_flush_method = O_DIRECT         # Avoid double buffering

# Files & Concurrency
innodb_open_files = 40000             # Match system open_files_limit
innodb_thread_concurrency = 0          # Let MySQL auto-manage threads

3. Why These Changes Matter

• max_connections = 2000: Allows more client connections. (Default is only 151, far too small for modern servers.)
• open_files_limit & innodb_open_files = 524288: Prevents “too many open files” errors under heavy workloads.
• table_open_cache & table_definition_cache = 20000: Keeps more tables cached in memory, reducing disk I/O.
• innodb_buffer_pool_size = 40G: Ensures most of your active dataset is cached in memory.
• innodb_log_file_size & innodb_log_buffer_size: Improve write performance and transaction throughput.
• O_DIRECT: Avoids double-buffering between OS cache and InnoDB buffer pool.

4. Example Performance Comparison

Result: Higher concurrency, reduced disk I/O, better transaction throughput, and more efficient use of memory.

5. Configuration Checklist ✅

• Increase system limits (nofile, nproc) to match workload.
• Adjust MySQL open_files_limit and max_connections.
• Optimize InnoDB memory pool (innodb_buffer_pool_size, instances).
• Tune redo logs (innodb_log_file_size, innodb_log_buffer_size).
• Enable O_DIRECT to avoid double buffering.
• Match innodb_open_files with system open_files_limit.

Conclusion

For a 64GB server, tuning MySQL beyond its defaults is not optional—it’s essential. By adjusting both system-level limits and MySQL configurations, you can eliminate connection bottlenecks, prevent file handle exhaustion, and maximize InnoDB performance.

This optimized setup ensures your MySQL 5.7 instance is production-ready for high concurrency and large datasets.

Other:

Cheap VPS Recommendations Page:

• VPS Deals