mysql5.7LBS压测

最新推荐文章于 2023-04-27 17:15:45 发布

转载最新推荐文章于 2023-04-27 17:15:45 发布 · 202 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/633941

文章标签：

#数据库

本文通过在CentOS 6.9上使用Percona Server 5.7.22进行了一系列GIS性能测试，包括创建R树索引前后的插入及更新操作性能对比，并评估了空间索引对查询效率的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

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

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

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

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