Percona MySQL 5.7 Configuration Review for Slave (Debian 11)

MySQL & MariaDB Percona XtraDB Cluster 5.x
, ,
1

Hello,

I would like to get feedback on my Percona MySQL 5.7 (mysqld) configuration and check whether it is properly tuned for my system.

Environment

  • MySQL: Percona MySQL 5.7

  • OS: Debian 11 (Bullseye)

Hardware

  • RAM, CPU, and disk details are shared via screenshots below.

MySQL Configuration

  • Current mysqld.cnf is attached.

  • I am mainly looking for feedback on:

    • InnoDB-related settings

    • Memory usage and buffer sizes

    • Connection/thread configuration

    • Any incorrect, unnecessary, or risky parameters for MySQL 5.7

Questions

  • Is this configuration appropriate for my hardware?

  • Are there any obvious improvements or misconfigurations?

Thank you for your help.


# The Percona Server 5.7 configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /DB
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp

log-error    = /var/log/mysql/error.log




# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

server-id=10
log-bin=/LOG/binlog
#log_slave_updates
expire_logs_days=14

max_allowed_packet=1G
skip_name_resolve
bind-address=0.0.0.0
open_files_limit = 65000
max_connections=1000
collation-server = utf8_unicode_ci
character-set-server = utf8
max_binlog_size=1G
gtid-mode=on                       # GTID only
enforce-gtid-consistency=true      # GTID only

innodb_file_per_table
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=45G
innodb_log_file_size=16G
innodb_buffer_pool_instances = 8

slave_parallel_type='LOGICAL_CLOCK'
slave_parallel_workers=8

image
image863×680 24.7 KB

2

Oh I will change the datadir pls dont make confuse.

3

How many CPU’s do you have?

4

Hello there ,

I’ve got 24 CPU.

5

image
image1333×829 29.1 KB

6

Hello @aycelen and happy new year!

I think you already know that you better upgrade to 8.0 and then to 8.4. Last Oracle MySQL release of 5.7 was in Oct 2023. With Percona’e post EoL support commit, latest version was released on Feb 2025 with bug fixes etc.

Let me introduce you to two amazing Percona tools: pt-summary, pt-mysql-summary. For the future, consider running and sharing the output from these tools.

Also, setup Percona PMM for better observability it will help you observe your system and assist you for better tuning.

Finally, commenting on your current config options

#log_slave_updates <<< Enable this if you may see potential for chained replication

innodb_flush_log_at_trx_commit=2 <<< This is not acid compliant and risky... it is not recommended for master. Use 1.

innodb_buffer_pool_size=45G <<< You may consider raising this upto 75% of the available memory, provided your system is dedicated MySQL server.

innodb_log_file_size=16G <<< Are you doing heavy writes, verify if you want this large redo logs.

innodb_buffer_pool_instances = 8 <<< Review if you want to increase the instances. (Refer references)

You might want to add binlog_format = ROW, make sure you read about it.

Some more config you might need to tune are:

  • innodb_adaptive_hash_index
  • table_open_cache/table_definition_cache

You may also like to adjust your OS configuration… Instead of writing more here I’d suggest you to go through the awesome blogs and video references below for further reading and improving your configuration.

References

Thanks,

Kedar

7

@aycelen The first thing you should do is switch to 8.4; 5.7 has been dead for over 10 years, and 8.0 dies in less than 4 months.

8

Dear Matthew,

I know but I have a Orphan Table and I didnt find how can I drop that table I tried to upgrade this database and mysql50 option Ive open ticket for this topic too. try to figure it out. Also this db will migrate to postgresql a few month later. + AWS

9

If required, you may perform logical dump restore to get rid of orphan tables in rds.