heidsoft commented 7 years ago

mysql 表字段获取

mysql> SELECT t1.COLUMN_NAME AS "列名称", t2.DATA_TYPE AS "数据类型" FROM INFORMATION_SCHEMA.STATISTICS t1 LEFT JOIN INFORMATION_SCHEMA.COLUMNS t2 ON t1.COLUMN_NAME = t2.COLUMN_NAME AND t1.TABLE_NAME = t2.TABLE_NAME AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA;
+--------------------+-----------+
| column_name        | data_type |
+--------------------+-----------+
| Host               | char      |
| Db                 | char      |
| User               | char      |
| Table_name         | char      |
| Column_name        | char      |
| Host               | char      |
| Db                 | char      |
| User               | char      |
| User               | char      |
| engine_name        | varchar   |
| device_type        | int       |
| cost_name          | varchar   |
| db                 | char      |
| name               | char      |
| name               | char      |
| source_uuid        | char      |
| interval_start     | bigint    |
| help_category_id   | smallint  |
| name               | char      |
| help_keyword_id    | int       |
| name               | char      |
| help_topic_id      | int       |
| help_keyword_id    | int       |
| help_topic_id      | int       |
| name               | char      |
| database_name      | varchar   |
| table_name         | varchar   |
| index_name         | varchar   |
| stat_name          | varchar   |
| database_name      | varchar   |
| table_name         | varchar   |
| epoch              | bigint    |
| orig_server_id     | int       |
| orig_epoch         | bigint    |
| name               | varchar   |
| db                 | char      |
| name               | char      |
| type               | enum      |
| Host               | char      |
| Db                 | char      |
| User               | char      |
| Routine_name       | char      |
| Routine_type       | enum      |
| Grantor            | char      |
| Host               | char      |
| User               | char      |
| Proxied_host       | char      |
| Proxied_user       | char      |
| Grantor            | char      |
| cost_name          | varchar   |
| Server_name        | char      |
| Channel_name       | char      |
| Channel_name       | char      |
| Id                 | int       |
| Channel_name       | char      |
| Host               | char      |
| Db                 | char      |
| User               | char      |
| Table_name         | char      |
| Grantor            | char      |
| Time_zone_id       | int       |
| Transition_time    | bigint    |
| Name               | char      |
| Time_zone_id       | int       |
| Transition_time    | bigint    |
| Time_zone_id       | int       |
| Transition_type_id | int       |
| Host               | char      |
| User               | char      |
| variable           | varchar   |
+--------------------+-----------+

mysql> select * from INFORMATION_SCHEMA.COLUMNS limit 1\G;
*************************** 1. row ***************************
           TABLE_CATALOG: def
            TABLE_SCHEMA: information_schema
              TABLE_NAME: CHARACTER_SETS
             COLUMN_NAME: CHARACTER_SET_NAME
        ORDINAL_POSITION: 1
          COLUMN_DEFAULT: 
             IS_NULLABLE: NO
               DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 32
  CHARACTER_OCTET_LENGTH: 96
       NUMERIC_PRECISION: NULL
           NUMERIC_SCALE: NULL
      DATETIME_PRECISION: NULL
      CHARACTER_SET_NAME: utf8
          COLLATION_NAME: utf8_general_ci
             COLUMN_TYPE: varchar(32)
              COLUMN_KEY: 
                   EXTRA: 
              PRIVILEGES: select
          COLUMN_COMMENT: 
   GENERATION_EXPRESSION: 

mysql> select * from INFORMATION_SCHEMA.STATISTICS limit 1\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: mysql
   TABLE_NAME: columns_priv
   NON_UNIQUE: 0
 INDEX_SCHEMA: mysql
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: Host
    COLLATION: A
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT:

mysql 安装

mysql ubuntu安装

主从同步测试

flush tables with read lock; 设置锁定有效
unlock tables; 恢复写操作;

创建复制用户

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.59.202' IDENTIFIED by '1234test';
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.59.202' IDENTIFIED by '1234test';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> 

MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      245 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.59.202' IDENTIFIED by '1234test';
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
MariaDB [(none)]>

slave 配置与启用

[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='172.16.59.201',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='1234test',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=401;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show processlist \G;
*************************** 1. row ***************************
      Id: 2
    User: root
    Host: localhost
      db: NULL
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 12
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 2
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)

说明slave 已经连接上master

主库上插入测试数据

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      401 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> unlock tables; 
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> use test;
Database changed
MariaDB [test]> show tables;
Empty set (0.00 sec)

MariaDB [test]> create table repl_test(id int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> insert into repl_test values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

查看从库数据同步状态

Database changed
MariaDB [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| repl_test      |
+----------------+
1 row in set (0.00 sec)

MariaDB [test]> select * from repl_test;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

MariaDB [test]> show processlist \G;
*************************** 1. row ***************************
      Id: 2
    User: root
    Host: localhost
      db: test
 Command: Query
    Time: 0
   State: NULL
    Info: show processlist
Progress: 0.000
*************************** 2. row ***************************
      Id: 3
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 361
   State: Waiting for master to send event
    Info: NULL
Progress: 0.000
*************************** 3. row ***************************
      Id: 4
    User: system user
    Host: 
      db: NULL
 Command: Connect
    Time: 135
   State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Info: NULL
Progress: 0.000
3 rows in set (0.00 sec)

heidsoft commented 6 years ago

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码'; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

heidsoft commented 6 years ago

mysqldump -uroot -p密码 -P3306 --databases 数据库名 > 数据库.sql

heidsoft commented 5 years ago

mysql 数据同步测试

https://github.com/alibaba/canal/wiki/QuickStart

测试环境：mysql 8.0+ mac 
CREATE USER canal IDENTIFIED BY 'cana1@2WSX';
GRANT SELECT, SHOW VIEW, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
-- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ; 需要具有SHOW VIEW 权限
FLUSH PRIVILEGES;

数据库压力测试

CREATE USER qbench IDENTIFIED BY 'qbench'; GRANT ALL PRIVILEGES ON . TO 'qbench'@'%' ;

mysql> show grants for qbench
    -> ;
+------------------------------------+
| Grants for qbench@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'qbench'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'qbench'@'%' ;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for qbench
    -> ;
+---------------------------------------------+
| Grants for qbench@%                         |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'qbench'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for qbench;
+---------------------------------------------+
| Grants for qbench@%                         |
+---------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'qbench'@'%' |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql>  GRANT ALL PRIVILEGES ON *.* TO 'qbench'@'localhost'  IDENTIFIED BY 'qbench';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select Host,User from user;
+-----------------------+---------------+
| Host                  | User          |
+-----------------------+---------------+
| %                     | qbench        |
| %                     | test          |
| 127.0.0.1             | root          |
| ::1                   | root          |
| localhost             |               |
| localhost             | mysql.session |
| localhost             | mysql.sys     |
| localhost             | qbench        |
| localhost             | root          |
| localhost.localdomain |               |
| localhost.localdomain | root          |
+-----------------------+---------------+
11 rows in set (0.00 sec)

压测脚本执行

sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=abtest --tables=32 --table-size=5000000 oltp_read_write --db-ps-mode=disable prepare

本地虚拟机压测结果


[root@172 src]# sysbench --db-driver=mysql --time=180 --threads=2 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=abtest --tables=5 --table-size=5000000 oltp_read_write --db-ps-mode=disable run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 2
Report intermediate results every 1 second(s)
Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 2 tps: 287.46 qps: 5774.25 (r/w/o: 4047.46/1149.86/576.93) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 2 tps: 348.14 qps: 6953.88 (r/w/o: 4865.02/1392.58/696.29) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 2 tps: 364.40 qps: 7296.07 (r/w/o: 5109.64/1457.62/728.81) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 2 tps: 328.51 qps: 6557.22 (r/w/o: 4586.15/1314.05/657.02) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 2 tps: 348.99 qps: 6980.81 (r/w/o: 4887.86/1395.96/696.98) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 2 tps: 336.04 qps: 6713.74 (r/w/o: 4697.52/1344.15/672.07) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 2 tps: 353.95 qps: 7097.97 (r/w/o: 4973.28/1415.79/708.90) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 2 tps: 337.97 qps: 6773.47 (r/w/o: 4737.63/1359.89/675.95) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 2 tps: 349.09 qps: 6959.85 (r/w/o: 4873.30/1388.37/698.19) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 2 tps: 344.95 qps: 6897.02 (r/w/o: 4827.32/1379.80/689.90) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 2 tps: 342.70 qps: 6857.07 (r/w/o: 4800.85/1370.81/685.41) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 2 tps: 347.34 qps: 6961.83 (r/w/o: 4875.78/1391.36/694.68) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 13s ] thds: 2 tps: 354.77 qps: 7089.47 (r/w/o: 4958.83/1421.09/709.55) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 14s ] thds: 2 tps: 339.21 qps: 6774.11 (r/w/o: 4742.88/1352.82/678.41) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 2 tps: 337.59 qps: 6765.71 (r/w/o: 4736.20/1354.34/675.17) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 16s ] thds: 2 tps: 328.19 qps: 6540.88 (r/w/o: 4575.71/1308.78/656.39) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 17s ] thds: 2 tps: 294.09 qps: 5888.75 (r/w/o: 4124.23/1176.35/588.18) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 2 tps: 318.05 qps: 6372.01 (r/w/o: 4463.71/1272.20/636.10) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 19s ] thds: 2 tps: 326.06 qps: 6507.18 (r/w/o: 4550.83/1304.24/652.12) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 2 tps: 353.02 qps: 7069.41 (r/w/o: 4951.29/1412.08/706.04) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 2 tps: 331.00 qps: 6625.07 (r/w/o: 4639.05/1324.01/662.01) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 22s ] thds: 2 tps: 344.94 qps: 6904.86 (r/w/o: 4831.21/1383.77/689.89) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 23s ] thds: 2 tps: 323.03 qps: 6457.60 (r/w/o: 4520.42/1291.12/646.06) lat (ms,95%): 16.12 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 2 tps: 353.57 qps: 7079.43 (r/w/o: 4955.00/1417.28/707.14) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 25s ] thds: 2 tps: 319.40 qps: 6379.08 (r/w/o: 4468.66/1271.61/638.81) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 26s ] thds: 2 tps: 338.96 qps: 6778.14 (r/w/o: 4742.40/1357.83/677.91) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 2 tps: 338.85 qps: 6774.97 (r/w/o: 4743.88/1353.40/677.70) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 28s ] thds: 2 tps: 343.13 qps: 6864.54 (r/w/o: 4805.77/1372.51/686.25) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 29s ] thds: 2 tps: 336.88 qps: 6740.55 (r/w/o: 4716.28/1350.51/673.75) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 2 tps: 328.17 qps: 6536.40 (r/w/o: 4570.38/1309.68/656.34) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 31s ] thds: 2 tps: 326.98 qps: 6560.61 (r/w/o: 4594.73/1311.92/653.96) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 32s ] thds: 2 tps: 334.03 qps: 6684.59 (r/w/o: 4684.42/1332.12/668.06) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 33s ] thds: 2 tps: 332.97 qps: 6666.38 (r/w/o: 4664.57/1335.88/665.94) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 34s ] thds: 2 tps: 339.70 qps: 6776.03 (r/w/o: 4741.82/1354.81/679.40) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 35s ] thds: 2 tps: 348.34 qps: 6962.82 (r/w/o: 4872.77/1393.37/696.68) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 36s ] thds: 2 tps: 351.87 qps: 7045.43 (r/w/o: 4934.20/1407.49/703.74) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 37s ] thds: 2 tps: 343.81 qps: 6882.14 (r/w/o: 4819.30/1375.23/687.61) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 38s ] thds: 2 tps: 340.03 qps: 6805.57 (r/w/o: 4763.40/1362.11/680.06) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 39s ] thds: 2 tps: 334.26 qps: 6675.16 (r/w/o: 4667.61/1339.04/668.52) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 2 tps: 343.02 qps: 6865.44 (r/w/o: 4811.31/1368.09/686.04) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 41s ] thds: 2 tps: 344.88 qps: 6889.61 (r/w/o: 4820.33/1379.52/689.76) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 2 tps: 337.07 qps: 6753.41 (r/w/o: 4726.99/1352.28/674.14) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 43s ] thds: 2 tps: 332.98 qps: 6645.57 (r/w/o: 4651.70/1327.91/665.96) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 44s ] thds: 2 tps: 346.07 qps: 6907.39 (r/w/o: 4831.97/1384.28/691.14) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 45s ] thds: 2 tps: 337.98 qps: 6762.58 (r/w/o: 4733.70/1351.92/676.96) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 46s ] thds: 2 tps: 348.02 qps: 6981.56 (r/w/o: 4893.41/1392.10/696.05) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 47s ] thds: 2 tps: 313.97 qps: 6282.39 (r/w/o: 4396.57/1257.88/627.94) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 48s ] thds: 2 tps: 335.00 qps: 6689.09 (r/w/o: 4681.06/1338.02/670.01) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 49s ] thds: 2 tps: 321.94 qps: 6441.74 (r/w/o: 4510.12/1287.75/643.87) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 2 tps: 344.69 qps: 6909.70 (r/w/o: 4834.59/1385.74/689.37) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 2 tps: 346.36 qps: 6912.25 (r/w/o: 4837.08/1382.45/692.73) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 52s ] thds: 2 tps: 346.06 qps: 6911.19 (r/w/o: 4838.83/1380.24/692.12) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 53s ] thds: 2 tps: 333.01 qps: 6677.25 (r/w/o: 4678.18/1333.05/666.03) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 2 tps: 332.19 qps: 6641.78 (r/w/o: 4646.65/1330.75/664.38) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 55s ] thds: 2 tps: 330.77 qps: 6610.40 (r/w/o: 4625.77/1323.08/661.54) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 56s ] thds: 2 tps: 348.84 qps: 6980.88 (r/w/o: 4890.82/1392.38/697.69) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 2 tps: 346.12 qps: 6925.49 (r/w/o: 4848.74/1384.50/692.25) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 58s ] thds: 2 tps: 319.92 qps: 6400.40 (r/w/o: 4477.88/1282.68/639.84) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 59s ] thds: 2 tps: 336.11 qps: 6723.23 (r/w/o: 4707.56/1343.44/672.22) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 2 tps: 349.92 qps: 6999.45 (r/w/o: 4898.91/1400.69/699.84) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 61s ] thds: 2 tps: 342.02 qps: 6834.30 (r/w/o: 4782.21/1368.06/684.03) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 62s ] thds: 2 tps: 357.87 qps: 7151.44 (r/w/o: 5003.21/1432.49/715.74) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 63s ] thds: 2 tps: 351.24 qps: 7041.80 (r/w/o: 4930.36/1408.96/702.48) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 64s ] thds: 2 tps: 324.86 qps: 6479.19 (r/w/o: 4538.03/1291.44/649.72) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 2 tps: 358.08 qps: 7151.61 (r/w/o: 5003.13/1432.32/716.16) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 2 tps: 340.06 qps: 6808.15 (r/w/o: 4766.80/1361.23/680.11) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 67s ] thds: 2 tps: 355.95 qps: 7126.07 (r/w/o: 4991.35/1422.82/711.91) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 68s ] thds: 2 tps: 336.95 qps: 6717.00 (r/w/o: 4695.30/1347.80/673.90) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 69s ] thds: 2 tps: 335.07 qps: 6728.45 (r/w/o: 4717.02/1341.29/670.14) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 2 tps: 354.75 qps: 7090.91 (r/w/o: 4960.44/1420.98/709.49) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00
[ 71s ] thds: 2 tps: 337.19 qps: 6745.80 (r/w/o: 4721.66/1349.76/674.38) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 72s ] thds: 2 tps: 343.98 qps: 6866.55 (r/w/o: 4806.68/1371.91/687.95) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 73s ] thds: 2 tps: 346.05 qps: 6938.04 (r/w/o: 4859.73/1386.21/692.10) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 74s ] thds: 2 tps: 358.06 qps: 7158.21 (r/w/o: 5011.85/1430.24/716.12) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 2 tps: 345.87 qps: 6915.31 (r/w/o: 4837.12/1386.46/691.73) lat (ms,95%): 14.73 err/s: 0.00 reconn/s: 0.00
[ 76s ] thds: 2 tps: 331.12 qps: 6610.30 (r/w/o: 4626.61/1321.46/662.23) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 77s ] thds: 2 tps: 312.97 qps: 6266.37 (r/w/o: 4388.56/1251.87/625.94) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 78s ] thds: 2 tps: 339.06 qps: 6777.10 (r/w/o: 4742.77/1356.22/678.11) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 79s ] thds: 2 tps: 348.87 qps: 6988.47 (r/w/o: 4895.23/1395.50/697.75) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 2 tps: 341.87 qps: 6812.34 (r/w/o: 4761.14/1367.47/683.73) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 81s ] thds: 2 tps: 358.27 qps: 7192.35 (r/w/o: 5039.75/1436.07/716.53) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 82s ] thds: 2 tps: 370.92 qps: 7405.45 (r/w/o: 5182.92/1480.69/741.85) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 83s ] thds: 2 tps: 334.05 qps: 6682.00 (r/w/o: 4676.70/1337.20/668.10) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 84s ] thds: 2 tps: 354.96 qps: 7105.21 (r/w/o: 4973.45/1421.84/709.92) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 85s ] thds: 2 tps: 346.86 qps: 6939.21 (r/w/o: 4861.05/1384.44/693.72) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 86s ] thds: 2 tps: 360.96 qps: 7222.15 (r/w/o: 5055.41/1444.83/721.92) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 87s ] thds: 2 tps: 346.14 qps: 6912.83 (r/w/o: 4834.98/1385.57/692.28) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 88s ] thds: 2 tps: 356.76 qps: 7140.13 (r/w/o: 5001.59/1425.03/713.51) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 89s ] thds: 2 tps: 341.86 qps: 6828.11 (r/w/o: 4776.98/1367.42/683.71) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 2 tps: 338.46 qps: 6774.16 (r/w/o: 4743.41/1353.83/676.92) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 91s ] thds: 2 tps: 365.99 qps: 7329.87 (r/w/o: 5129.91/1467.97/731.99) lat (ms,95%): 10.09 err/s: 0.00 reconn/s: 0.00
[ 92s ] thds: 2 tps: 354.08 qps: 7069.56 (r/w/o: 4949.09/1412.32/708.15) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 93s ] thds: 2 tps: 357.55 qps: 7170.08 (r/w/o: 5017.76/1437.22/715.11) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 94s ] thds: 2 tps: 346.35 qps: 6911.94 (r/w/o: 4840.86/1378.38/692.70) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 95s ] thds: 2 tps: 351.95 qps: 7045.08 (r/w/o: 4929.36/1411.82/703.91) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 96s ] thds: 2 tps: 345.68 qps: 6920.50 (r/w/o: 4845.45/1383.70/691.35) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 97s ] thds: 2 tps: 336.38 qps: 6718.59 (r/w/o: 4703.31/1342.52/672.76) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 98s ] thds: 2 tps: 359.94 qps: 7195.85 (r/w/o: 5036.20/1439.77/719.89) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00
[ 99s ] thds: 2 tps: 346.06 qps: 6926.19 (r/w/o: 4849.83/1384.24/692.12) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 2 tps: 342.93 qps: 6858.56 (r/w/o: 4802.99/1369.71/685.86) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 101s ] thds: 2 tps: 361.00 qps: 7210.98 (r/w/o: 5044.98/1444.00/722.00) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 102s ] thds: 2 tps: 355.04 qps: 7102.89 (r/w/o: 4972.62/1420.18/710.09) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 103s ] thds: 2 tps: 349.76 qps: 7005.21 (r/w/o: 4902.65/1403.04/699.52) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 104s ] thds: 2 tps: 357.25 qps: 7135.92 (r/w/o: 4996.45/1424.98/714.49) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 2 tps: 362.90 qps: 7265.90 (r/w/o: 5085.53/1454.58/725.79) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 106s ] thds: 2 tps: 328.11 qps: 6547.24 (r/w/o: 4581.57/1309.45/656.22) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 107s ] thds: 2 tps: 307.86 qps: 6168.27 (r/w/o: 4321.09/1231.46/615.73) lat (ms,95%): 16.41 err/s: 0.00 reconn/s: 0.00
[ 108s ] thds: 2 tps: 358.50 qps: 7156.02 (r/w/o: 5005.02/1434.00/717.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 109s ] thds: 2 tps: 358.66 qps: 7190.17 (r/w/o: 5038.23/1434.63/717.31) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 2 tps: 331.98 qps: 6638.65 (r/w/o: 4646.76/1327.93/663.97) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 111s ] thds: 2 tps: 355.02 qps: 7095.49 (r/w/o: 4965.35/1420.10/710.05) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00
[ 112s ] thds: 2 tps: 355.91 qps: 7107.15 (r/w/o: 4971.71/1423.63/711.82) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 113s ] thds: 2 tps: 343.03 qps: 6877.55 (r/w/o: 4819.38/1372.11/686.05) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 114s ] thds: 2 tps: 346.03 qps: 6923.68 (r/w/o: 4843.47/1388.14/692.07) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 115s ] thds: 2 tps: 353.03 qps: 7058.63 (r/w/o: 4944.44/1408.13/706.06) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 116s ] thds: 2 tps: 358.97 qps: 7172.34 (r/w/o: 5018.54/1435.87/717.93) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 117s ] thds: 2 tps: 344.02 qps: 6887.32 (r/w/o: 4820.22/1379.06/688.03) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 118s ] thds: 2 tps: 352.70 qps: 7060.99 (r/w/o: 4940.80/1414.80/705.40) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 119s ] thds: 2 tps: 350.31 qps: 6990.20 (r/w/o: 4892.34/1397.24/700.62) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 2 tps: 358.95 qps: 7175.94 (r/w/o: 5025.26/1432.79/717.89) lat (ms,95%): 14.46 err/s: 0.00 reconn/s: 0.00
[ 121s ] thds: 2 tps: 357.82 qps: 7160.35 (r/w/o: 5013.45/1431.27/715.64) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 122s ] thds: 2 tps: 367.21 qps: 7344.10 (r/w/o: 5140.87/1468.82/734.41) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 123s ] thds: 2 tps: 348.03 qps: 6951.55 (r/w/o: 4864.38/1392.11/695.05) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 124s ] thds: 2 tps: 358.84 qps: 7197.87 (r/w/o: 5039.81/1439.37/718.69) lat (ms,95%): 9.73 err/s: 0.00 reconn/s: 0.00
[ 125s ] thds: 2 tps: 353.17 qps: 7047.38 (r/w/o: 4931.37/1409.68/706.34) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 126s ] thds: 2 tps: 356.00 qps: 7127.04 (r/w/o: 4992.03/1423.01/712.00) lat (ms,95%): 10.46 err/s: 0.00 reconn/s: 0.00
[ 127s ] thds: 2 tps: 350.96 qps: 7022.14 (r/w/o: 4913.40/1406.83/701.91) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 128s ] thds: 2 tps: 330.57 qps: 6612.31 (r/w/o: 4629.92/1321.26/661.13) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 129s ] thds: 2 tps: 362.49 qps: 7231.81 (r/w/o: 5058.86/1447.96/724.98) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 2 tps: 360.89 qps: 7235.73 (r/w/o: 5067.41/1446.55/721.77) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 131s ] thds: 2 tps: 360.15 qps: 7193.92 (r/w/o: 5032.04/1441.58/720.29) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 132s ] thds: 2 tps: 354.97 qps: 7083.43 (r/w/o: 4958.60/1415.89/708.94) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 133s ] thds: 2 tps: 356.00 qps: 7139.07 (r/w/o: 5002.05/1424.01/713.01) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 134s ] thds: 2 tps: 355.03 qps: 7096.57 (r/w/o: 4966.40/1420.11/710.06) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 2 tps: 354.96 qps: 7109.20 (r/w/o: 4976.44/1422.84/709.92) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 136s ] thds: 2 tps: 329.99 qps: 6589.88 (r/w/o: 4612.92/1316.98/659.99) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 137s ] thds: 2 tps: 280.55 qps: 5603.07 (r/w/o: 3920.75/1122.21/560.11) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[ 138s ] thds: 2 tps: 278.21 qps: 5572.30 (r/w/o: 3902.01/1112.86/557.43) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 139s ] thds: 2 tps: 318.11 qps: 6361.16 (r/w/o: 4452.51/1272.43/636.22) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 2 tps: 305.11 qps: 6114.29 (r/w/o: 4279.60/1224.46/610.23) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 141s ] thds: 2 tps: 315.80 qps: 6304.04 (r/w/o: 4413.23/1259.21/631.60) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 142s ] thds: 2 tps: 303.03 qps: 6068.54 (r/w/o: 4250.38/1212.11/606.05) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 143s ] thds: 2 tps: 318.17 qps: 6367.42 (r/w/o: 4458.39/1272.68/636.34) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 144s ] thds: 2 tps: 330.99 qps: 6615.81 (r/w/o: 4629.86/1323.96/661.98) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 145s ] thds: 2 tps: 314.04 qps: 6269.73 (r/w/o: 4385.51/1256.15/628.07) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 146s ] thds: 2 tps: 318.97 qps: 6391.41 (r/w/o: 4477.58/1275.88/637.94) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 147s ] thds: 2 tps: 306.01 qps: 6103.24 (r/w/o: 4267.17/1224.05/612.02) lat (ms,95%): 15.27 err/s: 0.00 reconn/s: 0.00
[ 148s ] thds: 2 tps: 322.91 qps: 6481.15 (r/w/o: 4539.70/1295.63/645.82) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 149s ] thds: 2 tps: 312.04 qps: 6222.77 (r/w/o: 4354.54/1244.15/624.08) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 2 tps: 322.09 qps: 6452.77 (r/w/o: 4516.24/1292.35/644.18) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 151s ] thds: 2 tps: 319.86 qps: 6402.19 (r/w/o: 4484.03/1278.44/639.72) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 152s ] thds: 2 tps: 322.79 qps: 6455.71 (r/w/o: 4520.00/1290.14/645.57) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 153s ] thds: 2 tps: 306.28 qps: 6118.52 (r/w/o: 4279.86/1226.11/612.55) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 154s ] thds: 2 tps: 336.84 qps: 6749.72 (r/w/o: 4724.70/1351.34/673.67) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 155s ] thds: 2 tps: 361.19 qps: 7211.74 (r/w/o: 5051.62/1437.75/722.37) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00
[ 156s ] thds: 2 tps: 308.05 qps: 6151.92 (r/w/o: 4303.64/1232.18/616.09) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 157s ] thds: 2 tps: 345.01 qps: 6897.21 (r/w/o: 4827.15/1380.04/690.02) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 158s ] thds: 2 tps: 343.88 qps: 6891.55 (r/w/o: 4828.29/1375.51/687.76) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 159s ] thds: 2 tps: 338.04 qps: 6750.90 (r/w/o: 4722.63/1352.18/676.09) lat (ms,95%): 10.65 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 2 tps: 308.03 qps: 6158.58 (r/w/o: 4310.41/1232.12/616.06) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[ 161s ] thds: 2 tps: 318.85 qps: 6391.96 (r/w/o: 4477.87/1276.39/637.70) lat (ms,95%): 14.21 err/s: 0.00 reconn/s: 0.00
[ 162s ] thds: 2 tps: 315.12 qps: 6295.41 (r/w/o: 4404.69/1260.48/630.24) lat (ms,95%): 12.75 err/s: 0.00 reconn/s: 0.00
[ 163s ] thds: 2 tps: 344.68 qps: 6898.62 (r/w/o: 4828.53/1380.72/689.36) lat (ms,95%): 12.08 err/s: 0.00 reconn/s: 0.00
[ 164s ] thds: 2 tps: 342.38 qps: 6834.60 (r/w/o: 4783.32/1366.52/684.76) lat (ms,95%): 12.52 err/s: 0.00 reconn/s: 0.00
[ 165s ] thds: 2 tps: 354.92 qps: 7110.48 (r/w/o: 4977.93/1422.70/709.85) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
[ 166s ] thds: 2 tps: 337.06 qps: 6730.25 (r/w/o: 4710.88/1345.25/674.13) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 167s ] thds: 2 tps: 312.96 qps: 6276.25 (r/w/o: 4394.47/1255.85/625.92) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 168s ] thds: 2 tps: 342.93 qps: 6852.52 (r/w/o: 4798.96/1367.70/685.85) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 169s ] thds: 2 tps: 332.07 qps: 6645.48 (r/w/o: 4650.03/1331.30/664.15) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 2 tps: 340.98 qps: 6823.61 (r/w/o: 4775.73/1365.92/681.96) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 171s ] thds: 2 tps: 366.07 qps: 7317.50 (r/w/o: 5123.05/1462.30/732.15) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 172s ] thds: 2 tps: 335.02 qps: 6688.46 (r/w/o: 4678.32/1341.09/669.05) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 173s ] thds: 2 tps: 336.99 qps: 6745.77 (r/w/o: 4726.84/1343.95/674.98) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 174s ] thds: 2 tps: 349.02 qps: 6975.49 (r/w/o: 4877.34/1400.10/698.05) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[ 175s ] thds: 2 tps: 351.92 qps: 7046.32 (r/w/o: 4938.82/1403.67/703.83) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 176s ] thds: 2 tps: 350.02 qps: 7002.35 (r/w/o: 4900.24/1402.07/700.03) lat (ms,95%): 11.87 err/s: 0.00 reconn/s: 0.00
[ 177s ] thds: 2 tps: 331.88 qps: 6623.51 (r/w/o: 4634.26/1325.50/663.75) lat (ms,95%): 13.70 err/s: 0.00 reconn/s: 0.00
[ 178s ] thds: 2 tps: 291.12 qps: 5830.38 (r/w/o: 4083.67/1164.48/582.24) lat (ms,95%): 15.00 err/s: 0.00 reconn/s: 0.00
[ 179s ] thds: 2 tps: 355.95 qps: 7131.09 (r/w/o: 4989.37/1429.82/711.91) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 2 tps: 348.01 qps: 6945.28 (r/w/o: 4863.20/1386.06/696.03) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            856324
        write:                           244664
        other:                           122332
        total:                           1223320
    transactions:                        61166  (339.79 per sec.)
    queries:                             1223320 (6795.71 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          180.0119s
    total number of events:              61166

Latency (ms):
         min:                                    2.82
         avg:                                    5.88
         max:                                   57.27
         95th percentile:                       12.98
         sum:                               359872.14

Threads fairness:
    events (avg/stddev):           30583.0000/57.00
    execution time (avg/stddev):   179.9361/0.00

heidsoft commented 5 years ago

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourRootPassword'; -- or CREATE USER 'foo'@'%' IDENTIFIED WITH mysql_native_password BY 'bar'; -- then FLUSH PRIVILEGES;

heidsoft commented 5 years ago

内存占用排查 https://www.percona.com/blog/2014/01/24/mysql-server-memory-usage-2/ https://www.percona.com/blog/2006/05/17/mysql-server-memory-usage/

heidsoft commented 1 year ago

mysql慢日志

什么是慢查询日志

临时假表使用

mysql> select 1/2*100 "Percent" from dual;
+---------+
| Percent |
+---------+
| 50.0000 |
+---------+
1 row in set (0.00 sec)

mysql>

SQL优化逻辑

先看数据列的基数（数据值的类型数量）
数据列值是否分布均衡（非常重要）
单表返回 5% 以内的数据走索引，超过5%的数据走全表扫描

数据值的基数获取

mysql> select count(distinct id),count(distinct name) from user;
+--------------------+----------------------+
| count(distinct id) | count(distinct name) |
+--------------------+----------------------+
|                  1 |                    1 |
+--------------------+----------------------+
1 row in set (0.00 sec)

mysql>

数据选择性

基数与总记录的比值

MySQL行锁

MySQL 是怎么加行级锁的时候，其实是在说 InnoDB 引擎是怎么加行级锁的

//对读取的记录加共享锁(S型锁)
select ... lock in share mode;

//对读取的记录加独占锁(X型锁)
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin 或者 start transaction 开启事务的语句。

//对操作的记录加独占锁(X型锁)
updaet table .... where id = 1;

//对操作的记录加独占锁(X型锁)
delete from table where id = 1;

锁的类型

Record Lock，记录锁，也就是仅仅把一条记录锁上；
Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

MySQL 导出数据

-T 表
-B 备份数据库
-t 线程数
-r 多少行
-c 压缩输出文件
--less-locking  在InnoDB表使用最小的锁表时间

mydumper  -h 127.0.0.1 -u root -p mypwd -B test  -t 4 -r 100000 -c --less-locking  -v 3 -D -L /var/log/mydumper.log   -o /opt/mydumper

heidsoft commented 1 year ago

慢SQL治理

https://mp.weixin.qq.com/s/CrIHmXWSqvVj7hB1alZliw?st=A22ADD7CCD8C15BB916481B90B8752DC39C9244FF617467B663528F5EC009EF5F289AB00F6EB8A116858DDE4DE48060A55EBEDB97D1A1AE2B551D9ECFC6EE2193B1541CA26B0567FB98498A638C43086BC5716E52514F1B28B2DE1A6B5636FEFF95A617206D2D8EE5C18E5CA49D92B594D15C68AC440130213EF9A0898A2ECF42DCE7D6D2F8359E4E4072731B13A95543843D0A256184E8E4A4F9E38A9281CCC3C7B1BE13A3908E980421F60214A383BDB4D7087D4A0D586D197962E0BFFD6EBAC3FA50D005FE6C13150C978B05A6D5CF5D0C4ED485F2B970DA47F520672A8D526ED0B62070B3566648ADE41D8518101&vid=1688851237720931&cst=C71113FF705CE97BAAE2DC4311B35A00374A62EB6D507A51B68E06C96D0AD64EAFC2793A3DB1BD7BE98FF79981AE9C5C&deviceid=5a533066-72d2-462b-a822-ff20926873f8&version=3.1.18.6007&platform=win

heidsoft commented 1 year ago

QPS与TPS

mysql> show  status like "questions";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show  status like "queries";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Queries       | 21241470 |
+---------------+----------+
1 row in set (0.00 sec)

https://opensource.actionsky.com/20210902-mysql/

Queries. The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands. This variable was added in MySQL 5.0.76.

Questions. The number of statements executed by the server. As of MySQL 5.0.72, this includes only statements sent to the server by clients and no longer includes statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

heidsoft commented 1 year ago

创建用户`

mysql> CREATE USER 'root'@'10.188.66.67' IDENTIFIED BY 'admin';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>   SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password_check_user_name    | OFF   |
| validate_password_dictionary_file    |       |
| validate_password_length             | 8     |
| validate_password_mixed_case_count   | 1     |
| validate_password_number_count       | 1     |
| validate_password_policy             | LOW   |
| validate_password_special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

mysql> select plugin_name, plugin_status from information_schema.plugins;
+----------------------------+---------------+
| plugin_name                | plugin_status |
+----------------------------+---------------+
| binlog                     | ACTIVE        |
| mysql_native_password      | ACTIVE        |
| sha256_password            | ACTIVE        |
| CSV                        | ACTIVE        |
| MEMORY                     | ACTIVE        |
| InnoDB                     | ACTIVE        |
| INNODB_TRX                 | ACTIVE        |
| INNODB_LOCKS               | ACTIVE        |
| INNODB_LOCK_WAITS          | ACTIVE        |
| INNODB_CMP                 | ACTIVE        |
| INNODB_CMP_RESET           | ACTIVE        |
| INNODB_CMPMEM              | ACTIVE        |
| INNODB_CMPMEM_RESET        | ACTIVE        |
| INNODB_CMP_PER_INDEX       | ACTIVE        |
| INNODB_CMP_PER_INDEX_RESET | ACTIVE        |
| INNODB_BUFFER_PAGE         | ACTIVE        |
| INNODB_BUFFER_PAGE_LRU     | ACTIVE        |
| INNODB_BUFFER_POOL_STATS   | ACTIVE        |
| INNODB_TEMP_TABLE_INFO     | ACTIVE        |
| INNODB_METRICS             | ACTIVE        |
| INNODB_FT_DEFAULT_STOPWORD | ACTIVE        |
| INNODB_FT_DELETED          | ACTIVE        |
| INNODB_FT_BEING_DELETED    | ACTIVE        |
| INNODB_FT_CONFIG           | ACTIVE        |
| INNODB_FT_INDEX_CACHE      | ACTIVE        |
| INNODB_FT_INDEX_TABLE      | ACTIVE        |
| INNODB_SYS_TABLES          | ACTIVE        |
| INNODB_SYS_TABLESTATS      | ACTIVE        |
| INNODB_SYS_INDEXES         | ACTIVE        |
| INNODB_SYS_COLUMNS         | ACTIVE        |
| INNODB_SYS_FIELDS          | ACTIVE        |
| INNODB_SYS_FOREIGN         | ACTIVE        |
| INNODB_SYS_FOREIGN_COLS    | ACTIVE        |
| INNODB_SYS_TABLESPACES     | ACTIVE        |
| INNODB_SYS_DATAFILES       | ACTIVE        |
| INNODB_SYS_VIRTUAL         | ACTIVE        |
| MyISAM                     | ACTIVE        |
| MRG_MYISAM                 | ACTIVE        |
| PERFORMANCE_SCHEMA         | ACTIVE        |
| ARCHIVE                    | ACTIVE        |
| BLACKHOLE                  | ACTIVE        |
| FEDERATED                  | DISABLED      |
| partition                  | ACTIVE        |
| ngram                      | ACTIVE        |
| auth_socket                | ACTIVE        |
| validate_password          | ACTIVE        |
+----------------------------+---------------+
46 rows in set (0.01 sec)

heidsoft / cloud-bigdata-book

mysql 优化 #21

mysql 表字段获取

mysql 安装

主从同步测试

创建复制用户

slave 配置与启用

主库上插入测试数据

查看从库数据同步状态

mysql 数据同步测试

数据库压力测试

压测脚本执行

本地虚拟机压测结果

mysql慢日志

临时假表使用

SQL优化逻辑

数据值的基数获取

数据选择性

MySQL行锁

锁的类型

MySQL 导出数据

慢SQL治理

QPS与TPS

创建用户`