slow sql queries

[swiedernix]

we run a k3s cluster of seventeen nodes (Rocky 9.4 on VMWare) with

1. k3s version v1.29.6+k3s2 (b4b156d)
2. using postgresql as external datastore: postgresql 15.6 on Rocky 9.4 VM (8x cores, 16GB)
3. Rancher v2.9

this cluster is solely used for running an AWX cluster.

we're putting more and more Ansible load on this AWX cluster and then run into problems with
hundreds of daily Slow SQLs messages each on our k3s nodes (average is ~ 1.5 seconds)

our k3s database is not huge, I added some queries from other discussions:

postgres=# \c k3s
You are now connected to database "k3s" as user "postgres".
k3s=# select count(*) from kine;
count

56723
(1 row)

k3s=# select deleted,count(*) from kine group by deleted;
deleted | count
---------+-------
0 | 56759
(1 row)

k3s=# SELECT name, COUNT() FROM kine GROUP BY name HAVING COUNT() > 1000;
name | count
------+-------
(0 rows)

k3s=# SELECT name, COUNT() FROM kine GROUP BY name HAVING COUNT() > 100;
name | count
-----------------------------------------------------------+-------
/registry/leases/kube-system/k3s | 277
/registry/leases/kube-system/kube-scheduler | 277
/registry/leases/kube-system/k3s-cloud-controller-manager | 277
/registry/leases/kube-system/kube-controller-manager | 277
/registry/leases/storeawx-schwarz/awx-operator | 277
/registry/leases/kube-system/cattle-controllers | 277
(6 rows)

shared_buffers = 2048MB
work_mem = 32MB
maintenance_work_mem = 256MB

we configured log_min_duration_statement to two seconds and get ~ 20-30 of these statements each day:

2024-09-06 06:45:04.854 UTC [821516] LOG: duration: 2415.379 ms execute stmtcache_bf583346933bfe9e1edf4d0f0e12d943062d2796716c7256:
SELECT (
SELECT MAX(rkv.id) AS id
FROM kine AS rkv), COUNT(c.theid)
FROM (

                    SELECT *
                    FROM (
                            SELECT (
                    SELECT MAX(rkv.id) AS id
                    FROM kine AS rkv), (
                    SELECT MAX(crkv.prev_revision) AS prev_revision
                    FROM kine AS crkv
                    WHERE crkv.name = 'compact_rev_key'), kv.id AS theid, kv.name AS thename, kv.created, kv.deleted, kv.create_revision, kv.prev_revision, kv.lease, kv.value, kv.old_value
                            FROM kine AS kv
                            JOIN (
                                    SELECT MAX(mkv.id) AS id
                                    FROM kine AS mkv
                                    WHERE
                                            mkv.name LIKE $1
                                            AND mkv.name > $2
                                    GROUP BY mkv.name) AS maxkv
                                    ON maxkv.id = kv.id
                            WHERE
                                    kv.deleted = 0 OR
                                    $3
                    ) AS lkv
                    ORDER BY lkv.thename ASC

                            ) c

is this problem an indicator for slow storage (on our vmware environment)?

or do we have some postgresl configuration options?

[brandond]

Your numbers all seem pretty reasonable. It looks like database compaction is keeping up fine, and the resources with high change counts in the kine DB are just the leases which are renewed every few seconds, so that looks fine.

I'd probably look to see if there's anything else going on around the time you get those warnings?