MySQL 8 Performance Schema Monitoring

By Admin · Mar 26, 2026 · Updated Apr 23, 2026 · 6 views · 4 min read

In this article, we'll walk through the complete process of working with mysql in a server environment. Understanding performance-schema is essential for maintaining a reliable and performant infrastructure.

Prerequisites

The target database server installed and running
Basic familiarity with the Linux command line
Root or sudo access to the server
A database client tool for testing connections

Installation and Initial Setup

The default configuration works well for development environments, but production servers require additional tuning. Pay particular attention to connection limits, timeout values, and logging settings.


-- Check current configuration
SHOW VARIABLES LIKE '%performance-schema%';
SHOW STATUS LIKE '%performance-schema%';

-- Optimize settings
SET GLOBAL performance-schema_size = '256M';

Each line in the configuration serves a specific purpose. The comments explain the reasoning behind each setting, making it easier to customize for your specific use case.

Test disaster recovery procedures regularly
Document all configuration changes
Set up monitoring before going to production
Use version control for configuration files
Maintain runbooks for common operations

Configuration Tuning

If you encounter issues during setup, check the system logs first. Most problems can be diagnosed by examining the output of journalctl or the application-specific log files in /var/log/.


# Configuration file: /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add these optimizations:
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
max_connections = 200

# Restart the service
sudo systemctl restart mysql

Each line in the configuration serves a specific purpose. The comments explain the reasoning behind each setting, making it easier to customize for your specific use case.

Set up fail2ban for brute force protection
Keep all software components up to date
Use SSH keys instead of password authentication
Enable firewall and allow only necessary ports
Use strong, unique passwords for all services

Setting Up Replication

Regular maintenance is essential for keeping your mysql installation running smoothly. Schedule periodic reviews of log files, disk usage, and security updates to prevent issues before they occur.


# Check database performance
mysqltuner --host localhost --user root

# Monitor active queries
mysqladmin processlist
SHOW FULL PROCESSLIST;

The configuration above sets the recommended values for a VPS with 2-4GB of RAM. Adjust the memory-related settings proportionally if your server has different specifications.

Security Implications

For production deployments, consider implementing high availability by running multiple instances behind a load balancer. This approach provides both redundancy and improved performance under heavy load.

Use SSH keys instead of password authentication
Use strong, unique passwords for all services
Keep all software components up to date

Backup and Recovery

It's recommended to test this configuration in a staging environment before deploying to production. This helps identify potential compatibility issues and allows you to benchmark performance differences.


-- Check current configuration
SHOW VARIABLES LIKE '%performance-schema%';
SHOW STATUS LIKE '%performance-schema%';

-- Optimize settings
SET GLOBAL performance-schema_size = '256M';