当前位置：首页 > news >正文

东莞人才网站万网域名购买

news 2025/9/18 1:41:01

东莞人才网站,万网域名购买,有什么比较好的做海报网站,做网站的技术性说明一、测试数据构造 1. 数据样例官方文档有给出一批数据样例。优点是比较真实，缺点是太大了，动辄上百G不适合简单小测试 Anonymized Yandex.Metrica DatasetStar Schema BenchmarkWikiStatTerabyte of Click Logs from CriteoAMPLab Big Data Benchma…

一、测试数据构造

1. 数据样例

官方文档有给出一批数据样例。优点是比较真实，缺点是太大了，动辄上百G不适合简单小测试

Anonymized Yandex.Metrica Dataset
Star Schema Benchmark
WikiStat
Terabyte of Click Logs from Criteo
AMPLab Big Data Benchmark
New York Taxi Data
OnTime

相对来说 ssb-dbgen工具生成的表比较简单，数据量也可以自己控制，更加方便。

2. ssb-dbgen下载安装

下载

https://github.com/vadimtk/ssb-dbgen

安装依赖包

yum -y install gcc gcc-c++ make cmake

ssb-dbgen安装：解压，进入目录，执行 make 即可

3. ssb-dbgen生成测试数据

ssb-dbgen工具指定参数可以生成如下表的数据，其中lineorder是最大的

c–customer.tbl
d–date.tbl
p–part.tbl
s–supplier.tbl
l–lineorder.tbl
a-all

./dbgen -s 10 -T a

-s 100 lineorder表会生成6亿行数据（约67G），-s 1000则会为其生成60亿行数据（约670G），需要大量空间和时间，注意控制。

4. 创建表结构

测试表可以都用，也可以挑一些，官方文档只建了4个

CREATE TABLE customer
(C_CUSTKEY       UInt32,C_NAME          String,C_ADDRESS       String,C_CITY          LowCardinality(String),C_NATION        LowCardinality(String),C_REGION        LowCardinality(String),C_PHONE         String,C_MKTSEGMENT    LowCardinality(String)
)
ENGINE = MergeTree ORDER BY (C_CUSTKEY);CREATE TABLE lineorder
(LO_ORDERKEY             UInt32,LO_LINENUMBER           UInt8,LO_CUSTKEY              UInt32,LO_PARTKEY              UInt32,LO_SUPPKEY              UInt32,LO_ORDERDATE            Date,LO_ORDERPRIORITY        LowCardinality(String),LO_SHIPPRIORITY         UInt8,LO_QUANTITY             UInt8,LO_EXTENDEDPRICE        UInt32,LO_ORDTOTALPRICE        UInt32,LO_DISCOUNT             UInt8,LO_REVENUE              UInt32,LO_SUPPLYCOST           UInt32,LO_TAX                  UInt8,LO_COMMITDATE           Date,LO_SHIPMODE             LowCardinality(String)
)
ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY);CREATE TABLE part
(P_PARTKEY       UInt32,P_NAME          String,P_MFGR          LowCardinality(String),P_CATEGORY      LowCardinality(String),P_BRAND         LowCardinality(String),P_COLOR         LowCardinality(String),P_TYPE          LowCardinality(String),P_SIZE          UInt8,P_CONTAINER     LowCardinality(String)
)
ENGINE = MergeTree ORDER BY P_PARTKEY;CREATE TABLE supplier
(S_SUPPKEY       UInt32,S_NAME          String,S_ADDRESS       String,S_CITY          LowCardinality(String),S_NATION        LowCardinality(String),S_REGION        LowCardinality(String),S_PHONE         String
)
ENGINE = MergeTree ORDER BY S_SUPPKEY;

将star schema转换为flat schema（表关联转为大宽表）：

SET max_memory_usage = 20000000000;CREATE TABLE lineorder_flat
ENGINE = MergeTree ORDER BY (LO_ORDERDATE, LO_ORDERKEY)
AS SELECTl.LO_ORDERKEY AS LO_ORDERKEY,l.LO_LINENUMBER AS LO_LINENUMBER,l.LO_CUSTKEY AS LO_CUSTKEY,l.LO_PARTKEY AS LO_PARTKEY,l.LO_SUPPKEY AS LO_SUPPKEY,l.LO_ORDERDATE AS LO_ORDERDATE,l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,l.LO_QUANTITY AS LO_QUANTITY,l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,l.LO_DISCOUNT AS LO_DISCOUNT,l.LO_REVENUE AS LO_REVENUE,l.LO_SUPPLYCOST AS LO_SUPPLYCOST,l.LO_TAX AS LO_TAX,l.LO_COMMITDATE AS LO_COMMITDATE,l.LO_SHIPMODE AS LO_SHIPMODE,c.C_NAME AS C_NAME,c.C_ADDRESS AS C_ADDRESS,c.C_CITY AS C_CITY,c.C_NATION AS C_NATION,c.C_REGION AS C_REGION,c.C_PHONE AS C_PHONE,c.C_MKTSEGMENT AS C_MKTSEGMENT,s.S_NAME AS S_NAME,s.S_ADDRESS AS S_ADDRESS,s.S_CITY AS S_CITY,s.S_NATION AS S_NATION,s.S_REGION AS S_REGION,s.S_PHONE AS S_PHONE,p.P_NAME AS P_NAME,p.P_MFGR AS P_MFGR,p.P_CATEGORY AS P_CATEGORY,p.P_BRAND AS P_BRAND,p.P_COLOR AS P_COLOR,p.P_TYPE AS P_TYPE,p.P_SIZE AS P_SIZE,p.P_CONTAINER AS P_CONTAINER
FROM lineorder AS l
INNER JOIN customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
INNER JOIN supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
INNER JOIN part AS p ON p.P_PARTKEY = l.LO_PARTKEY;

5. 导入数据

cd ssb-dbgen-masterclickhouse-client --password --query "INSERT INTO hydb.customer FORMAT CSV" < customer.tbl
clickhouse-client --password --query "INSERT INTO hydb.part FORMAT CSV" < part.tbl
clickhouse-client --password --query "INSERT INTO hydb.supplier FORMAT CSV" < supplier.tbl
clickhouse-client --password --query "INSERT INTO hydb.lineorder FORMAT CSV" < lineorder.tbl

如果还不够，也可以多次执行以下语句，至满意数据量

insert into hydb.lineorder select * from hydb.lineorder;

二、 clickhouse-benchmark简单压测

clickhouse-benchmark是自带的一个简单压测工具，可以控制执行SQL的次数、并发度等。

1. 常用参数

-c 并发度，例如10个并发同时执行指定SQL
-d 间隔几秒执行SQL，默认为1，0表示禁用
-h 指定连接的db ip，可以同时指定多个-h 连接多个库进行对比
-i SQL执行总次数
-r 有多个SQL时，以随机顺序执行
-t 指定压测时间，到达指定时间后停止发送压测SQL。默认为0，表示无限制

2. 两种用法

直接执行，适合简单SQL

echo "SELECT toYear(LO_ORDERDATE),count(*) FROM hydb.lineorder group by toYear(LO_ORDERDATE) order by 2 desc" | clickhouse-benchmark --password='xxxx' -i 10

执行SQL文件，适合复杂、批量语句

vi queries_file#查询语句
SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue FROM hydb.lineorder WHERE toYear(LO_ORDERDATE) = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;

执行压测

clickhouse-benchmark --password='xxxx' -i 10 < queries_file

压测的语句可以简单写点，也可以参考：Star Schema Benchmark | ClickHouse Docs

3. 结果分析

Queries executed: 10.

localhost:9000, queries 10, QPS: 6.772, RPS: 67904487.440, MiB/s: 518.070, result RPS: 67721584.984, result MiB/s: 516.675.

0.000% 0.145 sec.
10.000% 0.146 sec.
20.000% 0.146 sec.
30.000% 0.146 sec.
40.000% 0.147 sec.
50.000% 0.148 sec.
60.000% 0.148 sec.
70.000% 0.148 sec.
80.000% 0.149 sec.
90.000% 0.150 sec.
95.000% 0.150 sec.
99.000% 0.150 sec.
99.900% 0.150 sec.
99.990% 0.150 sec.

在结果报告中，您可以找到:

查询数量：参见Queries executed:字段。
状态码（按顺序给出）:
- ClickHouse服务器的连接信息。
- 已处理的查询数。
- QPS：服务端每秒处理的查询数量
- RPS：服务器每秒读取多少行
- MiB/s：服务器每秒读取多少字节的数据
- 结果RPS：服务端每秒生成多少行的结果集数据
- 结果MiB/s.服务端每秒生成多少字节的结果集数据
查询执行时间的百分比。