Optimizing MySQL 8.0 Performance on Bare Metal Servers裸金属服务器上 MySQL 8.0 性能优化指南

Why MySQL Performs Best on Bare Metal

MySQL 8.0 is one of the most widely deployed relational databases, powering everything from small websites to massive e-commerce platforms. While cloud database services offer convenience, running MySQL on a dedicated server delivers consistent performance without noisy neighbor issues, lower per-query costs, and full control over configuration.

BRHOSTING.COM servers equipped with AMD EPYC 9555P processors and NVMe storage provide the ideal foundation for high-performance MySQL deployments.

Hardware Considerations

CPU Selection

MySQL benefits from high single-thread performance for query execution and high core counts for concurrent connections. The AMD EPYC 9555P with its 64 cores and high IPC (Instructions Per Clock) handles both requirements effectively.

Memory

For database servers, allocate 70-80% of available RAM to MySQL. On a 128 GB server, this means setting innodb_buffer_pool_size to approximately 96-100 GB.

Storage

NVMe SSDs are essential for database workloads. The random read/write IOPS of NVMe drives (500K+) dramatically outperform SATA SSDs (80-100K IOPS) for database operations involving random access patterns.

Key Configuration Parameters

InnoDB Buffer Pool

[mysqld]
innodb_buffer_pool_size = 96G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 6G

The buffer pool instances should be set so each instance is at least 1 GB. With 96 GB divided into 16 instances, each instance gets 6 GB, which aligns with the chunk size.

InnoDB I/O Configuration

innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_read_io_threads = 16
innodb_write_io_threads = 16

Setting innodb_flush_neighbors = 0 is critical for NVMe storage since sequential optimization is unnecessary for flash storage. The io_capacity values reflect NVMe capability.

Redo Log Configuration

innodb_redo_log_capacity = 4G
innodb_log_buffer_size = 64M

MySQL 8.0.30+ uses innodb_redo_log_capacity instead of the older innodb_log_file_size and innodb_log_files_in_group parameters.

Connection and Thread Settings

max_connections = 500
thread_cache_size = 64
table_open_cache = 4096
table_open_cache_instances = 16

Query Optimization

Enable the Performance Schema

performance_schema = ON
performance_schema_max_thread_instances = 600

Identify Slow Queries

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON

Using EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

The EXPLAIN ANALYZE statement, introduced in MySQL 8.0.18, provides actual execution times and row counts, making it invaluable for query tuning.

Monitoring with PMM

Percona Monitoring and Management (PMM) provides comprehensive MySQL monitoring:

# Install PMM Client
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt update
sudo apt install pmm2-client -y

# Register with PMM Server
pmm-admin config --server-insecure-tls --server-url=https://admin:[email protected]

# Add MySQL monitoring
pmm-admin add mysql --username=pmm --password=pmmpass --query-source=perfschema

Backup Strategy

# Full backup with Percona XtraBackup
xtrabackup --backup --target-dir=/backups/full/   --user=backup --password=backuppass

# Incremental backup
xtrabackup --backup --target-dir=/backups/inc1/   --incremental-basedir=/backups/full/   --user=backup --password=backuppass

Conclusion

Running MySQL 8.0 on a BRHOSTING.COM dedicated server with proper tuning delivers database performance that rivals or exceeds managed cloud database services at a fraction of the cost. Our servers in six global locations ensure low latency for your database-driven applications worldwide.