使用 sysbench 來取得 ProxySQL 效能差異

在之前筆記 使用 ProxySQL 來簡化 MySQL 的讀寫分離 提到需要進行壓力測試取得透過 ProxySQL 與直接存取 MySQL 的效能數據差來評估是否採用 ProxySQL

今天就來紀錄使用 sysbench 來進行 ProxySQL 與 MySQL 的效能差異比較

至於 sysbench,相信以我的理解可能只會誤導,還是請大家自行查閱 akopytov/sysbench

基本環境說明

  1. macOS Big Sur 11.4
  2. docker desktop 3.3.0 (62916)
  3. sysbench 1.0.20

    brew install sysbench
    
  4. docker images

    • bitnami/mysql:8.0
    • proxysql/proxysql:2.1.0
  5. MySQL replication 與 ProxySQL 的安裝

    透過 bitnami/bitnami-docker-mysql 提供的 yaml 加上 proxysql 來快速建立環境

    • proxysql.cnf

      • 多給一組帳密 radmin:radmin 方便從 host 上連線至 proxysql;預設的 admin:admin 必需進到 proxysql 的 container 中使用,且需要自行安裝 mysql client

      • 其中的 monitor_usernamemonitor_password 跟後續建立 monitor 用的 user 帳號密碼需相同,這邊以 monitor:pass.123 為例

      datadir="/var/lib/proxysql"
      
      admin_variables=
      {
          admin_credentials="admin:admin;radmin:radmin"
          mysql_ifaces="0.0.0.0:6032"
      }
              
      mysql_variables=
      {
          threads=4
          max_connections=2048
          default_query_delay=0
          default_query_timeout=36000000
          have_compress=true
          poll_timeout=2000
          interfaces="0.0.0.0:6033"
          default_schema="information_schema"
          stacksize=1048576
          server_version="5.5.30"
          connect_timeout_server=3000
          monitor_username="monitor"
          monitor_password="pass.123"
          monitor_history=600000
          monitor_connect_interval=60000
          monitor_ping_interval=10000
          monitor_read_only_interval=1500
          monitor_read_only_timeout=500
          ping_interval_server_msec=120000
          ping_timeout_server=500
          commands_stats=true
          sessions_sort=true
          connect_retries_on_failure=10
      }
      
    • docker-compose.yml

      version: '2.1'
              
      services:
      mysql-master:
          image: docker.io/bitnami/mysql:8.0
          ports:
          - '3306:3306'
          volumes:
          - 'mysql_master_data:/bitnami/mysql/data'
          environment:
          - MYSQL_REPLICATION_MODE=master
          - MYSQL_REPLICATION_USER=admin
          - MYSQL_REPLICATION_PASSWORD=pass.123
          - MYSQL_USER=yowko
          - MYSQL_PASSWORD=pass.123
          - MYSQL_DATABASE=test
          # ALLOW_EMPTY_PASSWORD is recommended only for development.
          #- ALLOW_EMPTY_PASSWORD=yes
          - MYSQL_ROOT_PASSWORD=pass.123
          healthcheck:
          test: ['CMD', '/opt/bitnami/scripts/mysql/healthcheck.sh']
          interval: 15s
          timeout: 5s
          retries: 6
              
      mysql-slave:
          image: docker.io/bitnami/mysql:8.0
          ports:
          - '3307:3307'
          depends_on:
          - mysql-master
          environment:
          - MYSQL_REPLICATION_MODE=slave
          - MYSQL_REPLICATION_USER=admin
          - MYSQL_REPLICATION_PASSWORD=pass.123
          - MYSQL_USER=yowko
          - MYSQL_PASSWORD=pass.123
          - MYSQL_DATABASE=test
          - MYSQL_MASTER_HOST=mysql-master
          - MYSQL_MASTER_PORT_NUMBER=3306
          - MYSQL_MASTER_ROOT_PASSWORD=pass.123
          # ALLOW_EMPTY_PASSWORD is recommended only for development.
          # - ALLOW_EMPTY_PASSWORD=yes
          healthcheck:
          test: ['CMD', '/opt/bitnami/scripts/mysql/healthcheck.sh']
          interval: 15s
          timeout: 5s
          retries: 6
      proxysql:
          image: proxysql/proxysql:2.1.0
          ports:
          - '6032:6032'
          - '6033:6033'
          - '6070:6070'
          volumes:
          - './proxysql.cnf:/etc/proxysql.cnf' 
          depends_on:
          - mysql-master
          - mysql-slave
              
      volumes:
      mysql_master_data:
          driver: local
      
  6. ProxySQL 讀寫分離設定請參考之前筆記 使用 ProxySQL 來簡化 MySQL 的讀寫分離

執行測試

/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua 這是 macOS 上的腳本位置,不同 os 可能有所不同

  1. 針對 MySQL

    • 前置準備

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 --mysql-port=3306 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      prepare
      

      1mysqlprepare

    • 執行測試

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 --mysql-port=3306 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      run
      

      2mysqlrun

    • 清理資料

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 --mysql-port=3306 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      cleanup
      

      3mysqlcleanup

  2. 針對 ProxySQL

    • 前置準備

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 \
      --mysql-port=6033 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      prepare
      

      4proxysqlprepare

    • 執行測試

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 \
      --mysql-port=6033 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      run
      

      5proxysqlrun

    • 清理資料

      sysbench /usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua \
      --oltp-table-size=10000 \
      --mysql-table-engine=innodb \
      --oltp-tables-count=10  \
      --mysql-db=test \
      --mysql-user=proxysql \
      --mysql-password=pass.123 \
      --mysql-port=6033 \
      --mysql-host=127.0.0.1 \
      --max-requests=0 \
      --time=10 \
      --report-interval=1 \
      --threads=10 \
      --oltp-point-selects=1 \
      --oltp-simple-ranges=0 \
      --oltp_sum_ranges=0 \
      --oltp_order_ranges=0 \
      --oltp_distinct_ranges=0 \
      --oltp-read-only=off \
      cleanup
      

      6proxysqlcleanup

測試數據

針對 MySQL 與 ProxySQL 各執行三次測試,每次測試完都會執行 cleanup,詳細測試數據如下

次數MySQLProxySQL
17mysql18proxysql1
210mysql29proxysql2
311mysql312proxysql3

心得

效能數據看起來是互有勝負 (MySQL 2勝 1敗),平均來看應該 MySQL 數據比較好,雖然在預料之中,但卻沒想到 ProxySQL 也有一勝,這樣看來要不是測試不夠準確就是 ProxySQL 對於效能的影響不大,目前先紀錄使用與設定方式,待後續與同事討論再評估下一步

參考資訊

  1. 使用 ProxySQL 來簡化 MySQL 的讀寫分離
  2. akopytov/sysbench
  3. MySQL benchmark tool - sysbench
  4. 性能测试–【MySQL】Sysbench 性能压测