high load on rpi3

[davisfeng70]

Anyone see high load from Raspberry Pi 3?

I'm using docker-compose, and I gave database /dev/shm 256m to be able to run vacuum

top - 13:51:03 up 48 days, 13:59,  2 users,  load average: 4.27, 4.02, 4.01
Tasks: 167 total,   1 running, 166 sleeping,   0 stopped,   0 zombie
%Cpu(s): 10.2 us,  2.9 sy,  0.0 ni, 25.0 id, 61.5 wa,  0.0 hi,  0.3 si,  0.0 st
MiB Mem :    909.8 total,     73.3 free,    267.9 used,    568.6 buff/cache
MiB Swap:    100.0 total,      0.0 free,    100.0 used.    442.4 avail Mem 

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND 
3297462 999       20   0  312780 152772 143736 D  13.2  16.4   0:05.76 postgres
3297463 999       20   0  312804 149648 140596 D  12.6  16.1   0:05.77 postgres
3295864 999       20   0  314048 155172 145348 D  12.3  16.7   0:06.11 postgres
     72 root      20   0       0      0      0 S   2.0   0.0   1160:06 kswapd0 
3294284 root       0 -20       0      0      0 I   1.3   0.0   0:05.00 kworker+
 757987 10000     20   0 2622804  77976   2500 S   1.0   8.4 192:45.04 beam.smp
3190541 root       0 -20       0      0      0 I   1.0   0.0   3:09.40 kworker+

[davisfeng70]

I resolved this by doing a new docker-compose install of teslamate with latest versions, and imported the latest db dump, and now no high load.

the new install has everything vanilla, no changes at all.

FYI, my db is now 1.0GB in size, 2 cars for 1.5 years

[davisfeng70]

It started happening again.

Traced it to a query
SELECT s0."drive_id" FROM (SELECT sp0."drive_id" AS "drive_id", count(*) FILTER (WHERE NOT (sp0."odometer" IS NULL) AND (sp0."ideal_battery_range_km" IS NULL)) AS "streamed_count" FROM "positions" AS sp0 WHERE (NOT (sp0."drive_id" IS NULL)) GROUP BY sp0."drive_id") AS s0 WHERE (s0."streamed_count" = 0)

which after several minutes returns 802 rows. The positions table has 11,983,435 rows.

Any recommendations? again this is on a Raspberry Pi.

[DrMichael]

That is strange.
time docker-compose exec database psql -P pager=off -U teslamate -d teslamate -c "SELECT s0."drive_id" FROM (SELECT sp0."drive_id" AS "drive_id", count(*) FILTER (WHERE NOT (sp0."odometer" IS NULL) AND (sp0."ideal_battery_range_km" IS NULL)) AS "streamed_count" FROM "positions" AS sp0 WHERE (NOT (sp0."drive_id" IS NULL)) GROUP BY sp0."drive_id") AS s0 WHERE (s0."streamed_count" = 0);"
gives me for 499 rows on RPI3
2.715u 0.439s 0:19.40 16.1% 0+0k 73104+0io 325pf+0w,
on an Ubuntu
0.269u 0.084s 0:00.96 35.4% 0+0k 0+0io 0pf+0w.
Rows in positions 3.322.513.

So, not at all several minutes (although the differences are significant... much I/O on the RPI3)

[davisfeng70]

Here are my results

time docker compose exec database psql -P pager=off -U teslamate -d teslamate -c "SELECT s0."drive_id" FROM (SELECT sp0."drive_id" AS "drive_id", count(*) FILTER (WHERE NOT (sp0."odometer" IS NULL) AND (sp0."ideal_battery_range_km" IS NULL)) AS "streamed_count" FROM "positions" AS sp0 WHERE (NOT (sp0."drive_id" IS NULL)) GROUP BY sp0."drive_id") AS s0 WHERE (s0."streamed_count" = 0);"

real 1m53.870s user 0m0.490s sys 0m0.630s

I also did an explain plan
time docker compose exec database psql -P pager=off -U teslamate -d teslamate -c "explain SELECT s0."drive_id" FROM (SELECT sp0."drive_id" AS "drive_id", count(*) FILTER (WHERE NOT (sp0."odometer" IS NULL) AND (sp0."ideal_battery_range_km" IS NULL)) AS "streamed_count" FROM "positions" AS sp0 WHERE (NOT (sp0."drive_id" IS NULL)) GROUP BY sp0."drive_id") AS s0 WHERE (s0."streamed_count" = 0);"
with these results

Subquery Scan on s0 (cost=228811.88..230284.33 rows=29 width=4)
-> Finalize GroupAggregate (cost=228811.88..230284.04 rows=29 width=12)
Group Key: sp0.drive_id
Filter: (count(*) FILTER (WHERE ((sp0.odometer IS NOT NULL) AND (sp0.ideal_battery_range_km IS NULL))) = 0)
-> Gather Merge (cost=228811.88..230154.58 rows=11508 width=12)
Workers Planned: 2
-> Sort (cost=227811.86..227826.24 rows=5754 width=12)
Sort Key: sp0.drive_id
-> Partial HashAggregate (cost=227394.97..227452.51 rows=5754 width=12)
Group Key: sp0.drive_id
-> Parallel Seq Scan on positions sp0 (cost=0.00..202803.57 rows=4918281 width=18)
Filter: (drive_id IS NOT NULL)
JIT:
Functions: 11
Options: Inlining false, Optimization false, Expressions true, Deforming true

[vincep5]

and for me, this query is very slow as well on a Rpi 3B (the positions table has 13,208,674 rows)
real 1m13.950s
user 0m0.354s
sys 0m0.152s

I changed the docker DATABASE_TIMEOUT env variable setting to 120000 and my CPU problems got resolved