cloud-bigdata-book
cloud-bigdata-book copied to clipboard
heidsoft
mysql 优化
- https://www.kancloud.cn/thinkphp/mysql-design-optimalize/39319
- https://www.percona.com/blog/how-mysql-queries-and-questions-are-measured/
- https://stackoverflow.com/questions/5814218/difference-between-questions-and-query
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 安装
主从同步测试
- 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;
- https://dev.mysql.com/doc/refman/5.6/en/grant.html
- https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Queries
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)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码'; ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
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
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;
内存占用排查 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/
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
慢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
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.
创建用户`
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)