mysql5.7LBS压测

CentOS release 6.9 (Final)

Kernel | 2.6.32-696.el6.x86_64

cpu 64xIntel(R) Xeon(R) Gold 6130 CPU @ 2.10GHz

内存 128G

bond0 2000Mb/s Full

磁盘 SSD

mysql版本

Server version: 5.7.22-22-log Percona Server (GPL), Release 22, Revision f62d93c

参数

innodb_buffer_pool_size=64G

sync_binlog=1

innodb_flush_log_at_trx_commit=1

对于 InnoDB 和 MyISAM 表，MySQL可以使用类似于创建常规索引的语法创建空间索引，但使用 SPATIAL 关键字。必须声明空间索引中的列 NOT NULL 。

SPATIAL INDEX 创建一个R树索引。对于支持空间列的非空间索引的存储引擎，引擎会创建B树索引。空间值的B树索引对于精确值查找很有用，但对于范围扫描则不行。

CREATE TABLE `tbl_pos` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`att1` int(11) DEFAULT NULL,

`att2` int(11) DEFAULT NULL,

`att3` int(11) DEFAULT NULL,

`mod_time` timestamp NULL DEFAULT NULL,

`pos` geometry NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

root@localhost:mysql3307.sock [db1] 11:27:46> insert into tbl_pos (att1, att2, att3, mod_time, pos) values ( 1, 1, 1, now(), ST_PointFromText('POINT(120 70)'));

Query OK, 1 row affected (0.01 sec)

root@localhost:mysql3307.sock [db1] 11:28:35> select *,ST_AsText(pos) from tbl_pos;

+----+------+------+------+---------------------+---------------------------+----------------+

| id | att1 | att2 | att3 | mod_time | pos | ST_AsText(pos) |

+----+------+------+------+---------------------+---------------------------+----------------+

| 1 | 1 | 1 | 1 | 2018-08-19 11:28:06 | ^@ Q@ | POINT(120 70) |

+----+------+------+------+---------------------+---------------------------+----------------+

未建R树索引下插入性能测试

[root@db001 ~]# ./mydbtest_linux64.bin query=insert.cnf degree=100

[root@db001 ~]# cat insert.cnf

option

name mysql57

loop 10000000

user ********************@10.11.5.1:3307:db1

show 15

tran yes

commit 10000

declare

att1 int 10 20

att2 int 10 20

att3 int 10 20

x int 120 130

y int 70 80

begin

insert into tbl_pos (att1, att2, att3, mod_time, pos) values ( :att1, :att2, :att3, now(), POINT(:x,:y) );

end

2018-08-19 11:58:51 Total tran=121294=1049/s, qtps=121294=1049/s, ela=115017 ms, avg=948 us

2018-08-19 11:58:51 Total tran=121167=1048/s, qtps=121167=1048/s, ela=115051 ms, avg=949 us

Summary: SQL01 exec=12133224, rows=12133224=100/e, avg=943 us

Summary: exec=103702/s, qtps= 103702/s

root@localhost:mysql3307.sock [db1] 11:57:39> select count(*) from tbl_pos;

+----------+

| count(*) |

+----------+

| 11000001 |

+----------+

未建R树索引的情况下插入更新

[root@db001 ~]# cat insertupdate.cnf

option

name mysql57

user **********@10.11.5.1:3307:db1

show 15

tran yes

commit 1

time m5

declare

id int 1 10000000

x int 120 130

y int 70 80

begin

insert into tbl_pos (id, mod_time, pos) values (:id, now(), POINT(:x,:y)) ON DUPLICATE KEY UPDATE pos=point(st_x(pos)+5-rand()*10, st_y(pos)+5-rand()*10), mod_time=now();

end

./mydbtest_linux64.bin query=insertupdate.cnf degree=20

Summary: SQL01 exec=7621675, rows=15243142=199/e, avg=162 us

Summary: exec=25237/s, qtps= 25237 /s

./mydbtest_linux64.bin query=insertupdate.cnf degree=50

Summary: SQL01 exec=12187675, rows=24374774=199/e, avg=253 us

Summary: exec=40356/s, qtps= 40356/s

./mydbtest_linux64.bin query=insertupdate.cnf degree=100

Summary: SQL01 exec=14250218, rows=28499628=199/e, avg=383 us

Summary: exec=47186/s, qtps= 47186/s

创建R索引

root@localhost:mysql3307.sock [db1] 13:00:20> alter table tbl_pos add SPATIAL KEY `idx_pos` (`pos`);

Query OK, 0 rows affected (3 min 54.37 sec)

Records: 0 Duplicates: 0 Warnings: 0

测试创建R索引情况下插入更新

2018-08-19 13:57:21 SQL01 143 ms exec= 1, ela= 142 ms, avg= 142024 us, pct= 0, 97

2018-08-19 13:57:21 SQL01 147 ms exec= 1, ela= 146 ms, avg= 146004 us, pct= 0, 98

2018-08-19 13:57:21 SQL01 168 ms exec= 1, ela= 167 ms, avg= 167473 us, pct= 0, 98

2018-08-19 13:57:21 SQL01 170 ms exec= 1, ela= 169 ms, avg= 169589 us, pct= 0, 98

2018-08-19 13:57:21 SQL01 171 ms exec= 1, ela= 170 ms, avg= 170377 us, pct= 0, 99

2018-08-19 13:57:21 SQL01 181 ms exec= 1, ela= 180 ms, avg= 180822 us, pct= 0, 99

2018-08-19 13:57:21 SQL01 215 ms exec= 1, ela= 214 ms, avg= 214500 us, pct= 0,100

插入更新 400多TPS

root@localhost:mysql3307.sock [db1] 14:00:16> alter table tbl_pos drop KEY `idx_pos`;

Query OK, 0 rows affected (10 min 50.44 sec)

Records: 0 Duplicates: 0 Warnings: 0

删掉索引后恢复正常

查询距离5公里范围的点

root@localhost:mysql3307.sock [db1] 15:01:50> explain select * from tbl_pos where ST_Contains(ST_Buffer(POINT(125,78),0.05), pos) order by ST_Distance_Sphere(Point(125,78), pos) limit 100;

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

| 1 | SIMPLE | tbl_pos | NULL | range | idx_pos | idx_pos | 34 | NULL | 150 | 100.00 | Using where; Using filesort |

+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+

1 row in set, 1 warning (0.00 sec)

select 5000*1/1852*1/60;

[root@db001 ~]# cat selectbygis.cnf

option

name mysql57

user ******************@10.11.5.1:3307:db1

show 15

tran no

commit 1

time m5

declare

x int 120 130

y int 70 80

begin

select * from tbl_pos where ST_Contains(ST_Buffer(POINT(:x,:y),0.04499640), pos) order by ST_Distance_Sphere(Point(:x,:y), pos) limit 100;

end

[root@db001 ~]# ./mydbtest_linux64.bin query=selectbygis.cnf degree=20

Summary: SQL01 exec=575872, rows=57587200=10000/e, avg= 10448 us

Summary: exec=1906/s, qtps= 1906/s

mysql的GIS还有比较远的路要走。