Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
6.4.1
-
CentOS 7
Description
Hi I am already using maxscale as a load balancer from version 2.4 so currently, I set up new env using maxscale version 6.4.1 and MariaDB 10.5.17
but I see the monitor connection between master and slave does not balance
on version 2.4 connection between master and slave is balance
example if master has a connection 100 slave also has 100 connection
this is my maxscale configuration
[maxscale]
|
threads=auto
|
max_auth_errors_until_block=0 |
admin_host=10.50.71.33 |
admin_port=8989 |
admin_enabled=1 |
admin_secure_gui=false |
|
|
## v38_43301 ##
|
[dbsf7_v38_71_35]
|
type=server
|
address=10.50.71.35 |
port=33301 |
protocol=MariadBBackend
|
|
|
|
|
[dbsf7_v38_71_36]
|
type=server
|
address=10.50.71.36 |
port=33301 |
protocol=MariadBBackend
|
|
|
[MariaDB-Monitor-v38-43301] |
type=monitor
|
module=mariadbmon
|
servers=dbsf7_v38_71_36,dbsf7_v38_71_35
|
user=maxscale
|
password=F33D9A6E1376BD25313EB4EF0733ED43
|
monitor_interval=1000 |
auto_failover=false |
#detect_stale_master=true deprecate variables |
failover_timeout=10 |
auto_rejoin=true |
failcount=5 |
master_failure_timeout=2 |
verify_master_failure=true |
switchover_timeout=90 |
replication_user=maxscale
|
replication_password=F33D9A6E1376BD25313EB4EF0733ED43
|
#detect_stale_slave=true deprecate variables |
master_conditions=none
|
|
|
[Read-Write-Service-v38-43301] |
type=service
|
router=readwritesplit
|
servers=dbsf7_v38_71_36,dbsf7_v38_71_35
|
user=maxscale
|
password=F33D9A6E1376BD25313EB4EF0733ED43
|
master_failure_mode=fail_on_write
|
strict_multi_stmt=false |
use_sql_variables_in=master
|
master_reconnection=true |
delayed_retry=true |
delayed_retry_timeout=20s
|
transaction_replay=true |
max_sescmd_history=60 |
prune_sescmd_history=true |
connection_timeout=36000s
|
filters=Hint
|
|
|
[Read-Write-Listener]
|
type=listener
|
service=Read-Write-Service-v38-43301 |
protocol=MariadbClient
|
port=43301 |
authenticator=MariaDBAuth
|
authenticator_options=lower_case_table_names=true |
## END ##
|
|
|
[Hint]
|
type=filter
|
module=hintfilter
|
|
|
|
and this is my database configuration for replication
Thisi Master Database
[server]
|
log_warnings = 1 |
server_id = 7135 #custom |
gtid-domain-id = 1 |
gtid_ignore_duplicates = ON
|
gtid_strict_mode = 0 |
rpl_semi_sync_master_enabled = ON
|
rpl_semi_sync_slave_enabled = ON
|
rpl_semi_sync_master_wait_point = AFTER_SYNC
|
slave-skip-errors = 1062,1032 |
|
|
#add new , replication config |
#sync_binlog = 1 |
#sync_master_info = 1 |
#sync_relay_log = 1 |
#sync_relay_log_info = 1 |
log_slave_updates = 1 |
|
|
# this is only for the mysqld standalone daemon |
[mysqld]
|
thread_handling = pool-of-threads
|
log-bin = /var/lib/mysqlbinlogs/mysql-bin #binlog
|
#skip-log-bin
|
datadir = /var/lib/mysql #data directory
|
lower_case_table_names = 1 #in-case sensitive |
sql-mode = "PIPES_AS_CONCAT" #closer to ANSI |
skip-host-cache #avoid dns lookup
|
skip-name-resolve #avoid dns lookup
|
log-slave-updates = 1 #binlog update each other |
query_cache_size = 0 #query cache is not supported with wsrep |
query_cache_type = 0 #query cache is not supported with wsrep |
|
|
# LIMIT #
|
net_buffer_length = 16384 |
max_allowed_packet = 1G
|
expire_logs_days = 3 |
max_connections = 10000 |
max_connect_errors = 1000 |
wait_timeout = 40 |
interactive_timeout = 40 |
max_statement_time = 60 |
open-files-limit = 393210 #65535 |
|
|
# INNODB #
|
default_storage_engine = InnoDB
|
innodb_data_home_dir = /var/lib/mysql
|
innodb_log_group_home_dir = /var/lib/mysql
|
innodb_file_per_table = 1 |
innodb_log_file_size = 2G #Normal value 2G
|
innodb_autoinc_lock_mode = 2 #avoid bulk insert using auto inc |
innodb_flush_log_at_trx_commit = 0 |
innodb_doublewrite = 1 |
binlog_format = ROW
|
log_bin_trust_function_creators = 1 |
|
|
# LOGGING #
|
log_error = /var/lib/mysql/mysql_error.log #error log
|
slow_query_log = 1 |
slow_query_log_file = /var/lib/mysql/mysql_slow.log
|
|
|
# CUSTOM #
|
port = 33301 |
bind-address = 0.0.0.0 |
#skip-log-bin #temporary disable if needed |
|
|
# Monitoring #
|
#innodb_monitor_enable=all
|
performance_schema = ON
|
|
|
|
|
## TUNING ##
|
innodb_buffer_pool_size = 4G
|
innodb_buffer_pool_instances = 4 |
innodb_buffer_pool_chunk_size = 128M
|
thread_cache_size = 256 |
join_buffer_size = 32M #64M
|
key_buffer_size = 128M
|
max_heap_table_size = 512M
|
tmp_table_size = 512M
|
table_open_cache = 2000 |
table_definition_cache = 400 |
innodb_flush_method = O_DIRECT #avoid double buffering |
## -- ##
|
|
|
|
|
# * Galera-related settings
|
#
|
[galera]
|
# Mandatory settings
|
#wsrep_on=OFF
|
#wsrep_provider_options="gcache.size=10G" |
#wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so |
#wsrep_cluster_address=gcomm:// |
#wsrep_cluster_address=
|
#wsrep_cluster_name=NBC-DataCentre_Reg
|
#wsrep_node_address=172.17.200.168 |
#wsrep_node_name=clusterreg168
|
#wsrep_sst_method=rsync
|
#innodb_autoinc_lock_mode=2 |
#
|
# Allow server to accept connections on all interfaces.
|
#
|
#port=133xx
|
#bind-address=0.0.0.0 |
#
|
# Optional setting
|
#wsrep_slave_threads=1 |
#innodb_flush_log_at_trx_commit=0 |
|
|
# this is only for embedded server |
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB, |
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.4 servers. |
# If you use the same .cnf file for MariaDB of different versions, |
# use this group for options that older servers don't understand |
[mariadb-10.5] |
|
This Is Slave Database
[server]
|
log_warnings = 1 |
server_id = 7136 #custom |
gtid-domain-id = 1 |
gtid_ignore_duplicates = ON
|
gtid_strict_mode = 0 |
rpl_semi_sync_master_enabled = ON
|
rpl_semi_sync_slave_enabled = ON
|
rpl_semi_sync_master_wait_point = AFTER_SYNC
|
slave-skip-errors = 1062,1032 |
|
|
#add new , replication config |
#sync_binlog = 1 |
#sync_master_info = 1 |
#sync_relay_log = 1 |
#sync_relay_log_info = 1 |
log_slave_updates = 1 |
|
|
# this is only for the mysqld standalone daemon |
[mysqld]
|
thread_handling = pool-of-threads
|
log-bin = /var/lib/mysqlbinlogs/mysql-bin #binlog
|
#skip-log-bin
|
datadir = /var/lib/mysql #data directory
|
lower_case_table_names = 1 #in-case sensitive |
sql-mode = "PIPES_AS_CONCAT" #closer to ANSI |
skip-host-cache #avoid dns lookup
|
skip-name-resolve #avoid dns lookup
|
log-slave-updates = 1 #binlog update each other |
query_cache_size = 0 #query cache is not supported with wsrep |
query_cache_type = 0 #query cache is not supported with wsrep |
|
|
# LIMIT #
|
net_buffer_length = 16384 |
max_allowed_packet = 1G
|
expire_logs_days = 3 |
max_connections = 10000 |
max_connect_errors = 1000 |
wait_timeout = 40 |
interactive_timeout = 40 |
max_statement_time = 60 |
open-files-limit = 393210 #65535 |
|
|
# INNODB #
|
default_storage_engine = InnoDB
|
innodb_data_home_dir = /var/lib/mysql
|
innodb_log_group_home_dir = /var/lib/mysql
|
innodb_file_per_table = 1 |
innodb_log_file_size = 2G #Normal value 2G
|
innodb_autoinc_lock_mode = 2 #avoid bulk insert using auto inc |
innodb_flush_log_at_trx_commit = 0 |
innodb_doublewrite = 1 |
binlog_format = ROW
|
log_bin_trust_function_creators = 1 |
|
|
# LOGGING #
|
log_error = /var/lib/mysql/mysql_error.log #error log
|
slow_query_log = 1 |
slow_query_log_file = /var/lib/mysql/mysql_slow.log
|
|
|
# CUSTOM #
|
port = 33301 |
bind-address = 0.0.0.0 |
#skip-log-bin #temporary disable if needed |
|
|
# Monitoring #
|
#innodb_monitor_enable=all
|
performance_schema = ON
|
|
|
|
|
## TUNING ##
|
innodb_buffer_pool_size = 4G
|
innodb_buffer_pool_instances = 4 |
innodb_buffer_pool_chunk_size = 128M
|
thread_cache_size = 256 |
join_buffer_size = 32M #64M
|
key_buffer_size = 128M
|
max_heap_table_size = 512M
|
tmp_table_size = 512M
|
table_open_cache = 2000 |
table_definition_cache = 400 |
innodb_flush_method = O_DIRECT #avoid double buffering |
## -- ##
|
|
|
|
|
# * Galera-related settings
|
#
|
[galera]
|
# Mandatory settings
|
#wsrep_on=OFF
|
#wsrep_provider_options="gcache.size=10G" |
#wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so |
#wsrep_cluster_address=gcomm:// |
#wsrep_cluster_address=
|
#wsrep_cluster_name=NBC-DataCentre_Reg
|
#wsrep_node_address=172.17.200.168 |
#wsrep_node_name=clusterreg168
|
#wsrep_sst_method=rsync
|
#innodb_autoinc_lock_mode=2 |
#
|
# Allow server to accept connections on all interfaces.
|
#
|
#port=133xx
|
#bind-address=0.0.0.0 |
#
|
# Optional setting
|
#wsrep_slave_threads=1 |
#innodb_flush_log_at_trx_commit=0 |
|
|
# this is only for embedded server |
[embedded]
|
|
|
# This group is only read by MariaDB servers, not by MySQL.
|
# If you use the same .cnf file for MySQL and MariaDB, |
# you can put MariaDB-only options here
|
[mariadb]
|
|
|
# This group is only read by MariaDB-10.4 servers. |
# If you use the same .cnf file for MariaDB of different versions, |
# use this group for options that older servers don't understand |
[mariadb-10.5] |
|
– EDITED
add maxscale.log
