The timing database needs to connect to a large amount of real-time data from devices, and loading performance is crucial. This section will take you to do data access performance tests to experience the powerful loading performance of MatrixDB.

1 Hardware environment

The machine configuration is as follows:

2 Related Tools

2.1 MatrixGate

MatrixGate is abbreviated as mxgate. It is a high-performance streaming data loading server developed by MatrixDB. It is located in bin/mxgate in the MatrixDB installation directory. This tool will give full play to the parallel processing performance of distributed databases and is a necessary tool for data loading in production environments.

Start matrixgate:

mxgate config --db-master-port 5432 --db-database demo > ~/mxgate.conf
mxgate start --config ~/mxgate.conf

Where --db-database is the name of the connection database and --db-master-port is the port number of the master.

For more detailed usage methods, please refer to MatrixGate

2.2 MatrixBench

MatrixBench is abbreviated as mxbench. It is a MatrixDB streaming data loading service pressure measurement and data simulation tool. It can quickly generate random data based on the given number of devices, number of indicators, etc., and insert it into the MatrixDB database at high speed through MatrixGate. This tool is located in bin/mxbench in the MatrixDB installation directory.

For more detailed usage methods, please refer to MatrixBench

3 Deployment method

MatrixDB: stand-alone deployment, master + 6 segments

MatrixGate, MatrixBench and MatrixDB are deployed on the same machine.

4 Start the test

4.1 Test cases

1. 10w equipment, 10 columns
2. 10w equipment, 50 columns
3. 10w equipment, 100 columns
4. 10w equipment, 400 columns

Because there are many columns in the test table, UDF is used to automatically create it. The UDF is defined as follows:

create or replace function f(name, int, text) returns void as $$  
declare  
  res text := 'ts timestamp,tag_id integer,';  
begin  
  for i in 1..$2 loop
  res := res||'c'||i||' integer,';  
  end loop;  
  res := rtrim(res, ',');  
  if $3 = 'ao_col' then  
    res := 'create table '||$1||'('||res||') with(appendonly=true, blocksize=8192, compresstype=zstd, orientation=column) DISTRIBUTED BY (tag_id)';  
  elsif $3 = 'ao_row' then  
    res := 'create table '||$1||'('||res||') with(appendonly=true, blocksize=8192, orientation=row) DISTRIBUTED BY (tag_id)';  
  elsif $3 = 'heap_row' then  
    res := 'create table '||$1||'('||res||') with(appendonly=false) DISTRIBUTED BY (tag_id)';  
  else  
    raise notice 'use ao_col, ao_row, heap_row as $3';  
    return;  
  end if;  
  execute res;  
end;  
$$ language plpgsql; 

Use the UDF as above to create 4 heap test tables with integer types including 10 columns, 50 columns, 100 columns, and 400 columns:

select f('sensor_data_10', 10, 'heap_row');
select f('sensor_data_50', 50, 'heap_row');
select f('sensor_data_100', 100, 'heap_row');
select f('sensor_data_400', 400, 'heap_row');

4.2 Start the test

Testing with mxbench:

4.2.1 10w equipment 10 columns

Common parameters of mxbench are described as follows:

| Parameter name | Parameter value | Parameter meaning | | --- | --- | | --table-name | tableName | A target table name must be specified | | --database | Default postgres | Target database name | | --batch-size | Default 1000 | Number of rows of data contained per request per connection | | --tag-range | Default 50000 | Number of target devices to load data | | --columns-per-row | Default 100 | Number of columns per row of loading data | | --column-data-type | Default simple | Indicator column type in the data table, simple single value types currently support, such as float/int, and compound types json and array | | --point-data-type | Default float8 | When --column-data-type is the compound type JSON and ARRAY, the numerical type of each indicator value in Column supports int, float4, float8 |

[mxadmin@mdw ~]$ mxbench --database demo --table-name sensor_data_10 --columns-per-row 10 --tag-range 100000 --column-data-type simple --batch-size 80659 --point-data-type int

31036-03-31 12:03:43: sent OK: 48718036, ERR 0, Lat 739.54ms last10s 739.54ms, Row/s: 4871803, last10s 4871803
31036-03-31 12:03:53: sent OK: 90499398, ERR 0, Lat 828.65ms last10s 932.54ms, Row/s: 4524969, last10s 4178136
31036-03-31 12:04:03: sent OK: 134055258, ERR 0, Lat 801.38ms last10s 744.72ms, Row/s: 4468508, last10s 4355586
31036-03-31 12:04:13: sent OK: 174546076, ERR 0, Lat 884.81ms last10s 1161.03ms, Row/s: 4363651, last10s 4049081
31036-03-31 12:04:23: sent OK: 228587606, ERR 0, Lat 841.72ms last10s 702.56ms, Row/s: 4571752, last10s 5404153

mxbench output interpretation:

After mxbench is started, the pressure test will be continued until the user presses ctrl-c to exit.

4.2.2 10w equipment 50 columns

[mxadmin@mdw ~]$ mxbench --database demo --table-name sensor_data_50 --columns-per-row 50 --tag-range 100000 --column-data-type simple --batch-size 19784 --point-data-type int

31036-03-31 12:06:58: sent OK: 13235496, ERR 0, Lat 564.96ms last10s 564.96ms, Row/s: 1323549, last10s 1323549
31036-03-31 12:07:08: sent OK: 28429608, ERR 0, Lat 563.19ms last10s 561.64ms, Row/s: 1421480, last10s 1519411
31036-03-31 12:07:18: sent OK: 42377328, ERR 0, Lat 595.10ms last10s 660.14ms, Row/s: 1412577, last10s 1394772
31036-03-31 12:07:28: sent OK: 48431232, ERR 0, Lat 738.76ms last10s 1744.37ms, Row/s: 1210780, last10s 605390
31036-03-31 12:07:38: sent OK: 63961672, ERR 0, Lat 702.60ms last10s 589.86ms, Row/s: 1279233, last10s 1553044

4.2.3 10w equipment 100 columns

[mxadmin@mdw ~]$ mxbench --database demo --table-name sensor_data_100 --columns-per-row 100 --tag-range 100000 --column-data-type simple --batch-size 10180 --point-data-type int

31036-03-31 12:14:41: sent OK: 14913700, ERR 0, Lat 544.40ms last10s 531.36ms, Row/s: 745685, last10s 792004
31036-03-31 12:14:51: sent OK: 22864280, ERR 0, Lat 539.02ms last10s 528.91ms, Row/s: 762142, last10s 795058
31036-03-31 12:15:01: sent OK: 31313680, ERR 0, Lat 535.67ms last10s 526.62ms, Row/s: 782842, last10s 844940
31036-03-31 12:15:11: sent OK: 38144460, ERR 0, Lat 561.63ms last10s 680.64ms, Row/s: 762889, last10s 683078
31036-03-31 12:15:21: sent OK: 45901620, ERR 0, Lat 561.66ms last10s 561.81ms, Row/s: 765027, last10s 775716

4.2.4 10w equipment 400 columns

[mxadmin@mdw ~]$ mxbench --database demo --table-name sensor_data_400 --columns-per-row 400 --tag-range 100000 --column-data-type simple --batch-size 2601 --point-data-type int

31036-03-31 12:19:44: sent OK: 3651804, ERR 0, Lat 556.68ms last10s 511.94ms, Row/s: 182590, last10s 204958
31036-03-31 12:19:54: sent OK: 5763816, ERR 0, Lat 542.92ms last10s 519.13ms, Row/s: 192127, last10s 211201
31036-03-31 12:20:04: sent OK: 7633935, ERR 0, Lat 542.64ms last10s 541.79ms, Row/s: 190848, last10s 187011
31036-03-31 12:20:14: sent OK: 9467640, ERR 0, Lat 576.16ms last10s 715.68ms, Row/s: 189352, last10s 183370
31036-03-31 12:20:24: sent OK: 10861776, ERR 0, Lat 620.16ms last10s 919.01ms, Row/s: 181029, last10s 139413

--batch-size can be used with 4 1024 1024 / byte number per line