mysql学习

[huhongbo]

了解mysql的基本，测试onesql和mysql的性能

[aricy]

通过benchmark工具测试TPC-C，在不更改任何参数时，发现每秒处理事务数差不多，没有提升。但响应时间有提升，提升约20%左右。 Mysql and OneSql (version 5.6.21) RUN 1: MySql RUN 2: OneSql OS:Ubuntu 14.04.1 LTS Scale:1 Session:10,20,30,40,50

[huhongbo]

不错，是否测试下文档中提到的队列限流/热点隔离/数据保护/事务提升/复制优化的性能，可以把oracle的测试方法用到mysql里面去，比如事务，并发，看看

[aricy]

可以，不过不一定测出效果，机器性能不好。

[aricy]

create table split_insert (object_id bigint,object_name varchar(50),rowid bigint) engine innodb; DELIMITER // set autocommit = 0; 分行提交 CREATE PROCEDURE p_split_insert(IN p_commit int)
BEGIN declare l_count bigint;
declare i bigint; set l_count = 10000000; set i = 1; while ( i <= l_count) do insert into split_insert values (11111111,'aaaaaaaa',i); set i = i + 1; if ( mod(i,p_commit) = 0 ) then commit; end if; end while; END;
//

分行UPDATE DELIMITER // set autocommit = 0; CREATE PROCEDURE p_split_update(IN p_update int)
BEGIN declare l_count bigint; set l_count = 1; while ( l_count = 1) do update split_insert set rowid=999999999 where rowid <> 999999999 limit p_update; commit; set l_count = 0; select 1 into l_count from split_insert where rowid <> 999999999 limit 1; end while; END;
//
DELIMITER ;

[aricy]

版本(ONESql / Mysql) : 5.6.21 为保证所有参数都保持默认值，所以只调整基本参数 innodb_log_files_in_group=4 innodb_buffer_pool_size=2147483648 key_buffer_size=419430400 场景1 ： 向表中插入10,000,000行数据 ENGINE ：INNODB 1、每1000条插入提交一次 ONESQL 时长：4 min 44.25 sec MYSQL 时长：4 min 16.94 sec

2、每10000条插入提交一次 ONESQL 时长：4 min 35.33 sec MYSQL 时长：3 min 51.30 sec

2、每50000条插入提交一次 ONESQL 时长：4 min 18.36 sec MYSQL 时长：4 min 9.97 sec

场景2 ： 向表中插入1,000,000行数据 版本 : 5.6.21 表ENGINE ：INNODB 1、每1000条更新提交一次 ONESQL 时长：10 min 8.42 sec MYSQL 时长：12 min 6.02 sec

2、每10000条更新提交一次 ONESQL 时长：1 min 5.56 sec MYSQL 时长：1 min 17.21 sec

2、每50000条更新提交一次 ONESQL 时长：19.21 sec MYSQL 时长：20.43 sec

[aricy]

最近比较忙，大致测试了下，发现ONESql 和 Mysql 差距不大。后续测试并发性能对比。

[aricy]

并行插入场景：

下面不分批提交，均为一次性提交

10线程并行插入1,000,000数据 ONESql : 3'25'' begin time : Fri Dec 26 14:38:03 CST 2014 end time : Fri Dec 26 14:41:28 CST 2014 Mysql : 3'43'' begin time : Fri Dec 26 15:10:51 CST 2014

end time : Fri Dec 26 15:14:34 CST 2014

20线程并行插入1,000,000数据 ONESql : 4'02'' begin time : Fri Dec 26 14:43:44 CST 2014 end time : Fri Dec 26 14:47:46 CST 2014 Mysql : 4'02'' begin time : Fri Dec 26 15:15:28 CST 2014

end time : Fri Dec 26 15:20:06 CST 2014

30线程并行插入1,000,000数据 ONESql : 4'47'' begin time : Fri Dec 26 14:50:17 CST 2014 end time : Fri Dec 26 14:55:04 CST 2014 Mysql : 5'34'' begin time : Fri Dec 26 15:21:32 CST 2014

end time : Fri Dec 26 15:26:56 CST 2014

40线程并行插入1,000,000数据 ONESql : 4'51'' begin time : Fri Dec 26 14:56:34 CST 2014 end time : Fri Dec 26 15:01:25 CST 2014 Mysql : 5'44'' begin time : Fri Dec 26 15:27:35 CST 2014

end time : Fri Dec 26 15:33:19 CST 2014

50线程并行插入1,000,000数据 ONESql : 5'01'' begin time : Fri Dec 26 15:02:51 CST 2014 end time : Fri Dec 26 15:07:52 CST 2014 Mysql : 5'52'' begin time : Fri Dec 26 15:33:47 CST 2014

end time : Fri Dec 26 15:39:39 CST 2014

80线程并行插入1,000,000数据 ONESql : 05'15'' begin time : Fri Dec 26 15:58:24 CST 2014 end time : Fri Dec 26 16:03:41 CST 2014 Mysql : 6:05 begin time : Fri Dec 26 15:40:23 CST 2014 end time : Fri Dec 26 15:46:28 CST 2014

[aricy]

[aricy]

ONESql 在并发上面应该是做过优化的，通过对比发现，ONESql在并发上面（测试数据为30并发以上）都要比Mysql原始版本要快，提升有20%以上。这个提升在TPCC中也有体现。不过因为主机上的IO WAIT已经达30，所以不再继续进行测试。

[aricy]

表达有误 ，应该是IDLE 在30%，使用率已达70%。

[aricy]

ONESQL热点隔离测试（thread:100 DO:update）： tcc_max_sub_query_concurrency = 4
tcc_max_sub_transaction_concurrency = 3 未使用trx_queue = 500 开始时间：2015/02/04 15:53:18 结束时间：2015/02/04 15:57:01 持续时间：3'43''

tcc_max_sub_query_concurrency = 4
tcc_max_sub_transaction_concurrency = 3 使用trx_queue = 500 开始时间：2015/02/04 16:01:01 结束时间：2015/02/04 16:04:43 持续时间：3'42''

tcc_max_sub_query_concurrency = 16 (max_value)
tcc_max_sub_transaction_concurrency = 16 (max_value)
使用trx_queue = 500 开始时间：2015/02/04 16:08:40 结束时间：2015/02/04 16:12:18 持续时间：3'32''

tcc_max_sub_query_concurrency = 16 (max_value)
tcc_max_sub_transaction_concurrency = 16 (max_value)
使用trx_queue = 16 开始时间：2015/02/04 16:16:10 结束时间：2015/02/04 16:19:51 持续时间：2'21'' 猜测：trx_queue 应该是与tcc_max_sub_query_concurrency 参数对应的，设置超过tcc_max_sub_query_concurrency，应该是无效的。

[aricy]

附上一张并行时的进程信息：

[aricy]

ONESQL事务提升测试，打开另一会话，对当前操作会话进行KILL，满足要求。 1、auto_commit命令成功执行则提交 mysql> SELECT * FROM SPLIT_INSERT where rowid=5; +-----------+-------------+-------+ | object_id | object_name | rowid | +-----------+-------------+-------+ | 11111111 | aaaaaaaa | 5 | | 11111111 | aaaaaaaa | 5 | +-----------+-------------+-------+ 2 rows in set (1.23 sec)

mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> update [auto_commit] split_insert set object_id=222222222 where rowid=5; Query OK, 2 rows affected (2.23 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql> commit; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 728 Current database: onesql

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM SPLIT_INSERT where rowid=5; +-----------+-------------+-------+ | object_id | object_name | rowid | +-----------+-------------+-------+ | 222222222 | aaaaaaaa | 5 | | 222222222 | aaaaaaaa | 5 | +-----------+-------------+-------+ 2 rows in set (1.26 sec) 2、auto_rollback命令失败则rollback mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> update [auto_rollback] split_insert set object_id=11111111 where rowid=5;
Query OK, 2 rows affected (2.33 sec) Rows matched: 2 Changed: 2 Warnings: 0

mysql> commit; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 729 Current database: onesql

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM SPLIT_INSERT where rowid=5; +-----------+-------------+-------+ | object_id | object_name | rowid | +-----------+-------------+-------+ | 222222222 | aaaaaaaa | 5 | | 222222222 | aaaaaaaa | 5 | +-----------+-------------+-------+ 2 rows in set (1.25 sec)

mysql>

3、只要命令执行成功，则自动提交事务，不管是否进行commit session 1: mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> update [auto_commit auto_rollback] split_insert set object_id=222222 where rowid=5;
Query OK, 2 rows affected (2.25 sec) Rows matched: 2 Changed: 2 Warnings: 0

session 2: mysql> select distinct object_id from split_insert; +-----------+ | object_id | +-----------+ | 11111111 | | 222222 |

4、如果更新超过1行，而affect_rows不与更新行数相等，则回滚，并报错。 mysql> select rowid,object_id from split_insert where rowid=5;
+-------+-----------+ | rowid | object_id | +-------+-----------+ | 5 | 333333 | | 5 | 333333 | +-------+-----------+ 2 rows in set (1.30 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec)

mysql> update [auto_commit auto_rollback affect_rows 1] split_insert set object_id=2222222 where rowid=5; ERROR 5000 (HY000): Affected rows does not match, require 1, real affected rows is 2. mysql> update [auto_commit auto_rollback affect_rows 2] split_insert set object_id=2222222 where rowid=5; Query OK, 2 rows affected (2.28 sec) Rows matched: 2 Changed: 2 Warnings: 0

[aricy]

限制模式：修改tcc_login_restrict_mode值0、2都达到了预期效果，可以满足。 mysql> show variables like 'tcc_login%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | tcc_login_restrict_mode | 2 | +-------------------------+-------+ 1 row in set (0.00 sec)

mysql> \q Bye wangbin1:/usr/local/mysql # mysql -utest -ptest Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'test'@'localhost' (using password: Restricted Mode) wangbin1:/usr/local/mysql # mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 193 Server version: 5.6.22 (OneSQL) Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql> set global tcc_login_restrict_mode=0; Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

mysql> \q Bye wangbin1:/usr/local/mysql # mysql -utest -ptest Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 259 Server version: 5.6.22 (OneSQL) Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

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

mysql>