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

Binlog router causing database slowness

    XMLWordPrintable

Details

    Description

      Situation is a local cluster of 2 nodes, 2 mxs nodes with configured with keepalived, and an external skysql instance.

      external db is connecting to maxscales vip via a binlog router

      ┌─────────────────────┬────────────────────────────────────────────────────────────────────────┐
      │ Service             │ Replication-Proxy                                                      │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Router              │ binlogrouter                                                           │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ State               │ Started                                                                │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Started At          │ Tue Sep  6 16:11:38 2022
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Current Connections │ 0
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Total Connections   │ 190653
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Max Connections     │ 3
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Cluster             │ MariaDB-Monitor                                                        │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Servers             │                                                                        │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Services            │                                                                        │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Filters             │                                                                        │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Parameters          │ {                                                                      │
      │                     │     "auth_all_servers": false,                                         │
      │                     │     "cluster": "MariaDB-Monitor",                                      │
      │                     │     "connection_keepalive": 300,                                       │
      │                     │     "connection_timeout": 0,                                           │
      │                     │     "datadir": "/var/lib/maxscale/binlogs/",                           │
      │                     │     "enable_root_user": false,                                         │
      │                     │     "expire_log_duration": 172800000,                                  │
      │                     │     "expire_log_minimum_files": 16,                                    │
      │                     │     "localhost_match_wildcard_host": true,                             │
      │                     │     "log_auth_warnings": true,                                         │
      │                     │     "max_connections": 0,                                              │
      │                     │     "net_timeout": 10000,                                              │
      │                     │     "net_write_timeout": 0,                                            │
      │                     │     "password": "*****",                                               │
      │                     │     "purge_poll_timeout": 120000,                                      │
      │                     │     "purge_startup_delay": 120000,                                     │
      │                     │     "rank": "primary",                                                 │
      │                     │     "retain_last_statements": -1,                                      │
      │                     │     "router_options": null,                                            │
      │                     │     "select_master": true,                                             │
      │                     │     "server_id": 109019485,                                            │
      │                     │     "session_trace": false,                                            │
      │                     │     "session_track_trx_state": false,                                  │
      │                     │     "strip_db_esc": true,                                              │
      │                     │     "targets": null,                                                   │
      │                     │     "user": "maxscale",                                                │
      │                     │     "version_string": null
      │                     │ }                                                                      │
      ├─────────────────────┼────────────────────────────────────────────────────────────────────────┤
      │ Router Diagnostics  │ {                                                                      │
      │                     │     "current_binlog": "/var/lib/maxscale/binlogs//mariadb-bin.000313", │
      │                     │     "gtid_io_pos": "0-2-10323413",                                     │
      │                     │     "master_config": {                                                 │
      │                     │         "host": "10.90.194.27",                                        │
      │                     │         "port": 3306,                                                  │
      │                     │         "ssl": false,                                                  │
      │                     │         "user": "maxscale"
      │                     │     }                                                                  │
      │                     │ }                                                                      │
      └─────────────────────┴────────────────────────────────────────────────────────────────────────┘
      

      When replication is enabled these errors spam.

      2022-10-07 19:45:19   error  : Write to Client DCB 10.15.1.115 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:49:14   error  : Write to Client DCB 10.15.1.115 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:58:44   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:58:46   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:58:49   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:58:53   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:58:55   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:59:04   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      2022-10-07 19:59:07   error  : Write to Client DCB 10.15.1.111 in state DCB::State::POLLING failed: 104, Connection reset by peer
      

      Cause is a spike in CPU usage on the database and overall slowness in the DB.

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              bbancroft Bryan Bancroft (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.