怎样用mysqlslap压测StarRocks

秦凯旋

2023-12-01

概要

我们知道StarRocks通过FE节点承接mysql连接，线上服务需要配置LoadBalance方案将query请求proxy到3台(或以上)的FE节点，来扩展并发连接能力。在合理的规划节点和资源下，可以在高并发点查场景下做到数千甚至上万QPS。
本文背景为在没有配置配置load balance的情况下，采用mysqlslap指向某台fe进行压测
另外，具备条件的，也可以采用jmeter工具或自己编辑并发脚本指向多个fe，随机传入分区、分桶或前缀索引参数，以压测在随机负荷下的并发能力

session级别FE配置

生产实践证明，单个FE的连接数最高不宜超过2000，对应的，需要调整参数来调大连接数：

-- 修改用户 jack 最大连接数为1000
SET PROPERTY FOR 'jack' 'max_user_connections' = '2000';

-- 查看root用户的连接数限制
SHOW PROPERTY FOR 'jack'; 

-- 在高并发场景下，CPU 资源往往已充分利用，因此建议设置执行并行度为1，以减少不同查询间资源竞争，从而提高整体查询效率。
SET global parallel_fragment_exec_instance_num = 1

BE配置

-- be.conf
-- 每台be增加如下配置, 需要重启be以生效 
fragment_pool_thread_num_max  = 8192

固定sql压测

使用mysqlSlap进行固定sql点查高并发测试

-- 并发测试 select * from table1  where dt=$date and code=$trecode;

-- 数据探查

MySQL [APP]> select count(1) from table1 where dt >= '2010-08-01' and dt < '2010-09-01';
+------------+
| count(1)   |
+------------+
| 1273972590 |
+------------+
1 row in set (14.737 sec)

MySQL [APP]> select distinct dt from table1 limit 10;
+------------+
| dt     |
+------------+
| 2010-04-29 |
| 2010-08-03 |
| 2010-02-23 |
| 2010-01-22 |
| 2010-10-06 |
| 2010-06-07 |
| 2010-03-04 |
| 2010-07-09 |
| 2010-04-09 |
| 2010-11-19 |
+------------+
10 rows in set (0.701 sec)


MySQL [APP]> select distinct code from table1 limit 10;
+------------+
| code  |
+------------+
| 0105080000 |
| 0411020000 |
| 0501080000 |
| 0404040000 |
| 0415060000 |
| 0812050000 |
| 0210090000 |
| 0102070000 |
| 0506070000 |
| 0913010000 |
+------------+
10 rows in set (0.118 sec)


select * from table1  where dt='2010-10-31' and code='0406040000' limit 1;

MySQL [APP]> select * from table1  where dt='2010-10-31' and code='0406040000' limit 1;
+--------------+------------+------------+-------------------+-----------------+-----------------+-------------------+------------------+--------------+-----------+------------+-----------+------------+----------+--------------+----------+--------------+-----------+---------------+----------------+------------------+-----------------+
| pkno | dt     | code  | a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s |
+--------------+------------+------------+-------------------+-----------------+-----------------+-------------------+------------------+--------------+-----------+------------+-----------+------------+----------+--------------+----------+--------------+-----------+---------------+----------------+------------------+-----------------+
|   1488041475 | 2010-10-31 | 0406040000 | 0406040000        | 0406000000      | 0400000000      | 010111            | 金融             | 9            | 0         | a          | b         | 1          |     3.73 |        27.82 |    33.13 |       744.51 |   2369.52 | i             | j              | k                | l               |
+--------------+------------+------------+-------------------+-----------------+-----------------+-------------------+------------------+--------------+-----------+------------+-----------+------------+----------+--------------+----------+--------------+-----------+---------------+----------------+------------------+-----------------+
1 row in set (0.014 sec)

-- 测试1 用mysqlslap点查某一个查询条件
MySQL [(none)]> SET PROPERTY FOR 'simon' 'max_user_connections' = '2000';
Query OK, 0 rows affected (0.019 sec)

MySQL [(none)]> show property for 'simon';
+------------------------+-------+
| Key                    | Value |
+------------------------+-------+
| default_load_cluster   |       |
| max_user_connections   | 2000 |
| quota.high             | 800   |
| quota.low              | 100   |
| quota.normal           | 400   |
| resource.cpu_share     | 1000  |
| resource.hdd_read_iops | 80    |
| resource.hdd_read_mbps | 30    |
| resource.io_share      | 1000  |
| resource.ssd_read_iops | 1000  |
| resource.ssd_read_mbps | 30    |
+------------------------+-------+
11 rows in set (0.002 sec)



-- be.conf
-- 增加如下配置
fragment_pool_thread_num_max  = 8192


[root@starrocks01 bm]# mysqlslap -hsr2 -usimon -p12345 -P 19030  --concurrency=500 --number-of-queries=5000 --create-schema=APP --query="select * from table1  where dt='2010-10-31' and code='0406040000'  limit 1"
Benchmark
	Average number of seconds to run all queries: 2.130 seconds
	Minimum number of seconds to run all queries: 2.130 seconds
	Maximum number of seconds to run all queries: 2.130 seconds
	Number of clients running queries: 500
	Average number of queries per client: 10

-- QPS 2347
[root@starrocks01 bm]# echo "scale=2;5000/2.130" | bc
2347.41


mysqlslap -hsr2 -usimon -p12345 -P 19030  --concurrency=1000 --number-of-queries=10000 --create-schema=APP --query="select * from table1  where dt='2010-10-31' and code='0406040000'  limit 1"

[root@starrocks01 bm]# mysqlslap -hsr2 -usimon -p12345 -P 19030  --concurrency=1000 --number-of-queries=10000 --create-schema=APP --query="select * from table1  where dt='2010-10-31' and code='0406040000'  limit 1"
Benchmark
	Average number of seconds to run all queries: 6.171 seconds
	Minimum number of seconds to run all queries: 6.171 seconds
	Maximum number of seconds to run all queries: 6.171 seconds
	Number of clients running queries: 1000
	Average number of queries per client: 10

-- QPS 1620
[root@starrocks01 bm]# echo "scale=2;10000/6.171" | bc
1620.48
[root@starrocks01 bm]#


-- 增加pkno = '1488041475'条件
-- QPS 1808
[root@starrocks01 bm]# mysqlslap -hsr2 -usimon -p12345 -P 19030  --concurrency=1000 --number-of-queries=10000 --create-schema=APP --query="select * from table1  where dt='2010-10-31' and code='0406040000' and pkno = '1488041475' limit 1"
Benchmark
	Average number of seconds to run all queries: 5.529 seconds
	Minimum number of seconds to run all queries: 5.529 seconds
	Maximum number of seconds to run all queries: 5.529 seconds
	Number of clients running queries: 1000
	Average number of queries per client: 10

[root@starrocks01 bm]# echo "scale=2;10000/5.529" | bc
1808.64

随机参数压测

参考脚本（有jemter也可以）

#!/usr/bin/env python3
from multiprocessing import Pool
import os
import time
import random

# args
process_pool = 50
queries_per_process = 500
thread_per_process = 10

# vars
code_list = []
# fe ips or hostnames
host_list=['fe1','fe2','fe3']  

def f2l(fpath):
    f = open(fpath, 'r')
    rl = f.readlines()
    l = [ x.strip() for x in rl  ]
    f.close()
    return l

def f(dt):
    """
    mysqlslap -hsr2 -usimon -p12345 -P 19030  --concurrency=1000 --number-of-queries=10000 --create-schema=APP \
        --query="select * from table1  where dt='2010-10-31' and code='0406040000'  limit 1"
    """
    tpl_fixed='''mysqlslap -h%s -usimon -p12345 -P 19030  --concurrency=%s --number-of-queries=%s --create-schema=APP '''
    tpl_rand='''--query="select * from table1  where dt='%s' and code='%s'  limit 1" '''      
    h_index=random.randint(0,len(host_list)-1)
    h=host_list[h_index]
    code_index=random.randint(0,len(code_list)-1)
    code=code_list[code_index]
    sql = tpl_fixed %(h, thread_per_process, queries_per_process) + " " + tpl_rand %(dt, code)
    pid=os.getpid()
    print("pid=%s, run sql @%s, " %(pid, round(time.time(),2) ) + sql)
    # time.sleep(1)
    res=os.popen(sql)
    print(res.read())
    return

if __name__ == '__main__':
    start_time = time.time()
    dt_list=f2l("dt.list")
    total_process=len(dt_list)
    code_list=f2l("code.list")    
    with Pool(process_pool) as p:
        p.map(f, dt_list )
    end_time = time.time()
    total_queries=queries_per_process * total_process
    total_elapsed=round(end_time - start_time, 4)
    cost_per_query=round(1000* total_elapsed/total_queries,4)
    qps=int(total_queries/total_elapsed)
    total_concurrency=process_pool* thread_per_process
    print("### statistics ###")
    print('''### process_pool:%s, thread_per_process:%s '''%(process_pool, thread_per_process))
    print('''### total_concurrency: %s'''%(total_concurrency))
    print('''### total_process:%s, queries_per_process:%s'''%(total_process, queries_per_process))
    print("### total queries: %s" %(total_queries))
    print("### total elapsed: %s s" %(total_elapsed))
    print("### total qps: %s" %qps)
    print("### cost_per_query: %s ms" %cost_per_query)

测试效果：

[root@starrocks01 bm]# head -n 10 mp.py
#!/data/app/python-3.6.9/bin/python3
from multiprocessing import Pool
import os
import time
import random

# args
process_pool = 50
queries_per_process = 500
thread_per_process = 10
[root@starrocks01 bm]# time /data/app/python-3.6.9/bin/python3 mp.py  >> mp5.log

real	1m29.553s
user	0m1.695s
sys	0m1.171s
[root@starrocks01 bm]# tail -n 15 mp5.log
	Average number of seconds to run all queries: 49.483 seconds
	Minimum number of seconds to run all queries: 49.483 seconds
	Maximum number of seconds to run all queries: 49.483 seconds
	Number of clients running queries: 10
	Average number of queries per client: 50


### statistics ###
### process_pool:50, thread_per_process:10
### total_concurrency: 500
### total_process:100, queries_per_process:500
### total queries: 50000
### total elapsed: 89.5029 s
### total qps: 558
### cost_per_query: 1.7901 ms

StarRocks招聘：

招解决方案，DBA，数据库研发，测试，前后端开发等岗位，

有意者请投递简历到 hr@starrocks.com

怎样用mysqlslap压测StarRocks

概要

session级别FE配置

BE配置

固定sql压测

随机参数压测

StarRocks招聘：

相关阅读

相关文章

相关问答

相关文档