Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-3325

LP #1516919: online upgrade GTID cause binlog damage in high write QPS situation

    Details

      Description

      **Reported in Launchpad by songjiao last update 29-03-2017 03:00:48

      percona verson:
      $ rpm -qa |grep -i percona
      Percona-Server-client-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-tokudb-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-devel-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-shared-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-server-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-test-56-5.6.27-rel75.0.el6.x86_64
      Percona-Server-56-debuginfo-5.6.27-rel75.0.el6.x86_64

      OS:
      $ cat /etc/redhat-release
      CentOS release 6.6 (Final)
      $ uname -a
      Linux 2.6.32-504.16.2.el6.x86_64 #1 SMP Wed Apr 22 06:48:29 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

      Server:
      aws c4.4xlarge with 3.4TB gp2 ssd volume

      Runing 2 mysql instance in one server 3306(master)<-3307(slave)

      Myql config file :
      ------------------------------------------------------------------------------------------------------------------------------------------
      [mysqld_multi]
      mysqld = /usr/bin/mysqld_safe
      mysqladmin = /usr/bin/mysqladmin

      [mysqld3306]
      socket = /data/mysqldata/mysql3306/mysql3306.sock
      port = 3306
      pid-file = /data/mysqldata/mysql3306/bjzw3306.pid
      datadir = /data/mysqldata/mysql3306
      user = mysql
      server-id = 1523306

      gtid_mode = ON
      gtid_deployment_step = ON
      log_slave_updates = 1
      enforce_gtid_consistency = 1

      log_error = /data/mysqllog/mysql3306/yxmysql-error.log
      log_bin = /data/mysqllog/mysql3306/yxmysql-bin.log
      relay_log = /data/mysqllog/mysql3306/yxmysql-relay-bin.log
      slow_query_log_file=/data/mysqllog/mysql3306/slow-queries.log

      log_bin_trust_function_creators = 1

      thread_handling = pool-of-threads
      thread_pool_max_threads = 100000
      thread_pool_size = 22

      slave-skip-errors=1062
      slave_compressed_protocol=ON

      log-slave-updates
      skip-slave-start

      innodb_file_per_table = 1
      innodb_file_format=BARRACUDA

      innodb_thread_concurrency = 0
      thread_cache_size = 32

      innodb_io_capacity = 20000
      innodb_read_io_threads = 8
      innodb_write_io_threads = 8

      innodb_log_file_size = 56M
      innodb_log_files_in_group = 3
      innodb_log_buffer_size = 8M

      innodb_buffer_pool_size = 56M
      innodb_buffer_pool_instances = 2

      query_cache_type = OFF

      long_query_time = 1
      slow_query_log = ON
      log-queries-not-using-indexes
      log-slow-admin-statements
      max_connect_errors = 200
      max_connections = 10000
      character_set_server = utf8mb4
      character-set-client-handshake = FALSE
      collation-server = utf8mb4_unicode_ci
      init_connect='SET NAMES utf8mb4'
      table_open_cache=10240
      open_files_limit=20480
      innodb_flush_log_at_trx_commit=0
      innodb_open_files=10000
      expire_logs_days = 7

      -------------------------------------------------END------------------------------------------------------------------------------------
      3307's conf is same with 3306 with diffrent server-id and port number

      How to replay:
      1set up mysql cluster as 3306(master)<--3307(slave)
      2create table and user for test in 3306
      create user test@127.0.0.1 identified by '123456';
      grant all on test.* to test@127.0.0.1;
      use test;
      create table seq_test(
      iid bigint(20) auto_increment,
      dt1 timestamp,
      dt2 timestamp default CURRENT_TIMESTAMP,
      primary key(iid));

      3create a script to load data into mysql
      $ cat /tmp/insert.py
      #!/usr/bin/python

      1. coding:utf-8

      import MySQLdb
      import datetime

      db = MySQLdb.connect('127.0.0.1','test','123456', '', 3306)
      cursor = db.cursor()
      cnt = 0
      while True:
      cursor.execute('''insert into test.seq_test(dt1) values('%s');''' %(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
      cnt = cnt + 1
      if cnt % 10000 == 0:
      print cnt
      db.commit()

      4run 3 load data script instance to load data
      python /tmp/insert.py &
      python /tmp/insert.py &
      python /tmp/insert.py &

      now the wirte qps nearly 30000

      5turn off gtid_deployment_step in master (3306)
      SET GLOBAL gtid_deployment_step = OFF;

      6execute "show slave status\G;" in 3307 shows:
      Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; the first event 'yxmysql-bin.000001' at 151, the last event read from '/data/mysqllog/mysql3306/yxmysql-bin.000001' at 464889689, the last byte read from '/data/mysqllog/mysql3306/yxmysql-bin.000001' at 464889708.'

      this because the binlog file in the master is damaged while turn GTID ON

      I have tried 3 times ,the first time is ok,but the last 2 times are meet binlog damage problem.

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                zsolt.parragi Zsolt Parragi
                Reporter:
                lpjirasync lpjirasync (Inactive)
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 3 hours, 44 minutes
                  3h 44m