How To Install Percona on Fedora 42

Database management is a critical aspect of system administration, and choosing the right database server can significantly impact performance, security, and manageability. Percona Server offers enhanced features over standard MySQL installations, making it a preferred choice for businesses and individuals seeking improved database performance. This guide provides detailed instructions for installing Percona on Fedora 42, one of the latest releases of this popular Linux distribution.

Introduction

Percona Server is an enhanced, drop-in replacement for MySQL that offers superior performance, scalability, and instrumentation. Designed as a fork of MySQL, Percona maintains full compatibility while providing additional features that boost performance and simplify database management tasks. For Fedora 42 users looking to optimize their database operations, Percona offers a compelling alternative to the default MySQL implementation with better monitoring capabilities, improved storage engines, and enhanced security features.

This comprehensive guide will walk you through the entire process of installing Percona on Fedora 42, from preparation to configuration and optimization. By following these instructions, you’ll establish a robust database environment that surpasses standard MySQL installations in performance and functionality.

What is Percona?

Percona Server is an open-source database server that serves as a drop-in replacement for MySQL. Developed by Percona, a company founded in 2006 by Peter Zaitsev and Vadim Tkachenko, this enhanced MySQL variant focuses on delivering superior performance, scalability, and availability for demanding database workloads.

The Percona product suite includes several offerings:

Percona Server for MySQL – A drop-in replacement for MySQL with enhanced performance features, instrumentation, and management capabilities. It maintains full compatibility with MySQL while addressing many limitations of the original implementation.

Percona Server for MongoDB – An enhanced, drop-in replacement for MongoDB with additional features and enterprise-grade functionality.

Percona Server for PostgreSQL – A distribution of PostgreSQL that includes additional enterprise features and tools to simplify deployment and management.

Percona XtraDB Cluster – A MySQL clustering solution for high availability and scalability.

Compared to standard MySQL, Percona Server offers several advantages:

• Enhanced performance through improved storage engine implementations
• Advanced monitoring and diagnostic capabilities
• Better scalability for high-traffic environments
• Improved query optimization
• Enhanced security features
• More detailed query logging and profiling
• Thread pool implementation for better connection handling
• Improved backup and recovery tools

For database administrators looking to maximize performance while maintaining MySQL compatibility, Percona Server represents an excellent choice, especially on a robust platform like Fedora 42.

Prerequisites for Installation

Before beginning the Percona installation process on Fedora 42, ensure your system meets the necessary requirements and prepare your environment properly:

System Requirements:

• A Fedora 42 system with at least 1GB RAM (4GB or more recommended for production environments)
• At least 5GB of free disk space
• A 64-bit x86 processor
• Internet connectivity for repository access
• Root or sudo privileges for installation

Required Packages:

• Ensure basic development tools are installed: sudo dnf install -y wget curl gnupg2
• Verify that package management utilities are updated: sudo dnf update -y

Existing Database Considerations:

• Check for existing MySQL or MariaDB installations: rpm -qa | grep -E 'mysql|mariadb'
• If present, consider backing up your data before replacing with Percona: mysqldump --all-databases > mysql_backup.sql
• Document existing configurations: cp /etc/my.cnf /etc/my.cnf.backup (if exists)

User Permissions:

• Ensure you have sudo access or root privileges to install system packages
• Prepare database user credentials for post-installation security setup

Network Considerations:

• If running a firewall, prepare to open port 3306 for MySQL traffic if needed
• Decide whether remote connections will be allowed or if the database will be local-only

Taking time to properly prepare your system will help ensure a smooth installation process and prevent potential data loss or configuration issues.

Preparing Your Fedora 42 System

Proper preparation of your Fedora 42 system is crucial for a successful Percona installation. This section guides you through updating your system and addressing potential conflicts:

Update System Packages:
Begin by ensuring your Fedora 42 system is fully updated with the latest packages:

sudo dnf update -y

Allow the update process to complete before proceeding. This ensures you have the latest security patches and package dependencies.

Disable Conflicting MySQL Module:
Fedora 42, like other RHEL-based systems, enables the MySQL module by default. This module can hide Percona-provided packages and must be disabled:

sudo dnf module disable mysql -y

This command disables the default MySQL module, making Percona packages visible to the package manager.

Remove Existing MySQL/MariaDB Installations:
If you have an existing MySQL or MariaDB installation, it’s recommended to remove it to prevent conflicts:

sudo dnf remove mysql* mariadb* -y

Configure Firewall Rules:
If you’re using firewalld (default in Fedora), configure it to allow MySQL traffic if needed:

sudo firewall-cmd --permanent --add-service=mysql
sudo firewall-cmd --reload

Create Configuration Backup:
If you’re migrating from an existing MySQL installation, back up your configuration files:

# For existing MySQL configurations
if [ -f /etc/my.cnf ]; then
    sudo cp /etc/my.cnf /etc/my.cnf.backup
fi

# For MariaDB configurations
if [ -f /etc/my.cnf.d/server.cnf ]; then
    sudo cp /etc/my.cnf.d/server.cnf /etc/server.cnf.backup
fi

Clean MySQL Data Directory:
If you’re performing a fresh installation and don’t need existing data:

sudo rm -rf /var/lib/mysql/*

Warning: This will delete all existing MySQL data. Only execute this if you’re certain you don’t need this data or have already backed it up.

Create Required Directories:
Ensure the necessary directories exist with proper permissions:

sudo mkdir -p /var/log/mysql
sudo chmod 750 /var/log/mysql
sudo chown -R mysql:mysql /var/log/mysql

With these preparation steps complete, your Fedora 42 system is ready for the installation of Percona Server.

Installing Percona Repository

Installing the Percona repository is the first technical step in the installation process. This repository provides access to all Percona products and keeps them updated with the latest versions. Follow these steps to install and configure the Percona repository on your Fedora 42 system:

1. Download the Percona Repository Package:
Use the following command to download the Percona repository package:

sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

This command downloads and installs the repository configuration package, which sets up the necessary repository entries and installs the Percona GPG key for package verification.

2. Verify Repository Installation:
After installation, verify that the Percona repository was successfully installed:

sudo percona-release --list

This command lists all available Percona repositories. You should see entries for various Percona products including Percona Server.

3. Enable the Percona Server Repository:
For Percona Server 5.7 (as an example), enable the specific repository:

sudo percona-release setup ps57

If you prefer a different version, you can use:

• ps80 for Percona Server 8.0
• ps57 for Percona Server 5.7
• psmdb-42 for Percona Server for MongoDB 4.2

4. Verify Repository Configuration:
Check that the repository was properly enabled:

sudo dnf repolist | grep percona

You should see the enabled Percona repositories in the output.

5. Repository Structure Understanding:
Percona’s repository structure includes:

• original – Contains the original release packages
• release – Contains the latest GA (Generally Available) release packages
• testing – Contains pre-release packages for testing purposes

6. Troubleshooting Repository Issues:
If you encounter repository connection issues, verify your internet connection and DNS configuration:

ping repo.percona.com

If the repository GPG key verification fails, manually import the key:

sudo rpm --import https://repo.percona.com/yum/PERCONA-PACKAGING-KEY

With the Percona repository properly installed and configured, you’re now ready to install Percona Server on your Fedora 42 system.

Installing Percona Server for MySQL

With the repository properly configured, you can now install Percona Server for MySQL on your Fedora 42 system. This section guides you through the installation process and initial verification steps.

1. Choose the Version to Install:
Decide which version of Percona Server best fits your requirements. For most users, installing the latest stable version is recommended. In this guide, we’ll use Percona Server 5.7 as an example, but you can substitute the appropriate version for your needs.

2. Install Percona Server Packages:
Install the core Percona Server packages using DNF:

sudo dnf install -y percona-server-server percona-server-client percona-server-common

For a more complete installation with additional tools, use:

sudo dnf install -y percona-server-server percona-server-client percona-server-common percona-toolkit percona-xtrabackup

The installation process will download and install all necessary packages and dependencies. During installation, you might be prompted to set a root password for the MySQL server.

3. Understanding Package Selection:
The main packages include:

• percona-server-server – The core server component
• percona-server-client – Client programs and libraries
• percona-server-common – Files common to server and client
• percona-toolkit – Advanced tools for database management
• percona-xtrabackup – Hot backup utility for InnoDB and XtraDB databases

4. Start and Enable the Percona Server Service:
After installation completes, start the Percona Server service and enable it to start automatically at boot:

sudo systemctl start mysqld
sudo systemctl enable mysqld

5. Verify Service Status:
Check that the service started successfully:

sudo systemctl status mysqld

Look for “active (running)” in the output to confirm the service is running correctly.

6. Initial Server Configuration:
During the first-time startup, Percona Server generates a temporary root password. Retrieve this password from the log file:

sudo grep 'temporary password' /var/log/mysqld.log

Note this password as you’ll need it for the secure installation process.

7. Verify Installation:
Confirm that Percona Server is properly installed and running:

mysql -V

This should display the Percona Server version information, confirming a successful installation.

8. Troubleshooting Installation Issues:
If the service fails to start, check the error logs:

sudo journalctl -xe
sudo cat /var/log/mysqld.log

A common issue is when the service fails to start without updating the PID file. This often indicates permission problems or conflicts with previous MySQL installations. Ensure proper ownership of MySQL directories:

sudo chown -R mysql:mysql /var/lib/mysql
sudo chown -R mysql:mysql /var/log/mysql

With Percona Server installed and running, proceed to the next section to secure your installation and configure it for optimal performance.

Securing Your Percona Installation

After successfully installing Percona Server, it’s crucial to secure your database setup. Percona Server comes with a security script that helps configure several important security settings. Follow these steps to secure your Percona installation:

1. Run the MySQL Secure Installation Script:
Use the temporary password retrieved from the previous section to run the security configuration script:

sudo mysql_secure_installation

2. Set a Strong Root Password:
The script will first prompt you to enter the current root password (the temporary one from the logs). Then, you’ll be asked to set a new strong password for the root user. Choose a complex password with a mix of uppercase, lowercase, numbers, and special characters.

3. Remove Anonymous Users:
When prompted, choose ‘Y’ to remove anonymous users. Anonymous user accounts are created by default during MySQL installation and pose a security risk by allowing unauthenticated database access.

4. Disallow Remote Root Login:
Select ‘Y’ when asked if you want to disallow root login remotely. This restricts root access to local connections only, enhancing security.

5. Remove Test Database:
Choose ‘Y’ to remove the test database and access to it. The test database is installed by default but is not needed in production environments.

6. Reload Privilege Tables:
Select ‘Y’ to reload the privilege tables, which ensures that all the security changes take effect immediately without restarting the server.

7. Implement Additional Security Measures:
Beyond the basic security script, consider these additional security measures:

Create Application-Specific Database Users:
Instead of using root for all operations, create specific users with limited privileges for each application:

mysql -u root -p
mysql> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'secure_password';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON app_database.* TO 'appuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

Configure Network Binding:
By default, Percona Server listens on all interfaces. For added security, configure it to listen only on necessary interfaces. Edit /etc/my.cnf and add:

[mysqld]
bind-address = 127.0.0.1

This restricts connections to the local machine only. For specific network access, use your server’s specific IP address.

Enable Binary Logging with Secure Settings:
Binary logging helps with backup and replication but should be configured securely:

[mysqld]
log-bin = mysql-bin
binlog-format = ROW
expire_logs_days = 7

Set File Permissions:
Ensure configuration files have appropriate permissions:

sudo chmod 640 /etc/my.cnf
sudo chown root:mysql /etc/my.cnf

With these security measures in place, your Percona Server installation is now protected against common security vulnerabilities. This forms a strong foundation for building secure database applications on your Fedora 42 system.

Configuring Percona Server

Proper configuration is essential to get the most out of your Percona Server installation. This section covers key configuration options to optimize performance and functionality for your specific workload.

1. Locating Configuration Files:
Percona Server’s main configuration file on Fedora 42 is located at /etc/my.cnf, with additional configuration files in the /etc/my.cnf.d/ directory. Create or edit these files to customize your Percona Server:

sudo nano /etc/my.cnf

2. Basic Configuration Structure:
The configuration file uses a section-based format. The main sections include:

[mysqld]
# Server-specific settings

[mysql]
# Client settings

[client]
# General client settings

3. Essential Performance Parameters:
Add these key performance parameters to the [mysqld] section:

Memory Allocation:

# Memory Allocation
innodb_buffer_pool_size = 1G  # Adjust to 50-70% of available RAM
innodb_buffer_pool_instances = 4  # Set to number of CPU cores (up to 8)
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
join_buffer_size = 4M
sort_buffer_size = 4M
read_rnd_buffer_size = 8M
read_buffer_size = 2M

InnoDB Settings:

# InnoDB Configuration
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1  # Set to 2 for better performance with slight durability trade-off
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200  # Increase for SSDs (1000+)

Connection Handling:

# Connection Settings
max_connections = 150
thread_cache_size = 16
table_open_cache = 2000
max_allowed_packet = 64M

Query Cache Settings:
For Percona Server 5.7 (disable for 8.0 as it’s removed):

# Query Cache
query_cache_type = 0
query_cache_size = 0

4. Logging Configuration:
Set up appropriate logging to monitor performance and troubleshoot issues:

# Error Logging
log_error = /var/log/mysql/error.log

# Slow Query Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2  # Log queries taking longer than 2 seconds
log_queries_not_using_indexes = 1

5. Apply Configuration Changes:
After making configuration changes, restart the Percona Server service to apply them:

sudo systemctl restart mysqld

6. Verify Applied Configuration:
Check that your configuration changes were successfully applied:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

7. Performance Schema:
Enable the Performance Schema for advanced monitoring:

# Performance Schema
performance_schema = ON

8. Binary Logging Configuration:
Configure binary logging for backup and replication purposes:

# Binary Logging
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1  # Set to 0 for better performance but potential data loss on crash

With these configuration settings tailored to your system’s hardware and workload, your Percona Server should perform optimally. Remember to regularly monitor your database performance and adjust these parameters as needed based on actual usage patterns.

Installing Percona Toolkit

Percona Toolkit is a collection of advanced command-line tools that help database administrators perform a variety of MySQL and Percona Server tasks more efficiently. These tools simplify complex operations like schema changes, performance analysis, and server monitoring. Here’s how to install and start using Percona Toolkit on your Fedora 42 system:

1. Enable the Percona Tools Repository:
If you haven’t already enabled the tools repository, do so with the following command:

sudo percona-release enable tools

This command specifically enables the repository containing Percona Toolkit and other utility packages.

2. Install Percona Toolkit:
Install the toolkit using DNF:

sudo dnf install -y percona-toolkit

This will install all the tools included in the toolkit package.

3. Verify the Installation:
Confirm that Percona Toolkit was installed correctly by checking the version of one of its core tools:

pt-query-digest --version

You should see version information for the tool, indicating a successful installation.

4. Essential Toolkit Components:
Percona Toolkit includes various specialized tools, each designed for specific database management tasks:

• pt-online-schema-change: Alters table structure with minimal downtime
• pt-query-digest: Analyzes MySQL queries from slow logs, general logs, and tcpdump
• pt-table-checksum: Verifies MySQL replication integrity
• pt-table-sync: Synchronizes MySQL table data efficiently
• pt-kill: Monitors and terminates problematic MySQL connections
• pt-archiver: Archives rows from a MySQL table into another table or file
• pt-duplicate-key-checker: Finds duplicate indexes and foreign keys
• pt-variable-advisor: Analyzes MySQL variables and provides configuration advice

5. Basic Usage Examples:
Here are examples of how to use some of the most common Percona Toolkit tools:

Analyze a slow query log:

pt-query-digest /var/log/mysql/slow-query.log > query_report.txt

Check for duplicate indexes:

pt-duplicate-key-checker -u root -p

Alter a table structure with minimal downtime:

pt-online-schema-change --alter "ADD INDEX idx_column (column)" \
  --host=localhost --user=root --ask-pass \
  D=database,t=table --execute

6. Advanced Tool Configuration:
Many Percona Toolkit tools accept a wide range of options to customize their behavior. For detailed information about specific tools and their options, use the help flag:

pt-online-schema-change --help

7. Automating Database Tasks:
Percona Toolkit tools can be incorporated into scripts and cron jobs to automate routine database maintenance tasks. For example, to set up a nightly analysis of slow queries:

echo "0 1 * * * root pt-query-digest /var/log/mysql/slow-query.log > /root/reports/query_digest_\$(date +\%Y\%m\%d).txt" | sudo tee -a /etc/cron.d/mysql-analysis

Percona Toolkit greatly enhances your ability to manage and optimize your database servers. The time invested in learning these tools pays significant dividends in improved database performance and reduced administrative overhead. As you become more familiar with them, you’ll discover numerous ways to streamline your database operations.

Monitoring and Management Tools

Effective monitoring is crucial for maintaining optimal database performance and identifying potential issues before they affect your applications. Percona provides comprehensive monitoring solutions, with Percona Monitoring and Management (PMM) being the flagship option. This section covers setting up monitoring for your Percona Server installation on Fedora 42.

1. Install Percona Monitoring and Management (PMM) Client:
First, ensure you have the proper repository enabled:

sudo percona-release enable pmm

Then install the PMM client:

sudo dnf install -y pmm2-client

2. Set Up PMM Client:
Configure the PMM client to connect to your PMM server. If you don’t have a PMM server yet, you’ll need to set that up first (usually via Docker or as a virtual appliance).

sudo pmm-admin config --server-url=https://admin:admin@pmm-server-address

Replace pmm-server-address with your actual PMM server address.

3. Register Percona Server with PMM:
Add your Percona Server instance to PMM monitoring:

sudo pmm-admin add mysql --username=pmm_user --password=password --query-source=perfschema

Note: You should create a dedicated MySQL user with appropriate permissions for monitoring:

mysql -u root -p
mysql> CREATE USER 'pmm_user'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT SELECT, PROCESS, SUPER, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm_user'@'localhost';
mysql> GRANT SELECT ON performance_schema.* TO 'pmm_user'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;

4. Essential Metrics to Monitor:
Through PMM or other monitoring tools, focus on these critical metrics:

• Query Throughput: Queries per second
• Query Latency: Average query execution time
• Connection Usage: Current connections vs. max connections
• Buffer Pool Utilization: InnoDB buffer pool hit rate
• Disk I/O: Read/write operations and throughput
• Table Cache: Open tables vs. table cache size
• Temporary Tables: Created on disk vs. in memory
• Thread Usage: Running threads vs. connected threads

5. Setting Up MySQL Enterprise Monitor (Alternative):
If you prefer using MySQL Enterprise Monitor:

sudo dnf install -y mysql-monitor-agent
sudo /opt/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start

Configure the agent through its web interface.

6. Using Percona Monitoring Plugins with Existing Solutions:
If you already use monitoring systems like Nagios, Zabbix, or Grafana, you can integrate Percona monitoring plugins:

sudo dnf install -y percona-nagios-plugins

7. Server Status Checks:
For quick status checks from the command line:

# Check server status variables
mysql -u root -p -e "SHOW GLOBAL STATUS"

# Check current processes
mysql -u root -p -e "SHOW PROCESSLIST"

# Check InnoDB status
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

8. Set Up Email Alerts:
Configure important alerts to be sent via email using your monitoring system. Key events to monitor include:

• High disk usage (>85%)
• Excessive connection usage (>80% of max_connections)
• Slow query frequency increases
• Replication lag exceeding thresholds
• Server restarts

Regular monitoring ensures you stay ahead of potential problems and can proactively optimize your database environment. With proper alerting in place, you’ll be notified of critical issues before they impact your applications and users.

Troubleshooting Common Installation Issues

Even with careful preparation, you might encounter issues during or after Percona Server installation on Fedora 42. This section covers common problems and their solutions to help you resolve issues quickly and get your database server running smoothly.

1. Service Fails to Start After Installation:

Problem: The MySQL service fails to start with an error like “The server quit without updating PID file.”

Solutions:

• Check log files for specific errors:

  sudo cat /var/log/mysqld.log

• Verify proper directory permissions:

  sudo chown -R mysql:mysql /var/lib/mysql
  sudo chown -R mysql:mysql /var/log/mysql

• Check disk space availability:

  df -h

• Ensure no other MySQL instances are running:

  sudo ps aux | grep mysql
  sudo netstat -tlpn | grep 3306

2. Repository Connection Problems:

Problem: Unable to connect to Percona repositories during installation.

Solutions:

• Verify internet connectivity:

  ping repo.percona.com

• Check DNS resolution:

  nslookup repo.percona.com

• Try using a different DNS server temporarily:

  echo "nameserver 8.8.8.8" | sudo tee /etc/resolv.conf.d/head

3. GPG Key Verification Issues:

Problem: Package installation fails with GPG key errors.

Solutions:

• Manually import the Percona GPG key:

  sudo rpm --import https://repo.percona.com/yum/PERCONA-PACKAGING-KEY

• Reinstall the percona-release package:

  sudo dnf remove percona-release -y
  sudo dnf install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

4. Dependency Conflicts:

Problem: Package dependencies cannot be resolved during installation.

Solutions:

• Update the package cache:

  sudo dnf clean all
  sudo dnf makecache

• Manually install required dependencies:

  sudo dnf install -y perl-DBI perl-DBD-MySQL libaio libev

• Try installing specific versions if available:

  sudo dnf install --allowerasing percona-server-server

5. Authentication Issues After Installation:

Problem: Unable to log in with root user after installation.

Solutions:

• Reset the root password:

  sudo systemctl stop mysqld
  sudo mkdir -p /var/run/mysqld
  sudo chown mysql:mysql /var/run/mysqld
  sudo mysqld_safe --skip-grant-tables --skip-networking &
  mysql -u root
  mysql> FLUSH PRIVILEGES;
  mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
  mysql> EXIT;
  sudo killall mysqld
  sudo systemctl start mysqld

6. Performance Issues:

Problem: Database performs poorly after installation.

Solutions:

• Check configuration parameters against system resources:

  sudo cat /etc/my.cnf
  free -m

• Adjust InnoDB buffer pool size to match available memory
• Verify that performance schema is properly configured
• Check for disk I/O bottlenecks:

  sudo iostat -xm 5

7. Log File Issues:

Problem: Log files not being created or updated.

Solutions:

• Ensure log directories exist with proper permissions:

  sudo mkdir -p /var/log/mysql
  sudo chown mysql:mysql /var/log/mysql

• Verify log file paths in configuration:

  sudo grep log /etc/my.cnf

• Check for file system issues:

  sudo dmesg | grep -i error

If you encounter persistent issues not covered here, consult the official Percona documentation or seek assistance from the Percona community forums. Many complex problems have already been solved by the community, and searching for specific error messages often yields valuable solutions.

Performance Optimization

After successfully installing Percona Server on your Fedora 42 system, optimizing its performance for your specific workload is the next critical step. This section covers essential optimization strategies to help you achieve maximum performance.

1. InnoDB Buffer Pool Optimization:
The InnoDB buffer pool is one of the most important parameters for performance optimization:

# Add to /etc/my.cnf in [mysqld] section
innodb_buffer_pool_size = XG  # Set to 70-80% of available RAM
innodb_buffer_pool_instances = Y  # Set to number of CPU cores (max 8)

For a server with 16GB RAM, you might use:

innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8

2. Disk I/O Optimization:
Optimize disk I/O performance with these settings:

# Disk I/O settings
innodb_io_capacity = 2000  # Higher for SSDs (2000+), lower for HDDs (200)
innodb_io_capacity_max = 4000  # Typically 2x the io_capacity
innodb_flush_method = O_DIRECT  # Bypasses OS cache for direct disk I/O
innodb_flush_neighbors = 0  # Disable for SSDs, enable (1) for HDDs
innodb_write_io_threads = 8  # Increase for high I/O servers
innodb_read_io_threads = 8   # Increase for read-heavy workloads

3. Connection Handling:
Optimize connection handling to prevent overloading the server:

# Connection settings
max_connections = 200  # Set based on your application needs
thread_cache_size = 16  # Helps reuse threads for new connections
table_open_cache = 4000 # Adjust based on number of tables in your databases

4. Query Optimization:
For Percona Server 5.7, configure the query cache appropriately:

# Query cache - disable for busy servers
query_cache_type = 0
query_cache_size = 0

For Percona Server 8.0, the query cache is removed, so these settings aren’t needed.

5. Temporary Tables:
Configure temporary table settings:

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M  # Should match tmp_table_size

6. Benchmark Your Configuration:
Use tools like sysbench to benchmark your server performance:

# Install sysbench
sudo dnf install -y sysbench

# Prepare benchmark
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=password prepare

# Run benchmark
sysbench oltp_read_write --table-size=1000000 --mysql-db=test --mysql-user=root --mysql-password=password --time=60 --threads=16 run

7. Monitor and Tune:
Install and use Percona Monitoring and Management (PMM) to identify bottlenecks and fine-tune your settings:

# Check thread utilization
mysql -u root -p -e "SHOW STATUS LIKE 'Thread%';"

# Check buffer pool stats
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool%';"

# Check I/O activity
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE '%io%';"

8. Hardware Considerations:

• Use SSDs for database storage whenever possible
• Separate data files and log files onto different physical disks
• Consider using RAID configurations appropriate for your workload (RAID 10 for balanced performance)
• Ensure adequate RAM for your database size
• Use multi-core processors to take advantage of Percona’s parallel processing capabilities

9. Advanced Optimization:
For production environments, consider these advanced optimizations:

# Transaction log settings
innodb_log_file_size = 512M  # Larger for write-heavy workloads
innodb_log_buffer_size = 32M  # Increase for high-throughput workloads

# Concurrency settings
innodb_thread_concurrency = 0  # Let InnoDB manage thread concurrency 

# File per table setting
innodb_file_per_table = 1  # Keeps tables in separate files for better management

Remember that database optimization is an iterative process. Monitor your server’s performance regularly and adjust settings based on real-world usage patterns. What works for one workload might not be optimal for another, so continuous monitoring and adjustment are key to maintaining peak performance.

Backup and Recovery Setup

Implementing a robust backup and recovery strategy is essential for protecting your data. Percona provides powerful tools like Percona XtraBackup that enable efficient, consistent backups with minimal impact on server performance. This section guides you through setting up a comprehensive backup strategy for your Percona Server on Fedora 42.

1. Install Percona XtraBackup:
First, install Percona XtraBackup using the Percona repository:

sudo percona-release enable tools
sudo dnf install -y percona-xtrabackup-80  # For Percona Server 8.0
# Or use percona-xtrabackup-24 for Percona Server 5.7

2. Create a Backup Directory:
Set up a dedicated directory for your backups with appropriate permissions:

sudo mkdir -p /var/backups/mysql
sudo chown mysql:mysql /var/backups/mysql

3. Perform a Full Backup:
Execute a full backup using XtraBackup:

sudo xtrabackup --backup --target-dir=/var/backups/mysql/full-backup-$(date +%Y-%m-%d)

4. Prepare the Backup for Restoration:
After taking a backup, prepare it to ensure it’s consistent:

sudo xtrabackup --prepare --target-dir=/var/backups/mysql/full-backup-$(date +%Y-%m-%d)

5. Create an Automated Backup Script:
Set up a script for automated backups:

sudo nano /usr/local/bin/mysql-backup.sh

Add the following content:

#!/bin/bash
# MySQL backup script using XtraBackup

# Set variables
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
FULL_BACKUP_DIR="$BACKUP_DIR/full-$DATE"
RETENTION_DAYS=7

# Create full backup
xtrabackup --backup --target-dir=$FULL_BACKUP_DIR

# Prepare backup
xtrabackup --prepare --target-dir=$FULL_BACKUP_DIR

# Remove backups older than retention period
find $BACKUP_DIR -name "full-*" -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

# Log completion
echo "Backup completed successfully at $(date)" >> $BACKUP_DIR/backup_log.txt

Make the script executable:

sudo chmod +x /usr/local/bin/mysql-backup.sh

6. Schedule Regular Backups with Cron:
Set up a cron job to run the backup script automatically:

sudo crontab -e

Add a line to schedule daily backups at 1:00 AM:

0 1 * * * /usr/local/bin/mysql-backup.sh

7. Set Up Point-in-Time Recovery:
For point-in-time recovery capabilities, enable binary logging in your Percona Server configuration:

# Add to /etc/my.cnf in [mysqld] section
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

Restart MySQL to apply changes:

sudo systemctl restart mysqld

8. Test Backup Restoration:
Regularly test your backup restoration process to ensure your backups are valid:

# Stop MySQL service
sudo systemctl stop mysqld

# Move or rename existing data directory
sudo mv /var/lib/mysql /var/lib/mysql.old

# Create new data directory
sudo mkdir /var/lib/mysql
sudo chown mysql:mysql /var/lib/mysql

# Restore from backup
sudo xtrabackup --copy-back --target-dir=/var/backups/mysql/full-backup-YYYY-MM-DD

# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql

# Start MySQL service
sudo systemctl start mysqld

9. Consider Off-Site Backup Storage:
For additional security, set up automatic transfers of your backups to an off-site location or cloud storage:

# Install AWS CLI (example for Amazon S3)
sudo dnf install -y awscli

# Add to your backup script
aws s3 sync $BACKUP_DIR s3://your-bucket-name/mysql-backups/

A comprehensive backup strategy should include regular full backups, point-in-time recovery capabilities through binary logs, and off-site storage of backup files. By implementing these practices, you’ll be well-prepared to recover from various failure scenarios and protect your valuable data.

Congratulations! You have successfully installed Percona. Thanks for using this tutorial for installing the Percona on your Fedora 42 Linux system. For additional or useful information, we recommend you check the official Percona website.