Kolide Experts, i’ve been facing an issue with fleet mysql the performance is so bad even after increasing the server specs to 64GB Ram and 12 CPU. still facing the same issue where fleet is not able to search for a host/label to run a query against and eventually the process on the db server get killed after eating all the resources. number of hosts joined to fleet are 11K host. and here are some of the queries i was able to get from a DBA.

INSERT INTO hosts (
                        detail_update_time,
                        osquery_host_id,
                        seen_time,
                        node_key
                ) VALUES ('1970-01-02 00:00:00', 'web01', '2020-09-02 13:50:33.053319', 'REDACTEDREDACTED')
                ON DUPLICATE KEY UPDATE
                        node_key = VALUES(node_key),
                        deleted = FALSE;

SELECT DISTINCT dqc.id, q.query
                FROM distributed_query_campaigns dqc
                JOIN distributed_query_campaign_targets dqct
                    ON (dqc.id = dqct.distributed_query_campaign_id)
                LEFT JOIN label_query_executions lqe
                    ON (dqct.type = 0 AND dqct.target_id = lqe.label_id AND lqe.matches)
                LEFT JOIN hosts h
                    ON ((dqct.type = 0 AND lqe.host_id = h.id) OR (dqct.type = 1 AND dqct.target_id = h.id))
                LEFT JOIN distributed_query_executions dqe
                    ON (h.id = dqe.host_id AND dqc.id = dqe.distributed_query_campaign_id)
                JOIN queries q
                    ON (dqc.query_id = q.id)
                WHERE dqe.status IS NULL AND dqc.status = 1 AND h.id = 4862
                        AND NOT q.deleted
                        AND NOT dqc.deleted

SELECT DISTINCT *  
FROM hosts  
WHERE ( id IN  
   (  
   SELECT id  
   FROM hosts  
   WHERE MATCH ( host_name , uuid ) AGAINST ( ? IN BOOLEAN MODE )  
   )  
OR id IN  
   (  
   SELECT host_id  
   FROM network_interfaces  
   WHERE MATCH ( ip_address ) AGAINST ( ? IN BOOLEAN MODE )  
   ) )  
AND NOT deleted LIMIT ?

would you please also share some insights when every query of these is executed by fleet? https://github.com/kolide/fleet/issues/2293

What version of Fleet are you running? In 3.0.0 we made some major improvements that will allow you to easily scale up to 10x that number of hosts.

I’m runing 2.6.0 but definitely i can upgrade and see how it goes.

Yes, please do upgrade. That should solve your problem.