✕

Join us for a virtual meetup on Zoom at 8 PM, July 31 (PDT) about using One Time Series Database for Both Metrics and Logs 👉🏻 Register Now

✕
On this page
Engineering
•
August 30, 2024

GreptimeDB vs. SQLite - A Performance Comparison Report on the Qualcomm 8155 Platform

In order to more fully evaluate GreptimeDB's performance in embedded and edge computing environments, we tested it against SQLite in a comprehensive comparison, and this article is a detailed test report.
Lei HuangSoftware Engineer
Dennis ZhuangCo-Founder and CEO, Greptime

GreptimeDB Vehicle-Cloud integration solution has been successfully adopted by leading new energy vehicle manufacturers, effectively addressing the challenges of millisecond-level signal collection, storage, and analysis. This implementation has significantly reduced costs and improved efficiency for these companies, resulting in lower data traffic and storage costs, as well as more real-time data analysis.

However, to comprehensively evaluate GreptimeDB’s performance in embedded and edge computing environments, we conducted a detailed comparison with SQLite. The goal of this test is to assess their data processing capabilities in similar application scenarios, verifying GreptimeDB's adaptability and advantages in IoT, Vehicle, and observability applications.

SQLite is a compact, fast, and feature-rich SQL database engine, widely regarded as the most commonly used database system globally. It can be deployed on various mobile devices (such as smartphones, set-top boxes, and TVs) and embedded within local applications.

This article compares the performance of GreptimeDB and SQLite on the Qualcomm Snapdragon 8155 smart cockpit chip platform, focusing on aspects such as ingestion performance, resource usage, and compression rate.

Testing Environment ​

Hardware Platform ​

ComponentSpecification
ChipsetQualcomm Technologies, Inc SA8155P
Memory12 GB
StorageeMMC NAND Flash

Software Version ​

DatabaseTest Version
GreptimeDB Edgev1.0 Commercial Edition
SQLite3.46.0, based on rusqlite packaging https://github.com/rusqlite/rusqlite

Software Configuration ​

Both GreptimeDB Edge and SQLite have Write-Ahead Logging (WAL) turned off. GreptimeDB Edge disables WAL by default. For SQLite, WAL and synchronous writes are disabled using the following commands:

sql
PRAGMA journal_mode=OFF;
PRAGMA synchronous=OFF;

All other settings are in their default configuration.

Test Data and Methodology ​

Test Data ​

We used CPU monitoring data generated by TSBS, totaling 10 million rows. Each row contains 10 tags and fields, along with a timestamp, resulting in a total of 21 columns. Sample data is shown below:

go
cpu,hostname=host_0,region=eu-central-1,datacenter=eu-central-1a,rack=6,os=Ubuntu15.10,arch=x86,team=SF,service=19,service_version=1,service_environment=test usage_user=58i,usage_system=2i,usage_idle=24i,usage_nice=61i,usage_iowait=22i,usage_irq=63i,usage_softirq=6i,usage_steal=44i,usage_guest=80i,usage_guest_nice=38i 1686441600000000000

Table creation statements are provided in the appendix. SQLite creates a primary key index only on the hostname and ts columns, as these are necessary for queries. Other tag columns are not included in the primary key to avoid significant performance degradation during writes. In contrast, GreptimeDB indexes all tag columns, with ts as a TIME INDEX constraint, and utilizes the append mode.

Write Mode ​

  • GreptimeDB Edge utilizes a commercial SDK for data writes through shared memory, enhancing write efficiency.

  • SQLite uses a batch insertion method, combining multiple rows into a single INSERT statement and executing it with sqlite3_step. The batch size is set to 32, and increasing the batch size has a negligible impact on overall throughput.

Shared memory-based writes within GreptimeDB Edge will be more efficient

Query ​

After writing 10 million rows, SQL queries were executed. We selected 7 SQL queries from TSBS scenarios to cover typical use cases. Details can be found in the appendix.

Test Report ​

Write Performance ​

Since SQLite does not support multi-threaded concurrent writes, we tested the single-core maximum write scenario:

Write Throughput

GreptimeDB’s Insert TPS（Transactions Per Second） is approximately 1.7 times higher than SQLite, reaching 72,000 rows per second. We also tested GreptimeDB’s concurrent write performance separately, achieving a write rate of 100,000 rows per second with an average CPU consumption of 160% and full I/O（Input/Output） utilization.

Compression Rate ​

In addition to write performance, the disk usage of embedded databases is also a crucial factor. Embedded devices often have limited storage space and typically use Flash memory. Excessive disk usage can limit data storage and shorten the storage medium's lifespan. In this test, we measured the data directory sizes of both GreptimeDB and SQLite after writing 10 million rows of data.

Disk Usage

Thanks to the high compression ratio of columnar storage used by GreptimeDB Edge, the final storage space required is only 87 MB, compared to 1,686 MB for SQLite. This means GreptimeDB achieves a compression rate 19 times higher than SQLite.

Resource Usage ​

With a write throughput limit set to 43,000 rows per second, we measured the CPU and memory usage for both GreptimeDB and SQLite:

CPU UsageMemory Usage

Conclusion: ​

  • GreptimeDB exhibits lower CPU usage at just 67%, compared to 96% for SQLite.

  • SQLite’s write process involves minimal memory allocation, remaining stable at 10MB. In contrast, GreptimeDB Edge requires 200MB of memory.

  • The memory usage of GreptimeDB Edge can be optimized through configuration. Thanks to its well-designed architecture, GreptimeDB Edge can flexibly balance CPU usage, memory consumption, and compression ratio through configuration, based on user requirements.

Query Performance ​

The results for the 7 typical SQL queries we selected are as follows (query execution time, in milliseconds):

Query ScenariosSQLiteGreptimeDBQuery Scenarios Description
FIVE_MINUTE_1133Retrieve all metric data for a single host from the last 5 minutes.
COUNT25211Calculate the total number of rows.
CPU_MAX_ALL_11652Find the maximum value for all CPU metrics for a single host over the past 8 hours, aggregated by hour.
CPU_MAX_ALL_811978Find the maximum value for all CPU metrics for 8 hosts over the past 8 hours, aggregated by hour.
SINGLE_GROUP_BY_1_1_1145105Retrieve the maximum value for a single CPU metric for a single host over the past hour, aggregated by minute.
LAST_POINT_ALL12461860Get the most recent row of data for all hosts.
LAST_POINT_ONE118140Get the most recent row of data for a single host.
Query Comparison

It can be observed that SQLite's query performance is notably impressive, while GreptimeDB Edge performs comparably and demonstrates faster results in time-series aggregation queries.

Summary ​

On the Qualcomm Snapdragon 8155 platform, GreptimeDB Edge demonstrates a write performance that is 1.7 times that of SQLite, a data compression rate that is 19 times higher, and superior query performance in time-series aggregation scenarios. While GreptimeDB Edge consumes less CPU but requires more memory compared to SQLite, its flexible configuration allows for optimal balance between CPU, memory, and compression rates.

The version of GreptimeDB Edge used in this test is still the commercial 1.0 release. The latest 2.0 version will support additional features such as a log engine, a lightweight stream processing engine called Flow, and Vector Search Engine designed for embedded LLM applications. The 2.0 version will also offer further optimizations in read/write performance and resource utilization. GreptimeDB's edge-cloud integrated solution significantly reduces data traffic and storage costs, enhances edge computing capabilities, and allows flexible coordination of computing Resources between edge and cloud.

For further discussions on GreptimeDB Edge’s use in edge environments such as IoT devices, please feel free to contact us.

Appendix ​

Table creation statements ​

GreptimeDB ​

sql
CREATE TABLE IF NOT EXISTS cpu (
  hostname STRING NULL,
  region STRING NULL,
  datacenter STRING NULL,
  rack STRING NULL,
  os STRING NULL,
  arch STRING NULL,
  team STRING NULL,
  service STRING NULL,
  service_version STRING NULL,
  service_environment STRING NULL,
  usage_user BIGINT NULL,
  usage_system BIGINT NULL,
  usage_idle BIGINT NULL,
  usage_nice BIGINT NULL,
  usage_iowait BIGINT NULL,
  usage_irq BIGINT NULL,
  usage_softirq BIGINT NULL,
  usage_steal BIGINT NULL,
  usage_guest BIGINT NULL,
  usage_guest_nice BIGINT NULL,
  ts TIMESTAMP(9) NOT NULL,
  TIME INDEX (ts),
  PRIMARY KEY (hostname, region, datacenter, rack, os, arch, team, service, service_version, service_environment)
) with (append_mode='true');

SQLite ​

sql
CREATE TABLE IF NOT EXISTS cpu (
  hostname VARCHAR NULL,
  region VARCHAR NULL,
  datacenter VARCHAR NULL,
  rack VARCHAR NULL,
  os VARCHAR NULL,
  arch VARCHAR NULL,
  team VARCHAR NULL,
  service VARCHAR NULL,
  service_version VARCHAR NULL,
  service_environment VARCHAR NULL,
  usage_user BIGINT NULL,
  usage_system BIGINT NULL,
  usage_idle BIGINT NULL,
  usage_nice BIGINT NULL,
  usage_iowait BIGINT NULL,
  usage_irq BIGINT NULL,
  usage_softirq BIGINT NULL,
  usage_steal BIGINT NULL,
  usage_guest BIGINT NULL,
  usage_guest_nice BIGINT NULL,
  ts TIMESTAMP NOT NULL,
  PRIMARY KEY (hostname, ts)
);

Query Language ​

1. FIVE_MINUTE_1E ​

sql
SELECT * FROM cpu WHERE hostname='host_1701' AND ts>='2016-01-01T00:00:00Z' AND ts<'2016-01-01T00:05:00';

2. COUNT ​

sql
SELECT count(*) FROM cpu;

3. CPU_MAX_ALL_1 ​

sql
-- GreptimeDB
SELECT 
  max(usage_user), 
  max(usage_system), 
  max(usage_idle), 
  max(usage_nice), 
  max(usage_iowait), 
  max(usage_irq), 
  max(usage_softirq), 
  max(usage_steal), 
  max(usage_guest), 
  max(usage_guest_nice), 
  date_trunc('hour', ts) 
FROM 
  cpu
WHERE 
  hostname = 'host_1435' AND 
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z' 
GROUP BY 
  date_trunc('hour', ts);

-- SQLite
SELECT 
  max(usage_user), 
  max(usage_system), 
  max(usage_idle), 
  max(usage_nice), 
  max(usage_iowait), 
  max(usage_irq), 
  max(usage_softirq), 
  max(usage_steal), 
  max(usage_guest), 
  max(usage_guest_nice), 
  strftime('%Y-%m-%dT%H:00:00', ts)
FROM 
  cpu
WHERE 
  hostname = 'host_1435' AND 
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z'
GROUP BY 
  strftime('%Y-%m-%dT%H:00:00', ts);

4. CPU_MAX_ALL_8 ​

sql
SELECT 
  max(usage_user), 
  max(usage_system), 
  max(usage_idle), 
  max(usage_nice), 
  max(usage_iowait), 
  max(usage_irq), 
  max(usage_softirq), 
  max(usage_steal), 
  max(usage_guest), 
  max(usage_guest_nice), 
  date_trunc('hour', ts) 
FROM 
  cpu 
WHERE 
  (
    hostname = 'host_249'     OR 
    hostname = 'host_1403'    OR 
    hostname = 'host_1435'    OR 
    hostname = 'host_3539'    OR 
    hostname = 'host_3639'    OR 
    hostname = 'host_3075'    OR 
    hostname = 'host_815'     OR 
    hostname = 'host_2121'
  )  AND
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z' 
GROUP BY 
  date_trunc('hour', ts)

-- SQLite  
SELECT 
  max(usage_user), 
  max(usage_system), 
  max(usage_idle), 
  max(usage_nice), 
  max(usage_iowait), 
  max(usage_irq), 
  max(usage_softirq), 
  max(usage_steal), 
  max(usage_guest), 
  max(usage_guest_nice), 
  strftime('%Y-%m-%dT%H:00:00', ts)
FROM 
  cpu 
WHERE 
  (
    hostname = 'host_249'     OR 
    hostname = 'host_1403'    OR 
    hostname = 'host_1435'    OR 
    hostname = 'host_3539'    OR 
    hostname = 'host_3639'    OR 
    hostname = 'host_3075'    OR 
    hostname = 'host_815'     OR 
    hostname = 'host_2121'
  ) AND 
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z' 
GROUP BY 
  strftime('%Y-%m-%dT%H:00:00', ts);

5. SINGLE_GROUP_BY_1_1_1 ​

sql
SELECT 
  max(usage_user), 
  date_trunc('minute', ts) 
FROM 
    cpu 
WHERE 
  (hostname = 'host_3701') AND 
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z' 
GROUP BY 
  date_trunc('minute', ts)

-- SQLite
SELECT 
  max(usage_user), 
   strftime('%Y-%m-%dT%H:%M:00', ts)
FROM 
  cpu 
WHERE 
  (hostname = 'host_1701') AND 
  ts >= '2016-01-01T00:00:00Z' AND 
  ts < '2016-01-01T08:00:00Z' 
GROUP BY 
   strftime('%Y-%m-%dT%H:%M:00', ts);

6. LAST_POINT_ALL ​

sql
-- GreptimeDB
SELECT                                                                                                                                                                           
  last_value(hostname order by ts),
  last_value(region order by ts),
  last_value(datacenter order by ts),
  last_value(rack order by ts),
  last_value(os order by ts),
  last_value(arch order by ts),
  last_value(team order by ts),
  last_value(service order by ts),
  last_value(service_version order by ts),
  last_value(service_environment order by ts),
  last_value(usage_user order by ts),
  last_value(usage_system order by ts),
  last_value(usage_idle order by ts),
  last_value(usage_nice order by ts),
  last_value(usage_iowait order by ts),
  last_value(usage_irq order by ts),
  last_value(usage_softirq order by ts),
  last_value(usage_steal order by ts),
  last_value(usage_guest order by ts),
  last_value(usage_guest_nice order by ts)
FROM cpu GROUP BY hostname;

--SQLite
SELECT 
  last_value(hostname) over(order by ts), 
  last_value(region) over(order by ts), 
  last_value(datacenter) over(order by ts), 
  last_value(rack) over(order by ts), 
  last_value(os) over(order by ts), 
  last_value(arch) over(order by ts), 
  last_value(team) over(order by ts), 
  last_value(service) over(order by ts), 
  last_value(service_version) over(order by ts), 
  last_value(service_environment) over(order by ts), 
  last_value(usage_user) over(order by ts), 
  last_value(usage_system) over(order by ts), 
  last_value(usage_idle) over(order by ts), 
  last_value(usage_nice) over(order by ts), 
  last_value(usage_iowait) over(order by ts), 
  last_value(usage_irq) over(order by ts), 
  last_value(usage_softirq) over(order by ts), 
  last_value(usage_steal) over(order by ts), 
  last_value(usage_guest) over(order by ts), 
  last_value(usage_guest_nice) over(order by ts) 
FROM cpu GROUP BY hostname;

7. LAST_POINT_ONE ​

sql
-- GreptimeDB Edge
SELECT
  last_value(hostname order by ts),
  last_value(region order by ts),
  last_value(datacenter order by ts),
  last_value(rack order by ts),
  last_value(os order by ts),
  last_value(arch order by ts),
  last_value(team order by ts),
  last_value(service order by ts),
  last_value(service_version order by ts),
  last_value(service_environment order by ts),
  last_value(usage_user order by ts),
  last_value(usage_system order by ts),
  last_value(usage_idle order by ts),
  last_value(usage_nice order by ts),
  last_value(usage_iowait order by ts),
  last_value(usage_irq order by ts),
  last_value(usage_softirq order by ts),
  last_value(usage_steal order by ts),
  last_value(usage_guest order by ts),
  last_value(usage_guest_nice order by ts)
FROM cpu
WHERE hostname='host_1701'

-- SQLite
SELECT
  last_value(hostname) over(order by ts),
  last_value(region) over(order by ts),
  last_value(datacenter) over(order by ts),
  last_value(rack) over(order by ts),
  last_value(os) over(order by ts),
  last_value(arch) over(order by ts),
  last_value(team) over(order by ts),
  last_value(service) over(order by ts),
  last_value(service_version) over(order by ts),
  last_value(service_environment) over(order by ts),
  last_value(usage_user) over(order by ts),
  last_value(usage_system) over(order by ts),
  last_value(usage_idle) over(order by ts),
  last_value(usage_nice) over(order by ts),
  last_value(usage_iowait) over(order by ts),
  last_value(usage_irq) over(order by ts),
  last_value(usage_softirq) over(order by ts),
  last_value(usage_steal) over(order by ts),
  last_value(usage_guest) over(order by ts),
  last_value(usage_guest_nice) over(order by ts)
FROM cpu
WHERE hostname='host_1701'

About Greptime ​

We help industries that generate large amounts of time-series data, such as Connected Vehicles (CV), IoT, and Observability, to efficiently uncover the hidden value of data in real-time.

Visit the latest version from any device to get started and get the most out of your data.

  • GreptimeDB, written in Rust, is a distributed, open-source, time-series database designed for scalability, efficiency, and powerful analytics.
  • Edge-Cloud Integrated TSDB is designed for the unique demands of edge storage and compute in IoT. It tackles the exponential growth of edge data by integrating a multimodal edge-side database with cloud-based GreptimeDB Enterprise. This combination reduces traffic, computing, and storage costs while enhancing data timeliness and business insights.
  • GreptimeCloud is a fully-managed cloud database-as-a-service (DBaaS) solution built on GreptimeDB. It efficiently supports applications in fields such as observability, IoT, and finance.

Star us on GitHub or join GreptimeDB Community on Slack to get connected. Also, you can go to our contribution page to find some interesting issues to start with.

SQLite
report

Join our community

Get the latest updates and discuss with other users.