• a

    Ahmed

    1 year ago
    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
  • zwass

    zwass

    1 year ago
    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.
  • a

    Ahmed

    1 year ago
    I’m runing 2.6.0 but definitely i can upgrade and see how it goes.
  • zwass

    zwass

    1 year ago
    Yes, please do upgrade. That should solve your problem.