Analyzing GreptimeDB Query Performance: 10s Response Time for 3-Hour Nginx Access Log Analysis (378M+ Rows, 30K/s Ingest Rate)

[zhaoyao]

I have a nginx access log table auto generated as

CREATE TABLE IF NOT EXISTS `access_logs` (
  `domain` STRING NULL,
  `geoip_region_name` STRING NULL,
  `hostname` STRING NULL,
  `http_forward` STRING NULL,
  `http_protocol` STRING NULL,
  `http_referer` STRING NULL,
  `http_user_agent` STRING NULL,
  `remote_addr` STRING NULL,
  `request` STRING NULL,
  `request_method` STRING NULL,
  `request_time` DOUBLE NULL,
  `scheme` STRING NULL,
  `size` BIGINT NULL,
  `status` STRING NULL,
  `time` BIGINT NULL,
  `type` STRING NULL,
  `upstream_addr` STRING NULL,
  `upstream_cache_status` STRING NULL,
  `upstream_response_time` DOUBLE NULL,
  `greptime_timestamp` TIMESTAMP(9) NOT NULL,
  `http_apptype` STRING NULL,
  `http_request_id` STRING NULL,
  `http_ziroom_from_app` STRING NULL,
  `z_full_code` STRING NULL,
  `z_origin_code` STRING NULL,
  `z_status_code` STRING NULL,
  `z_traceid` STRING NULL,
  `@version` STRING NULL,
  `host` JSON NULL,
  `input` JSON NULL,
  `message` STRING NULL,
  `prospector` JSON NULL,
  `tags` JSON NULL,
  `proxy_add_x_forwarded_for` STRING NULL,
  `proxy_protocol_addr` STRING NULL,
  `x-forward-for` STRING NULL,
  TIME INDEX (`greptime_timestamp`)
)

ENGINE=mito
WITH(
  append_mode = 'true'
)

And when I execute a simple query on last 3h:

SELECT
    to_timestamp_seconds(greptime_timestamp) as time_sec,   
    domain as metric,    
    count(*) RANGE '1m'  FILL LINEAR as value
FROM access_logs where greptime_timestamp between FROM_UNIXTIME(1744195125) AND FROM_UNIXTIME(1744205925)  AND domain = '****'
  ALIGN '1m' by (domain)
  order by greptime_timestamp asc

It tooks greptimedb 10s to respond.

The table contains ~ 378165955 rows and grows at ~30k/s.

The host

# lscpu 
Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                32
On-line CPU(s) list:   0-31
Thread(s) per core:    2
Core(s) per socket:    8
Socket(s):             2
NUMA node(s):          2
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 63
Model name:            Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz
Stepping:              2
CPU MHz:               1598.156
BogoMIPS:              4803.79
Virtualization:        VT-x
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              20480K
NUMA node0 CPU(s):     0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30
NUMA node1 CPU(s):     1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31

# free -g
              total        used        free      shared  buff/cache   available
Mem:            251          24          35           3         191         221
Swap:             0           0           0

I run greptimedb using docker:

docker run --security-opt seccomp=unconfined -p 4000-4003:4000-4003    -v "$(pwd)/data:/tmp/greptimedb"    --name greptime --rm    harbor.ziroom.com/cache/greptime/greptimedb:v0.13.2 standalone start    --http-addr 0.0.0.0:4000    --rpc-bind-addr 0.0.0.0:4001    --mysql-addr 0.0.0.0:4002    --postgres-addr 0.0.0.0:4003

[killme2008]

It would be helpful if you could provide the explain analyze <sql> results. It helps to figure out the bottleneck.

I think you must add an index for domain, maybe the skipping index is proper. WDYT? @waynexia @evenyag

About the data index https://docs.greptime.com/user-guide/manage-data/data-index

[evenyag]

If you are using the latest nightly version, you can use explain analyze verbose <sql> to get more execution metrics about the query.