Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-4374

Not Balanced Connection Between Master & Slave From monitor

    XMLWordPrintable

Details

    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

      Attachments

        Activity

          People

            markus makela markus makela
            febridev febriyant
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.