When tuning MySQL performance, setting the InnoDB buffer pool size correctly is crucial - yet many administrators encounter situations where their configuration changes don't persist after restart. The root cause typically lies in one of these areas:

• Configuration file loading order issues
• Improper file permissions
• Multiple configuration files overriding settings
• Missing required configuration groups

Before making changes, always verify your current InnoDB settings:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';

For a production server with 7GB RAM running web services, I'd recommend these baseline settings:

[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

Many administrators forget these essential steps when modifying InnoDB parameters:

1. Stop MySQL service completely: sudo systemctl stop mysql
2. Remove existing log files: rm /var/lib/mysql/ib_logfile*
3. Verify configuration file loading order: mysqld --verbose --help | grep -A 1 "Default options"
4. Check for included configurations: Many distributions use !includedir directives

Here's the complete procedure I use when configuring new MySQL servers:

# Backup current configuration
sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bak

# Edit main configuration file
sudo nano /etc/mysql/my.cnf

# Add these lines under [mysqld] section
[mysqld]
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_log_buffer_size = 128M
innodb_flush_method = O_DIRECT

# Restart MySQL properly
sudo systemctl stop mysql
sudo rm /var/lib/mysql/ib_logfile*
sudo systemctl start mysql

# Verify changes
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

If changes still aren't applying, consider these diagnostic steps:

# Check which configuration files are loaded
mysql --help | grep "Default options"

# Look for syntax errors in config files
mysqld --validate-config

# Verify file permissions
ls -la /etc/mysql/

# Check MySQL error log for clues
sudo tail -100 /var/log/mysql/error.log

Remember that some cloud-hosted MySQL instances may have management layers that override certain configurations - always check your provider's documentation for any special considerations.

When adjusting MySQL's InnoDB parameters like buffer pool size, several common pitfalls can prevent your changes from taking effect. Let me walk through the proper configuration process and potential issues.

First, check your current configuration directly in MySQL:

SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW VARIABLES LIKE 'innodb_log%';

If these show default values despite your configuration changes, one of these issues might be occurring:

MySQL loads configuration files in a specific order. On Linux systems, it typically checks these locations:

/etc/my.cnf
/etc/mysql/my.cnf
/usr/etc/my.cnf
~/.my.cnf

To verify which files MySQL is actually using:

mysql --help | grep "Default options" -A 1

For a server with 7GB RAM running both MySQL and a web server, here's a complete configuration example:

[mysqld]
# Buffer pool configuration (50-70% of available RAM)
innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=4
innodb_buffer_pool_load_at_startup=ON
innodb_buffer_pool_dump_at_shutdown=ON

# Log file configuration
innodb_log_file_size=1G
innodb_log_files_in_group=2
innodb_log_buffer_size=128M

# Other recommended settings
innodb_flush_method=O_DIRECT
innodb_flush_neighbors=1
innodb_read_io_threads=8
innodb_write_io_threads=8

After modifying your configuration:

1. Stop MySQL completely: sudo systemctl stop mysql
2. Remove old log files: rm /var/lib/mysql/ib_logfile*
3. Start MySQL: sudo systemctl start mysql

Verify your changes with these queries:

SHOW ENGINE INNODB STATUS;
SELECT (1-((SELECT variable_value FROM information_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_reads')/(SELECT variable_value 
FROM information_schema.global_status 
WHERE variable_name = 'Innodb_buffer_pool_read_requests'))) 
AS 'Buffer Pool Hit Rate';

Aim for a hit rate above 99% for optimal performance.

• Not removing old log files before restart
• Setting buffer pool size too high (causing swapping)
• Mixing configuration directives across multiple files
• Forgetting to account for other services' memory needs

For production systems, consider these additional parameters:

innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_lru_scan_depth=2048
innodb_adaptive_flushing=ON
innodb_change_buffering=all