After a network hiccup we are seeing thousands of these errors:

fleet[7390]: {"component":"http","err":"authentication error: find host","level":"info","path":"/api/v1/osquery/log"

We are running 4.4.0 and we had policies running but removed them as the DB became unresponsive but now we have these errors, what can we do to solve this?

it should reset on its own. Depending on your setup, there might be a lot of threads trying to do things in the database as hosts check in, and the hiccup made everything fail. If you want it to stop, you might need to stop fleet serve for a bit, make sure your db is stable, and then restart it. However, this is just so the logs are clean, you don't really need to do this. As connections resume, if the db is accessible through the same parameters, it'll start working

Thanks for the feedback @Tomas Touceda Still trying to get it stable if it does not work will upgrade to 4.4.2

could you tell me a bit more about your infrastructure? database size, type of deployment, fleet instance count/size, etc. I feel like I already asked you this question, so apologies for the repeat, but it's hard to keep track.

Hey @Tomas Touceda No problem. We have about 10k hosts connected. 3 fleet instances talking to 1 DB, DB is 16cores and 64 GB. Till the hickup everything was working fine, after that all the time 100% CPU usage and unusable DB. Tried different things, also tried your tip regarding

delete from policies

to see if that helped. But nothing, for me it seems there is some issue with the policy data that osquery want to keep sending to the DB.

100% CPU usage in the db, correct? do you have a list of the top queries?

Yes on the DB correct, how is the best way to get that list? Btw on the fleet side I keep getting bombarded with

authentication error: find host

do you have slow log enabled?

slow_query_log

is disabled

enabling that could be good,

SHOW FULL PROCESSLIST;

would list what's running, but there are other performance tools that might be easier depending on your setup. If you have prometheus, seeing ops and times could be useful

I did some changes per your tips, enabled slow query, see if it logs anything and I lowered

max_open_conns

and

max_idle_cons

they were 1000 and 200, now 100 and 20, DB seems to be more stable now, don't know if it will cause issues anywhere else

could you tell me the output of the following query:

SELECT TABLE_ROWS, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'fleet'

?

hm, what does the following query give you:

explain SELECT DISTINCT s.id, scv.cve
		FROM host_software hs
		JOIN hosts h ON (hs.host_id=h.id)
		JOIN software s
		JOIN software_cpe scp ON (s.id=scp.software_id)
		JOIN software_cve scv ON (scp.id=scv.cpe_id)
		WHERE hs.host_id=1

?

Seems as if something ain't write...

probably the host_id, grab a random id from the hosts table, and plug it at the end instead of the 1

is that the

osquery_host_id

from hosts or

uuid

?

an example could be what comes from

select id from hosts limit 1

censored a bit, not sure what i'm looking at 😄

could you tell me the fleet serve config you're using? (note that this is not

fleetctl get config

, but the config you set for fleet to start

size in like CPU?

CPU+RAM+disk space

CPU: 4 cores * 3 servers RAM: 16GB HDD: 60GB

you have a specific instance you set for vulnerability processing, correct?

yes correct

gotcha, let's try disabling vulnerability processing in that instance, restart it, and see if that calms the db down

with disabling you mean changing the

current_instance_checks:

setting in that one server?

yes

disabled the setting

to help confirm that this is the case, could you run the following:

SELECT DISTINCT s.id, scv.cve
		FROM host_software hs
		JOIN hosts h ON (hs.host_id=h.id)
		JOIN software s
		JOIN software_cpe scp ON (s.id=scp.software_id)
		JOIN software_cve scv ON (scp.id=scv.cpe_id)
		WHERE hs.host_id=<the id you used before>

and then compare the speed with the following:

SELECT DISTINCT s.id, scv.cve
		FROM host_software hs
		JOIN hosts h ON (hs.host_id=h.id)
		JOIN software s ON  (s.id=hs.software_id)
		JOIN software_cpe scp ON (s.id=scp.software_id)
		JOIN software_cve scv ON (scp.id=scv.cpe_id)
		WHERE hs.host_id=1

At the moment the lower one is giving me an

Empty set

based on the id and the other one is hanging, waiting on the return at the moment

oh, right, please change that 1 for the same id you used in the other

Yes already did that, but still empty, the first query took: 2727 rows in set (1 min 59.21 sec)

how long did it take?

But the second query did not give me an results and finished immediately, so I don't think that is correct either right?

do you see any vulnerabilities reported in the host details page for that host?

Indeed no vulnerability data 😮

yup, so this is the issue for sure

So the other query was just returning all the vulnerabilities and not selecting based on id?

correct

Understandable than that the DB is dying on me with 10k+ hosts 😮

running on all of them

That is why all my improvements had no effect, I understand it now

will do!

Hey @Tomas Touceda I can confirm that identified query is the issue, I disabled the vulnerability check for now, waiting on the patch release and all systems are working fine again, no errors and no load issues anymore.

ETA is today, so unless we have see anything surprising, it'll land in a few hours

I'm still seeing this issue on kubernetes or something similar: component=http path=/api/v1/osquery/distributed/write err="authentication error: find host .... context canceled" Fleet-Webserver docker.io/fleetdm/fleet:v4.6.1, Fleet-database-mysql docker.io/mysql:8.0.27 and fleet-cache-redis docker.io/bitnami/redis:6.2.6-debian-10-r53. There are no k8s/pod limits imposed, so resources are not an issue. Cluster is pretty beefy.

@Flngen Flugen did you resolve this issue ?