MySQL 主从同步延迟的测试与重复数据的探讨 (ab 压力测试)

Linux系统： CentOS7.2
mySQL版本： mySQL5.7.32

MySQL的主从复制都是单线程的操作，
主库对所有 DDL 和 DML 产生的日志写进 binlog，由于 binlog 是顺序写，所以效率很高。
Slave 的 SQL Thread 线程将主库的 DDL 和 DML 操作事件在slave中重放。
DML 和 DDL 的 IO 操作是随即的，不是顺序的，成本高很多。

另一方面，由于SQL Thread 也是单线程的，
当主库的并发较高时，产生的 DML 数量超过 slave 的 SQL Thread 所能处理的速度，
或者当 slave 中有大型 query 语句产生了锁等待那么延时就产生了

有朋友会问：“主库上那个相同的 DDL 也需要执行 10 分，为什么 slave 会延时？”
答案是 : master 可以并发，Slave_SQL_Running 线程却不可以

1. show master status\G; 	# 查看主库的 position 号记录到多少了.
2. 从库中执行 show slave status\G; 	# 查看从库现在获取到哪个 position 号了.
3. 如果从库的 postion 号远小于主库的 position 号,则表示主库 dump 线程传送二进制出问题了.

   NULL，表示 io_thread 或是 sql_thread 有任何一个发生故障；
   0，该值为零，表示主从复制良好；
   正值，表示主从已经出现延时，数字越大表示从库延迟越严重。

function testMysql(){
        $randID = rand(300,500);
        $exitTag = Db::name('xtest_logs')
            ->where('open_id',"$randID")->count('id');
        if ($exitTag){
            return 0;
        }else{
            $op_id = Db::name('xtest_logs')
                ->insertGetId(['open_id'=>"$randID",'add_time'=>time()]);
            try{
                //TODO 查询数据库里是否存在这个新加入的 记录
                $count = Db::name('xtest_logs')
                    ->where('id',intval($op_id))
                    ->count('id');
                //判断是否存在 $op_id 的记录，都更新到 name 字段
                $msg = ($count>0) ? $op_id: "--$count--";
            }catch (\Exception $e){
                $msg = substr($e->getMessage(),0,300);
            }
            Db::name('xtest_logs')
                ->where('id',$op_id)
                ->update(['name' => $msg]);
            return 1;
        }
    }

Server Software:        nginx/1.15.11
Server Hostname:        tp5pro.com
Server Port:            80

Document Path:          /index/test
Document Length:        37957 bytes

Concurrency Level:      15
Time taken for tests:   134.282 seconds
Complete requests:      10000
Failed requests:        9919
   (Connect: 0, Receive: 0, Length: 9919, Exceptions: 0)
Total transferred:      376049112 bytes
HTML transferred:       374419112 bytes
Requests per second:    74.47 [#/sec] (mean)
Time per request:       201.423 [ms] (mean)
Time per request:       13.428 [ms] (mean, across all concurrent requests)
Transfer rate:          2734.81 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0      15
Processing:    38  201  97.4    190     912
Waiting:       19   79  85.0     63     823
Total:         38  201  97.4    190     912

Percentage of the requests served within a certain time (ms)
  50%    190
  66%    218
  75%    233
  80%    241
  90%    264
  95%    295
  98%    616
  99%    702
 100%    912 (longest request)

Server Software:        nginx/1.15.11
Server Hostname:        tp5pro.com
Server Port:            80

Document Path:          /index/test
Document Length:        37962 bytes

Concurrency Level:      15
Time taken for tests:   146.288 seconds
Complete requests:      10000
Failed requests:        9998
   (Connect: 0, Receive: 0, Length: 9998, Exceptions: 0)
Total transferred:      376055076 bytes
HTML transferred:       374425076 bytes
Requests per second:    68.36 [#/sec] (mean)
Time per request:       219.431 [ms] (mean)
Time per request:       14.629 [ms] (mean, across all concurrent requests)
Transfer rate:          2510.41 [Kbytes/sec] received

Connection Times (ms)
              min  mean[+/-sd] median   max
Connect:        0    0   0.3      0       8
Processing:    35  219  93.8    210     857
Waiting:       18  103  85.8     83     790
Total:         35  219  93.7    210     857

Percentage of the requests served within a certain time (ms)
  50%    210
  66%    235
  75%    251
  80%    260
  90%    282
  95%    313
  98%    617
  99%    694
 100%    857 (longest request)