Skip to main content
Version: latest

CnosDB vs MySQL

CnosDB 2.4 vs MySQL 5.6

Comparative performance testing between CnosDB 2.4 and MySQL 5.6 has been conducted on write, query, and compression ratio performance, below are the test conclusions and test details information.

Conclusion

CnosDB is better than MySQL for writing, query and compression

Pre-test

1. Test Environment Preparedness

CnosDBMySQL
Version2.45.6
Machine11
Configuration3.10.0-1160.81.1.el7.x86_643.10.0-1160.81.1.el7.x86_64
Operating System VersionCentOS Linux release 7.9.2009 (Core)CentOS Linux release 7.9.2009 (Core)
CPU32-core Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz32-core Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz
Memory256G256G
Disk1 SDD (1T)1 SDD (1T)

2. Test case preparation

  1. The db environment of the corresponding machine is installed in advance to ensure proper connectivity.

  2. Install CnosDB:

    Refer to the deployment document: Install CnosDB

  3. Install MySQL

    Reference network: [MySQL 5.6] (https://www.mysql.com/cn/downloads/)

3) Configuration Check & Modify

CnosDB and MySQL only modified the storage folder paths for Data, Wal, and Meta, while keeping the rest as default. This will not be repeated here.

4. Dataset preparation

  1. Rows: 410522759
  2. Format: CSV
  3. Size: 39G

Test data schema

FieldMySQL TypeCnosDB Type
timeBIGINTTIMESTAMP
device_versionVARCHAR(255)TAG
driverVARCHAR(255)TAG
fleetVARCHAR(255)TAG
modelVARCHAR(255)TAG
elevationVARCHAR(255)TAG
fuel_capacityDOUBLEDOUBLE
fuel_consumptionDOUBLEDOUBLE
gradeDOUBLEDOUBLE
headingDOUBLEDOUBLE
latitudeDOUBLEDOUBLE
load_capacityDOUBLEDOUBLE
longitudeDOUBLEDOUBLE
nominal_fuel_consumptionDOUBLEDOUBLE
velocityDOUBLEDOUBLE

Test medium term

  1. Write data to CnosDB:
COPY INTO readings FROM '/data/data/csv/cnodb-iot-seed-123-scale-100-2017_csv/cnodb-iot-seed-123-scale-100-2017_readings_no_blank.csv' 
FILE_FORMAT = (TYPE = 'CSV', DELIMITER = ',');
  1. Write data to MySQL:
load data local infile '/data/data/csv/cnodb-iot-seed-123-scale-100-2017_csv/cnodb-iot-seed-123-scale-100-2017_readings_no_blank.csv' 
into table readings character set utf8 fields terminated by ',' lines terminated by '\n' ignore 1 lines;

Write Test Results

CnosDB 2.4MySQL 5.6
write time11 min 1 s1 hour 55 min 20 s

Query Test Results

SQLCnosDB 2.4MySQL 5.6
select count(*) from readings1 min 41 sec4 min 41 sec
select count(*) from readings where latitude = 1003.7 sec3 min 55.75 sec
select count(*), max(latitude), min(latitude), avg(latitude) from readings3.5 sec5 min 46 sec
sselect count(*), max(latitude), min(latitude), avg(latitude) from readings group by driver9.4 sec7 min 3.47 sec

Compression Ratio Test Result

CnosDB 2.4MySQL 5.6
Original Data39G39G
Data written to disk after being written15G62G
Compression ratio2.60.6