databend 与 duckdb性能比较

〇〇

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26-v0.7.41-nightly-2bd575e-simd(rust-1.61.0-nightly-2022-05-08T16:24:45.378913882Z) 0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> SELECT avg(number) FROM numbers_mt(1000000000);
+-------------+
| avg(number) |
+-------------+
| 499999999.5 |
+-------------+
1 row in set (0.204 sec)

MySQL [(none)]> SELECT avg(number) FROM numbers_mt(1000000000) group by number%5;
+-------------+
| avg(number) |
+-------------+
| 499999997.5 |
| 49999999 8.5 |
| 499999999.5 |
| 500000000.5 |
| 500000001.5 |
+-------------+
5 rows in set (2.180 sec)

MySQL [(none)]> SELECT avg(number) FROM numbers_mt(1000000000) group by round(log(number));
+-------------------+
| avg(number)       |
+-------------------+
|                 1 |
|              1237 |
|             167.5 |
|          74039886 |
|                62 |
|           3686229 |
| 899951088.4999999 |
|         547084872 |
|           24837.5 |
|          10020209 |
|            498877 |
|           67515.5 |
|            3361.5 |
|               8.5 |
|          27237752 |
|          183526.5 |
|                23 |
|                 3 |
|              9137 |
|               455 |
|         201261277 |
|           1356088 |
|                 0 |
+-------------------+
23 rows in set (5.660 sec)

〇〇

duckdb 0.7.1

D .timer on
D SELECT avg(a)avg1 FROM range(1000000001)t(a);
** ▕████████████████████████████████████████████████████████████▏
┌─────────────┐
│    avg1     │
│   double    │
├─────────────┤
│ 500000000.0 │
└─────────────┘
Run Time (s): real 4.199 user 4.196427 sys 0.000000
D SELECT avg(a)avg1 FROM range(100000001)t(a);
┌────────────┐
│    avg1    │
│   double   │
├────────────┤
│ 50000000.0 │
└────────────┘
Run Time (s): real 0.474 user 0.405603 sys 0.000000
D SELECT avg(a)avg1 FROM range(100000001)t(a) group by a%5;
** ▕████████████████████████████████████████████████████████████▏
┌────────────┐
│    avg1    │
│   double   │
├────────────┤
│ 50000000.0 │
│ 4999999 8.5 │
│ 49999999.5 │
│ 50000000.5 │
│ 50000001.5 │
└────────────┘
Run Time (s): real 4.087 user 3.946825 sys 0.000000
D SELECT a FROM range(11)t(a);
┌───────┐
│   a   │
│ int64 │
├───────┤
│     0 │
│     1 │
│     2 │
│     3 │
│     4 │
│     5 │
│     6 │
│     7 │
│     8 │
│     9 │
│    10 │
└───────┘
Run Time (s): real 0.171 user 0.000000 sys 0.000000
D SELECT avg(a)avg1 FROM range(100000001)t(a) group by round(log(a+1));
** ▕████████████████████████████████████████████████████████████▏
┌────────────┐
│    avg1    │
│   double   │
├────────────┤
│        1.0 │
│       16.5 │
│      173.0 │
│     1738.5 │
│    17391.5 │
│   173924.0 │
│  1739251.5 │
│ 17392526.0 │
│ 65811388.0 │
└────────────┘
Run Time (s): real 7.884 user 7.675249 sys 0.000000
DD create table t as SELECT a FROM range(1000000001)t(a)
> ;
** ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 49.646 user 10.405267 sys 39.187451
D SELECT avg(a)avg1 FROM t group by round(log(a+1));
** ▕████████████████████████████████████████████████████████████▏
┌─────────────┐
│    avg1     │
│   double    │
├─────────────┤
│    173924.0 │
│   1739251.5 │
│  17392526.0 │
│ 173925270.5 │
│ 658113883.0 │
│         1.0 │
│        16.5 │
│       173.0 │
│      1738.5 │
│     17391.5 │
├─────────────┤
│   10 rows   │
└─────────────┘
Run Time (s): real 5.462 user 121.415578 sys 0.015600

〇〇

启动脚本
cd databend

./bin/databend-meta -c bin/m.toml > meta.log 2>&1 &
sleep 2
curl -I  http://127.0.0.1:28**/v1/health

./bin/databend-query -c bin/q.toml > query.log 2>&1 &
sleep 2
curl -I  http://127.0.0.1:8080/v1/health

mysql -h127.0.0.1 -uroot -P3307

〇〇

在鲲鹏920上
dd:root@localhost/default=> select avg(number)from numbers(1000000000)group by round(log(number));
+-------------------+
|    avg(number)    |
+-------------------+
| 1.0               |
| 1237.0            |
| 167.5             |
| 74039886.0        |
| 62.0              |
| 3686229.0         |
| 899951088.4999999 |
| 547084872.0       |
| 24837.5           |
| 10020209.0        |
| 498877.0          |
| 67515.5           |
| 3361.5            |
| 8.5               |
| 27237752.0        |
| 183526.5          |
| 23.0              |
| 3.0               |
| 9137.0            |
| 455.0             |
| 201261277.0       |
| 1356088.0         |
| 0.0               |
+-------------------+
(23 rows)

Time: 4683.833 ms (4.684s)

〇〇

MySQL [(none)]> create table t2 storage_format = 'native' compression = 'lz4'  as SELECT number FROM numbers(200000000);
Query OK, 200000000 rows affected (27.947 sec)

MySQL [(none)]> SELECT avg(number) FROM t2 group by number%5;                                             
+-------------+
| avg(number) |
+-------------+
|  99999997.5 |
|  9999999 8.5 |
|  99999999.5 |
| 100000000.5 |
| 100000001.5 |
+-------------+
5 rows in set (1.125 sec)
D create table t as SELECT i FROM range(1,200000000)t(i);
** ▕████████████████████████████████████████████████████████████▏
Run Time (s): real 5.788 user 10.538402 sys 0.978863
D SELECT avg(i) FROM t group by i%5;
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│  9999999 8.5 │
│  99999999.5 │
│ 100000000.5 │
│ 100000001.5 │
│ 100000000.0 │
└─────────────┘
Run Time (s): real 0.897 user 6.788605 sys 0.010458

--databend
SELECT avg(number) FROM numbers_mt(1000000000) group by number%5;
+-------------+
| avg(number) |
+-------------+
| 499999997.5 |
| 49999999 8.5 |
| 499999999.5 |
| 500000000.5 |
| 500000001.5 |
+-------------+
5 rows in set (2.295 sec)

--duckdb
D SELECT avg(i) FROM range(1,1000000000)t(i) group by i%5;
** ▕████████████████████████████████████████████████████████████▏
┌─────────────┐
│   avg(i)    │
│   double    │
├─────────────┤
│ 49999999 8.5 │
│ 499999999.5 │
│ 500000000.5 │
│ 500000001.5 │
│ 500000000.0 │
└─────────────┘
Run Time (s): real 18.352 user 36.671181 sys 0.008397

〇〇

~ $ proot-distro login debian
root@localhost:~# /data/data/com.termux/files/home/ch/clickhouse local
ClickHouse local version 22.5.1.1485 (official build).

localhost.localdomain SELECT avg(number) FROM numbers_mt(1000000000) group by round(log(number));

SELECT avg(number)
FROM numbers_mt(1000000000)
GROUP BY round(log(number))

Query id: f8608000-2a13-4038-a908-4ed19977ad53

┌───────avg(number)─┐
│                 1 │
│           24837.5 │
│              9137 │
│              1237 │
│          27237752 │
│           3686229 │
│                62 │
│          10020209 │
│                 0 │
│                23 │
│          183526.5 │
│             167.5 │
│         547084872 │
│            498877 │
│                 3 │
│          74039886 │
│           1356088 │
│               8.5 │
│            3361.5 │
│         201261277 │
│ 899951088.4999999 │
│               455 │
│           67515.5 │
└───────────────────┘

23 rows in set. Elapsed: 3.616 sec. Processed 978.91 million rows, 7.83 GB (270.72 million rows/s., 2.17 GB/s.)

localhost.localdomain SELECT avg(number) FROM numbers_mt(1000000000) group by number%5;

SELECT avg(number)
FROM numbers_mt(1000000000)
GROUP BY number % 5

Query id: dcd47563-a6f7-4bd1-b010-281b187e52fa

┌─avg(number)─┐
│ 499999997.5 │
│ 49999999 8.5 │
│ 499999999.5 │
│ 500000000.5 │
│ 500000001.5 │
└─────────────┘

5 rows in set. Elapsed: 0.906 sec. Processed 899.38 million rows, 7.20 GB (992.60 million rows/s., 7.94 GB/s.)

root@localhost:~# cd /data/data/com.termux/files/home/da
root@localhost:/data/data/com.termux/files/home/da# scripts/start.sh
Stop old Databend instances
databend-meta: no process found
databend-query: no process found
Deploy new Databend(standalone)
scripts/start.sh: line 5: ulimit: open files: cannot modify limit: Operation not permitted
nohup: redirecting stderr to stdout
nohup: redirecting stderr to stdout
root@localhost:/data/data/com.termux/files/home/da# ==> meta.log <==
    Stderr: enabled=false(To enable: LOG_STDERR_ON=true or RUST_LOG=info), level=INFO, format=text
Id:
Raft Cluster Name: foo_cluster
Raft Dir: /var/lib/databend/raft
Raft Status: single

HTTP API
   listened at 0.0.0.0:28**
gRPC API
   listened at 0.0.0.0:9191

==> query.log <==
    listened at 0.0.0.0:3307
    connect via: mysql -uroot -h0.0.0.0 -P3307
Clickhouse(http)
    listened at 0.0.0.0:8124
    usage:  echo 'create table test(foo string)' | curl -u root: '0.0.0.0:8124' --data-binary  @-
echo '{"foo": "bar"}' | curl -u root: '0.0.0.0:8124/?query=INSERT%20INTO%20test%20FORMAT%20JSONEachRow' --data-binary @-
Databend HTTP
    listened at 0.0.0.0:8000
    usage:  curl -u root: --request POST '0.0.0.0:8000/v1/query/' --header 'Content-Type: application/json' --data-raw '{"sql": "SELECT avg(number) FROM numbers(100000000)"}'


root@localhost:/data/data/com.termux/files/home/da# mysql -uroot -h0.0.0.0 -P3307
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.26-v1.0.58-nightly-0130fd131abcd408c99cfa591dff093d7f083d5d(rust-1.70.0-nightly-2023-04-09T16:47:27.491375963Z) 0

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> SELECT avg(number) FROM numbers_mt(1000000000) group by round(log(number));
+-------------------+
| avg(number)       |
+-------------------+
|               1.0 |
|            1237.0 |
|             167.5 |
|        74039886.0 |
|              62.0 |
|         3686229.0 |
| 899951088.4999999 |
|       547084872.0 |
|           24837.5 |
|        10020209.0 |
|          498877.0 |
|           67515.5 |
|            3361.5 |
|               8.5 |
|        27237752.0 |
|          183526.5 |
|              23.0 |
|               3.0 |
|            9137.0 |
|             455.0 |
|       201261277.0 |
|         1356088.0 |
|               0.0 |
+-------------------+
23 rows in set (6.192 sec)

MySQL [(none)]> SELECT avg(number) FROM numbers_mt(1000000000) group by number%5;
+-------------+
| avg(number) |
+-------------+
| 499999997.5 |
| 49999999 8.5 |
| 499999999.5 |
| 500000000.5 |
| 500000001.5 |
+-------------+
5 rows in set (2.414 sec)

MySQL [(none)]>
麒麟990