mysql8 binlog优化_MySQL-8.0.19 优化日志及压测

概要

MySQL-8.0.19 发布的第一时间 dbm 就提供了支持，那 dbm 自动化安装的 MySQL 实例在性能上是一个怎样的表现呢？我的测试环境如下。

IPCPUMemDisk系统版本MySQL版本角色

192.168.100.10

32(逻辑核心)

128G

4TSSD

centos-7.6

MySQL-8.0.19

Master

192.168.100.20

32(逻辑核心)

128G

4TSSD

centos-7.6

MySQL-8.0.19

Slave

dbm-agent 在安装 MySQL 时会根据主机配置自动的完成参数优化，默认情况下的性能表现如下图。

安装

安装 Master。

dbma-cli-single-instance --port=3306 --max-mem=131072 install

安装 Slave。

dbma-cli-build-slave --host=192.168.100.10 --port=3306 --max-mem=131072 build-slave

验证同步是否正常。

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.100.10

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000051

Read_Master_Log_Pos: 88792484

Relay_Log_File: relay.000078

Relay_Log_Pos: 433520819

Relay_Master_Log_File: mysql-bin.000027

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

dbm-agent 自动生成的配置文件如下。

[mysqld]

####for basic

user = mysql3306

basedir = /usr/local/mysql-8.0.19-linux-glibc2.12-x86_64

datadir = /database/mysql/data/3306

server_id = 1794

port = 3306

bind_address = *

admin_address = 127.0.0.1

mysqlx_port = 33060

admin_port = 33062

socket = /tmp/mysql-3306.sock

mysqlx_socket = /tmp/mysqlx-33060.sock

pid_file = /tmp/mysql-3306.pid

character_set_server = utf8mb4

open_files_limit = 102000

max_prepared_stmt_count = 1048576

skip_name_resolve = 1

super_read_only = OFF

log_timestamps = system

event_scheduler = OFF

auto_generate_certs = ON

activate_all_roles_on_login = ON

end_markers_in_json = OFF

tmpdir = /tmp/

max_connections = 1024

autocommit = ON

sort_buffer_size = 256K

join_buffer_size = 256K

eq_range_index_dive_limit = 200

####for table

big_tables = OFF

sql_require_primary_key = OFF

lower_case_table_names = 1

auto_increment_increment = 1

auto_increment_offset = 1

table_open_cache = 4000

table_definition_cache = 2000

table_open_cache_instances = 32

####for net

max_allowed_packet = 1G

connect_timeout = 10

interactive_timeout = 28800

net_read_timeout = 30

net_retry_count = 10

net_write_timeout = 60

net_buffer_length = 32K

####for logs

log_output = FILE

general_log = OFF

general_log_file = general.log

##error

log_error = err.log

log_statements_unsafe_for_binlog = ON

##slow

slow_query_log = ON

slow_query_log_file = slow.log

long_query_time = 2

log_queries_not_using_indexes = OFF

log_slow_admin_statements = OFF

log_slow_slave_statements = OFF

##binlog

log_bin = /binlog/mysql/binlog/3306/mysql-bin

binlog_checksum = none

log_bin_trust_function_creators = ON

binlog_direct_non_transactional_updates = OFF

binlog_expire_logs_seconds = 604800

binlog_error_action = ABORT_SERVER

binlog_format = ROW

max_binlog_stmt_cache_size = 1G

max_binlog_cache_size = 1G

max_binlog_size = 1G

binlog_order_commits = ON

binlog_row_image = FULL

binlog_row_metadata = MINIMAL

binlog_rows_query_log_events = ON

binlog_stmt_cache_size = 32K

log_slave_updates = ON

binlog_transaction_dependency_history_size =25000

binlog_transaction_dependency_tracking = WRITESET

sync_binlog = 1

binlog_cache_size = 96K

binlog_group_commit_sync_delay = 0

binlog_group_commit_sync_no_delay_count = 0

####for replication

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_timeout = 1000

rpl_semi_sync_master_wait_point = AFTER_SYNC

rpl_semi_sync_master_wait_no_slave = ON

rpl_semi_sync_master_wait_for_slave_count = 1

master_info_repository = table

sync_master_info = 10000

skip_slave_start = OFF

slave_load_tmpdir = /tmp/

plugin_load_add = semisync_master.so

plugin_load_add = semisync_slave.so

relay_log = relay

sync_relay_log = 10000

sync_relay_log_info = 10000

relay_log_info_repository = table

slave_preserve_commit_order = ON

slave_parallel_type = logical_clock

slave_parallel_workers = 2

slave_max_allowed_packet = 1G

####for gtid

gtid_mode = ON

binlog_gtid_simple_recovery = ON

enforce_gtid_consistency = ON

gtid_executed_compression_period = 1000

####for clone

plugin-load-add = mysql_clone.so

clone = FORCE_PLUS_PERMANEN

####for engines

default_storage_engine = innodb

default_tmp_storage_engine = innodb

internal_tmp_mem_storage_engine = TempTable

####for innodb

innodb_data_home_dir = ./

innodb_data_file_path = ibdata1:64M:autoextend

innodb_page_size = 16K

innodb_default_row_format = dynamic

innodb_log_group_home_dir = ./

innodb_redo_log_encrypt = OFF

innodb_online_alter_log_max_size = 256M

innodb_undo_directory = ./

innodb_undo_log_encrypt = OFF

innodb_undo_log_truncate = ON

innodb_max_undo_log_size = 1G

innodb_rollback_on_timeout = OFF

innodb_rollback_segments = 128

innodb_log_checksums = ON

innodb_checksum_algorithm = crc32

innodb_log_compressed_pages = ON

innodb_doublewrite = ON

innodb_commit_concurrency = 0

innodb_read_only = OFF

innodb_dedicated_server = OFF

innodb_old_blocks_pct = 37

innodb_old_blocks_time = 1000

innodb_random_read_ahead = OFF

innodb_read_ahead_threshold = 56

innodb_max_dirty_pages_pct_lwm = 20